Lets explain SQL Injection OK?
SQL Injection is a form of XSS (Cross Side Scripting). The thought behind this, is users that are able to control your SQL server the way they want. A remedy for this problem is very simple, but first lets see a dangerous script.
CODE
<?php
// reading the post data from a form (login form)
$login = $_POST['login'];
$pass = $_POST['password'];
// query to search login data
$check = mysql_query("SELECT id FROM members WHERE username = '".$login."'
AND password = '".$pass."' LIMIT 1") or die(mysql_error());
// count the number of found rows
$count = mysql_num_rows($check);
// if its zero, no login combination found
if($count < 1){
echo'No valid login data!';
} else {
// login combination found!
$data = mysql_fetch_assoc($check);
$_SESSION['loggedin'] = $data['id']
echo'Logged in successfully.';
}
?>
// reading the post data from a form (login form)
$login = $_POST['login'];
$pass = $_POST['password'];
// query to search login data
$check = mysql_query("SELECT id FROM members WHERE username = '".$login."'
AND password = '".$pass."' LIMIT 1") or die(mysql_error());
// count the number of found rows
$count = mysql_num_rows($check);
// if its zero, no login combination found
if($count < 1){
echo'No valid login data!';
} else {
// login combination found!
$data = mysql_fetch_assoc($check);
$_SESSION['loggedin'] = $data['id']
echo'Logged in successfully.';
}
?>
Just a simple login script. You would think this would work right? Well this has quite a big problem. Think this is a admin login, so when logged in the user could delete everything he wants.
Ok lets take a look at the MySQL query when a user tries to login.
CODE
SELECT id FROM members WHERE username = 'NaRzY' AND password = 'myPassword' LIMIT 1
Now I try to login, no problem right?
Ok now think the user would write this as his password:
' OR username = 'NaRzY
In a Query this would look like this:
CODE
SELECT id FROM members WHERE username = 'ScriptKiddy' AND password = '' OR username = 'NaRzY' LIMIT 1
Now everyone is able to Login with my name!
There are many things you could do with this security flaw. This is just one easy but big problem.
The remedy:
Well this is actually very simple. I will handle 2 options.
Using "mysql_real_escape_string()" on the login variables. This will add a slash to a dangerous quote. So when using this with our login system, the password variable would become:
\' OR username = \'NaRzY
By using the slashes, MySQL will not read the quotes. So your problem is easily fixed!
You use mysql_real_escape_string like this:
CODE
<?php
// user
$user = mysql_real_escape_string($_POST['login']);
$pass = mysql_real_escape_string($_POST['login']);
?>
// user
$user = mysql_real_escape_string($_POST['login']);
$pass = mysql_real_escape_string($_POST['login']);
?>
Ok, quite simple right?
The function addslashes() versus mysql_real_escape_string()
Some of you know might already know something about SQL Injection and how to prevent it. But using the function addslashes() is not all you need! This function has a flaw, it can't handle a specific kind of quote. You can read all about it here. So always use mysql_real_escape_string()!
Now the second option. "magic_quotes_gp" is a config option for PHP. This will add slashes always when there is user input. So when a user uses GET of POST the variables will always contain slashed quotes. This is quite a bit more easy right?
But be warned! Not every server has this on, so just to be sure always use the mysql_real_escape_string function!
Jacob.