PRESERVESINGLEQUOTES |
Description Prevents ColdFusion from automatically escaping single-quotation mark characters that are contained in a variable. ColdFusion does not evaluate the argument.
Returns (None)
Category Other functions
Function syntax
    PreserveSingleQuotes(variable)
History 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.)
Parameters
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 = "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>

VARIABLE |

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