SQL Injections

SQL is a language that allows prorammers to access data in databases. Most of the time (always?) you pass your queries in form of strings to the database. In online services it is quite common that the programmer formulates a template and the user fills in variables.

Example: IMDb

Take a look at IMDb. Users can search for movies by title:

When you search for "Harry Potter" for example, the following happens:

You obviously interacted with imdb.com in a very dynamic way. The output of the website depends on what you typed in and IMDb has to search in its database for your search terms.

The programmers might have created a query that looks like this

SELECT * FROM `movie` WHERE title=$_GET['q']

Where $_GET['q'] is your query.

Proof of concept

You need:

• Apache Web Server
• PHP
• MySQL
• PhpMyAdmin (for convenience)

When you search for "LAMP" (for Linux users) or for "WAMP" (for Windows users) you find a lot of information how to install this.

Place the following as hack.php in your web servers directory (might be /var/www):

<?
$mysqlhost = "localhost";
$mysqluser = "root";
$mysqlpwd = "asdfasdf";
$connection = mysql_connect($mysqlhost, $mysqluser, $mysqlpwd) or die
("Your connection string was wrong");
$db_selected = mysql_select_db('imdb', $connection);

$q = $_GET['q'];
if ($q != "") {
    $result = mysql_query("SELECT * FROM `movies` WHERE title='$q'");
    if (!$result) {
        die('MySQL query error: ' . mysql_error());
    }
    echo "Found ".mysql_num_rows($result)." movies:<br/>";
    while ($row = mysql_fetch_assoc($result)) {
        echo $row["id"].": ".$row["title"]."<br/>";
    }
}
?>

<form method="get" action="hack.php">
  <input type="text" name="q"/>
  <input type="submit" />
</form>

Now create a database called imdb with PHPMyAdmin and execute the following SQL:

CREATE TABLE IF NOT EXISTS `movies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=4;

INSERT INTO `movies` (`id`, `title`) VALUES
(1, 'Harry Potter'),
(2, 'Lord of the Rings'),
(3, 'Rise of the Silver Surfer');

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(32) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=3 ;

INSERT INTO `users` (`id`, `username`, `password`, `email`) VALUES
(1, 'admin', 'qewrtqwert', '[email protected]'),
(2, 'user', 'secret', '[email protected]');

Now go to http://localhost/hack.php. It should look like this:

When you search for "Harry Potter" it should show you "1: Harry Potter". Note that there could be a lot of information, but I wanted to keep this example as small as possible.

This resulted in the following query:

SELECT * FROM `movies` WHERE title='Harry Potter'

But a Hacker could also enter a string like this: ' OR '1'='1:

Even worse, the attacker could know that you use MySQL. Then he might know that MySQL uses INFORMATION_SCHEMA tables. He might enter this into the title input element:

' UNION SELECT table_name, table_type FROM information_schema.tables WHERE '1'='1

which results in this query:

SELECT * FROM `movies` WHERE title='' UNION SELECT table_name, table_type FROM information_schema.tables WHERE '1'='1'

which gives:

This way, the attacker gets all table names from all databases on this machine. So he essentially can get everything stored in your database. And, of course, after getting everything he could drop it:

History

Just a few famous examples to show you that this happens all the time:

• 2005: USC admissions page (source)
• 2006: 800,000 datasets of personal information of students of UCLA (source)
• 2008, 2009: Heartland Payment Systems; 130 million credit and debit cards (source 1, source 2, see Albert Gonzalez)
• 2010: Royal Navy Website; Passwords and Usernames stolen (source)
• 2011: Sony:

  LulzSec says it accessed the passwords, email addresses, home addresses and dates of birth of one million users. The group says it also stole all admin details of Sony Pictures, including passwords. 75,000 music codes and 3.5 million music coupons were also accessed, according to the press release.

  (source)
• 2011: Expedia (source)
• 2011: MySql - Usernames and passwords stolen (source 1, source 2)
• 2011: Comodo (source 1, source 2)

Solutions

• Sanitize user input, e.g. with mysql_real_escape_string
• Use prepared statements
• Switch of error reporting (this makes attacks more difficult, but doesn't prevent them)

See also

• Challenge Websites: Try if you can write SQL injections yourself ☺