Quantumstate
06-28-2008, 01:47 PM
This is probably the largest security problem with scripts which use a database so I thought I would write a brief tutorial on what it is, how it is done and how to stop it happening.
Basically SQL injection is when a person enters some input into a script such as a username which contains some sql code. This sql code is then run by the server and can potentially do harmful things to your database. This is only possible if your script does not protect the application from this happening.
The easiest way to explain this is with an example. Scripts are coded differently so sql injection attacks usually need to be tailored to the application being attacked. There would often be several stages to an attack, perhaps first getting information about the system and then actually causing damage.
In my example I will use a simple database login form. The table will have two fields, the username and password. We will assume that the username is known, this is a likely scenario since most applications allow the username to be shown.
Here is the mysql to create the table with a couple of users in the table.
CREATE TABLE IF NOT EXISTS `users` (
`username` text collate latin1_general_ci NOT NULL,
`password` text collate latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `users` (`username`, `password`) VALUES
('admin', 'Ghksu782'),
('foo', 'qple56lss');
so we have a couple of users with random passwords. The login script for this would be very simple as well. The script here is insecure so I can demonstrate the attack. It is all on one page just for convenience.
login.php:
<?php
if (isset($_POST['username'])){
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database_name');
$query = "SELECT * FROM users WHERE username = '{$_POST['username']}' AND password = '{$_POST['password']}'";
echo $query;
echo '<br/>';
$result = mysql_query($query);
if(mysql_num_rows($result)>0){
echo "You logged in successfully";
}else{
echo "invalid username or password";
}
}
?>
<br/>
<form action="login.php" method="POST">
Username<input type="text" name="username">
Password<input type="password" name="password">
<input type="submit" value="Login">
</form>
Most of this is pretty basic, first it checks if a username was sent from the form and if there was then it connects to the database (you will need to change the username, password and database_name values if you want to test the script). A simple query is created using the values from the form and then the script checks if more than one row was returned. Then a message is outputted just to say if the login was successful.
I also told the script to output the mysql query so that it is clear what is happening. I tested the script using normal users and password and got this output:
SELECT * FROM users WHERE username = 'admin' AND password = 'blah'
invalid username or password
SELECT * FROM users WHERE username = 'admin' AND password = 'Ghksu782'
You logged in successfully
So you can see that the script works, it rejected my bad password and accepted the good one. Now of course the attacker will not know the password so this is where the mysql injection comes in.
This time we try and login with the username of admin and the password of ' OR 'a'='a
Now we can look at the generated query and see what has happened.
SELECT * FROM users WHERE username = 'admin' AND password = '' OR 'a'='a'
You logged in successfully
Here you can see our attack has worked and we have logged in successfully. The mysql query shows how it was done. The first ' in our password closed off the string which should have contained the password so the query now checks to see if the password equals nothing which is false so we shouldn't be able to log in. This however is bypassed by the next code which says OR 'a'='a' which will always be true. Hence it will return the row because of the OR. note that the last ' was provided by the php script so we didn't include it in our "password".
To prevent this happening there is a function mysql_real_escape_string() which will escape any characters which could be used in mysql injection. So the ' will be replaced by what is called an escape sequence \' which tells mysql that it is part of the string rather than the end of the string. magic quotes in php can also help prevent attacks but there are problems with this so it should not be relied upon, also many people will not have it enabled. php6 will have it disabled by default. See http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string for more details.
So the sample script would be modified to:
<?php
if (isset($_POST['username'])){
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database_name');
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
echo $query;
echo '<br/>';
$result = mysql_query($query);
if(mysql_num_rows($result)>0){
echo "You logged in successfully";
}else{
echo "invalid username or password";
}
}
?>
<br/>
<form action="login.php" method="POST">
Username<input type="text" name="username">
Password<input type="password" name="password">
<input type="submit" value="Login">
</form>
So always remember to check your input is safe when using mysql queries. bear in mind that I could have placed any mysql code in the injection to insert new rows, delete the table or modify the values. Using UNION this can get very dangerous.
Basically SQL injection is when a person enters some input into a script such as a username which contains some sql code. This sql code is then run by the server and can potentially do harmful things to your database. This is only possible if your script does not protect the application from this happening.
The easiest way to explain this is with an example. Scripts are coded differently so sql injection attacks usually need to be tailored to the application being attacked. There would often be several stages to an attack, perhaps first getting information about the system and then actually causing damage.
In my example I will use a simple database login form. The table will have two fields, the username and password. We will assume that the username is known, this is a likely scenario since most applications allow the username to be shown.
Here is the mysql to create the table with a couple of users in the table.
CREATE TABLE IF NOT EXISTS `users` (
`username` text collate latin1_general_ci NOT NULL,
`password` text collate latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `users` (`username`, `password`) VALUES
('admin', 'Ghksu782'),
('foo', 'qple56lss');
so we have a couple of users with random passwords. The login script for this would be very simple as well. The script here is insecure so I can demonstrate the attack. It is all on one page just for convenience.
login.php:
<?php
if (isset($_POST['username'])){
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database_name');
$query = "SELECT * FROM users WHERE username = '{$_POST['username']}' AND password = '{$_POST['password']}'";
echo $query;
echo '<br/>';
$result = mysql_query($query);
if(mysql_num_rows($result)>0){
echo "You logged in successfully";
}else{
echo "invalid username or password";
}
}
?>
<br/>
<form action="login.php" method="POST">
Username<input type="text" name="username">
Password<input type="password" name="password">
<input type="submit" value="Login">
</form>
Most of this is pretty basic, first it checks if a username was sent from the form and if there was then it connects to the database (you will need to change the username, password and database_name values if you want to test the script). A simple query is created using the values from the form and then the script checks if more than one row was returned. Then a message is outputted just to say if the login was successful.
I also told the script to output the mysql query so that it is clear what is happening. I tested the script using normal users and password and got this output:
SELECT * FROM users WHERE username = 'admin' AND password = 'blah'
invalid username or password
SELECT * FROM users WHERE username = 'admin' AND password = 'Ghksu782'
You logged in successfully
So you can see that the script works, it rejected my bad password and accepted the good one. Now of course the attacker will not know the password so this is where the mysql injection comes in.
This time we try and login with the username of admin and the password of ' OR 'a'='a
Now we can look at the generated query and see what has happened.
SELECT * FROM users WHERE username = 'admin' AND password = '' OR 'a'='a'
You logged in successfully
Here you can see our attack has worked and we have logged in successfully. The mysql query shows how it was done. The first ' in our password closed off the string which should have contained the password so the query now checks to see if the password equals nothing which is false so we shouldn't be able to log in. This however is bypassed by the next code which says OR 'a'='a' which will always be true. Hence it will return the row because of the OR. note that the last ' was provided by the php script so we didn't include it in our "password".
To prevent this happening there is a function mysql_real_escape_string() which will escape any characters which could be used in mysql injection. So the ' will be replaced by what is called an escape sequence \' which tells mysql that it is part of the string rather than the end of the string. magic quotes in php can also help prevent attacks but there are problems with this so it should not be relied upon, also many people will not have it enabled. php6 will have it disabled by default. See http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string for more details.
So the sample script would be modified to:
<?php
if (isset($_POST['username'])){
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database_name');
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
echo $query;
echo '<br/>';
$result = mysql_query($query);
if(mysql_num_rows($result)>0){
echo "You logged in successfully";
}else{
echo "invalid username or password";
}
}
?>
<br/>
<form action="login.php" method="POST">
Username<input type="text" name="username">
Password<input type="password" name="password">
<input type="submit" value="Login">
</form>
So always remember to check your input is safe when using mysql queries. bear in mind that I could have placed any mysql code in the injection to insert new rows, delete the table or modify the values. Using UNION this can get very dangerous.