Problem
A SQL Injection attack by a malicious hacker can wreak havoc on a database and expose sensitive information. A simple statement such as SELECT * FROM tbl_user WHERE userId = #URL.userId# could easily be changed to SELECT * FROM tbl_user WHERE userId = 1;DROP TABLE tbl_user. That equals a failure of Titanic proportion, second only to the Patriots Super Bowl loss to the Giants!
Solution
ColdFusion offers a couple very simple and easy solutions to prevent this from happening. The first is setting the Allowed SQL on the datasource and using the <cfqueryparam> tag in all of your queries!
Detailed explanation
Setting the Allowed SQL on a Datasource
When a new datasource is setup by default, ColdFusion sets the Allowed SQL to SELECT, INSERT, UPDATE, DELETE, Create, DROP, ALTER, GRANT, REVOKE and Stored Procedures. When setting up a datasource the Allowed SQL should only be what is needed by the application. If the application doesn't use DROP or GRANT then they should be turned off.
The Allowed SQL is accessed from the Advanced Settings screen in the datasource settings.
Using the <cfqueryparam> Tag
The <cfqueryparam> tag has multiple uses, it verfies the data type of the parameter passed in and for databases that use bind variables it enables ColdFusion to use bind variables which increases performance. By verifying the data type, it also helps prevent SQL injection attacks. Adobe's security bulletin on the issue is here: http://www.adobe.com/devnet/security/security_zone/asb99-04.html
To use <cfqueryparam> simply add it to you query!
<cfquery datasource="#request.dsn#" name="qGetUserByUserId"> SELECT * FROM tbl_user WHERE userId = <cfqueryparam value="#URL.userId#" cfsqltype="cf_sql_integer" /> </cfquery>
Now, if the value in the URL variable is not an integer as it should be, ColdFusion will throw an error. This is a quick and easy fix provided by ColdFusion and should be in all queries!
+
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License. Permissions beyond the scope of this license, pertaining to the examples of code included within this work are available at Adobe.
