Sigurdhsson

Note: This is a translated version of this article written by cic.

By reading this article you’ll get an idea of what SQL injections are and how an evil-minded user might use this to take control of your database. We’ll also bring up some methods of protection against these

We’ll start with a simple example to shed some light on what SQL injections really are. In this example I will use a MySQL database and PHP as the server-side language.

The Database

Okay, let’s start with the database part. We have a database called community in which we have a table set up like this:

CREATE TABLE 'users' ('username' varchar(50), 'password' varchar(50));

This is where we store all information about our users. Let’s add some sample values:

INSERT INTO 'users' VALUES ('admin', 'password'), ('root', 'mysecretpassword'), ('cic', '90Hi4UHax');

Note that it is generally not a good idea to store passwords as plaintext anywhere but for the sake of simplicity we do that here.

The Form

We obviously need a form for the users to log in through:

<form action="login.php" method="post"> <label for="username">Username:</label> 

<input id="username" name="username" type="text" /> 

<label for="password">Password:</label>

<input id="password" name="password" type="password" /> 

<input type="submit" /> </form>

We can see from looking at this form that it will send POST data to login.php containing the username and password. All well so far.

The PHP Code

Let’s have a look at login.php: (Excuse the spaces, the textile plugin gave me a hard time.)

< ?php
// login.php
// Connect to database server and select database
mysql_connect('localhost', 'root', '');
mysql_select_db('community');

// Check that the user really entered the right username in combination with its password
$query = mysql_query('SELECT * FROM users WHERE username="' . $_POST['username'] . '" AND password="' . $_POST['password'] . '"');

// 'Check if any of the users matched
if (mysql_num_rows($query) > 0)
echo '< p>You have been logged in.

';
else
echo '< p>You have < strong>not been logged in.

';
?>

This is where things start to go wrong, horribly wrong. There’s no valitation what-so-ever of the user’s input. This means the user can send any data he or she wishes to your MySQL server. This means the user can rewrite our query to whatever he or she likes.

The Database + The Form + The PHP Code = Security hole

Another example will make it clearer. A normal visitor will never notice this major security hole, he or she will happily log in and out without problems. But let’s say someone decides to enter “” (i.e., nothing, as it doesn’t matter at the moment) in the username field and ' OR ''=' in the password field.

Okay, WTF. Why would you do that? The password is wrong and nothing will happen, right?

Let’s do a deeper analysis of how the query will look like: $_POST['username'] is now set to nothing and $_POST['password'] is set to ’ OR ‘’=’@, and thus the query will now look like this:

SELECT * FROM users WHERE username='' AND password='' OR ''='';

Okay, looks a bit strange but so what? Let’s see what you get when you send that query to the database server:

As you can see there’s more rows than zero, and that’s all we need to care about here. Since mysql_num_rows now returns a value greater than zero your script will think that the user entered a vaild username and correct password, and thus logs him or her in. Nice.

Why did this happen?

Okay, why did the server return all users in the database? The answer is trivial: AND has a higher priority than OR, an thus:

SELECT * FROM users WHERE (username='' AND password='') OR ''='';

Is the same thing as:

SELECT * FROM users WHERE username='' AND password='' OR ''='';

And the other way ‘round. This means that since the first part always returns FALSE for all rows (since both username nad password is wrong) and the other part always returns TRUE for all rows (since nothing always equals nothing) the result will be FALSE OR TRUE, which evaluates to TRUE for all rows. This makes your MySQL server return all rows.

Magic quotes and PHP

Since it’s hard to show how to protect your code from SQL injections the rest of this article will mostly be centered on PHP and problems around it. But even if you aren’t going to write your webapp in PHP reading through the rest could be good for you anyway, since the methods of protection are the same in most languages.

If you happen to be part of the PHP developer group of people you probably know everything about magic quotes already. But since this is a guide for beginners, it might be worth bringing up anyway. If you think you know everything about magic quotes, feel free to skip this part.

First; what’s wrong with these magic quotes? What could possibly be wrong with something that’s called magic quotes? Shouldn’t everything that’s magic automatically be wonderful?

Well, magic quotes were introduced to PHP to “help-and-protect”(TM) beginners from SQL injections and a variety of othersecurity holes (which we might bring up later in this series). But the way they work right now they’re mostly irritating and in the way. Magic quotes can either be on or off depending on how you set up PHP. What magic quotes does is that it automagically escapes all user input. Escaping means transforming all characters that have a special meaning (in this case only quotes) to the character it would normally be, like this:

• A random user enters “My name is '” (note the last single quote) in a form somewhere,

• PHP then automagically transforms it into "My name is \'".

Since the single quote has a speciall meaning we have to represent is without that speciall meaning. This is done by adding a backslash in front of it. You can do this with other characters to, for example \n gives you a newline and \t gives you a tab character.

It sounds so much more complicated than it is. As you already know, PHP has two ways of specifying a string, the single and double quotes:

	// Example 1
echo "Hello, world!";
echo 'Hello again, world!';

// Example 2
echo "Hello, this is \"PHP\"";
echo 'Hello, this is "PHP"';

// Example 3
echo "Hello, this is 'PHP'";
echo 'Hello, this is \'PHP\'';
?>

Nothing strange in example 1, you can represent a string using either single or duoble quotes.

In the second example things get exciting. PHP now has to print a double quote! Crazy! The only way for PHP to understand that we want to print a quote and not end the string is to escape the quote. This makes PHP handle the quote as normal text instead of something that ends the string.

The third example is basically the opposite of the second, in that we now try to print a single quote instead. This means that in the second string we have to escape the single quote the same way we did before.

If the double quote keeps its special meaning it could be used by a malicious user in the way we just saw (i.e., SQL injections.) Since we started our string with a double quote the only way to end it is with another double quote. If we take this privelige from the user we should be safe, no? Relatively safe.

How to protect yourself

So, how do we protect ourselves from these attacks, in PHP? The answer varies depending on what SQL server (MySQL, MSSQL, PostgreSQL, SQLite etc.) and server-side language you might use.

For the common combination of PHP and MySQL there is the mysql_real_escape_string function. If you use another SQL database you’ll have to check the PHP function reference for your database to see if there is a similar function, or use one of the alternatives we’ll bring up later. For completeness, below is a “fixed” version of login.php from our example above: (Again, excuse the spaces.)

< ?php
// login.php
// Connect to database server and select database
mysql_connect('localhost', 'root', '');
mysql_select_db('community');

// Escape all user input
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);

// Check that the user really entered the right username in combination with its password
$query = mysql_query('SELECT * FROM users WHERE username="' . $username . '" AND password="' . 	$password . '"');

// 'Check if any of the users matched
if (mysql_num_rows($query) > 0)
echo '< ;p>You have been logged in.

';
else
echo '< p>You have < strong>not been logged in.

';
?>

As stated above I am not the author of this article. I merely translated it from swedish to english. And yes, I have the permission to do it.