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?

Statolith

Notice: mysql()*is deprecated as of PHP 5.5.0, it is not recommended for writing new code as it will be removed in the future. instead, use either the mysqli or the PDO – Joselynjoseph

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);

Sasha

"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." Yeah that was beginning to happen! – Statolith

Fantastic answer, problem solved. Thankyou for going through the logic as to where the three slashes were coming from, makes sense now. – Statolith

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.

Subclinical

I've run an SQL query using a MySQL app directly on the database and the above string is returned. So it appears to me it <em>is</em> being stored in the database with three slashes. – Statolith

I don't know exactly what you mean by that, but slashes are used because you're embedding data within code. Just like string literals in php source code. The same rules apply. – Subclinical

The slashes need to escape the data are not stored in the DB. If you see slashes then they must be extra. – Schweinfurt

Answer 3

If get_magic_quotes_gpc() is off in SERVER, so only we can use

$data= mysql_real_escape_string($_POST['data']);

if get_magic_quotes_gpc() is on in SERVER, we have to use

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

otherwise add two backslashes with your data.

Also another solution is we can use stripslashes($data) while fetch from datadase if we use only use mysql_real_escape_string($_POST['data']);

Underbody

stripslashes and mysql_real_escape_string cannot be used together. The former one have to be used at the very beginning of the script. to clean ALL data, not only one going to DB. While the latter one have to be used immediately before adding a string (and only string!) into query. And the last one is not a solution at all – Testis