Dealing with MySQL escape sequences

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?

Encapsulate the value in a cfqueryparam tag, which you should always be doing anyway. On Fri, May 16, 2008 at 3:58 AM, Jim McAtee <jmcatee@mediaodyssey.com> wrote: ----- Excess quoted text cut - see Original Post for more ----- -- mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/

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 -----

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 -----

Thanks.  I'll have to try it. ----- Excess quoted text cut - see Original Post for more -----

> 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.