Hi.
Generally, to avoid SQL Injection, you need to validate any piece of data that is going to go into a dynamically created SQL query.
For example, if I were to accept a username and a password from a HTML form to be checked against a database of users, you may be tempted to do this:
-
<?php
-
$name = $_POST['username'];
-
$pwd = $_POST['password'];
-
-
$sql = "SELECT `UserID` FROM `User`
-
WHERE `UserName` = '{$name}'
-
AND `Password` = '{$pwd}'";
-
$result = $mysql_query($sql) or die(mysql_error());
-
-
if($result && mysql_num_rows($result) > 0) {
-
echo "User is valid!";
-
}
-
else {
-
echo "User is invalid";
-
}
-
?>
Fairly simple, right?
And this would work perfectly for typical users.
But consider what would happen if you were to pass this as the password:
Now your query becomes:
- SELECT `UserID` FROM `User`
-
WHERE `UserName` = 'something'
-
AND `Password` = '' OR 1='1'
Which would return every single row in the table and pass the validation, regardless of which username you passed.
The safest way to prevent this sort of tampering is to make sure the data passed does not contain any characters that might mess with the query.
Which is exactly what the mysql_real_escape_string function does.
If I were to change lines number 2 and 3 in the example above like so:
-
$name = mysql_real_escape_string($_POST['username']);
-
$pwd = mysql_real_escape_string($_POST['password']);
And pass it the same bogus data I did before, this is the query that would get executed (from PHP's point of view):
- SELECT `UserID` FROM `User`
-
WHERE `UserName` = 'something'
-
AND `Password` = '\' OR 1=\'1'
As you see, the quote-marks inside the Password field are now escaped, which means they are now
a part of the value, rather than used to close the string and add an extra OR clause.
So, unless there is actually a user named 'something' using that password, the query would return 0 rows and the validation would fail.
Another simple way to lessen the risk of simple tampering like that is to group boolean expressions together. Consider if my query had looked like:
-
$sql = "SELECT `UserID` FROM `User`
-
WHERE (`UserName` = '{$name}')
-
AND (`Password` = '{$pwd}')";
Passing the bogus data, un-validated, into this would produce:
- SELECT `UserID` FROM `User`
-
WHERE (`UserName` = 'something')
-
AND (`Password` = '' OR 1='1')
Which would also fail if the username is invalid.
Granted, this is far from being adequate by itself, but every little bit helps :]
