SQL Injection
Example
Do not trust browser defined variables to properly escape special characters.
$query = "SELECT * FROM users WHERE username='" . $username . "' AND password='" . $password . "'";
// the record_exists function is defined elsewhere
if (record_exists($query))
{
echo "Access granted";
}
else
{
echo "Access denied";
}
The SQL statement can be modified in a malicious manner to allow logging in as an administrator.
check.php?username=admin&password=a%27+OR+1%3Di%271
Translates to:
password='a' OR 1='1'
This can be thwarted by using the configuration variable magic_quotes_gpc. However, magic quotes only applies to GET, POST, and cookie data. It does not protect against data retrieved by other means (files, database records, etc.).
It is possible to bypass magic_quotes_gpc, so do not rely on just magic_quotes_gpc alone.
Content Control
Variables should be checked for their content to ensure the content is appropriate. Use addslashes to properly escape special characters.
The the passwords (user input and database entry) can also be compared in PHP after the database query. (remember to always store passwords hashed in the database).
/*
password is encrypted with md5 in database, and
database-query/fetch is already done....
*/
//tell the script to die to avoid confusing if-loops
if ( $recordset[0]['password'] != md5($password) )
{
die("Access denied.");
}
echo "Access granted.";
Database Abstraction Layer
Another option is to use a Database Abstraction Layer such as what PEAR provides and prepared statements which will properly quote/escape values.
$db = new db();
$sth = $db->prepare('SELECT * FROM users WHERE username=? AND password=?');
$result = $db->execute($sth, array($username, $password));
