1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

I'm not using double quotes I tell ya!

Discussion in 'Programming' started by jonnyewing, May 11, 2010.

  1. #1
    I have a problem where Coldfusion 8 appears to take my code and turn single quotes into double quotes for me.

    This happened to me once before but I can't remember how I dealt with it. I may have just given up and tried a different approach. But I figured maybe someone here could help me this time.

    Here's what happens.

    Step one:

    I'm looping through a list to build a SQL query

    <cfset strSQLDomainsList = strSQLDomainsList & "(members.email like '%#trim(listgetat(strDomainsList, intDomainLoop))#%') or ">
    Code (markup):
    Notice that I am using single quotes around the variables.

    This is how the resulting query ought to look:

    select distinct(member_id) from members (nolock) where 
    ((members.opt_out is null) 
    or (members.opt_out = 0)) 
    and members.email <> '' 
    and (
    (members.email like '%hotmail.com%')
     or (members.email like '%hotmail.co.uk%')
     or (members.email like '%live.com%')
     or (members.email like '%msn.com%')
    ) 
    Code (markup):
    That would work fine for my needs.

    But it's not happening.

    Step two:

    I run the code.

    This is the query that Coldfusion comes up with:

    select distinct(member_id) from members (nolock) where ((members.opt_out is null) or (members.opt_out = 0)) and members.email <> '' and ((members.email like ''%hotmail.com%'') or (members.email like ''%hotmail.co.uk%'') or (members.email like ''%live.com%'') or (members.email like ''%msn.com%''))
    Code (markup):
    Note that there are double quotes around the domain name variables where I clearly used single quotes.

    I tried the obvious...

    <cfset strSQLDomainsList = replace(strSQLDomainsList, "''", "'", "ALL")>
    Code (markup):
    ...but that doesn't help at all.

    When I output strSQLDomainsList as a string, there are single quotes in it. When I try to run the string as part of a cfquery, the single quotes become doubles!

    Then I get this error message:

     Error Executing Database Query.
    [Macromedia][SQLServer JDBC Driver][SQLServer]The column prefix 'hotmail' does not match with a table name or alias name used in the query. 
    Code (markup):
    Any ideas?
     
    jonnyewing, May 11, 2010 IP
  2. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Yeah, that's gonna happen with dynamic sql queries like this:
    ie
    <cfquery ...>#someVariableContainingSQL#</cfquery>

    CF automatically escapes single quotes. Turning 1 quote into 2, to protect you from sql injection. A good thing too, seeing as how you're using MS SQL. The only way around this behavior is to use the PreserveSingleQuotes function. Of course that leaves your db wide open to sql injection attacks. But the choice is yours.
     
    cfStarlight, May 11, 2010 IP
  3. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    So, what did you eventually decide to do?
     
    cfStarlight, May 21, 2010 IP
  4. jonnyewing

    jonnyewing Member

    Messages:
    30
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #4
    Sorry, cfStarlight, it was rude of me not to post a reply after you'd been so helpful.

    Actually, the way I got around it was to write out the SQL rather than putting it into a variable.

    So, instead of:

    <cfset strSQLDomainsList = strSQLDomainsList & "(members.email like '%#trim(listgetat(strDomainsList, intDomainLoop))#%') or ">
    Code (markup):
    I ended up with something like...

    (members.email like '%#trim(listgetat(strDomainsList, intDomainLoop))#%')
    Code (markup):
    It was a little bit more messy to put the code in the middle of a CFQUERY rather than defining it all as a variable beforehand, but it worked okay.

    Thanks again for your help and apologies for leaving you hanging.
     
    jonnyewing, Nov 4, 2010 IP
  5. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Hey, we all get busy with stuff. But thanks for following up!

    Yeah, it may seem that way if you're used to other languages. But that's the common way of doing things w/cfquery. But since you're using sql server I'd strongly suggest you start using cfqueryparam. Strings are usually safe

    (members.email like '%#trim(listgetat(strDomainsList, intDomainLoop))#%')
    Code (markup):
    .. but numeric values (that dont' have to be quoted) are always dangerous

    WHERE tablename.NumCol  = #someID#
    Code (markup):
    .. safer to use cfqueryparam everywhere. It can also give a performance boost for db's like sql server. ie Helps the db reuse query plans.

    Cheers
     
    Last edited: Nov 4, 2010
    cfStarlight, Nov 4, 2010 IP