Page 1 of 1

mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 4:39 am
by Mike2009
Dear forum readers,

As a beginner I'm trying to build a php (version 5.3.0) website connected to a MySQL database. As a security measure I intend to use mysql_real_escape_string for user input to reduce the risk of SQL-injection. My code looks like this:

Code: Select all

<?php
if ($_POST["submit"]=='submit')
    {
    include "db.php";
    $user = mysql_real_escape_string($_POST['user']);
    $sql = "INSERT INTO test (user) VALUES('$user')";
    mysql_query($sql);
    mysql_close($con);
    }
?>
<html><body><form action="" method="post"><input type="text" name="user"><input type="submit" name="submit" value="submit"></form></body></html>
But the output does not seem to give any escapes anywhere. I have tried echo to display the escaped string, but it seems to remain unchanged. Am I misunderstanding this function or did I something wrong?

Thanks,

Mike

Re: mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 5:26 am
by jackpf
Various types of quotes should be escaped. Is that not the case?

Re: mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 7:25 am
by Mike2009
jackpf wrote:Various types of quotes should be escaped. Is that not the case?
Yes, that is one of the things mysql_real_escape_string is supposed to do as far as I understood. If for instance, I post the value ' OR 1 this exact value is posted to the database - no slash in front of it as mentioned on some websites. If I leave out the mysql_real_escape_string from the script, the very same value is still stored in the database. So mysql_real_escape_string does not seem to do anything with the string?

Re: mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 7:37 am
by jackpf
That's odd...do you have magic quotes turned on?

Re: mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 8:15 am
by Mike2009
Dear Jack,

Magic quotes wasn't turned on. But turning it on does the job. My thanks to you.

It crossed my mind last night that this might cause the problem, but I reckoned it couldn't as the php.ini file states Also note, this feature has been deprecated as of PHP 5.3.0 and is scheduled for removal in PHP 6 where as mysql_real_escape_string isn't deprecated (in contrary to mysql_escape_string) and the documentation at php dot net on mysql_real_escape_string even notes If magic_quotes_gpc is enabled implying to me that the function does not depend on magic quotes. Apparantly it does :D

Thanks again.

Mike

Re: mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 9:31 am
by jackpf
You shouldn't rely on magic quotes...I was actually thinking that you may have them turned on, which would affect your data (it would be double escaped).

You should turn it back off and use mysql_real_escape_string() instead :)

Re: mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 9:57 am
by Mordred
Mike, you are seeing your (correct) script behave securely, as intended.

If you comment-out the escaping and enable error displaying, you will notice that your sample attack vector will indeed "break" the query.
Due to the fact that you successfully escape it, it goes in the database as a string value, which is what you wanted.

Re: mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 10:02 am
by jackpf
I thought he meant he was displaying it before he inserted it into the database and it wasn't being escaped :/

But yeah, reading back, it looks like I misunderstood.

Re: mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 10:05 am
by pickle
Mordred wrote:Due to the fact that you successfully escape it, it goes in the database as a string value, which is what you wanted.
+1

Output your query and you'll see the value escaped.

Re: mysql_real_escape_string does not seem to give escapes?

Posted: Fri Sep 25, 2009 12:47 pm
by Mike2009
Okay, I understand it a bit more now. I was expecting to see a backslash in the stored string, but I shouldn't as Mordred pointed out - the '\' only forces the parsers not to interpret the following character as a part of an instruction, but as a symbol instead. Back with magic quotes turned off it still works now. Using the following code indeed gives a different output between the escaped and un-escaped string:

Code: Select all

if ($_POST["submit"]=='submit')
    {
    include "db.php";
    $user = $_POST['user'];
    echo $user."<br \>";
    $user = mysql_real_escape_string($user);
    echo $user;
    mysql_close($con);
    }
Thank you guys for helping me :D