PHP: SQL Injections

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:

Quote

And this is displayed on this screen:

Quote

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:

Quote

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!

Edited by John, 21 October 2010 - 10:36 AM.