How to Test Your Own Web Server Against Mysql Vulnerabilities

If you are a programmer you should take care of the security of your code and servers. This instructable demonstrates how a mysql injection works and what you can do to prevent your own code to get used for other purposes that you expect it.

After reading this, you will understand what a mysql injection is, how it works technically and what you can do to protect your code.

Step 1: Set Up a Test Environment

To test your code, you should set up a test environment. This can be done using xampp or lampp on your computer. For sure, you can set up a apache webserver with php yourself.

You will need an webserver, a mysql database and a running php interpreter to execute your code.

Step 2: Example Database

For our test case, we use a very basic database with two users. Passwords are protected using the md5 algorithm. Just run this mysql code on your database to create the tables:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
CREATE TABLE IF NOT EXISTS `user` (
`id` bigint(15) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(32) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `user` (`id`, `email`, `password`) VALUES
(1, '[e-mail]', 'b655e3f4ae881514c4896b9cd707e4d2'),
(2, '[e-mail]', '5d41402abc4b2a76b9719d911017c592');
ALTER TABLE `user`
 ADD PRIMARY KEY (`id`);
ALTER TABLE `user`
MODIFY `id` bigint(15) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;COMMIT;

Step 3: A Short Excursion About Password Security

Since the computing power all over the world is growing very fast, md5 is no longer a strong hashing algorithm. The used hashes are generated using the following passwords:

b655e3f4ae881514c4896b9cd707e4d2 = top secret password that will never be cracked that fast
5d41402abc4b2a76b9719d911017c592 = hello

Since there are things like rainbow table, even google itself is a good password cracking machine. When you google "5d41402abc4b2a76b9719d911017c592", the first result is the clear text password. When you google "b655e3f4ae881514c4896b9cd707e4d2" you will get no result since the input string is just too long to get cracked using the brute force method.

So: Use long passwords that don't appear in any dictionary!

Step 4: Example Code

For testing reasons, I wrote this little script. It opens up a database connection, offers a form and tries to find a user in the database matching the credentials you entered (email and password). Just save the script on your webserver and call the file "mysql-injection.php". You may have to update the database name for the "mysql_select_db" function when your database has not the same name:

<?php
error_reporting(E_ALL ^ E_DEPRECATED);
mysql_connect('localhost', 'root', '');
mysql_select_db('mysql-injection');
if (!empty($_POST['email']) && !empty($_POST['password']))
{
    $query    = 'SELECT * FROM `user` WHERE `email` = \''.$_POST['email'].'\' AND `password` = \''.md5($_POST['password']).'\'';
    echo 'Used query: ',$query,'<br /><br />';
    $resource = mysql_query($query);
    $matches  = mysql_num_rows($resource);
    if ($matches > 0)
    {
        $user     = mysql_fetch_assoc($resource);
        echo 'Hello '.$user['email'].'!';
    }
    else
    {
        echo 'Invalid credentials!';
    }
}
?>
<form method="post" action="mysql-injection.php">
    <p>Log in to get privileges!</p>
    Mail:     <input type="email"    name="email"    value="<?=!empty($_POST['email'])    ? $_POST['email']    : ''?>" /><br />
    Password: <input type="password" name="password" value="<?=!empty($_POST['password']) ? $_POST['password'] : ''?>" /><br />
    <br />
    <input type="submit" name="submit" />
</form>

Step 5: Testing Your Script

To get started, just call the script on your web browser. In my case the url is http://sand.box:8081/mysql-injection.php since I routed "sand.box" to "127.0.0.1". You can just call http://sand.box:8081/mysql-injection.php or http://sand.box:8081/mysql-injection.php to make it work.

We now can test the login with the following credentials:

[e-mail]:top secret password that will never be cracked that fast
[e-mail]:hello

As you can see in the screenshots, the script returns a "Hello <email>" to proofe that your login credentials were correct. Use any other combinations to get an "Invalid credentials!" error.

This is a working user authentication. But its very dangerous. Check out the next steps to see, why.

Step 6: Behind the Scenes

Since the script outputs the database query that is used to select the correct user, you can see whats going on behind the scenes. When you login as admin the following query:

'SELECT * FROM `user` WHERE `email` = \''.$_POST['email'].'\' AND `password` = \''.md5($_POST['password']).'\''

is computed to:

'SELECT * FROM `user` WHERE `email` = \'[e-mail]\' AND `password` = \''.md5('top secret password that will never be cracked that fast').'\''

and then to:

'SELECT * FROM `user` WHERE `email` = \'[e-mail]\' AND `password` = \'b655e3f4ae881514c4896b9cd707e4d2\''

and what is sent to the mysql database is:

SELECT * FROM `user` WHERE `email` = '[e-mail]' AND `password` = 'b655e3f4ae881514c4896b9cd707e4d2'

So it just select a user by its email address and password. In this case, this is secure expect of the mail validation. The variable $_POST['email'] is not secured before it is attached to the query, so we can inject a lot of evil things here. The $_POST['password'] variable is passed to md5() before its concated to the database query, so this is "accidentally safe" since md5() only returns numbers and characters and nothing that could crash our query.

Step 7: Hack It!

As we learned in the previous step, $_POST['email'] is our backdoor. So when you enter the following email, you can login as any user you want. For the user with id 1:

[e-mail]' OR `id` = 1 OR 1 = '

For The user with id 2:

[e-mail]' OR `id` = 2 OR 1 = '

As you can see, the used mysql query is the following:

SELECT * FROM `user` WHERE `email` = '[e-mail]' OR `id` = 1 OR 1 = '' AND `password` = '098f6bcd4621d373cade4e832627b4f6'

Since there are no brackets in the query, we can stop the string by just passing a ' in the email. After the ' we can modify the query itself. So we just add another "OR" condition that relates to the user id. This makes the email and the password obsolete since mysql stops checking the condition as soon as one "OR" condition is true. So if there is a user with the id "1" in our database, we are logged in.

Step 8: Protected Your Code

To make this code secure, just escape the users input. This can be done, for example, by using the method mysql_real_escape_string. Another function may be addslashes. So a "secure" solution would be using:

mysql_real_escape_string($_POST['email'])

instead of

$_POST['email']

So this is the final php code:

$query = 'SELECT * FROM `user` WHERE `email` = \''.mysql_real_escape_string($_POST['email']).'\' AND `password` = \''.md5($_POST['password']).'\'';

And the computed query is:

SELECT * FROM `user` WHERE `email` = '[e-mail]\' OR `id` = 1 OR 1 = \'' AND `password` = '098f6bcd4621d373cade4e832627b4f6'

So as you can see, the single quotes in the string are now escaped and no longer ending the mysql string. This causes the query to search for the following email addres:

[e-mail]\' OR `id` = 1 OR 1 = \'

And of course, this will fail, since there is no matching email in the database.

Step 9: Just to Get an Idea

This instructable was just written to get an idea of mysql injections in a very basic case. In 2015 you don't write php applications yourself, you use frameworks and libraries that handle the most of the security and database stuff for you. But you should know whats going on behind the scenes to write your code fast and secure.

For example, a good solution is using the symfony2 framework.