Coldfusion Adding Extra Quotes When Constructing Database Queries in Strings

how to stop coldfusion from adding single quotes in string

You sql query can be simplified by using the following query with ISNULL() function:

select ParcelID
      ,LTRIM(
         ISNULL(' ' + AddNum , '') 
       + ISNULL(' ' + AddDir , '')  
       + ' ' + AddStreet 
       + ISNULL(' ' + AddUnitNum, '') 
       + ' ' + AddCity + ', ' + AddState + ' ' 
       + AddZip 
       + ISNULL('-'+ AddZip4, '')
       ) as Address
from PropertyParameters
where AddZip = '#URL.zip#

To escape the single quotes you need to use the PreserveSingleQuotes() function.

<cfquery ...>#PreserveSingleQuotes(query)#</cfquery>

In ColdFusion what do single and double quotes around attribute values do?

SQL needs quotes for strings.

Quotes are part of standard SQL syntax to indicate a string (as indeed they are in almost every language).

If it didn't have quotes then SQL parser would have no idea where the string ended and the SQL continued.

Quotes are not necessary for numbers - where there is no ambiguity about where the value ends.

Remember also that the #hashes# are nothing to do with SQL - they are entirely on the CFML side. When running the cfquery tag, CF evaluates the body (including any hash expressions it contains) to create an SQL string, which is then passed to the database, (along with additional settings/parameters/etc). The SQL server has no knowledge of what parts of that string was hard-coded and what parts might have been evaluated from hashes.

cfqueryparam doesn't need quotes.

When you're fixing the queries to use cfqueryparam, you are creating parameters, and the tag handles everything necessary to indicate strings/etc to the SQL database. (You never need to wrap the cfqueryparam tag itself in quotes.)

Within the cfqueryparam tag, it makes zero difference whether or not you use quotes for the attributes - these three all produce the same result:

<cfqueryparam value="#var#" />
<cfqueryparam value='#var#' />
<cfqueryparam value=#var# />

How to double singlequotes in a string using coldfusion or mssql (Replace not working and can't use cfparam)

When your SQL string is within a variable, in an attempt to mitigate SQLi, CF will automatically escape any single quotes within the string. This is the correct thing to do with SQL statements, but if you also have data values hard-coded in the SQL statement (which you should not!), it will quite possible cause the problems you are seeing.

You really ought to pull your data values out of your SQL statement, as it's the wrong place for them to be.

If you are unable or unwilling to do it properly, you can work around this by using the preserveSingleQuotes() function, eg:

<cfquery>
    #preserveSingleQuotes(sqlStatementString)#
</cfquery>

How to insert data with singlequotes, doublequotes and slashes?

<cfqueryparam>

will solve this for you.

CFQUERY Not escaping single quotes properly

If you're going to do it that way, you need preserveSingleQuotes()

INSERT INTO page( title )
VALUES ( '#preserveSingleQuotes( page.getTitle() )#' )

Of course, insert the standard caveat about how you should be using cfqueryparam to avoid SQL injection attacks.

INSERT INTO page( title )
VALUES ( <cfqueryparam value="#page.getTitle()#" cfsqltype="cf_sql_varchar" /> )

For reference:

• http://cfquickdocs.com/cf9/#preservesinglequotes
• http://cfquickdocs.com/cf9/#cfqueryparam

ColdFusion - How to output single quotes into a text field?

A single quote should not give you a problem in an attribute value in HTML, unless:
* you're not quoting the attribute values, eg:

<input value=#myvar#>

The solution here is to quote your attributes, eg:

<input value="#myvar#">

or
* you are quoting your attributes, but are using single quotes :

<input value='#myVar#'>

Will end up being:

<input value='value with a ' in it'>

This - of course is invalid mark-up: the browser sees the value as 'value with a ', and the rest of it is just garbage.

If you need to do this:
* switch to using double-quote delimiters
* use htmlEditFormat() around your variable value (this will escape embedded double-quotes).

To troubleshoot this sort of thing, ALWAYS look at the HTML source. This will help you work out what's going on.

NB: to everyone mentioning preserveSingleQuote(): this function does NOTHING outside of a CFQUERY block. So it's not going to help here.

---

Related Topics