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.

<cfquery name="getItems" datasource="test">
SELECT * FROM TestTable WHERE TestID = #url.testID#

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.)

Dave G's Gravatar To be honest, when I first read your posting I though 'What? That can't be right, maybe this guy is getting his DSN connections cached or something and they are using the old settings still'.... but after a bit of testing myself, it seems you are on the money...

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
# Posted By Dave G | 11/5/09 8:28 PM
Dave G's Gravatar Just to clarify my understanding (and I realise you noted this at the end of your post) - this may not be specifically a CF issue also - I do recall working with MySQL in the past, and the "allowMultiQueries" connection string setting certainly did have the desired effect, which could mean this is a result of how the default MSSQL jdbc drivers used in CF work, or how SQL Server itself handles these options. If this is the case, then perhaps the "Allowed SQL" options are also handled differently depending on driver or database used...

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.
# Posted By Dave G | 11/5/09 8:38 PM
Gary F's Gravatar Wow, I'm surprised. Those CF Admin settings give a sense of security if you disable delete, drop, etc. I do use cfqueryparam all the time, but it's like we have 1 less line of defence than we thought we had.

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.
# Posted By Gary F | 11/5/09 9:12 PM
Dave G's Gravatar Ok, did a bit more playing around:

* 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.
# Posted By Dave G | 11/5/09 9:18 PM
Mark W. Breneman's Gravatar That is good to know. I have never played with the "Allowed SQL" options in the DSN setting. I have always preferred to lock down the SQL user account to only allow necessary permissions per user. I have considered setting up two dsns per site. One for read only (SQL select) front end website use and a second with read, write and delete permissions for the admin folder.
# Posted By Mark W. Breneman | 11/6/09 10:19 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.