Skip to main content
T
TiGGi
Inspiring
October 4, 2006
Answered

CFQUERY where '

  • October 4, 2006
  • 9 replies
  • 926 views
I have a regular cfquery which is fine.
The problem that I ran into is that one entry in database that I search by contains ',
so it looks like something like
<CFQUERY ........
select * from table
WHERE house = 'Mike's House'
</CFQUERY>
well obviously ' in Mike's is closing the opening ' . I can't change data in the db, so is there a way to make this work?
    This topic has been closed for replies.
    Correct answer Newsgroup_User
    I have JS line where it says var html = '#house#'

    Do you have data that has double quotes in it? If you do not you can
    get away with var html = "#house#". CF allows one to nest quotes.

    If you have data with both single and double quotes, you will need to
    use escape sequences. What those are depend on what technology you are
    working with. I believe the escape character for JS is the backslash \.

    You may also be able to use encoding functions such as URLEncodedFormat().

    9 replies

    N
    Newsgroup_User
    Inspiring
    October 4, 2006
    I did try escape sequence but since data is coming from db I can't place
    it in right position, infront of the '. I did some <cfif>s and
    processed that record differently since it's only one on db.
    Thank you for your help!


    It would be trivial to use string manipulation to add the escape
    characters to the string after it is read from the database.

    <cfset escapedString = replace(dbString,"'","\'","ALL")> for one example.

    T
    TiGGiAuthor
    Inspiring
    October 4, 2006
    I did try escape sequence but since data is coming from db I can't place it in right position, infront of the '. I did some <cfif>s and processed that record differently since it's only one on db.
    Thank you for your help!
    N
    Newsgroup_UserCorrect answer
    Inspiring
    October 4, 2006
    I have JS line where it says var html = '#house#'

    Do you have data that has double quotes in it? If you do not you can
    get away with var html = "#house#". CF allows one to nest quotes.

    If you have data with both single and double quotes, you will need to
    use escape sequences. What those are depend on what technology you are
    working with. I believe the escape character for JS is the backslash \.

    You may also be able to use encoding functions such as URLEncodedFormat().
    T
    TiGGiAuthor
    Inspiring
    October 4, 2006
    it looks like it's more of JS problem, it doesn't like ' either.
    I have JS line where it says var html = '#house#'
    T
    TiGGiAuthor
    Inspiring
    October 4, 2006
    I think it works on basic query, but I am also using Google API Maps and it doesn't work with it. I'll do some more checking and tweaking.
    Thanks for your help!
    P
    paross1
    Participating Frequently
    October 4, 2006
    quote:
    But honestly the <cfquerparam ...> solution handles all of this and has many other potential benefits


    Ian, that would have been my next suggestion...

    Phil
    N
    Newsgroup_User
    Inspiring
    October 4, 2006
    wouldn't this also cancel the actual meaning of the Mike's House to Mike
    s House?

    It shouldn't. In CF one escapes control characters by doubling them.
    So to escape the pound sign you go ##, the double quotes "" and single
    quotes ''. But honestly the <cfquerparam ...> solution handles all of
    this and has many other potential benefits. There is also the
    preserverSingleQuotes() function.
    T
    TiGGiAuthor
    Inspiring
    October 4, 2006
    wouldn't this also cancel the actual meaning of the Mike's House to Mike s House?
    P
    paross1
    Participating Frequently
    October 4, 2006
    No, it "shields" the meaning of the middle quote from the interface so that it is not treated as a close quote, and allows the other single quote in Mike's to be passed to the database as a single quote within the string. Same thing applies if you happen to have a # sign......

    Did you try it?

    Phil
    N
    Newsgroup_User
    Inspiring
    October 4, 2006
    Or better yet, use the <cfqueryparam ...> tag so you get all the
    benifits, including single quote handling.

    WHERE house = <cfqueryparam value="#var#" ...>

    I.E.

    WHERE house = <cfqueryparam value="Mike's House" ...>
    P
    paross1
    Participating Frequently
    October 4, 2006
    You might try using two single quotes... doubling cancels the "special meaning".

    WHERE house = 'Mike''s House'

    Also, this works.....

    <cfset xxx="Mike's House">
    <CFQUERY ........
    select * from table
    WHERE house = '#xxx#'
    </CFQUERY>

    Phil
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices