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

mysql_real_escape_string

[santocki]

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 features, in this this, how can I search the "Friend's" with the apostrophe? If I query like:

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

or

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

will not work!

Thanks in advance.

[borntobeweb]

santocki, if you do things right, the text should be stored in the database without the backslashes, so if you use phpmyadmin or something to look at the title, you should see "My Friend's best friend" with no \'s. If the saved text has backslashes, either mysql_real_escape_string() was called twice somewhere, or magic quotes [php.net] is on (which adds its own backslashes), and your SQL insert statement became:

insert into article (title) values ("My Friend\\'s best friend")

instead of the correct way:

insert into article (title) values ("My Friend\'s best friend")

Generally, i prefer to turn off magic quotes and use mysql_real_escape_string(), addslashes(), etc when needed so i have more control... As far as your select statement, the first one is the safe way to do it, i.e.:

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

Hope this helps