• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Problem with single quotes


I have a SQL statement stored in a table like following.

SELECT * FROM MyTable1 where 0=0
AND FinalStatus IN (2,8)
AND datediff(day,getDate(),activedate) <= (25)
AND State IN ('AK','FL')
Order by ID

I am getting that value with the following query

<cfquery name="myQuery" datasource="myDatasource">
Select * from MyTable2 WHERE ID = #pMyID#

#myQuery.sqlQuery# is replacing one of my AND condition, which is AND State IN ('AK','FL') TO AND State IN (''AK'',''FL'').

It is placing the single quotes twice when i get the value and is causing my query to fail with error Incorrect syntax near 'AK'.

Is there any way to avoid this? Please help me out.

Thanks in advance.
  • 2
1 Solution
It is placing the single quotes twice when i get the value

That's one of the down sides of using dynamic sql. CF automatically doubles up the quotes to protect you against sql injection (a big problem in web apps). The only way to avoid it is to use the PreserveSingleQuotes() function. Unfortunately, doing that makes your db completely vulnerable to sql injection.  

    #PreserveSingleQuotes( yourSQLStringWithQuotesHere )#
One more note ... personally I'd avoid using that kind of dynamic sql if at *all* possible. You can't use cfqueryparam with entirely dynamic sql strings,  so the main burden of protecting against ever changing sql injection threats falls on you!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now