Page 1 of 1

Strange (?) results of mysql_real_escape_string

Posted: Sat Oct 31, 2009 12:51 pm
by klubbit
I'm curious if this is what mysql_real_escape_string is supposed to do. It seems to escape backslashes correctly, but it escapes an apostrophe by adding a second apostrophe before it, and it doesn't escape double quotes at all. e.g., if I enter "\x00, \n, \r, \, ', " and \x1a" into my form, the database stores "\\x00, \\n, \\r, \\, '', " and \\x1a".

Also, am I correct in assuming that an escaped character only counts as one character towards the max? For example, a varchar(5) field can store \'\'\'\'\' (10 characters), in which case a line break would count as 2 characters towards the max.

Thanks.

Re: Strange (?) results of mysql_real_escape_string

Posted: Sat Oct 31, 2009 5:33 pm
by Skara
php.net wrote:mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.
Escaping a character means adding a backslash. It shouldn't be adding second quotes.

As for the second question, \' is a single character. I've actually never tried that out, but I can't imagine it filling up two spaces.

Re: Strange (?) results of mysql_real_escape_string

Posted: Sat Oct 31, 2009 7:50 pm
by klubbit
Yeah, that's what I thought it was supposed to do. I did some more testing and found out that mysql_real_escape_string is functioning correctly. It's MySQL that changes \' to '' and \" to " -- even if I run a query through phpMyAdmin. I assume it's still safe from injections though since \" is what gets sent to MySQL. I also assume this is the standard behavior for MySQL since I'm using WAMP with default settings. If this is the case, it seems one would be susceptible to a previously attempted SQL injection if they run a query that pulls the injection from the database, then use that value in a second SQL query without escaping it. Right? :?

Re: Strange (?) results of mysql_real_escape_string

Posted: Sat Oct 31, 2009 7:58 pm
by Skara
klubbit wrote:It's MySQL that changes \' to '' and \" to " -- even if I run a query through phpMyAdmin.
That doesn't sound right to me. The second one sounds great, remember that \" will always display as " (since that is, in fact, what it is). \" is generated by \\\".
Therefore, \' should appear as ', not ''. That seems really awkward to me.

Re: Strange (?) results of mysql_real_escape_string

Posted: Sun Nov 01, 2009 1:44 am
by John Cartwright
Sounds like you have register globals and should be disabled (check the manual for why).

Re: Strange (?) results of mysql_real_escape_string

Posted: Sun Nov 01, 2009 4:31 pm
by klubbit
Skara wrote:
klubbit wrote:It's MySQL that changes \' to '' and \" to " -- even if I run a query through phpMyAdmin.
That doesn't sound right to me. The second one sounds great, remember that \" will always display as " (since that is, in fact, what it is). \" is generated by \\\".
Therefore, \' should appear as ', not ''. That seems really awkward to me.
Hmm, I think maybe all this confusion is due to me misunderstanding what's stored in the database. I thought the database literally stored \", \', \r, \n, etc., but it sounds like it stores each one as a single character (", ',
) [that's a line break ;)]. This is what I saw when I looked at the database in phpMyAdmin, but I thought the default view was just that way for readability. When you export the database it shows everything as plain text using SQL commands, which is where is was seeing ", '', \r, and \n. Two single quotes is an escaped single quote when the SQL command is surrounded by single quotes. Same thing if you replace "single quotes" with "double quotes" in that sentence. See http://dev.mysql.com/doc/refman/5.0/en/ ... yntax.html

So it's also true that an escaped character only counts as one character towards the max, with a line break counting as 2 (\r\n). I did some testing to verify this.

Also, I have register_globals off, thanks.