Prevents ColdFusion from automatically escaping single-quotation mark characters that are contained in a variable. ColdFusion does not evaluate the argument.
(None)
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.)
Parameter |
Description |
---|---|
variable |
Variable that contains a string in which to preserve single-quotation marks. |
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)#)"> **tharwood 11/16
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 = "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>