Cybertalent CTF is an annual CTF organized by the Norwegian intelligence service. Had a lot of fun with it this year; my favorite problems were NoSQL and Kaffemaskin. Here’s a writeup of NoSQL. Sorry that there are no screenshots!

Challenge Description

In this challenge we are given a flag checker website and its source code. We can input a flag and the backend will tell us whether that flag is correct.

The backend code (Express) uses a SQL database with all the flags. Before making a SQL query, it sends our requests through a nosql middleware function aiming to block a SQL injection by imposing a blacklist:

function nosql(req, res, next) {
  let input = JSON.stringify(req.body || "").toLowerCase();

  let blacklist = [
	 'select',
	 'union',
	 'insert',
	 'update',
	 'delete',
	 'drop',
	 'alter',
	 'create',
	 'truncate',
	 'replace',
	 'rename',
	 'handler',
	 'load',
	 'limit',
	 'or',
	 'and',
	 'xor',
	 'like',
	 'regexp',
	 'sleep',
	 'benchmark',
	 'extractvalue',
	 'updatexml',
	 'information_schema',
	 'mysql',
	 'sys',
	 'version',
	 'pg_catalog',
	 'sqlite_master',
	 'case',
	 'when',
	 'then',
	 'end',
	 '--',
	 '#',
	 '/*',
	 '*/',
	 ';',
	 "'",
  ];

  if (blacklist.some(k => input.includes(k))) {
    return res.status(403).json({
      ok: false,
      error: "Hacking attempt detected! No SQL allowed!"
    });
  }

  next();
}

The code also features a rate limit middleware to prevent brute forcing of the flag.

The SQL library being used is the high quality mysql2. In particular, this library supports SQL prepared statements which prevents SQL injection by not directly including potentially malicious data into a raw SQL query. Instead, a statement is first prepared with ? placeholders and the data is provided in a seperately. The challenge seemingly correctly uses this functionality:

app.post('/validate', async (req, res) => {
  let { flag } = req.body;

  if (!flag)
    return res.status(400).json({ ok: false, error: "Can't check a missing flag." });

  let sql = 'SELECT flag FROM flags WHERE flag = ? LIMIT 1';

  try {
    let [rows] = await db.query(sql, [flag]);
    flag = rows.length ? rows[0].flag : null;
    if (!flag)
      return res.json({ ok: false, message: 'That flag is invalid!' });
    return res.json({ ok: true, message: `${flag} is a valid flag!` });
  } catch (err) {
    console.error('DB error:', err);
    return res.status(500).json({ ok: false, error: 'db error' });
  }
});

The use of prepared statements makes the keyword blacklist useless as SQL injection is not possible. However, the challenge name NoSQL does indicate that there might be a non-SQL vulnerability to exploit.

The vulnerability

It took me a while until I found the vulnerability because I was looking too closely at what was in the code, and not really considering what was not in the code. I really liked this challenge because the code looks correct while still being vulnerable. In particular, there is no suspicious superfluous code that CTF challenges often have. Apart from the unnecessary blacklist, the code seems clean and to the point.

So how did I find it? Google. Even if the vulnerability may not be SQL injection, the data we want is inside the database, so I tried searching things like:

• is sql injection possible with prepared statements
• mysql2 query use prepared statements

Eventually I found this blog post and finally understood the issue. In essense, mysql2 prepared statements behave differently depending on the data type of the inputs. As expected, strings are included as string literals without modification:

let sql = 'SELECT flag FROM flags WHERE flag = ? LIMIT 1';
db.query(sql, ["banana"]) --> SELECT flag FROM flags WHERE flag = banana LIMIT 1

However if you pass in a Javascript object, mysql2 will do expand it as follows:

db.query(sql, {foo: "foo", bar: "bar"}) 
--> SELECT flag FROM flags WHERE flag = `foo` = 'foo', `bar` = 'bar' LIMIT 1

Now our statement doesn’t really make sense anymore. It seems like this object expansion is meant to allow you to use Javascript objects more naturally with SQL queries. For example:

let sql = 'UPDATE students SET ? WHERE id = 10';
db.query(sql, {age: 19, blood_type: "A"})
--> UPDATE students SET `age` = 19, `blood_type` = 'A' WHERE id = 10

(Backticks correspond to SQL column names) Arrays are also expanded in a similar fashion (['a','b'] becomes 'a','b'). While convenient, this feature is a deadly footgun because this expansion can be used to conduct SQL injection attacks. But didn’t we say that prepared statements protected against those? Well, as also noted in the blog post, mysql2’s prepared statements are not real SQL prepared statements. 🤦 They should definitely make this clearer in their documentation.

Back to the challenge. The challenge uses the express.json middleware, which populates req.body exactly as the given JSON object. This means the attacker can make flag an arbitrary object and exploit the expansion to leak the flag in classic SQL injection fashion.

For example, we can use flag = {"id" : 0} which will result in the following SQL query:

SELECT flag FROM flags WHERE flag = `id` = 0 LIMIT 1

SQL is evaluated left to right, so the statement evaluates as follows:

flag = `id` (false, the column id is not the same as the column flag) --> 0
0 = 0 --> 1

(The query will fail if you refer to a column that doesn’t exist!)

so the final query is

SELECT flag FROM flags WHERE 1 LIMIT 1

which returns the flag!

After action

Defintely one of my favorite CTF challenge of all time. Quite simple while being educational.

The main takeaway from this challenge is to never forget about types! Javascript (and other dynamically typed langauges) are full of typing footguns - we’ve all seen (0 == "") --> true. What types does the code expect, and what happens if those expectations are not matched?

Another takeaway - don’t take libraries for granted. I stalled looking into mysql2 because I thought such a popular library could not be vulnerable. However, even popular libraries may have security footguns. Sometimes they are well documented, like Golang’s ServeMux CONNECT footgun. In that case a quick skim of documentation can reveal the issue. Otherwise, one might need to delve into the library code (which I should do more often) or, even better, directly test the library to see whether it works the way you expect.

That’s it for now. Thanks for reading!