What is SQL Injection?
is the act of injecting some arbitrary SQL code into a query, usually by using unescaped characters like quotes, to trick the application into doing something it was not meant to do. For example, this might mean logging in with administrator privileges without a password, or deleting records.
It's easier to understand if you have an example. Pretend you have a login form that takes a username and password. The SQL query that authenticates the user, by looking it up in the database, might look like this:
SELECT * FROM user WHERE username='$user' AND password='$pass'
If the user input is not properly checked and sanitized, then an attacker can inject SQL into the query and gain access without requiring a password (red text would be user inputted text):
SELECT * FORM user WHERE username='admin' AND password='blah' OR 1='1'
By inputting a single quote after blah
, the attacker could inject any code he wanted.
How do I Prevent SQL Injection?
You have to check and sanitize any user input you use. Rule number one for programmers is never trust the user
If you've got a select box with numbers from 1-100, make sure the form data the user sent is actually a number! (Note: is_numeric checks for any numerical value, including floats etc. You will still want to cast it to the target type)
if(!is_numeric($_POST['fav_num'])) die('Please choose a number.');
// Or simply cast it into a number
$fav_num = (int)$_POST['fav_num'];
Sanitizing the data before inserting it into the database means escaping any characters that could change the query. A lot of programmers think this means using addslashes()
to escape quotes -- but this may
not be enough. Databases that use different character sets (for example) will still be vulnerable if you only escape quotes.
Quoting the correct characters differs from DBMS to DBMS, so be careful. For MySQL, you can use the mysql_real_escape_string
function to make user data 100% safe.
If you use the DB PEAR package
, then use the quoteSmart()
method. If you use another database abstraction class, be sure to check its documentation for a similar method.
$user = mysql_real_escape_string($_POST['user']);
$pass = mysql_real_escape_string($_POST['pass']);
$sql = "SELECT * FROM user WHERE username='$user' AND password='$pass'";
// Or use the quoteSmart() method from PEAR::DB
$user = $db->quoteSmart($_POST['user']);
$pass = $db->quoteSmart($_POST['pass']);
$sql = "SELECT * FROM user WHERE username=$user AND password=$pass";
/* Note that the quoteSmart() method automatically
adds quotes around the value when it is needed,
so you do not need to put them directory into your