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

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

Posted by Brad Wood
Jul 14, 2008 02:07:00 UTC
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">
FROM table
WHERE column = '#url.foo#'
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 --">
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 --">
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.


Azadi Saryev

MySQL, starting with ver 5.0.1, has NO_BACKSLASH_ESCAPES mode. More here: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html and here: http://www.coldfusionmuse.com/index.cfm/2008/5/16/disable-backslash-escape-on-mysql

Seth Feldkamp

Thanks for the very clear write up Brad! I had heard about this recently, but you've really clarified how that can occur and how to prevent it.

Brad Wood

@Azadi: Thanks for that info. That's really good to know. I'm going to update MySQL today to apply that setting. In the mean time I'm considering filing an enhancement request with Adobe for cfquery NOT to escape any single ticks which are preceeded by a backslash when MySQL is the datasource. Of course, if MySQL can turn that "feature" off, CF would need to be able to tell. Hmmm... sounds tricky.

No wait, I'm going to file an enhnacement request that cfquery will simply throw an error if cfqueryparam isn't used. :)

Lu Sancea

You are right my friend. The single most important best practice when it comes to db interactions is ALWAYS use cfqueryparam. I read the same article and all the hacks on his list were easily thwarted with use of cfqueryparam.

PS: Never trust any type of input, always clean your variables.

Jochem van Dieten

cfqueryparam is really the only solution. Not escaping single quotes when they are preceded by a backslash is not going to help too much because in a LIKE statement you can define your own escape character: http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html

The enhancement request to throw an error when a variable is used in an SQL string outside a cfqueryparam tag has been ignored my Allaire-Macromedia-Adobe for years so don't hold your breath.


>>update MySQL today to apply that setting

I never know under which section heading to put things in the mysql file. Do you know where this one goes?

Site Updates

Entry Comments

Entries Search