addslashes() and MySQL question

slashes not appearing in the database

[Cherewest]

I just ran across a weird thing.

I have a small function that basically checks for magic quotes and if it is OFF, the functions escapes characters using addslashes().

The data is then added to a MySQL database.

When I look at the data in the database table using phpmyadmin, there are no slashes ahead of say single quotes.

That is I'd expect to see
BOB\'s cats.

Instead I see
BOB's cats.

I know the function works and I know that magic_quotes_gpc() is OFF.

Shouldn't there be slashes in the data?

This is PHP 4.3.10 with MySQL 4.0.22.

Thanks
Chere

[edited by: Cherewest at 11:51 pm (utc) on Feb. 8, 2007]

[mcavic]

No, addslashes adds slashes to the data that you're sending to MySQL, but MySQL removes them before storing the data in the database. When you retreive the data, you can then display it as is.

[Cherewest]

Thanks!

How does MySQL know what to strip out? What if I want to have a slash? Some data was entered directly into the database (using phpmyadmin) with an escaped apostrophe and it didn't strip that. What mysql function makes the "decision"? I'd like to look it up. I've been searching the manual but I'm hitting a wall.

Thanks again!

[mcavic]

Basically, MySQL interprets \' as a piece of data, while interpreting a single ' as part of the SQL statement syntax.

If you want to store a \ then \\ should do it.

And if you want to store \' then it would be sent to MySQL as \\\' I believe. That is, an escaped slash followed by an escaped apostrophe.

[dreamcatcher]

Or use mysql_real_escape_string [uk2.php.net].

dc

[Cherewest]

Thanks all.

I am definitely going to abandon addslashes() in favor of mysql_escape_string.

Thanks again.

[mcavic]

What's the difference between addslashes and mysql_escape_string? They both escape quotes.

[eelixduppy]

What's the difference between addslashes [us2.php.net] and mysql_escape_string [us2.php.net]?

Well for one, they do not escape the exact same characters. And if you are talking about the case of mysql_real_escape_string [us2.php.net], it takes into accounts the charset of the database.

Generally, you should use mysql_real_escape_stirng or mysql_escape_string to escape query variables.

[mcavic]

Ok, I read the manual again.

mysql_real_escape_string() ... prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

The \ and ' are the most important, but the others should be escaped too.

[henry0]

Did you notice how many threads are here on the topic:
get_magic_quotes_gpc()
pending on if it's On or Off in your php.ini

Since a while I decided to code it in order to care for both models On or Off
Although for security reason it is better to have it Off and code accordingly
so,
One) check if get_magic_quotes_gpc()
if yes: use stripslashes $aaa=stripslashes($_POST['aaa'];
Two) else
$aaa=$_POST['aaa'];
Three) the query
using sprintf("INSERT into mytable('aaa')
VALUES('%s')",
mysql_real_escape_string($aaa);