Whatever message this page gives is out now! Go check it out!
PreserveSingleQuotes
Last update:
May 18, 2026
Description
Prevents ColdFusion from automatically escaping single-quotation mark characters that are contained in an expression. ColdFusion does not evaluate the argument.
Returns
(None)
Category
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:
<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)#)"> **tharwood 11/16is 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>
We're glad. Tell us how this page helped.
We're sorry. Can you tell us what didn't work for you?
Share additional feedback to help us improve.
0/255
|
