The Risk

Would you give a random stranger full access to your database? You most certainly would not, however this is exactally what a SQL Injection vulnerability allows. The attacker may have access to read, update, insert, delete any of the data in your database. In some cases attackers may also be able to escalate permission to take full control of the database server itself.

A Vulnerable Example

Let's assume we are writing some code to display a news article, news.cfm by passing in a ?id=123. Our query to fetch the news story might look like this:

<cfquery name="news">
    SELECT id, title, story
    FROM news
    WHERE id = #url.id#
</cfquery>

If we request news.cfm?id=123 then the SQL Statement send to the database server might look like this:

SELECT id, title, story
FROM news
WHERE id = 123

How is it exploited?

While it seems obvious that url.id should be an integer, it is never validated as such in our code. So an attacker can take advantage of this by passing something malicious into the id value. For example if an attacker passes ?id=123;DELETE+FROM+news then the actual SQL passed to the database server might be passed as:

SELECT id, title, story
FROM news
WHERE id = 123;DELETE FROM news

Because of the ; in the above query it actually runs two different SQL Statements, the first on selects a story from the news table, and the second one deletes all of the stories in the news table!

Not all databases will allow multiple SQL statements from a single cfquery tag, however there are other ways that an attacker could exploit this vulnerability in our code. For example, a UNION statement could allow the attacker to select data from another table, such as:

SELECT id, title, story
FROM news
WHERE id = 0 
UNION 
SELECT id, top, secret 
FROM superSecretStuff

How can we prevent it?

SQL Injection can occurr whenever you make database calls with untrusted variables in the SQL. There are a few different ways you can send SQL Statements in CFML. Each method has a way to prevent SQL Injection that involves parameritization.

Preventing SQL Injection in CFQuery Tags

When using cfquery the best way to prevent SQL Injection is to use the cfqueryparam tag. For example, we can rewrite the vulnerable example as:

<cfquery name="news">
    SELECT id, title, story
    FROM news
    WHERE id = <cfqueryparam value="#url.id#" cfsqltype="integer">
</cfquery>

Tip: As of CF11 you no longer need to pass the cf_sql_ prefix into the cfsqltype attribute. So instead of cf_sql_integer you can simply pass integer.

Working with lists of values

At times you may have a list of values that you are using as part of a SQL IN statement, for example if we want to pull the news stories where the category is tech or sports we could specify

SELECT id, title, story 
FROM news
WHERE category IN ('tech', 'sports')

Now lets assume our category list is in a variable cats="tech,sports". We can utilize the list="true" option of cfqueryparam to automatically pass each list value as a parameter.

<cfquery name="news">
    SELECT id, title, story
    FROM news
    WHERE category IN (
         <cfqueryparam value="#cats#" list="true" cfsqltype="varchar">
    )
</cfquery>

QueryExecute

If you are using the queryExecute() function you need to make sure that there are no unsafe variables in the SQL statement passed into the first argument of the function. Here is a vulnerable example:

queryExecute("SELECT * FROM news WHERE id = #url.id#");

And here is how we can fix it using parameters in the second argument:

queryExecute("SELECT * FROM news WHERE id = :id", { id={value=url.id, cfsqltype="integer"} });

Note that we are just passing a struct with keys that match the names of cfqueryparam arguments, you can use list, maxlength, etc.

Understanding Parameterization

The way cfqueryparam works to protect you from SQL Injection actually has nothing to do with data type validation, but rather how it seperates the variable as a parameter, or an input into the SQL statement.

When you have a query such as:

<cfquery name="news">
    SELECT id, title, story
    FROM news
    WHERE id = <cfqueryparam value="#url.id#" cfsqltype="integer">
</cfquery>

ColdFusion will pass this to the database as the following SQL Statement:

SELECT id, title, story
FROM news
WHERE id = ?

Then it will pass the parameters seperatly from the SQL. This two step process seperates the variable from the SQL statement making it impossible for SQL Injection to execute in a query such as above.

You'll also get an added bonus of performance on many database servers. The parameterization allows the database engine to cache the query execution plan with the parameter placeholders, knowing that different values will be plugged in. Without parameters each query with each possible value is added to the cache independantly and the cache hit ratio will be much lower.

A SQL Injection Prevention Checklist

• Check each and every cfquery tag for unprotected variables
• Check each queryExecute() function call
• Check each ormExecuteQuery() call
• Check for new Query() calls (this was a cfscript syntax popular in CF9/CF10 before queryExecute was released)