MySQL PHP Escape String '\' - Why is it not saved in the database with the backslash?

Question

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?

Answer 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.