Contents > CFML Reference > ColdFusion Functions > PreserveSingleQuotes

PreserveSingleQuotes

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

(None)

Other functions

PreserveSingleQuotes(variable)

ColdFusion MX: Changed behavior: 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.)

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' 

<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>

Contents > CFML Reference > ColdFusion Functions > PreserveSingleQuotes

ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6.1

Comments are no longer accepted for ColdFusion MX 6.1. ColdFusion 8 is the current version.

Comments

Add Comment

nathandintenfas s said on Jan 12, 2004 at 3:05 PM :

preserveSingleQuotes seems to not like complex variables as arguments. Consider this code


<cfscript>
foo = structNew();
foo.key1 = "value1";
foo.key2 = "value2";
</cfscript>

<cfloop collection="#foo#" item="key">
<cfoutput>
#key# = #foo[key]#<br />
</cfoutput>
</cfloop>
<hr>
<cfloop collection="#foo#" item="key">
<cfoutput>
#key# = #trim(foo[key])#<br />
</cfoutput>
</cfloop>
<hr>
<cfloop collection="#foo#" item="key">
<cfoutput>
#key# = #preserveSingleQuotes(foo[key])#<br />
</cfoutput>
</cfloop>

halL said on Jan 13, 2004 at 6:30 AM :

There appears to be a parsing problem in parsing PreserveSingleQuotes
This has been submitted as ColdFusion Bug number 53977.

MFI said on Mar 7, 2004 at 8:37 AM :

in the example:
<cfset list0 = " '1','2''3' ">
is missing a comma between 2 and 3

Mike@Jobscience said on Mar 11, 2005 at 3:20 PM :

preserveSingleQuotes() also has an issue taking the output of a function call as well. Which when compounded with the fact that function call output doesn't get escaped in a cfquery (see cfquery comment I made) this presents a coding nightmare.

hereticmessiah said on May 10, 2005 at 10:28 AM :

Mike, what you describe as a bug in the way PreserveSingleQuotes() works in queries is actually a feature. One of the reasons it exists is to allow one to generate SQL code on the fly and embed it into a query.

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/6.1/htmldocs/functa53.htm

Add Comment