ColdFusion single quote issue with SQL Query

Question

In my ColdFusion 11 app, with SQL Server 2008-R2, I've following cfquery tag inside a CF Component:

<cfquery name="result_set" dataSource="#request.dsn_name#">
    select name, state from myTable #REReplace(where_clause,"''","'","ALL")#        
</cfquery>

Here where_clause is a variable. The CF replaces one single quote with two and hence I'm using the REReplace function to replace two single quotes back into one. So my query changes, e.g. from

select name, state from myTable WHERE name IN (''ABC'') 

to this:

 select name, state from myTable WHERE name IN ('ABC') 

The problem is when a name column value contains a single quote as well. E.g.

select name, state from myTable WHERE name IN ('Smith's bat') 

In such cases the query fails. How can I resolve such cases. I tried PreserveSingleQuotes but it has the same issue where column has values with single quotes.

UPDATE

This app was developed years ago by someone using ColdFusion MX 7. The original author is creating dynamic string for where_clause variable based on certain conditions. It's a long cfs file with several conditions used for creating dynamic string for where_clause. Hence, using a cfqueryparam may either not be suitable or may require a complete overhaul of the code that customer will not allow.

Answer 1

That's a nasty problem. I'm afraid I can only come up with a nasty "solution".

• Substitute the value delimiters: <cfset where_clause = replace(where_clause, "''", "§§", "ALL")>
• Then escape the actual single quotes: <cfset where_clause = replace(where_clause, "'", "\'", "ALL")>
• Now revert the substitution and normalize the delimiters: <cfset where_clause = replace(where_clause, "§§", "'", "ALL")>

Throwing it together:

<cfset substitution = "§§"> <!--- use whatever char sequence works best for your data --->

<!--- fallback in case the substitution is part of your data --->
<cfif where_clause contains substitution>

    <cfset substitution = "°°°">
    <!---
        you can basically start looping through a bunch of alternatives
        or even expand the substition with an additional character
        ...you get the idea
    --->

</cfif>

<cfset where_clause = replace(where_clause, "''", substitution, "ALL")>
<cfset where_clause = replace(where_clause, "'", "\'", "ALL")>
<cfset where_clause = replace(where_clause, substitution, "'", "ALL")>

<cfquery...

As you can see this is still highly problematic and may fail some day. But there's probably no better alternative as long as you have to deal with the where_clause variable.