SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2005
    Location
    Kennett Square, PA USA
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql search string contains apostrophe

    I'm having a hard time creating a query that searches for database content that contains single quotes, or apostrophe characters.

    I use mysql_real_escape_string on the data prior to putting it into the db. Then I have a search form where the user can enter a word or phrase.

    If the user searches for "Mayor's" it is not found because the apostrophe is escaped in the db. (When I look directly in the database using phpMyAdmin, I can see that the value is Mayor\'s)

    So, the question in a nutshell is: what's the best way to allow a user to search the database for values that contain an apostrophe, or a quotation mark, or any other character that would have been escaped by mysql_real_escape_string?

    Incidentally, the user's search string also is cleaned with mysql_real_escape_string, just to further complicate things.

    Thanks,
    Jack

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Using mysql_real_escape_string is the correct thing to do.
    If you see an escaped ' in the database, then maybe magic_quotes is 'on' on your server? Did you check the value of the data prior to passing it through mysql_real_escape_string?

    Did you try your query in phpMyAdmin?

  3. #3
    SitePoint Member
    Join Date
    Jun 2005
    Location
    Kennett Square, PA USA
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, Magic Quotes are ON.

    So, if Magic Quotes are not ON, then the apostrophe should not appear to be escaped in the database?

    What's the recommendation here? Turn off magic quotes? What are the possible repercussions?

    Thanks

  4. #4
    SitePoint Member
    Join Date
    Jun 2005
    Location
    Kennett Square, PA USA
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did I check the value in phpMyAdmin? Yes I did, and the apostrophe is escaped in the database

    did I check the value prior to passing it through mysql_real_escape_string? Yes, this has been a controlled experiment.

    The input value was "mayor's" (without double-quotes)

    The search string was mayor's

    The query failed to yield a result.

    Thanks

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by JackAlbright View Post
    What's the recommendation here? Turn off magic quotes? What are the possible repercussions?
    From the manual
    Warning

    This feature has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged.
    It's preferred to code with magic quotes off and to instead escape the data at runtime, as needed.
    There are no repercussions. You always have to sanitize user input before using it in a query anyways.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •