Data not escaping???

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Data not escaping???

Post 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?
User avatar
seppo0010
Forum Commoner
Posts: 47
Joined: Wed Oct 24, 2007 4:13 pm
Location: Buenos Aires, Argentina

Post by seppo0010 »

Hi,
I believe it's a MySQL intentional behaviour. Run this query in MySQL (not from PHP, straight in MySQL)

Code: Select all

SELECT " \' "
The result I'm getting is the single quote "'", so the slash is being used as a escape character
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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 :)
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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 :P

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???
Last edited by alex.barylski on Fri Oct 26, 2007 3:24 pm, edited 1 time in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
seppo0010
Forum Commoner
Posts: 47
Joined: Wed Oct 24, 2007 4:13 pm
Location: Buenos Aires, Argentina

Post 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
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Dude, mysql doesn't save the slashes. Just like if you..

Code: Select all

echo 'She isn\'t stupid';
It just tells MySQL to ignore it.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Read my follow up to the post you just quoted Hockey.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

Thanks Everah, I have everything cleared up now. :lol: :oops:
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post by Zoxive »

astions wrote:Dude, mysql doesn't save the slashes. Just like if you..

Code: Select all

echo 'She isn\'t stupid';
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.
Post Reply