PreserveSingleQuotes

Description

Prevents ColdFusion from automatically escaping single quotation mark characters that are contained in a variable. ColdFusion does not evaluate the argument.

Return value

(None)

Syntax

PreserveSingleQuotes(variable)

History

New in ColdFusion MX: ColdFusion automatically escapes simple-variable, array-variable, and structure-variable references within a cfQuery tag body or block. (Earlier releases did not automatically escape array-variable references.)

Parameters

Parameter Description

variable
Variable that contains a string in which to preserve single quotation marks.

Parameter	Description
variable	Variable that contains a string in which to preserve single quotation marks.

Usage

This function is useful in SQL statements to defer evaluation of a variable reference until runtime. This prevents errors that result from the evaluation of a single-quote or apostrophe data character (for example, "Joe's Diner") as a delimiter.

Example A: Consider this code:

<cfset mystring = "'Newton's Law', 'Fermat's Theorem'">

preservesinglequotes(#mystring#) is
<cfoutput>
#preservesinglequotes(mystring)#
</cfoutput>

The output is as follows:

preservesinglequotes(#mystring#) is 'Newton's Law', 'Fermat's Theorem'

Example B: Consider this code:

<cfset list0 = " '1','2''3' ">

<cfquery sql = "select * from foo where bar in (#list0#)">

ColdFusion escapes the single-quote characters in the list as follows:

""1"", ""2"", ""3""

The cfquery tag throws an error.

You code this function correctly as follows:

<cfquery sql = "select * from foo where bar in (#preserveSingleQuotes(list0)#)">

This function ensures that ColdFusion evaluates the code as follows:

'1', '2', '3'

Example

<h3>PreserveSingleQuotes Example</h3><p>This is a useful function for
  creating lists of information to return from a query. In this example, 
  we pick the list of Centers in Suisun, San Francisco, and San Diego, 
  using the SQL grammar IN to modify a WHERE clause, rather than looping 
  through the result set after the query is run.
<cfset List = "'Suisun', 'San Francisco', 'San Diego'">
<cfquery name = "GetCenters" datasource = "cfsnippets">
  SELECT Name, Address1, Address2, City, Phone
  FROM Centers
  WHERE City IN (#PreserveSingleQuotes(List)#)
</cfquery>
<p>We found <cfoutput>#GetCenters.RecordCount#</cfoutput> records.
<cfoutput query = "GetCenters">
<p>#Name#<br>  
#Address1#<br>
<cfif Address2 is not "">#Address2#
</cfif>
#City#<br>
#Phone#<br>
</cfoutput>