Alex Le’s Blog

While I was constructing a SQL string to run through a cfquery command, I found out about the bug in how ColdFusion’s cfquery handles quotes:



sql = “SELECT * FROM someTable WHERE thisColumn = ‘thatValue’”;


#sql#



The server refused to work the script! Why? Because ColdFusion for some mysterious reasons automatically replace the single quote around ‘thatValue’ with 2 single quotes, e.g. ”that value”.

So how do you solve this? Wrap the sql string with a replace like this


#replaceNoCase(sql, “””, “‘”, “all”)#



=== Update ===
There’s another way to solve the doubled single-quote problem is to use the #preserveSingleQuotes# as shown in this article from Adobecrodia (or Macrodobe, whatever works for you)





dbtype="#arguments.dbType#">
#preserveSingleQuotes(arguments.SQLString)#




(http://www.adobe.com/devnet/coldfusion/extreme/cftags_cfscript.html)