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