What is an SQL Injection attack?

An SQL Injection attack happens when a user gives your script data crafted to change your SQL to do something you didn't intend it to do. Consider this SQL:

$sql = 'SELECT * FROM users WHERE username="'.$username.'"
AND password="'.$password.'"';
if (mysql_query($sql)) {
  echo 'Logged in!';
}

It looks fine, but what if a user submitted this as their password:

" OR 1=1 OR ""="

This would cause the SQL to read:

SELECT * FROM users WHERE username="" AND password="" OR 1=1 OR ""=""

which would allow the attacker to get into your system without even knowing a login!

On many databases you can also run multiple queries by putting a semicolon in the SQL you pass. Consider this password:

"; DELETE FROM users WHERE ""="

This would run the first query, which would probably find no records, but it would then run the DELETE query which would delete all of yoru users. Note that this could also be used to delete any other data in yoru system or to change your data or insert a new user with admin priviledges.

To protect against this, you need to "escape" the variables you put into your SQL. When using Mysql you can do this:


$sql = 'SELECT * FROM users WHERE
username="'.mysql_real_escape_string($username).'" AND
password="'.mysql_real_escape_string($password).'"';


If you're using PEAR::DB you can do this (this will work for *any* database system that DB supports):

$sql = 'SELECT * FROM users WHERE
username='.$db->quoteSmart($username).' AND
password='.$db->quoteSmart($password);