How do you encode an apostrophe so that it's searchable in mysql?

Question

I don't think that was the most clear question, but an example should make it a little clearer.

I have a table filled with movie names, some of which contain apostrophes. I have a search box which is used to find movies.

If I perform searches via

mov_title = '$search_keywords'

it all works, but this method will not yield any results for partial searches, so I have to use this

mov_title LIKE '%$search_keywords%'

This method works fine for titles that are A-Za-z0-9, but if a title has an apostrophe, it's not able to find the movie, even if I do an exact match.

Before the titles are stored in the DB, I put them through this:

$search_keywords = htmlspecialchars(mysql_escape_string($_GET["search_keywords"]));

So in the DB, there is a forward slash before every single apostrophe.

The only way to match a movie title with an apostrophe is to physically put a forward slash in front of the apostrophe, in the search box.

This seems so trivial, and I'm sure the solution is painfully obvious, but I'm just not seeing it.

Answer 1

Use mysql_real_escape_string(), and do not use htmlspecialchars(). The latter is not for database escaping, it's for HTML production.

Answer 2

This only happens because you escaped the data for html output before doing the output! You should only do it right before doing the output, i.e.:

<li><?php echo htmlspecialchars($some_var); ?></li>

Unescape the values in your database and change the application to escape only on output. You currently have no other way than also doing htmlspecialchars(mysql_real_escape_string()) on the $search_string.

Even if it made sense to escape for HTML already on inserting into the database, mysql_real_escape_string() would be the outer function and not the inner function.

Answer 3

If you prefer to keep your database intact, you could just run the htmlspecialchars() function on any search words you are looking for before constructing the query. Though I recommend putting the data into the database exactly as is, (but use mysql_real_escape_string() to escape it to hinder sql injection attempts). Then use htmlspecialchars() when you are printing the data to the browser.