MySQL PHP Escape String '\\' - Why is it n

问题:

Confused about escape string and how it is stored in the database

In my MySQL call, I escaping a string with a backslash:

UPDATE `TABLE` SET `PERSONAL_BELONGINGS` = 'Tom\'s things'

But when I look in the phpadmin - the value was saved like this:

|Tom's things|

Why is the backslash not saved into the database? This causes problems when I read this value into javascript and then try passing it around - my javascript strings will terminate. This is why I escaped the character to begin with.

Why is MySQL removing the '\' backslash before it is saved into the database?

If not saving it in the database with the '\' - What then is best way to deal with this as you are passing it back to the javascript as a string? To escape it again when passed as a string to javascript?

回答1:

Let me begin by saying that you should really not really store data in any particular escaped format in the database, you'll regret it later if you need to extract it in another format or search the data for some reason later. The format you're saving now looks good, and adding backslashes for Javascript is better done in code when passing the data to the actual Javascript.

Now this is why it currently behaves like it does;

In the string 'Tom\'s things', \' is a character escape sequence and is really only used to let MySQL understand how to parse the SQL string, it's never saved as is to the database.

The reason you escape the character ' in the SQL statement you're showing to begin with is that otherwise MySQL has no way of knowing that the string does not end at the single quote after 'Tom.

If you use MySQLi or PDO prepared statements instead of building your SQL statements yourself, MySQL will let you save values entirely unchanged without having to ever escape anything. This is definitely the preferred option, since the MySQL API that does not support prepared statements is deprecated anyway.

回答2:

The backslash is treated as an "escape character". If there was no backslash your string would end at Tom but the remaining s things would cause a syntax error.

The \ tells MySQL to not treat the escaped ' as a string delimiter but carry on until the next unescaped ' is found.

This escape character is only used for the query purposes and is not treated as part of the string you want to update.

Like Alvin suggested in comments, if you want to keep the backslash in your database you have to add it by adding another escaped backslash, i.e. \\. This would make your query look like:

UPDATE `TABLE` SET `PERSONAL_BELONGINGS` = 'Tom\\\'s things'

And the data in database would look like:

|Tom\'s things|

You can read more about string literals and escaping special characters in MySQL Manual

It's worth noting though, that storing an already escaped string in database is a bad practice. You should take care of escaping special characters in your code.

回答3:

Because, in MySQL, the backslash is an escape character (just like it is in PHP). You have to escape the backslash itself to store it -- so, \\ would store a single backslash. \\\' would store a backslash followed by a quote, since the first backslash escapes the second, and the third escapes the quote.

回答4:

This causes problems when I read this value into javascript

Surely this doesn't.
Then you lock your money in a safe, you can blame a safe maker if it gets robbed.
But as soon as you get your money back and it got stolen - you cannot still blame a safe for this.
It is yours responsibility now.

So, if you need your data escaped for javascript - just escape it.
But of course not by using mysql_escape_string()