7

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?

Improve this question
3
  • 3
    \' is the escape sequence of '. Use \\\' for \'. Commented Feb 10, 2013 at 7:55
  • 3
    Because nobody needed that slash in the database? Commented Feb 10, 2013 at 8:06
  • Yeah... I think I understand that the '\' is not needed in the database - I guess I'm just trying to understand the best way to deal with the open text - so that I can use it again when the saved data is passed back to javascript - without breaking my javascript strings. Commented Feb 10, 2013 at 8:12

5 Answers 5

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

OK -Thanks, I understand. 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?
Yeah, I'd say translating it to the format you need is best done when you're passing it to what needs it (ie just when you're passing it off to the Javascript) If you decide JSON is a better format at some later time for example, you won't have to remove a lot of backslashes because they're in the database.
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.

Improve this answer

4 Comments

Thanks. Is it the best thing to keep it or not? I just don't want my javascript strings to terminate. people are entering free text into the form. I just want to make sure I don't break my javascript strings. thanks.
No, don't keep the backslashes in database. Your PHP/JavaScript should take care of escaping the data when needed.
OK - so you are saying escape it again when reading it from the database with PHP/JavaScript ? Yes?
Thank you for your help :) - boy that sure is a lot simpler than messing around with miscellaneous characters in the database. :)
0

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.

Improve this answer

Comments

0

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()

Improve this answer

Comments

0

try using this:

 Function EscapeBack(ByVal msData As Object) As String
        Return (Replace(msData, "\", "\\"))
    End Function

to use this:

EscapeQuote(filename.Text)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.