PHP: SQL Injections

[John]

In today's world of rapidly expanding technology, most of us have become completely dependent on the internet. For many of us, it serves as our main method of banking, holiday shopping, people use it to find their soul mate, and some of us even use it as our main method of social interaction [thats not me]. So it is a no-brainer to understand why keeping our data secure is important. The scary part is, all of these actions involve the storage of your personal information on a remote server and the security of your private information, is completely dependent on the security of the environment in which you interact. Which is why webmasters need to take into account the strength of their database queries. One of the most common security flaws is the ability of code to be injected into a database query and perform malicious actions. This is known as a SQL Injection.

What is an SQL injection?

Before I fully divulge what an SQL injection is, lets setup a simple scenario. Bob is a freelance programmer heired by the Extreme Banking Association to development an online banking system, which stores the credit card information of its users. Bob creates a mysql database similar to the following structure:

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(200) NOT NULL,
  `password` varchar(200) NOT NULL,
  `creditcard` varchar(200) NOT NULL,
  PRIMARY KEY  (`id`)
);

He then imports some data into the database:

INSERT INTO `users` (`id`, `username`, `password`, `creditcard`) VALUES 
(1, 'Sidewinder', 'monkey', '0123456789987654'),
(2, 'John', 'password!', '3123456769384659'),
(3, 'Bob', '1234', '7133116752374638');

At this point, the manager of the project should see Bob's incompetence and lack of skill. What kind of idiot would store passwords in plain text? Anyway, Bob continues with this code and creates a basic login system, and it has a structure similar to the following:

<?php
if(empty($_GET['username'])) {

echo "<form method='GET' action='login.php'>"
    ."Username: <input type='text' name='username' /><br />"
    ."Password: <input type='text' name='password' /><br />"
    ."<input type='submit' value='Login' />"
    ."</form>";
}

if(get_magic_quotes_gpc()) {
   $username = stripslashes($_GET['username']);
   $password = stripslashes($_GET['password']);
}

$link = mysql_connect($dbhost, $dbuser, $dbpass) 
    or die('Could not connect: ' . mysql_error());

mysql_select_db($mysqldb, $link) 
    or die('Could not select database.');

$query = mysql_query("SELECT * FROM `users` "
        . "WHERE `username` = '$username' "
        . "AND `password` = '$password'")
    or die('Could not select database.');

$row = mysql_fetch_assoc($query);

if(mysql_num_rows($query) >= 1) {
    echo "Hello {$row['username']}!<br />";
    echo "Your credit card number is: {$row['creditcard']}";
}
?>

At this point, it does everything the manager of the project wants, enter your username/password and the users credit card number is displayed. So, Bob withdraws his money from the escrow account and goes on his merry way. One day an ub3r l33t h4x0r comes along, and attempts an SQL injection. He sees that the form uses a GET method request, and attempts to alter the data that is sent to the server. He enters the following line into his address bar:

login.php?username=anything&password=anything'%20O R%201='1

And this is displayed on this screen:

Hello Sidewinder!
Your creditcard number is: 0123456789987654

And the hacker has successfully gained access to the database, and thus, all information in that database. As you can see from the URL, the provided username is anything and the password is anything'%20OR%201='1. The magic is in the password. In essence it translates to:

anything' or 1='1

And when that is injected into the query, it looks like this:

$query = mysql_query("SELECT * FROM `users` "
        . "WHERE `username` = 'anything "
        . "AND `password` = 'anything' OR 1='1'"); 

The query is evaluated as logically true. Although the username/password conjunction evaluates to false, the disjunctive 1=1 clause is evaluated to be true, hence the query returns the results. In the example above, the returns the results of the data in the first row. However, the injection can be altered to perform other actions. In most cases, the injection is rather complicated due tosome security measures taken by the programmer. This leads us to the next section of this article.

How do I prevent SQL injections?

The answer is simple – never trust user input. That is the single most important concept in developing a secure application. The concept is simple, but taking action is a little more difficult.

The easiest way to prevent SQL injections, is to escape data. The PHP developers implemented a feature(?) called Magic Quotes, which escapes quotes, NULL characters, backslashes, among other characters. This was designed as a security feature to help prevent SQL injections, however has caused both me, and many developers headaches, which is probably why as of PHP 6.0, Magic Quotes will be depreciated. As a result, it is a poor idea to rely on Magic Quotes as a solution to your SQL injections. In fact, even with Magic Quotes enabled, SQL injections are still possible. The code I am going to provide you is probably not the most secure code, as I am far from an expert on security, but I have found it as a decent solution for many of my problems regarding SQL injections.

First thing we want to do is remove all of the damage done by Magic Quotes:

// Is magic quotes on?

if (get_magic_quotes_gpc()) { // Yes? Strip the added slashes
    $_REQUEST = array_map('stripslashes', $_REQUEST);
    $_GET = array_map('stripslashes', $_GET);
    $_POST = array_map('stripslashes', $_POST);
    $_COOKIE = array_map('stripslashes', $_COOKIE);
} 

