Geek Answers Handbook

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

Confused by the escape line and how it is stored in the database

In my MySQL call, I avoid the backslash line:

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

But when I look in phpadmin, the value was saved as follows:

 |Tom things|

Why is the backslash not stored in the database? This causes problems when I read this value in javascript and then try to pass it - my javascript lines will stop. That is why I escaped the symbol to begin with.

Why does MySQL remove the backslash "\" before it is stored in the database?

If you don’t save it to the database using "\" - then what is the best way to handle this, since you pass it back to javascript as a string? To avoid it again when it is passed as a string in javascript?

+6
source share
4 answers

Let me start by saying that you really do not need to store data in any particular shielded format in the database, you will regret it later if you need to extract it in another format or later search for data for any reason. The format you save now looks good, and adding backslashes for Javascript performs better in code when passing data to actual Javascript.

Now that's why he behaves the same way he does;

The line 'Tom\ things' \' is a character escape sequence and is actually only used so that MySQL can figure out how to parse the SQL string, it is never saved as it is in the database.

The reason you escape the ' character in the SQL statement that you show for starters is because otherwise MySQL does not know that the string does not end in a single quote after 'Tom .

If you use prepared MySQLi or PDO statements instead of building your own SQL queries on your own, MySQL will allow you to completely save values ​​without any problems. This is certainly the preferred option, since the MySQL API, which does not support prepared statements, is deprecated anyway.

+8
source

The backslash is treated as an escape character . If there was no backslash, your line would end with Tom , and the remaining s things cause a syntax error.

\ tells MySQL not to treat escaped ' as a line separator, but to continue until the next unescaped ' is found.

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

Like Alvin suggested in the comments, if you want to keep the backslash in your database, you have to add it by adding another escape-slash, i.e. \\ . This will make your request look like:

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

And the data in the database will look like this:

 |Tom\ things|

You can read more about string literals and escape special characters in the MySQL Guide

However, it is worth noting that storing an already escaped string in the database is bad practice. You must take care of the evacuation of special characters in your code.

+3
source

Because in MySQL, the backslash is an escape character (as in PHP). You must avoid backslashes to preserve them, so \\ will store a single backslash. \\\' keep the backslash followed by a quote, since the first backslash defeats the second, and the third escapes the quote.

+1
source

This causes problems when I read this value in javascript

Of course it is not .
Then you lock your money in a safe, you can blame a safe producer if they rob him.
But once you get your money back and stolen it, you still can't blame it on the safe.
Now it is yours .

So, if you need your data hidden for javascript - just get away from it.
But of course not using mysql _escape_string ()

+1
source

More articles:


All Articles