title: ColdFusion single quote issue with SQL Query
body: <p>In my ColdFusion 11 app, with SQL Server 2008-R2, I've following cfquery tag inside a CF Component:</p>
  <pre><code>&lt;cfquery name=&quot;result_set&quot; dataSource=&quot;#request.dsn_name#&quot;&gt;
      select name, state from myTable #REReplace(where_clause,&quot;''&quot;,&quot;'&quot;,&quot;ALL&quot;)#        
  &lt;/cfquery&gt;
  </code></pre>
  <p>Here <code>where_clause</code> 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</p>
  <pre><code>select name, state from myTable WHERE name IN (''ABC'') 
  </code></pre>
  <p>to this:</p>
  <pre><code> select name, state from myTable WHERE name IN ('ABC') 
  </code></pre>
  <p>The problem is when a name column value contains a single quote as well. E.g.</p>
  <pre><code>select name, state from myTable WHERE name IN ('Smith's bat') 
  </code></pre>
  <p>In such cases the query fails. How can I resolve such cases. I tried <a href="http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6cbf.html" rel="nofollow noreferrer">PreserveSingleQuotes</a> but it has the same issue where column has values with single quotes.</p>
  <p><strong>UPDATE</strong></p>
  <p>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.</p>