Next, take advantage of the mysql_real_escape_string() function in our query:

$query = mysql_query("SELECT * FROM `users` "
        . "WHERE `username` = '" 
        . mysql_real_escape_string($username) . "' "
        . "AND `password` = '"
        . mysql_real_escape_string($password) . "'"); 

Now I am going to change the form method from GET to POST which, although provides no explicit security, implicitly it does.

if(empty($_GET['username'])) {
echo "<form method='POST' action='login.php'>"
    ."Username: <input type='text' name='username' /><br />"
    ."Password: <input type='text' name='password' /><br />"
    ."<input type='submit' value='Login' />"
    ."</form>";
}
$username = $_POST['username'];
$password = $_POST['password']; 

Finally, strengthen the session validation, and the final script will look like this:

<?php
// Is magic quotes on?
if (get_magic_quotes_gpc()) { // Yes? Strip the added slashes
    $_REQUEST = array_map('stripslashes', $_REQUEST);
    $_GET = array_map('stripslashes', $_GET);
    $_POST = array_map('stripslashes', $_POST);
    $_COOKIE = array_map('stripslashes', $_COOKIE);
}

if(empty($_POST['username'])) {
    echo "<form method='POST' action='login.php'>"
    ."Username: <input type='text' name='username' /><br />"
    ."Password: <input type='text' name='password' /><br />"
    ."<input type='submit' value='Login' />"
    ."</form>";
}

$username = $_POST['username'];
$password = $_POST['password'];

$link = @mysql_connect($dbhost, $dbuname, $dbpassword) 
    or die('Could not connect: ' . mysql_error());

mysql_select_db($mysqldb, $link) 
    or die('Could not select database.');

$query = mysql_query("SELECT * FROM `users` "
        . "WHERE `username` = '" 
        . mysql_real_escape_string($username) . "' "
        . "AND `password` = '"
        . mysql_real_escape_string($password) . "'");

$row = mysql_fetch_assoc($query);

if((mysql_num_rows($query) >= 1) && ($password == $row['password'])) {
    echo "Hello {$row['username']}!<br />";
    echo "Your credit card number is: {$row['creditcard']}";
}
?>

As far as SQL injections go, you should be pretty safe!

[v0id]

Pretty good article.
There's a little bug in your SQL injection though. I don't think the SQL injection you come up with would work correctly.

' OR 1 = '1

As you see, you're comparing an integer (1) with a character/string ('1') I'm not sure if SQL cares about it or not, but I'd prefer to compare a character/string and a character/string.

' OR '1' = '1

It was only a little note, beside that; good work!

I actually made a blogpost on this topic a while ago, if anyone should be interested.

[TcM]

Thanks very much! When I saw this I thought that my website was hacked because of it, but I tested it and at least it is not vulnerable to this threat. So I can eliminate an SQL Injection. Thanks and great tutorial/article.

[Jordan]

Excellent article! Thank you for the Tutorial.

[John]

Pretty good article.
There's a little bug in your SQL injection though. I don't think the SQL injection you come up with would work correctly.

' OR 1 = '1

As you see, you're comparing an integer (1) with a character/string ('1') I'm not sure if SQL cares about it or not, but I'd prefer to compare a character/string and a character/string.

' OR '1' = '1

It was only a little note, beside that; good work!

I actually made a blogpost on this topic a while ago, if anyone should be interested.

I wasn't very concerned with a correct injection as the isn't a tutorial on how to do injections - rather how to prevent them. I'm rather happy the injection won't work.

[v0id]

I just thought it would be nicer with correct information, but you're right, it's a cliché. Anyways, like I said before, good work.

[John]

I just thought it would be nicer with correct information, but you're right, it's a cliché. Anyways, like I said before, good work.

Touché. I was actually going to create a login system and host it here as a demo, but I didn't have time. Although I would still like to do so in the future.

[John]

updated

[Whitey]

uhmm question...

why do you use

#
if (get_magic_quotes_gpc()) { // Yes? Strip the added slashes
    $_REQUEST = array_map('stripslashes', $_REQUEST);
    $_GET = array_map('stripslashes', $_GET);
    $_POST = array_map('stripslashes', $_POST);
    $_COOKIE = array_map('stripslashes', $_COOKIE);
}

rather then using something like this?

#
if(get_magic_quotes_gpc()) {
   $username = stripslashes($_GET['username']);
   $password = stripslashes($_GET['password']);
}

because i usually use that 1 then do the mysql_real_escape thing

[John]

Excellent question. Your method achieves the exact outcome as mine; however, you should note that $_GET is an (associative) array. Using array_map and passing it the callback function stripslashes(), you can strip the slashes of every element in the $_GET array by simply calling that single function. Whereas you call stripslashes() on every element individually, using my method, it is not necessary (as array_map will do it automatically).