Page 1 of 1
Data not escaping???
Posted: Fri Oct 26, 2007 2:24 pm
by alex.barylski
I have the following function:
Code: Select all
function createUser($udate, $sent, $status, $format, $email)
{
$email = mysql_real_escape_string($email);
$sql = sprintf('INSERT INTO users VALUES (0, %d, %d, %d, %d, "%s")', $udate, $sent, $status, $format, $email);
mysql_query($sql);
return mysql_insert_id();
}
I output the $sql and the following was shown:
Code: Select all
INSERT INTO users VALUES (0, 1193424189, 0, 0, 0, "o\'brien@hotmail.com")
Clearly the slash is added...however when I look at the data inside phpMyAdmin I see the slashes are not there????
Is it safe to assume that if my SQL statement echo'ed right before INSERT is escaped - then it's safely being added and I am not seeing slashes in PMA because of some funky server setup in PHP?
Any ideas as to what the heck is going on here?
Posted: Fri Oct 26, 2007 2:29 pm
by seppo0010
Hi,
I believe it's a MySQL intentional behaviour. Run this query in MySQL (not from PHP, straight in MySQL)
The result I'm getting is the single quote "'", so the slash is being used as a escape character
Posted: Fri Oct 26, 2007 2:35 pm
by alex.barylski
I am convinced it's a MySQL behavior...
I just ran the code directly:
Code: Select all
mysql_query('INSERT INTO users VALUES (0, 1193427175, 0, 0, 0, "o\'brien@hotmail.com")');
And even STILL when I looked at the result inside phpMyAdmin there are NO slashes added...
What is the purpose of this behavior, do you know?
Cheers and thanks

Posted: Fri Oct 26, 2007 2:51 pm
by Zoxive
Code: Select all
mysql_query('INSERT INTO users VALUES (0, 1193427175, 0, 0, 0, "o\'brien@hotmail.com")');
That ran directly threw PHP would think the \ is escaping the ', because you are using single quotes for the whole query.
Posted: Fri Oct 26, 2007 2:52 pm
by alex.barylski
Zoxive wrote:That ran directly threw Mysql PHP would think the \ is escaping the ', because you are using single quotes for the whole query.
Regardless of how I setup the SQL statement, using single quotes, double quotes, etc...the result is the same...no slashes in the stored data...so I am not sure that is the problem.
Ok, so I finally figured out how to invoke the mysql console...PITA
I ran the command you suggested and this is my result:
Code: Select all
mysql> SELECT " \' ";
+-----+
| ' |
+-----+
| ' |
+-----+
1 row in set (0.00 sec)
I am not sure what this is supposed to tell me though??? Why are single quotes allowed into the database without being escaped? Can you elaborate please?
EDIT: I have done some reasearch and it appears as though the escape characters added by mysql_real_escape_string are not included in the end result in the database table. These characters are used by the mysql_query() to prevent injection attacks but are not required in the result.
I'm not sure why I always thought the slashes were added to the DB field as well and were required to be removed via stripslashes when displaying to the screen. Perhaps I was adding slashes and magic quotes was already on and thus double escaping occurred and therefore the slashes were added to the data and required stripslashes before display.
Any ideas???
Posted: Fri Oct 26, 2007 3:21 pm
by RobertGonzalez
Select that user at the console and see what the data looks like.
phpMyAdmin is a PHP interface tool, so what is in the database may not be exactly what you see coming since it is written in PHP. View the data at the source (in the DB using the CLI) to see what is really in there.
Posted: Fri Oct 26, 2007 3:31 pm
by RobertGonzalez
Uh, nevermind. As I test and think about it, the slash is only there to allow entry into the database. It is not part of the data, so you should not expect to see the slash every again after insert.
Posted: Fri Oct 26, 2007 3:31 pm
by seppo0010
Hockey wrote: Perhaps I was adding slashes and magic quotes was already on and thus double escaping occurred and therefore the slashes were added to the data and required stripslashes before display.
Any ideas???
Probably that's correct... The escaping chars used are not supposed to show any where... you just need it to avoid injections
Posted: Fri Oct 26, 2007 3:38 pm
by alex.barylski
Everah wrote:Select that user at the console and see what the data looks like.
phpMyAdmin is a PHP interface tool, so what is in the database may not be exactly what you see coming since it is written in PHP. View the data at the source (in the DB using the CLI) to see what is really in there.
Tried that...same result...no escaping...
Is the escape character really needed in the database field though?
If I pass the string:
o'brien@hotmail.com to a database and it's escaped and it becomes:
o\'brien@hotmail.com then the mysql query engine simply uses it as an escape character and the result is stripped of slashes because they are not needed once the data has been escaped.
Like when you output a string to screen and escape a character or two...the slashes are not usually there...
I'm not sure why I always though the field in a database should always have that slash present...I figure it's probably because whenever I paid attention to that the server had magic quotes enabled and I never bothered stripping slashes so basically all my data was being escaped twice...but only the first round of escapes are used and the others are considered actual data. That is why I had to use stripslashes on data before being sent to screen.
Make sense? Can you validate or confirm my suspicions?
Posted: Fri Oct 26, 2007 4:00 pm
by Benjamin
Dude, mysql doesn't save the slashes. Just like if you..
It just tells MySQL to ignore it.
Posted: Fri Oct 26, 2007 4:01 pm
by RobertGonzalez
Read my follow up to the post you just quoted Hockey.
Posted: Fri Oct 26, 2007 4:48 pm
by alex.barylski
Thanks Everah, I have everything cleared up now.

Posted: Fri Oct 26, 2007 9:20 pm
by Zoxive
astions wrote:Dude, mysql doesn't save the slashes. Just like if you..
It just tells MySQL to ignore it.
That tells php to ignore it, so it doesn't think that it is the end of the string.