Combating SQL Injection

Using CFMX7:

In trying to block out SQL Injection we are implementing ‘<cfquery params’ on all related query statements for our application, however this is an undertaking for several queries that need to be validated with params.

In the meantime, we have been exploring methods which include the ODBC statement lockouts in CF Admin for the database connection.

What we have found is the following; setting the Allowed SQL of ‘DROP’ to FALSE, will catch an injection of DROP TABLE only if that statement is in the actual body of the query, alone.

This is trapped as exception:

<cfquery name="tryDrop" datasource="mydatasource">
DROP TABLE mytest
</cfquery>

However, the following is NOT caught:

<cfquery name="tryDrop" datasource="mydatasource">
UPDATE mytest SET sortorder = 5; DROP TABLE mytest
</cfquery>

In this case, the ODBC still allows the DROP statement to be executed.

We have also tested this case as shown above as well as a SQL INJECT item using a variable for an INT field (below), which also is allowed.

<cfset myString = “1; DROP TABLE mytest;”> <!--- simulate a form variable, INT field --->
<cfquery name="tryDrop" datasource="mydatasource">
UPDATE mytest SET sortorder = #myString#
</cfquery>

Is there a patch or fix that will correct the ODBC level to prevent this case?

Note: after the setting the SQL Command DROP to false, we tried restarting CF Service and also tried suspending all ODBC connections for that datasource and neither solved the problem.

Any information would be appreciated.