how to avoid sql injection

> I have tested this with Oracle, MS Sql, and redbrick databases. The only time
> I could get sql to function was with MS Sql numeric datatypes. Character
> fields would treat everything as text. Oracle and redbrick threw errors with
> numeric fields.

It's a problem on MySQL, as detailed here:
http://www.coldfusionmuse.com/index.cfm/2008/2/22/sql-injection-on-a-character-field.
I didn't know about that until just now... your comments piqued my
interest, Dan.

There's also more to SQL injection than "making unexpected SQL statements
to run". SQL injection can be used to effect buffer overflows too,
apparently. That said, these "opportunities" are limted.

It *is* far less easy to exploit SQL injection with text field values
though, you're right.

--
Adam

Dan Bracuk wrote:
> I have also tested javascript injection. Let's just say that anyone who
> thinks cfqueryparam makes your app safe from this should test it themselves.
>

That would be good, since if one thinks this, one has a misunderstanding
of the purpose of <cfqueryparam...>. JavaScript injection is an
entirely different beast and has nothing to do with SQL.
<cfqueryparam...> is only for SQL bind parameters.

I have tested this with Oracle, MS Sql, and redbrick databases. The only time I could get sql to function was with MS Sql numeric datatypes. Character fields would treat everything as text. Oracle and redbrick threw errors with numeric fields.

I have also tested javascript injection. Let's just say that anyone who thinks cfqueryparam makes your app safe from this should test it themselves.

bill0 wrote:
> If a query is in a stored procedure, would that make sql injection harmless?

Maybe, maybe not. Depends on what one does with passed in parameters in
the SQL code inside the stored procedure. But generally yes.

>
> Also I have been told cfqueryparam is helpful to prevent sql injection and I
> think I see how that would work by restricting certain fields to a specific
> types like integer, but what about string or memo fields? How are those
> sufficiently restricted?
>


Because they are 'bind' parameters. Which basically means they are
passed to the database separately from the SQL code and the database
knows they are variables and will never even look at them for sql code.

Here is an easy way to see this:

<cfquery name="foobar" dataSource="DSN" result="dbStuff">
SELECT aField
FROM aTable
WHERE aField = <cfqueryparam value="aValue" cfsqltype="cf_sql_varchar">
</cfquery>

<cfdump var="#dbStuff#">

Pay attention to the sql parameter and how it is seperated from the SQL
string. Then try the example again without the <cfqueryparam...> tag
and note how all the database gets is a SQL string and it must consider
everything it gets as desired code to be processed.