[SOLVED] mysql_real_escape_string

[phpfan]

Hi everybody,

 

I have try to use mysql_real_escape_string in order to prevent any sql injection attacks but i am getting some unexpected results. When i test the variable which is holding the string value, before i apply the above function i get the following result \'Tom\' (the string tha i pass was 'Tom'). After that i apply the mysql_real_escape_string i get the following result \\\'tom\\\'. I was expecting to get the first result after the application of mysql_real_escape_string function. Can you please explain me why i am getting these results...  ???

 

I am using PHP 5.2.1 and MySQL client version: 5.0.33

 

[wildteen88]

You most probably have a setting called magic_quotes_gpc enabled which is why you get \'Tom\' before applying mysql_real_escape_string.

 

You're best of checking the status of magic_quotes before using anyescape functions. SO create a function like this:

 

function makeSQLSafe($str)
{
    // check the status of magic_quotes_gpc, if it this returns true 
    // we remove the escaped characters. Allowing for the real escaping 
    // to be done via mysql_real_escape_string
    if(get_magic_quotes_gpc())
    {
        // remove the slashes.
        $str = stripslashes($str);
    }

    $str = mysql_real_escape_string($str);

    return $str;
}

// example usage:
$username = makeSQLSafe($_POST['username']);

[KevinM1]

Not to hijack the thread, but what does the escaping actually do?  And would the info need to be 'de-esaped' if retrieved from the database?

[wildteen88]

the mysql_real_escape_string function escapes any characters within a string to prevent SQL injection attacks. Its is recommended to use this function when dealing with any data that is being inserted into a database. Do not use raw variables in SQL queries example this is very bad:

 

$sql = "SELECT * FROM users WHERE username='$_POST['username']' AND password='$_POST['password']'";

 

As that query will allow for SQL Injection attacks. For example a malicous attacker may insert special SQL code in to the username field like the following:

' OR 1=1 --

 

What that will do is change the query to this:

SELECT * FROM users WHERE username='' OR 1=1 -- ' AND password=''

 

That now totally changes the query sent to MySQL. It will tell MYSQL to select the row from the users table where the username is blank OR if 1=1. No 1 always equals to 1 so MySQL will fetch the first row in the users table. This row will more than likely be the one that contains the administrators username and password . So now the malicious attacker is now logged into your web site as the administrator!

 

However if you did this:

$user = mysql_real_escape_string($_POST['username']);
$pass = mysql_real_escape_string($_POST['password']);

$sql = "SELECT * FROM users WHERE username='$user' AND password='$pass'";

 

And you entered ' OR 1=1 -- into the username field. The query now becomes:

SELECT * FROM users WHERE username='\' OR 1=1 --' AND password=''

 

Which tells MySQL to select the row from the users that where the username matchs ' OR 1=1 -- and the password is blank. That is now much more safer.

 

To learn more about SQL Injection do a google search and you will get lots of articles/tutorials that explain what it means and how to prevent it.

 

 

[phpfan]

Thank you wildteen88 for the help, but i have a last question.. Is there any difference from using magic quotes or mysql_real_escape_string? The final result seems to be the same, both of them output \'Tom\'

[wildteen88]

mysql_real_escape_string escapes more characters than magic quotes.