House of Fusion
Search over 2,500 ColdFusion resources here
  
Home of the ColdFusion Community

Mailing Lists
Home /  Groups /  ColdFusion Talk (CF-Talk)

Dealing with MySQL escape sequences

  << Previous Post |  RSS |  Sort Oldest First |  Sort Latest First |  Subscribe to this Group Next >> 
Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Jim McAtee
05/15/2008 03:59 PM

I ran into a problem inserting records into a MySQL 5.0 database when there's a backslash as the final character of a string value.  I never noticed this until I began working with some randomly generated character strings. INSERT INTO users (   name,   email,   username,   salt,   password   ) VALUES (    'Cindy Smith',   'cindy@smith.net',   'cindysmith',   'd}\',   'c9a96937d248c0f192ff217a72935cdd'   ) The workaround is to either enapsulate the value in a <cfqueryparam> tag or to escape the backslashes with \\.  But I also realize that there are any number of places where this could crop up in existing code.  Is this a bug in CF8's handling of these strings for MySQL?  I would have thought it would escape them transparently. Is there a better workaround?

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
James Holmes
05/15/2008 09:55 PM

Encapsulate the value in a cfqueryparam tag, which you should always be doing anyway. ----- Excess quoted text cut - see Original Post for more ----- -- mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Azadi Saryev
05/15/2008 10:03 PM

you can run MySQL in  NO_BACKSLASH_ESCAPES mode: see chapter 5.2.6 in MySQL ref manual there's more in the Appedixes: This SQL mode also can be enabled automatically when the server starts by using the command-line option --sql-mode=NO_BACKSLASH_ESCAPESor 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... hth Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Jim McAtee wrote: ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Mark Kruger
05/16/2008 08:29 AM

Azadi, Excellent tip - and another item to add to the arsenal against sql injection :) Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com you can run MySQL in  NO_BACKSLASH_ESCAPES mode: see chapter 5.2.6 in MySQL ref manual there's more in the Appedixes: This SQL mode also can be enabled automatically when the server starts by using the command-line option --sql-mode=NO_BACKSLASH_ESCAPESor 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... hth Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Jim McAtee wrote: ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Brad Wood
08/17/2008 01:34 AM

>there appears to be no jdbc connector option to change this behaviour, >so have to do in server config/start... > Sorry to drag up an old thread, but I wanted a record of this for anyone reading the archives.  There IS a JDBC URL option to disable backslash escaping in MySQL on a datasource level.  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" ~Brad

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Azadi Saryev
08/17/2008 04:47 AM

you are so right! this is most excellent! thanks for pointing this out here and on your blog! in case of using a custom mysql jdbc connector instead of cf's built-in one (i.e. on CFMX7 connecting to mysql 5), you can also add the same query string directly to the jdbc url: jdbc:mysql://localhost:3306/databasename?useUnicode=true&characterEncoding=utf8&characterSetResults=UTF-8&sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES iirc, the connection string box in the dsn setup in cf admin did not [always] work with mysql connector/j, i.e. putting the above Unicode-related params there did not work, but they worked fine aded to the jdbc url... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Brad Wood wrote: ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Tom Chiverton
05/16/2008 11:46 AM

> workaround is to either enapsulate the value in a <cfqueryparam> tag Err, you mean you do not do this for *every single query you write* already ? -- Tom Chiverton **************************************************** This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB.  A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP.  Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged.  If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents.   If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com.


<< Previous Thread Today's Threads Next Thread >>

Search cf-talk

October 23, 2014

<<   <   Today   >   >>
Su Mo Tu We Th Fr Sa
       1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31   

Designer, Developer and mobile workflow conference