Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?
like image 782
teaforchris Avatar asked Oct 05 '09 20:10

teaforchris


People also ask

What is the use of mysql_real_escape_string () function?

The real_escape_string() / mysqli_real_escape_string() function escapes special characters in a string for use in an SQL query, taking into account the current character set of the connection.

Is mysql_real_escape_string deprecated?

This extension was deprecated in PHP 5.5. 0, and it was removed in PHP 7.0.

What is Stripslashes PHP?

The stripslashes() function removes backslashes added by the addslashes() function. Tip: This function can be used to clean up data retrieved from a database or from an HTML form.

Why is the use of mysqli_real_escape_string () so important?

Using these functions makes your site less vulnerable to SQL injection attacks, where an attacker puts SQL syntax into a form field to compromise your site. mysqli_real_escape_string() "escapes" special characters so that MySQL interprets them as literal string characters rather than operators in the query.


2 Answers

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);
like image 88
TJ L Avatar answered Oct 05 '22 08:10

TJ L


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.

like image 22
troelskn Avatar answered Oct 05 '22 10:10

troelskn