ColdFusion single quote issue with SQL Query

3
votes

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.

2
sql-servercoldfusioncoldfusion-11cfquerysingle-quotes
A much better approach is to rewrite your query to use parameters (<cfqueryparam>). Doing it the way you are doing it is extremely error-prone and likely to lead to a SQL injection vulnerability. - elixenide
... and is the reason you are having a problem with the query in the first place. Do not use that kind of dynamic sql. Instead, build the sql within the cfquery and use cfqueryparam on all parameters (or look into using cfscript queries, which offer a bit more flexibility with "parameterized" sql). That will both protect the database and eliminate this type of error. - Leigh
If you wanna persist creating your SQL string outside of a <cfquery> call, use queryExecute() instead which will allow you to put parameter place holders into the SQL string; and then pass the parameter values in a separate array / struct. Just don't hard code the values into the SQL string. That's dreadful, awkward, and dangerous. - Adam Cameron
@nam - The fact that their db is currently at very high risk for sql injection should provide them with a strong motivation. While I am sure you could find an ugly work-around for now, frankly it is only prolonging the inevitable, and more importantly increasing the risk their db could be hacked in the mean time... - Leigh
Using cfqueryparam may not be an option Take another look at Adam's suggestion. Switching from cfquery to queryExecute would fix the issue while allowing you to still use dynamic sql. Only in a cleaner and more secure way. - Leigh

2 Answers

1
votes

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.

0
votes

You need to use the Function PreserveSingleQuotes, that way:

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

Have a good day!