Mysql storing quotes as '

Question

I have some PHP code which stores whatever is typed in a textbox in the databse. If I type in bob's apples, it gets stored in the database as bob's apples.

What can be the problem?

The table storing this has the collation of latin1_swedish_ci.

Answer 1

Looks like your PHP code is converting special char to HTML entities using htmlentities. You can make use of the function html_entity_decode to get back the original string.

$a = "bob's apples";
echo htmlentities($a,ENT_QUOTES);                                // bob's apples
echo html_entity_decode(htmlentities($a,ENT_QUOTES),ENT_QUOTES); //bob's apples

Answer 2

The function htmlspecialchars() with ENT_QUOTES for the seconde parameter also converts a single quote to '

htmlspecialchars($a, ENT_QUOTES);

Answer 3

Your HTML escaping strings (with either htmlspecialchars() or htmlentities()) before inserting into the database, which is a bad idea. You can use html_entity_decode() to repair the damage but it's better not to do it in the first place.

The time when you should be escaping HTML is right before you output to the browser usually with the fetched rows from SELECT queries:

Do a Google for XSS.

Not that HTML escaping has little to do with "adding slashes" that you should be doing before inserting string into the database with mysql_real_escape_string(). This is to avoid SQL injection vulnerabilities.

<?php $row =  mysql_fetch_row($result);
echo htmlspecialchars($row['someField']); // good place to escape HTML.

<?php $str = htmlspecialchars($_GET['foo']); // bad place to escape HTML.
$str = mysql_real_escape_string($str); // good place to escape for DB.
$q = 'INSERT INTO .... VALUES (' . $str . ')';
mysql_query($q);