How to Prevent ColdFusion SQL Injection Attacks

Unfortunately, SQL Injection attacks have been on the increase in recent months. We hope that this SQL Injection Defense tutorial will help you to upgrade your ColdFusion code to defend against these destructive and costly attacks.

What is an SQL Injection Attack?
An SQL Injection Attack is the act of embedding partial SQL queries inside of input that is sent to a WHERE clause in an existing SQL query. Usually it involves someone or more often an automated program trying to add, delete or change data in an online database by making changes to the query string that is passed in an URL or FORM.

Here's an example of a dynamic ColdFusion query which is prone to attack:
SELECT *
FROM CONTENT
WHERE ContentID=#URL.ContentID#
In this query, A WHERE clause is being populated dynamically using a URL parameter. This type of code is common and popular, and is often used in data drill-down interfaces.

If the URL was:
http://domain/path/file.cfm?ContentID=100

then the resulting SQL statement would be:
SELECT *
FROM CONTENT
WHERE ContentID=100

A hacker can tamper with this query to do something like this:
http://domain/path/file.cfm?ContentID=100;DELETE+CONTENT

Now the resulting SQL would be:
SELECT *
FROM CONTENT
WHERE ContentID=100;
DELETE CONTENT
Depending on the DBMS being used, you could end up executing two statements; first the SELECT, and then DELETE CONTENT (which would promptly delete all data from the CONTENT table).

How to remove the vulnerabillity:
This line of code will lock SQL injection attacks out of number fields:
<cfparam name="URL.ContentID" type="integer">

This is because SQL injection (within ColdFusion apps) is a greater risk in non-textual fields. Numbers are not enclosed within quotes, so extraneous text can easily be tampered with to create an additional SQL statement..

If you want more control, you could use code like this:
<cfif IsDefined("URL.ContentID")
and not IsNumeric(URL.ContentID)>
... throw an error or something ...
</cfif>

As an additional line of defense you can use:<cfqueryparam>, as seen here:
<cfquery ...>
SELECT *
FROM CONTENT
WHERE ContentID=<cfqueryparam value="#URL.ContentID#" cfsqltype="CF_SQL_INTEGER">
</cfquery>

The CFSQLTYPE (aside from binding variables) performs data type validation checks, and values that do not match the type are rejected.

If the previous tampered URL was passed to this query, the value would be rejected and an error would be thrown. Now only integers allowed, and malicious tampered URL parameters are not integers.

ColdFusion makes it possible to protect yourself against SQL Injection attacks, but it's up to you to determine whether your code is at risk!

How to upgrade your ColdFusion code to protect your numeric fields:
Start your IDE, search for every instance of <cfquery> in your code. Next, search within these results to find any queries that contain numbers in them (that are not enclosed in quotes or passed to <cfqueryparam>), and make a list of the variables used. If any of them are URL parameters or FORM fields, create a <cfparam> for each (at the top of the page, or before the <cfquery>).

Good luck!
Surfwriter, Inc.