3

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.

Community
  • 1
  • 1
nam
  • 21,967
  • 37
  • 158
  • 332
  • 5
    A much better approach is to rewrite your query to use parameters (``). Doing it the way you are doing it is extremely error-prone and likely to lead to a [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection) vulnerability. – elixenide Nov 06 '15 at 21:27
  • 5
    ... 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 Nov 06 '15 at 21:49
  • 4
    If you wanna persist creating your SQL string outside of a `` 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 Nov 06 '15 at 21:53
  • @AdamCameron I've added an UPDATE section to my original post. Using cfqueryparam may not be an option in my situation as I'm just fixing the single quote issue. And customer will not allow to overhaul the entire .cfc file. – nam Nov 06 '15 at 22:38
  • 3
    @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 Nov 07 '15 at 02:52
  • 1
    *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 Nov 07 '15 at 06:37
  • 1
    I will second @AdamCameron's solution, but if there are conditions that prevent a rewrite, I will just make the point that if the original string in the last example is "WHERE name IN('Smith's bat')", there's no preexisting difference between the content ' and the qualifier '. You would need to address this first if it was the case. Can you provide an example of that where_clause string as it is passed in? – GumbyG Nov 13 '15 at 14:29
  • 1
    It is also worth noting that rewriting portions of it to use `queryExecute` should be a lot easier, since it already supports dynamic sql strings (of a sort). Instead of embedding parameter values within the sql string, use a parameter marker and append the values to a separate array. Then simply call `queryExecute(sqlString, parameters)` instead of cfquery. – Leigh Nov 14 '15 at 11:50

2 Answers2

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.

Alex
  • 7,743
  • 1
  • 18
  • 38
1

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!

Paulo Teixeira
  • 448
  • 5
  • 11
  • @PauloTexeira. Your suggestion gives the error: `Complex constructs are not supported with function PreserveSingleQuotes` – nam Nov 10 '15 at 21:50
  • Print a cfdump of your string to help us to understand the data. – Paulo Teixeira Nov 11 '15 at 14:36
  • Keep in mind, that only works if the single quotes are properly escaped, which they are not (judging from the OP). Speaking from personal experience, this kind of dynamic sql is extremely error prone and a royal pain to debug... Not to mention it leaves the database wide open to sql injection. Though it is possible to make it work, switching to "parameterized" queries is the safer - and saner - option IMO – Leigh Nov 13 '15 at 01:24