[SOLVED] confusion re addslashes, etc. for mysql

[squibs]

Hello.

 

I was always under the impression that after I escape dangerous string characters with addslashes or mysqli_real_escape_string, that they would be stored in the database with the slash included. I could later use striplslashes to get rid of them when retrieved from the db.

 

On my current project, I'm using mysqli_real_escape_string to prepare a sql statement. I get a statement like:

 

INSERT INTO (somefield) VALUES ('Dangerous \' Character')

 

But this actually puts "Dangerous ' Character" (slash is gone) in the database, whether I do it programatically or from phpmyadmin. If I want to get the string in I need to use "Dangerous \\'' Character" to get it stored in the db as "Dangerous \' Character".

 

What am I missing?

[Adam]

No. mysql_real_escape_string() only escapes quotes and a few other characters whilst entered into the database, so that MySQL basically knows it's not a break in the string. MySQL removes them. Consider..

 

'Dangerous ' Character'

 

MySQL would think the string ends after dangerous. The backslash is used to tell MySQL, or what ever the situation (PHP strings, regex, etc.) to use the literal meaning of the character. Also when you see two or more backslashes that means to use the literal meaning of the backslash.

[squibs]

Thanks MrAdam. That all makes perfect sense. Two things confuse me however:

1) If the slash doesn't get in, why does most of the literature I've read on PHP say to use stripslashes when pulling records out of the database?

2) In some editions of PHP and MySQL Web development By Luke Welling, Laura Thomson, it actually says When you use Addslashes(), the string will be stored in the database with the slashes in it". This can be seen in the Second Edition online version at books.google.com, but I've just seen that it is missing from the Third Edition which I own, so it looks like it was a mistake (and a big one!)

[Bjom]

You are missing that you can escape certain characters in PHP and for that the \ is used.

So \' is an escaped '.

 

This for example would not work, producing a syntax error, because  a ' is missing:

 

'Dangerous ' Character'

 

So to assign Dangerous ' Character to a string using single quotes you need to escape the inner single quote with backslash in front.

 

'Dangerous \' Character'

 

produces

 

'Dangerous ' Character'

 

if you need the backslash add another one, because the first was "used up" escaping the '

[Bjom]

addslashes and stripslashes is not the same as mysqli_real_escape_string. With the latter all that stripping and adding is done in the background for you.

 

You actually never should use addslashes/stripslashes for that purpose. Some literature is outright bad.

 

(also never use or die() )

[Adam]

Edit: Bjom beat me too it. The literature you've read is probably out of date now.

[squibs]

Thanks