SQL Injection Consideration
Here is something I discovered the other day that surprised me a bit. ColdFusion data sources have an advanced option named "Allowed SQL" which, according to the documentation, defines "the SQL operations that can interact with the current data source." I know some shops use this setting to help protect against SQL injection attacks. For example they may limit a data source to only allow SELECT and Stored Procedures. While you may think that this would go a long way toward protecting the data source against SQL injection, this may not be the case. If the database credentials used for the data source have additional permissions these statements may be executed via SQL injection. For example, consider the following setup:
- A SQL Server database: TESTDB
- A table TESTDB.TestTable
- A database user test_user with SELECT, INSERT, UPDATE, and DELETE permissions to TestTable
- A ColdFusion data source "test" connected to TESTDB using the test_user credentials and the Allowed SQL option set to only SELECT and Stored Procedures.
Now, consider the following query.
SELECT * FROM TestTable WHERE TestID = #url.testID#
</cfquery>
You may think the above query would be protected against SQL injection because the data source limits the Allowed SQL to SELECT and Stored Procedures, but you would be wrong. Given this setup, the above this code is actually susceptible to SQL injection. Assuming this query were in a template index.cfm, you could easily delete all records in TestTable by issuing a request like index.cfm?testID=1%20DELETE%20FROM%20TestTable.
Now there are a number of ways you could protect against this attack, one of the easiest being the cfqueryparam tag, another being to limit the database user's permissions, but that isn't the point of this post. The point is you can't rely on the Allowed SQL advanced option to protect against SQL injection. You have been warned. (Note: I have only tested this against MS SQL Server so it may not apply to all database engines.)
It seems that the "Allowed SQL" options only seem to come into play under certain circumstances eg, with DELETE unchecked:
* SELECT * FROM blah = query runs
* DELETE FROM blah = CF throws error
* SELECT * FROM blah; DELETE FROM blah = CF throws an error
* SELECT * FROM blah DELETE FROM blah = both queries run
Those last two surpised me a little. Another thing that raised some concern is the use of "allowMultiQueries" in the connection string. At least on CF8 using the default MSSQL Server drivers, it appears that this option also has no effect (contrary to numerous postings and discussions I've had with developers over the years) - this may be more of a problem with my understanding, so will need to dig a bit further...
Thankfully, we're pretty strict on our use of cfqueryparam and datatypes, but still - we'll need to review what security measures we have in place and what else we can do in light of this...
* "- ie, if DELETE is unchecked, and you have "DELETE FROM blah", then CF with throw an error, but if you have "SELECT
Whatever the case, as you mentioned, CFQUERYPARAM and database user permissions are certainly 2 layers of security that should be used, regardless of any other measures you might take.
Most of the injection techniques I've seen use multi-line commands, so those Admin settings are effectively useless. Thanks for the heads up, Nathan. I hope all CF'ers get to read this blog.
* CFs built in drivers, jTDS and Microsofts JDBC drivers - none of these appear to support allowMultiQueries=false for MS SQL Server (makes me wish we were back on MySQL :P) Surely this is mentioned in some documentation somewhere, but I guess thats what I get for not testing it until now :)
* For the "Allowed SQL options" - the only time these take affect for MS SQL Server is when the unselected commends (eg DELETE) are either the only query statement, or (if there are multi statements) if the statements are separated by a semi-colon - if the statements are just separated by a space, then the DELETE command will still run.
Yeah, bit of a bugger that :) At least I've learnt something new today, and it just drives home the importance of relying on multiple levels of protection.