Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to search strings escaped by mysql real escape string()?

    I am currently developing an article script and there are Titles and Contents. To prevent sql injection, people say we must use mysql_real_escape_string().

    So let's say if there is a Title that says "My Friend's best friend", if I look into the MySQL table record, the text will be saved as "My Friend\'s best friend", where the apostrophe is escaped.

    Now, I am trying to create a search feature, how can I search the "Friend's" with the apostrophe? If I try to search submitting a query like:

    Select * from `article` where `title` like "%Friend\'s%";

    or

    Select * from `article` where `title` like "%Friend's%";

    both of them will not work!

    Thanks in advance.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Use a fulltext index.

  • #3
    New Coder
    Join Date
    Aug 2006
    Posts
    34
    Thanks
    0
    Thanked 1 Time in 1 Post
    You're wrong about how it will be saved in the database.

    Assume the following:

    $string = "I am full of 'slashes'";

    if you mysql_real_escape that, it'll become

    $string = "I am full of \'slashes\'";

    When you insert that in your DB query

    $query = "INSERT INTO foo VALUES( '$string' );";

    $query will be:

    $query = "INSERT INTO foo VALUES( 'I am full of \'slashes\'' );";

    But, and here's the clue of the whole story, those slashes escape those quotes, because otherwise you would have a string that's terminated and opened again, which shouldn't happen that way. But anyway, those quotes are only escaped *IN THE SQL TEXT*. MySQL will see them, but they won't go in the database, precisely because they're just escape characters. In the database, the slashes will *not* be there. And that's exactly the way it should be.

    If those slashes *are* in the DB, then you're doing something wrong, and likely one of those pesky automatic slashing things are on.


  •  

    Posting Permissions

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