Disabling MySQL's Backslash Escaping Per Data Source

Disabling MySQL's Backslash Escaping Per Data Source

Posted by Brad Wood
Aug 17, 2008 08:05:00 UTC
It has been mentioned in several places that MySQL 5.0.1 has a NO_BACKSLASH_ESCAPES mode it can be run in to prevent backslashes from being an escape character. Thanks to Azadi Saryev for pointing it out on my blog and Mark Krugers as well. Jake Munson even posted instructions for applying the setting to your SQL server at startup. For the record, you can also use this setting on a specific data source.While editing your JDBC data source, Add "sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES" (without the quotes) in to the Connection String box in the Advanced Settings of your datasource. Remember, mulitple connection string properties are delimited by an ampersand with MySQL, so if you allowed multiple queries AND disabled backslash escaping, your connection string would be "allowMultiQueries=true&sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES" If you aren't sure whether or not your MySQL server is allowing a backslash to be used as an escape character, run the following code:
[code]<cfset foo = "te\'-- st">
<cfquery name="qry_escape_me" datasource="your_dsn">
	select '#foo#'
</cfquery>
<cfdump var="#qry_escape_me#">[/code]
If \ is being treated as an escape character, the output of the query will be
[code]te'[/code]
If \ is NOT being treated as an escape character, the output of the query will be
[code]te\'-- st[/code]
The latter behavior is desirable in my opinion as it stops SQL Injection attacks against characters fields with MySQL if cfqueryparam was not used. Let it be known though that this setting can also break any software that purposely uses a backslash as an escape character.

 


Brad Wood

Quick note, Azadi pointed out on the talk list that the extra setting can also be set directly into the JDBC URL. Thanks!

jdbc:mysql://localhost:3306/databasename?useUnicode=true&characterEncoding=utf8&characterSetResults=UTF-8&sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES

Site Updates

Entry Comments

Entries Search