SQL Injections: Introduction with PHP/MySQL

Providing you know SQL you can perform SQL injections on websites that perform unescaped queries.

In this reference we’ll use a hands on approach and start by performing our own basic injections based on website user input and then looking into prevention methods.

What is an SQL Injection?
Preparations
Performing Injections
Preventing Injections

What is an SQL Injection?

The title says it all. You simply “inject” your own SQL to make your own query. SQL Injections have been around a long time and it’s not acceptable for your site to be prone to them. If your site allows SQL Injections your entire database, and all the data stored in it, has practically been made public. All information is available to be seen and deleted.

As a lot of websites, especially Content Management Sites, have administrative systems based on usernames and passwords in their database your entire site could be compromised.

Preparations

We are going to make our site vulnerable to SQL Injections so do all testing locally and not on a remote or live server!

First of all make sure that Magic Quotes is disabled. Go to your php configuration file (php.ini) and change/make sure magic_quotes_gpc is set to off (magic_quotes_gpc = Off).

First of all lets create a simple table called “injection_test” (example for MySQL). We are going to have some fake entries that we will get later on. Feel free to create your own table but to keep things easy for this reference you can paste the following database:

CREATE TABLE IF NOT EXISTS 'injection_test' (
  'id' int(2) NOT NULL auto_increment,
  'username' varchar(255) default NULL,
  'password' varchar(255) default NULL,
  'email' varchar(255) default NULL,
  PRIMARY KEY  ('id')
);
 
 
INSERT INTO 'injection_test' ('id', 'username', 'password', 'email') VALUES
(1, 'admin', 'd0be2dc421be4fcd0172e5afceea3970e2f3d940', 'admin@website.url'),
(2, 'user', '8d6e34f987851aa599257d3831a1af040886842f', 'user@website.url'),
(3, 'user2', '2d27b62c597ec858f6e7b54e7e58525e6a95e6d8', 'anotheremail@website.url');

For our example we are going to select all details for a user. Below is the PHP script I’ll be using (injection.php):

<h1>SQL Injection Test</h1>
<?php
mysql_connect('localhost', 'root', '');
mysql_select_db('database');
?>
 
<form method="post" action="injection.php">
Username: <input type="text" name="username" />
<input type="submit" value="Login" name="login" />
</form>
 
<?php
if (isset($_POST['login']))
{
    $username = $_POST[username];
    echo 'Input: '.$_POST[username].'<br />';
    echo 'Query: SELECT * FROM injection_test 
        WHERE username="$username" <br /><br />';
 
    $sql = 'SELECT * FROM injection_test 
        WHERE username="$username"';
 
    $q = mysql_query($sql) or die (mysql_error());
 
    while ($r = mysql_fetch_array($q))
    {
        echo '<code>';
        echo 'Username: '.$r[username].'<br />';
        echo 'Password: '.$r[password].'<br />';
        echo 'Email: '.$r[email].'<br />';
        echo '</code>';
    }
}
?>

Once again feel free to make modifications. We are now ready to start playing around.

Performing Injections

I understand that it’s highly unlikely to have this sort of information revealed on a real site but queries based on user input are common. This is only a demonstration so lets begin.

The script we are using performs the query:

1	SELECT * FROM injection_test WHERE username='$username'

Enter “user2″, without the quotes, into the text field and the username, password and email for “user2″ will be echoed.

Now let’s assume that we don’t know any usernames to bring up details. By entering ‘ OR ’1 into the text field we are changing the query to:

1	SELECT * FROM injection_test WHERE username='$username' OR '1'

OR 1 always returns true. Now that everything in the WHERE clause is true all results in the table will be returned. A successful injection with a valid MySQL query has just been performed.

PHP’s mysql_query function only supports one query at a time so injections are usually limited to what query is made prior to our injected parameters.

Injections are generally used to snoop but they can be used to modify dangerous queries such as DELETE, UNION, DROP etc.
The next section contains more prevention methods and information.

Preventing Injections

Escape Special Characters
Use PHP’s mysql_real_escape_string() function to escape special characters. This makes queries safe and would escape an injection like below:

1	SELECT * FROM injection_test WHERE username='$username' OR '1'

Escaped MySQL query with PHP’s mysql_real_escape_string() function.

In the script we used to perform the injection earlier we would simply use the function on the $username variable like:

1 2	$sql = "SELECT * FROM injection_test WHERE username='".mysql_real_escape_string($username)."'";

Limit Priviliges
It’s good practice to always limit the database priviliges of your PHP scripts so that queries such as DELETE and DROP aren’t allowed whatsoever. You can usually do this via applications such as CPanel or create your own function to replace keywords inside the PHP script itself.

Using addslashes()
There is a great debate on addslashes vs mysql_real_escape_string (amongst other functions). Generally I would only use addslashes for URL encoding. It’s up to you although vulnerabilities have been found in addslashes.

Magic Quotes
A lot of PHP configurations have “Magic Quotes” enabled by default. Although this escapes all $_GET, $_POST and $_COOKIE variables making database queries safe it has quite a few issues and has finally been depracted from PHP 6.

One of the major problems is that not all PHP configurations have it enabled. If you port a project to a different server and it’s
disabled you have additional work to do, especially if you don’t have access or the privileges to change php.ini. In the worst case you may get lazy and completely forget to check it’s enabled, making your project wide open to injections. There are also speed and efficiency issues when compared to other functions such as addslashes().

You may also find that you don’t want or need everything to be escaped which, again, means more work on your behalf. Allow yourself more stability, efficiency and flexibility by using mysql_real_escape() or addslashes().

In a nutshell

Escape and validate strings as well as limiting dangerous SQL commands wherever possible.

This is just an introduction to SQL Injections. There is a lot more to it than just escaping characters. Take a look around google for more detailed and complex injection examples.