PHP mysql_real_escape_string() -> stripslashes() leaving multiple slashes

Question

I'm having issues escaping/stripping strings with PHP/MySQL - there always seems to be redundant slashes.

---

Let's take the following string as an example:

<span style="text-decoration:underline;">underline</span>

When adding a string to the database, I'm escaping it with mysql_real_escape_string() and the following gets stored in the database (EDIT: checked this by querying the database directly with mysql app):

<span style=\\\"text-decoration:underline;\\\">underline</span>

When reading back out of the database, I'm passing the string through stripslashes() and the following is returned:

<span style=\"text-decoration:underline;\">underline</span>

Since the quotes are still escaped, it breaks the html and the text is not underlined.

---

1. Why is mysql_real_escape_string() adding three slashes, and stripslashes() removing two slashes? I would expect them both to add/remove one slash.
2. How can I prevent this from happening?
3. Am I approaching this the correct way?

Answer 1

Best Solution

In your php.ini file, odds are that the magic_quotes_gpc directive is set to on. This should be disabled for security reasons. If you don't have access to the php.ini file (eg. on a shared host), you can always accomplish the same using an .htaccess directive (assuming this is an apache server).

In your php.ini

magic_quotes_gpc Off

In an .htaccess file:

php_flag magic_quotes_gpc Off

Why is this happening?

The reason this is happening is due to the following course of logic.

1. A string that needs escaping is sent to the server.
   • This is my string. It's awesome.
2. Magic Quotes escapes the apostrophe before it gets to your code.
   • This is my string. It\'s awesome
3. mysql_real_escape_string now has two characters to escape, the backslash \\ as well as the apostrophe \'.
   • This is my string. It\\\'s awesome
4. This new super-escaped string is stored in the database.
5. When the string is retrieved from the database, it get's passed to stripslashes. This removes the two escapes added in step 3, but since one of the backslashes has been escaped stripslashes thinks it belongs.
   
   • This is my string. It\'s awesome

This problem can really get out of hand when you re-submit these strings to the database, as each time the number of backslashes multiplies.

Alternative Solution

A quick-and easy alternative would be to simply remove the slashes added by magic_quotes before passing the string to mysql_real_escape_string.

$str = stripslashes($_POST['str']);
$str = mysql_real_escape_string($str);

Answer 2

When adding a string to the database, I'm escaping it with mysql_real_escape_string() and the following gets stored in the database:

<span style=\\\"text-decoration:underline;\\\">underline</span>

No it's not. When you escape strings in a sql query, it is only to transport the data in the query. The database parses the query and stores the data in the database, without any extra slashes. Thus, when you retrieve data from the database, you should not unescape anything. It's a common misconception.

If you find that there are excess slashes in the output, you probably have magic quotes turned on. Turn them off.

Edit:

mysql> create table foo (bar text) ;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO foo (bar) VALUES ("<span style=\\\"text-decoration:underline;\\\">underline</span>");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM foo;
+-------------------------------------------------------------+
| bar                                                         |
+-------------------------------------------------------------+
| <span style=\"text-decoration:underline;\">underline</span> | 
+-------------------------------------------------------------+
1 row in set (0.00 sec)

As you can see, the query has one more level of escaping than the data appears within the database and consequently how it comes out when querying for it. In your case, what is probably going on, is that you have magic quotes turned on and then you escape strings before embedding them in a query. This leads to double-escaping, tampering your data. The proper solution is to keep escaping strings as you do, but turn off magic quotes. And don't do anything on the data as it comes out of the database. Beware that data already in the system needs to be cleaned up first.