PreserveSingleQuotes

Description

Prevents ColdFusion from automatically escaping single-quotation mark characters that are contained in an expression. ColdFusion does not evaluate the argument.

Returns

(None)

Function syntax

PreserveSingleQuotes(expression)

History

ColdFusion MX: Changed behavior: ColdFusion automatically escapes single quotes within any expression value within a cfquery tag body or block. (Earlier releases did not automatically escape array-variable references.)

Parameters

Parameter	Description
expression	Expression that evaluates to a string in which to preserve single-quotation marks.

Usage

This function is useful in SQL statements to defer evaluation of an expression 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:

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)#)"> **tharwood 11/16

is 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 = "cfdocexamples"> 
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>