ColdFusion Muse

Disabling Backslash Escaping in MySQL

For muse readers who read my previous post on SQL injection examples that use character rather than numeric fields, I offer this tip I picked up on CF-Talk from Azadi Saryev. It appears you can disable the ability to escape special characters using the backslash. Here is the exact note from Azadi.

you can run MySQL in NO_BACKSLASH_ESCAPES mode. see chapter 5.2.6 in MySQL ref manual. This SQL mode also can be enabled automatically when the server starts by using the command-line option --sql-mode=NO_BACKSLASH_ESCAPES or by setting sql-mode=NO_BACKSLASH_ESCAPES in the server option file (for example, my.cnf or my.ini, depending on your system).

there appears to be no jdbc connector option to change this behaviour, so have to do in server config/start...

Thanks Azadi, for a great tip! Readers with more MySQL experience than I can let me know about the nuances of this approach. While this solves the issue of the SQL injection using single quotes escaped with a backslash, I suspect that it may cause other problems. Are there other types of characters that would be precluded or need to be escaped in some way? Either way, it's nice to have another arrow in the quiver.

Related Blog Entries

Comments
JC's Gravatar probably breaks lots of open source software that expects that style of escaping. But I suppose if you're running CF that's less of an issue, you probably aren't also running PHP and likely to install PHPBB/Wordpress/Gallery/etc.

So far as I can tell, for MS SQL, simply quoting everything, including numeric values, works to block malicious entries.

Coldfusion sees it as a string and escapes any quotes; SQL receives it, looks to see if it's an int, continues as normal if it is, rejects it if it's not (or if the field can contain normal characters, it will simply store the whole string as text without executing it. You do take a small performance hit from that though.

Obviously, using a cfqueryparam helps too and gives you a performance boost instead of reduction. And cfset myInt = int(myInt) can kick it out before it even gets to SQL.

The single quote escape issue may be the first time I've ever seen a good reason to praise MSSQL over its competitors... now if only they'd give us order by rand() and limit 20,30...
# Posted By JC | 5/16/08 9:51 AM
Azadi Saryev's Gravatar Hey Mark,

Thanks for the tip of the hat and I am glad I could be of help! Love your blog, btw.

My tip of the hat goes entirely to MySQL reference manual - most of my post on HoF was a verbatim copy from there. You can download it in PDF, HTML or CHM formats from mysql website. It is a true treasure trove of information on everything MySQL.

The only consequence of using NO_BACKSLASH_ESCAPES slq mode that I know of, is that when using string comparison with LIKE operator in queries, and seraching for a literal MySQL wildcard character (% or _) you must use and specify a different escape sequence in the format: expr LIKE pat [ESCAPE 'escape_char'].
For example: SELECT 'ColdFusion' LIKE 'ColdF|%' ESCAPE '|'

I may also add that NO_BACKSLASH_ESCAPES is available starting from MySQL 5.0.1

See you on the CF-Talk list!
# Posted By Azadi Saryev | 5/16/08 10:42 PM
njoroge's Gravatar Yah!
This works more than fine thanks guys God bless
# Posted By njoroge | 4/9/09 4:59 AM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.