how to stop coldfusion from adding single quotes in string

Question

I have a dynamic sql statement that I want to fire off to SQL Server. The base of the query is in a variable called query:

<cfset query ="select 
            ParcelID,
            LTRIM(IIF(AddNum IS NOT NULL, ' ' + AddNum, '') + IIF(AddDir IS NOT NULL, ' ' + AddDir, '')  + ' ' + AddStreet + IIF(AddUnitNum IS NOT NULL, ' ' + AddUnitNum, '') + ' ' + AddCity + ', ' + AddState + ' ' + AddZip + IIF(AddZip4 IS NOT NULL, '-'+ AddZip4, '')) as Address
        from PropertyParameters
        where AddZip = '#URL.zip#'">

My problem is the areas where you see quote-space-quote plus field (ie. ' ' + AddNum), the single quote adds more single quotes, and sends the following to sql server:

select ParcelID,
LTRIM(IIF(AddNum IS NOT NULL, '' '' + AddNum, '''') + IIF(AddDir IS NOT NULL, '' '' + AddDir, '''')  + '' '' + AddStreet + IIF(AddUnitNum IS NOT NULL, '' '' + AddUnitNum, '') + '' '' + AddCity + '', '' + AddState + '' '' + AddZip + IIF(AddZip4 IS NOT NULL, ''-''+ AddZip4, '')) as Address
from PropertyParameters
where AddZip = 20001 and Score > 70;

as you can see, the quotes are doubled and fails in sql server. How do I overcome this?

Thanks.

UPDATE 1: I tried escaping the quote according to coldfusion instructions ('' and "") and that still didn't work. Thanks

score 0 · Accepted Answer · answered Aug 30 '15 at 18:48

0

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>

answered Aug 30 '15 at 18:48

M.Ali

67,945
13
101
127

thanks for both ideas. That's great. – arcee123 Aug 30 '15 at 19:26
Make sure not to hard-code data values such as `#URL.zip#` into your SQL statement though; pass them as parameters. It makes the query execution more efficient, and doesn't leave you exposed to SQLi. – Adam Cameron Aug 30 '15 at 19:44
While PreserveSingleQuotes does work, do not use it unless you know what you are doing! The function "undoes" CF's automatic escaping of single quotes, **leaving sql statements completely vulnerable to sql injection**. Using bind variables (or query parameters) instead, avoids that risk. If you still choose to use PreserveSingleQuotes, just understand the query is NOT protected, so be sure to thoroughly scrub all input. – Leigh Aug 30 '15 at 20:44

how to stop coldfusion from adding single quotes in string

1 Answers1