[RESOLVED] mysql_real_escape_string()

VisionsOfCody

I've been reading up in the manual about this function and they give a really simple example:

<?php
// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
   OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
           mysql_real_escape_string($user),
           mysql_real_escape_string($password));
?>

but i really don't understand what's happening in the query with mysql_real_escape_string($user)

why do they do that ?

if someone could give me a very simple explanation i would be very grateful as i need to learn all this stuff

thanks

bradgrafelman

They're trying to prevent SQL injection attacks.

Basically, it's sanitizing data that was provided by (or could have been altered by... HINT: anything from HTTP headers in the $SERVER array, $GET data, $_POST data, etc.) the outside world. It does this by escaping (or adding a backslash before) certain characters that can potentially alter or just break your SQL query string.

VisionsOfCody

thanks for your reply

what i don't understand is why there's mysql_real_escape_string($user) as an argument in the sprintf() function

I mean - ok there's the normal SELECT query with the wildcards etc but what's the purpose of the other two args ?

VisionsOfCody

ok I've got it - I hadn't realized that those %s things were placeholders for the variables that are in the args

thanks

Piranha

Let's make an easy example. You have a login script that takes one username and one password from $_POST. Here I add them in normal variables, but you get the point. This is how you want it to work (without using mysql_escape_string):

$username = "pelle";
$password = "kalle";
$sql = sprintf("SELECT COUNT(*) FROM login WHERE username = '%s' AND password = '%s'",
    $username,
    $password);
echo $sql;
// The echo on next row:
SELECT COUNT(*) FROM login WHERE username = 'pelle' AND password = 'kalle'

After that part the code simply checks if there is any user with that username and password, and if there are then you get access. In this case this works, but if we change the $_POST data a little it may be like this:

$username = "' OR '' = '";
$password = "' OR '' = '";
$sql = sprintf("SELECT COUNT(*) FROM login WHERE username = '%s' AND password = '%s'",
    $username,
    $password);
echo $sql;
// The echo on next row:
SELECT COUNT(*) FROM login WHERE username = '' OR '' = '' and password = '' OR '' = ''

Now the person will get logged in without having an account. With mysql_real_escape_string slashes are added:

$username = "' OR '' = '";
$password = "' OR '' = '";
$sql = sprintf("SELECT COUNT(*) FROM login WHERE username = '%s' AND password = '%s'",
    mysql_real_escape_string($username),
    mysql_real_escape_string($password));
echo $sql;
// The echo on next row:
SELECT COUNT(*) FROM login WHERE username = '\' OR \'\' = \'' and password = '\' OR \'\' = \''

It won't read the ' signs that the user inputted as the end of the string anymore, and the user won't get access.

VisionsOfCody

that's absolutely crystal clear

thanks !