ColdFusion SQL String In CFQUERY Tag Single Quote Problem

Escaped single quotes mess up SQL used in the cfquery tag as described in ColdFusion single quote string screwup.

The behavior described in the page above also affects ColdFusion MX 7: When a string variable is put into a cfquery tag any single quotes are changed to TWO single quotes, ( note, not a double quote). This makes creating SQL strings difficult because they do not work inside of the cfquery tag, for example a typical SQL string with should have a single quote around a string value but inside the cfquery tag the string would become:

select * from mytable where first_name=''Joe Bob''

NOTE: it is 2 single quotes not a "double quote", this does not work as SQL. Worse yet, it looks like a "double quote" problem and so it is a masked error that is harder to figure out.

This error is one of the most annoying bugs I have seen in Cold Fusion, it was in ColdFusion 6.1 MX from years ago and still plagues every release. What is wrong with Adobe Systems that they let a fundamental problem like creating SQL scripts from string variables used in almost every Cold Fusion application be such a burden to developers for years and years? I always wondered why the construct of a simple string for SQL was such a convoluted pile of worm crap in all the Adobe docs for ColdFusion. The docs hide that it does not pass a single quote properly, no wonder PHP cleaned their clock and took the web scripting market years ago. So what that CF limps along as a JVM based language, it is a crappy JVM based language. Switch to JRuby if you want a JVM based language.

ColdFusion SQL String Single Quote Hack

My method: use the string function REReplace to change the two single quotes, '', to one single quote, ', but this must happen inside the cfquery tag after the string is evaluated, not outside of the cfquery tag when the 2 single quotes are not yet present.

.cfset string_variable="select * from mytable where first_name='Joe Bob'">

.cfquery name="string_with_single_quote" datasource="schema_user" result="my_result">
            #REReplace(string_variable,"''","'","ALL")#
./cfquery>

I prefer the regular expression replace to the PreserveSingleQuotes function in ColdFusion because at least I have some idea what the regular expression is doing instead of just some black box. Big whoop, now you can make a string to pass SQL to an RDBMS with ColdFusion. Just do not forget this lame crutch or your application will be impossible to create. The convoluted ColdFusion documentation just does not cover this basic problem.