how to stop coldfusion from adding single quotes in string.

Greetings,
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#'">

Open in new window


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;

Open in new window


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
LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
CF automatically escapes quotes contained within any variables inside a cfquery to protect against sql injection.  If you absolutely must use dynamic sql, you must wrap the variable in PreserveSingleQuotes to suppress that behavior:

    <cfquery name="yourQuery" ....> #PreserveSingleQuotes(query)#</cfquery>

BUT .... that will also expose the db to sql injection. Especially bad with db's like SQL Server because they support execution of multiple statements by default. If possible, don't use that kind of dynamic sql.  Either build it dynamically within the cfquery, using bind variables:
<cfquery name="yourQuery">
	SELECT   ParcelID,   .... rest of columns
        FROM  PropertyParameters
	WHERE  AddZip =  <cfqueryparam value="#URL.zip#" cfsqltype="cf_sql_varchar">
       <cfif someCondition>
                AND   SomeColumn = <cfqueryparam value="#URL.someValue#" cfsqltype=".....">
      </cfif>
       <cfif otherCondition>
                AND   OtherColumn = <cfqueryparam value="#URL.otherValue#" cfsqltype=".....">
      </cfif>
</cfquery>

Open in new window

... or try the cfscript version of cfquery in CF9+.  It does the same thing, but may be a better fit for protecting queries dynamic sql strings:

Not sure which version you're using, but here is an example for CF9. There's also improved features/syntax in CF11.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Thank you so much.
I'm still learning functionality for Coldfusion.
This is great.
0
_agx_Commented:
You're welcome. Glad it helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.