MySQL Escape Backslash

Question

I have a series of regular expressions that include escape characters that I need to store in a mysql table.

If I don't escape the backslash, it is eliminated.

I have tried escaping the backslash in PHP using mysql_real_escape_string, addslashes, str_replace, and every time the database stores a double backslash rather than a single one.

I have also tried using sed in bash to escape the backslash, but it also prints 2.

Example:

$regex = "stackoverflow\.com\/questions\/ask";
$query_text = addslashes($regex);
$query = "INSERT INTO my_table (url) VALUES ('$query_text')";

me@server:$ echo "select * from my_table" | mysql -uuser -Ddatabase -p'password'

stackoverflow\\.com\\/questions\\ask

Any ideas as to what I am doing wrong?

useful link - http://stackoverflow.com/questions/4731144/the-mysql-use-of-addslashes — ajreal, Sep 02 '11 at 18:22
**Only ever use `mysql_real_escape_string` to escape injected $vars in your database, never combine or replace it with anything else** Or even better, use PDO. — Johan, Sep 02 '11 at 18:25

Arnaud Le Blanc · Accepted Answer · 2011-09-02T18:31:27.357

The slashes are escaped in the mysql client's console output; not in the database ;)

Try running the client interactively:

mysql -uuser -Ddatabase -p'password'
mysql> select * from my_table;
+------------------------------------+
| x                                  |
+------------------------------------+
| stackoverflow\.com\/questions\/ask |
+------------------------------------+

And non-interactively:

mysql -uuser -Ddatabase -p'password' <<< "select * from my_table"
stackoverflow\\.com\\/questions\\/ask

Use --raw to disable this escaping:

mysql -uuser -Ddatabase -p'password' --raw <<< "select * from my_table"
stackoverflow\.com\/questions\/ask

From the manual:

--raw, -r

For tabular output, the “boxing” around columns enables one column value to be distinguished from another. For nontabular output (such as is produced in batch mode or when the --batch or --silent option is given), special characters are escaped in the output so they can be identified easily. Newline, tab, NUL, and backslash are written as \n, \t, \0, and \. The --raw option disables this character escaping.

BTW mysql_real_escape_string was the right escape function to use.

Great, this was the issue. Interestingly though I have an explode() to break the string into an array. If I mysql_real_escape_string($array[1]) it does not escape at all. Instead I was able to mysql_real_escape_string before the explode() and it worked fine. Thanks for your response. — Sean, Sep 02 '11 at 18:48

MySQL Escape Backslash

1 Answers1