Inserting ' and " into SQL

I have a query blowing up when I try to insert a ' or " into a text field. What is the trick for handling this? A replace list? or this there something better? Justin Hansen - jhansen@n2biz.com Web Application Developer Interactive Business Solutions, Inc 816-221-5200 ext. 1305 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm

The CF magic trick is to use the "preservesinglequotes()" function around your data in your SQL statement. I have a query blowing up when I try to insert a ' or " into a text field. What is the trick for handling this? A replace list? or this there something better? Justin Hansen - jhansen@n2biz.com Web Application Developer Interactive Business Solutions, Inc 816-221-5200 ext. 1305 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm

This is the code I use to do it, hasn't failed me yet.  I have no idea who originally wrote it, and I'd love to give them credit for it. It's helped me a lot. Anyway... put this above your query somewhere.... <!--- initialize the variable - bad_chars ---> <cfparam name="bad_chars" default=""> <!--- initialize the variable - good_chars ---> <cfparam name="good_chars" default=""> <!--- loop through the range of high-ascii ---> <cfloop index="i" from="127" to="255">    <!--- append each high-ascii character to a list           contained in the variable bad_chars --->    <cfset bad_chars = ListAppend(bad_chars, Chr(i))>    <!--- append each numeric character entity           representation of the high-ascii character to           a list contained in the variable good_chars  --->    <cfset good_chars = ListAppend(good_chars, "&##"                                   & NumberFormat(i, '0000') & ";")> </cfloop> And then in your query, use this line... '#Trim(ReplaceList(variable, bad_chars, good_chars))#' And change "variable" with what ever your variable name is. This will replace any bad characters with it's ascii equivalent... making ' and " into characters that the db will agree with. Stuart Duncan MaracasMedia Inc. At 11:19 AM 7/13/01 -0500, you wrote: ----- Excess quoted text cut - see Original Post for more ----- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm

Very cool! I made the char list creation a custom tag (for easy reuse) and the replace list works great! Thanks a lot! Justin Hansen - jhansen@n2biz.com Web Application Developer Interactive Business Solutions, Inc 816-221-5200 ext. 1305 This is the code I use to do it, hasn't failed me yet.  I have no idea who originally wrote it, and I'd love to give them credit for it. It's helped me a lot. Anyway... put this above your query somewhere.... <!--- initialize the variable - bad_chars ---> <cfparam name="bad_chars" default=""> <!--- initialize the variable - good_chars ---> <cfparam name="good_chars" default=""> <!--- loop through the range of high-ascii ---> <cfloop index="i" from="127" to="255">    <!--- append each high-ascii character to a list           contained in the variable bad_chars --->    <cfset bad_chars = ListAppend(bad_chars, Chr(i))>    <!--- append each numeric character entity           representation of the high-ascii character to           a list contained in the variable good_chars  --->    <cfset good_chars = ListAppend(good_chars, "&##"                                   & NumberFormat(i, '0000') & ";")> </cfloop> And then in your query, use this line... '#Trim(ReplaceList(variable, bad_chars, good_chars))#' And change "variable" with what ever your variable name is. This will replace any bad characters with it's ascii equivalent... making ' and " into characters that the db will agree with. Stuart Duncan MaracasMedia Inc. At 11:19 AM 7/13/01 -0500, you wrote: ----- Excess quoted text cut - see Original Post for more ----- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm