Problem with single quotes

Posted on 2011-04-27
Last Modified: 2012-05-11

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.
Question by:Tpaul_10
    LVL 51

    Accepted 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 )#
    LVL 51

    Expert Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
    PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now