Just when you felt safe... SQL Injection and MySQL

Zac Spitzer recently blogged about an article explaining how to hack ColdFusion. Overall the "exposé" was mostly meaningless drivel not having anything much to do specifically with ColdFusion itself. It was accompanied by an array of Code Samples that look like they were written by a third grader. One point the article made though caught my eye. It claimed MySQL would let you inject SQL into a cfquery not using cfqueryparam even if the variable was enclosed in single ticks. "Could it be?", I scoffed. Oh yes, yes it is true.You should all know that ColdFusion automatically doubles up all single tick marks inside of variables being output within a cfquery. That means that generally it is impossible to inject SQL into a variable which is a varchar or something else that will have ticks around it. Consider the following example:

[code]<cfquery datasource="dns_name" name="qry_get_stuff">
SELECT *
FROM table
WHERE column = '#url.foo#'
</cfquery>
[/code]

Granted, without the bound parameter you won't reap the benefit of a reusable cached execution plan, but I had always regarded the previous code as immune to SQL Injection attacks. This is what would happen if you tried to "break out" of the single ticks:

[code]<cfset url.foo = " ' or 1=1 --">
[/code]

If would render the following SQL statement:

[code]SELECT *
FROM table
WHERE column = ' '' or 1=1 --'[/code]

The single tick was escaped and therefore rendered useless. However the article points out that MySQL let's you escape single ticks in by preceding them with a back slash. Let's try this:

[code]<cfset url.foo = " \' or 1=1 --">
[/code]

That produces this:

[code]SELECT *
FROM table
WHERE column = ' \'' or 1=1 --'[/code]

MySQL ignores the first of our injected ticks and uses the second one to end our quotes. The remainder of our string is now free to roam about our database! This is the equivilant code that gets executed (ignoring escaped ticks and comments):

[code]SELECT *
FROM table
WHERE column = ' ' or 1=1[/code]

Wow-who would have thunk? I tried this with CF8 on Windows with MySQL 5.0. So what do we learn from this kids?

1. ALWAYS use cfqueryparams. They box-in your inputs to your query as a bound parameter that cannot be escaped. Not only that, but they will help most DBMS's cache a reusable execution plan which will usually improve performance.
2. ALWAYS make sure your data source authenticates with the least permissions necessary. A data source connecting with the sa account is just begging for your database to get dropped.
3. I didn't really talk about this but NEVER use preservesinglequotes() unless there is no other way or a gun is to your head. If you must, don't you dare put any variables into that thing without sanitizing them first.
4. Form, url, and cookie scopes can never be trusted. (They aren't the only ones though)
5. It's a jungle out there-- be vigilant.