Decoding mysql_real_escape_string() for outputting HTML

Question

I'm trying to protect myself from sql injection and am using:

mysql_real_escape_string($string);

When posting HTML it looks something like this:

<span class="\&quot;className\&quot;">
<p class="\&quot;pClass\&quot;" id="\&quot;pId\&quot;"></p>
</span>

I'm not sure how many other variations real_escape_string adds so don't want to just replace a few and miss others... How do I "decode" this back into correctly formatted HTML, with something like:

html_entity_decode(stripslashes($string));

Answer 1

The mysql_real_escape_string() manual page tells you which characters are escaped:

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

You could successfully reverse the escaping by replacing those escaped characters with their unescaped forms.

mysql_real_escape_string() shouldn't be used to sanitize HTML though... there's no reason to use it before outputting web page data. It should only be used on data that you're about to put into the database. Your sanitization process should look something like this:

Input

1. Accept user input from a form or HTTP request
2. Create database query using mysql_real_escape_string()

Output

1. Fetch data out of the database
2. Run any user-defined data through htmlspecialchars() before printing

Using a different database driver such as MySQLi or PDO will allow you to use prepared statements, which take care of escaping most inputs for you. However, if you can't switch or take advantage of those, then definitely use mysql_real_escape_string()... just only use it before inserting data.