View Full Version : how to search strings escaped by mysql real escape string()?

06-28-2007, 11:38 PM
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%";


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

both of them will not work!

Thanks in advance.

06-29-2007, 06:52 PM
Use a fulltext (http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html) index.

06-29-2007, 08:00 PM
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.