NahamCon 2022 CTF Write-up: Flaskmetal Alchemist

One of the most fun challenges in NahamCon 2022 was Flaskmetal Alchemist. This is a medium web challenge that involves an SQL injection that is relatively easy to spot, but tricky to exploit. I learned a few new things from this, so hopefully this write-up will provide inspiration to all you reading this. In this post I will walk you through my thought process and how I eventually exploited the vulnerability.

The challenge

The challenge has the following description:

Edward has decided to get into web development, and he built this awesome application that lets you search for any metal you want. Alphonse has some reservations though, so he wants you to check it out and make sure it’s legit.

Press the Start button on the top-right to begin this challenge.

NOTE: this flag does not follow the usual MD5 hash style format, but instead is a short style with lower case flag{letters_with_underscores}


First off, this challenge gets points for references to Fullmetal Alchemist, one of my favourite anime series. But I digress.

After navigating to the challenge website, we are presented with a list of metal elements from the periodic table. We can search for an element, and we can sort the list by atomic number, symbol, or name. So far, so simple.

Flaskmetal Alchemist challenge site

Investigating the source

The first step I take is to unzip and take a look at the files. We are given the following source code tree:

├── Dockerfile
├── requirements.txt
└── src

I quickly look through the files to get a sense of what sort of application I am dealing with. The requirements.txt and files shows me that it is a Python app using the Flask web framework, with SQL Alchemy as a database layer. This must be where the “flask” and the “alchemy” parts of the challenge name come from, with the “metal” part being the list of elements we saw on the site.

Then I start looking around to find where the flag is. I find it in, where I can see that it is loaded from /flag.txt and stored in the database using the Flag SQL Alchemy model.

from models import Metal, Flag
from database import db_session, init_db

def seed_db():
    # -- snip --

    with open("/flag.txt") as filp:
        flag =


From the Flag model in in I find out that the flag is stored in the flag column of the flag table.

from database import Base

# -- snip --

class Flag(Base):
    __tablename__ = "flag"
    flag = Column(String(40), primary_key=True)

    def __init__(self, flag=None):
        self.flag = flag

That’s the end goal sorted out. Now to work out how to reach it. To do that, I look at, where all the Flask routes are defined. It defines only one endpoint, /, that accepts both GET and POST requests. Looking at the code for the POST request, I see that we can get an order parameter from the POST data.

if "order" in request.form:
    order = request.form["order"]

This parameter is passed into the “order by” part of the query to control the sorting order of the list of metals. When passed into SQL Alchemy, it is wrapped by SQL Alchemy’s text function.

metals = Metal.query.filter("%{}%".format(search))

I’m not familiar with the text function, so I looked it up in the documentation, which says the following:

Construct a new TextClause clause, representing a textual SQL string directly.


from sqlalchemy import text

t = text("SELECT * FROM users")
result = connection.execute(t)

So the text function marks something as raw SQL, and doesn’t perform any special escaping. Because we control the input, this means we can write directly into the query; in other words, the order parameter is vulnerable to SQL injection in the ORDER BY clause of the SQL query.


To exploit this, I try to find out what the raw SQL query looks like. Knowing the raw SQL will make it much easier to craft a payload, but I’m not so familiar with SQL Alchemy, so I can only guess at how it might convert its query object into SQL. Luckily, it turns out that SQL Alchemy has a really nice string representation for its query objects that shows you the SQL that will be executed. After installing SQL Alchemy in a Python virtual environment, I am able to display the query using the following code:

>>> from sqlalchemy import text
>>> from models import Metal
>>> query = Metal.query.filter(                                                    
... ).order_by(text("atomic_number"))
>>> print(query)

This reveals that SQL Alchemy is running the following query (newlines added for clarity):

SELECT metals.atomic_number AS metals_atomic_number,
  metals.symbol AS metals_symbol, AS metals_name 
FROM metals 
WHERE LIKE ? ORDER BY atomic_number

So, we are injecting into a SELECT clause that has three columns. Also, our injection point is right at the end of the query, which means we don’t have to worry about any subsequent SQL commands.

My first thought is to exploit this using a UNION injection, so I try the following payload:


This creates the following query:

SELECT metals.atomic_number AS metals_atomic_number,
  metals.symbol AS metals_symbol, AS metals_name 
FROM metals 

However, trying this payload on the challenge site results in a 500 Internal Server Error. So do several similar queries; no matter how many columns I use or how I format things it always results in an error.

To get to the bottom of this I decide to set up a local testing environment. This involves installing the Python dependencies listed in requirements.txt, adding a flag.txt file with a fake flag, editing to use the new flag.txt path, and making my own home.html Jinja template. For some reason the home.html template isn’t included in the zipped source code, but replacing it with a simple one isn’t too hard:

{% for metal in metals %}
<li>{{ metal.atomic_number }}, {{metal.symbol}}, {{}}</li>{% endfor %}

When I am finished, my source tree looks like this:

├── Dockerfile
├── requirements.txt
└── src
    ├── flag.txt
    └── templates

I can then run the app using the command python from inside the Python virtual environment, which shows me all of the SQL Alchemy logs. The problem is immediately apparent:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) ORDER BY clause should come after UNION not before

So, it turns out you can’t use UNION injection when injecting into ORDER BY clauses, because UNION clauses are not allowed at that point in the query. Who knew?

After some research, I learn that only blind SQL injection will work inside an ORDER BY clause. Writing your own scripts to exploit blind SQL injection is fiddly, so I turn to everyone’s favourite SQL injection exploitation tool, sqlmap.


I decide to test my sqlmap command on my local testing environment before trying it out on the challenge site. By default, sqlmap tries all possible injection techniques for all supported DBMSs, resulting in a large number of unnecessary requests. Making these requests locally instead of over the internet is a great time-saver while you tune the sqlmap parameters.

I know that the app was using SQLite as the DBMS from the source code, so first I limit sqlmap to only testing payloads for SQLite. I also limit sqlmap to only test the “order” parameter, as I know that it is vulnerable.

Sqlmap isn’t able to find any injection points with these settings, so I increase the --level and --risk options until sqlmap detects an injection on the “order” parameter using the time-based blind technique. To do this I need to change the number of seconds to delay the database response (the --time-sec option) from the default of 5 to the minimum of 1, as with the default value the Flask development server keeps crashing, causing sqlmap to output “connection refused” errors.

All of this parameter tuning gives me the following command:

sqlmap '' \
  --data='search=a&order=atomic_number' \
  -p order \
  --dbms sqlite \
  --technique=T \
  --level 3 \
  --risk 2  \
  --time-sec 1 \
  --dump -T flag -C flag

This is enough to extract the test flag on my local machine. Getting the real flag should just be a matter of changing the URL to that of the challenge site, but when I try that, I keep getting errors like these:

[CRITICAL] unable to connect to the target URL. sqlmap is going to retry the request(s)
[CRITICAL] unable to connect to the target URL ('Connection refused')

These are the same errors that I was getting before reducing the --time-sec parameter, which gives me the idea that this could be due to the request taking too long. But --time-sec requires an integer; you cannot set it lower than 1. How can I set it to below the minimum?

Tamper scripts to the rescue

Looking at the query that sqlmap is running, I notice that the part that causes SQLite to take a long time to respond looks like this:


This is a very computationally expensive operation: it is generating 50MB of random bytes, converting those bytes to hexadecimal, then converting the resulting hexadecimal characters to upper case. Presumably, sqlmap does this because SQLite doesn’t have a sleep command, so it can’t specify an explicit time period to sleep for like it does for other DBMSs. Instead, it has to make the database server do lots of busywork to make the time pass before the request can complete.

So the problem here is that 50MB of random data is too much for the challenge server to handle. It is either causing the server to reach a memory or CPU limit, or it is just too much work for the server to complete before the request times out.

My guess is that sqlmap’s query is tuned for beefy database servers with several cores and heaps of RAM that can plough through 50MB’s worth of data transformation with ease. However, the challenge site is run in a container, on a machine hosting several other containers which are all being actively attacked as part of the CTF. While my testing VM just about coped with the amount of computation sqlmap was requiring of it, it was just too much for the challenge server.

I briefly consider giving up on sqlmap and writing my own exploitation script that uses a boolean-based instead of a time-based blind SQL injection. Before I do that, though, thankfully, I decide to read the sqlmap manual one more time, where I discover the tamper script feature.

Tamper scripts are meant to alter queries to bypass WAFs and similar security features, by replacing text in queries before they are sent over the network. Sqlmap provides several built-in tamper scripts, but also gives you the ability to write custom tamper scripts if you have a use case that isn’t covered by them. I decide to (ab)use this feature to reduce the computational load sqlmap is placing on the challenge server. I come up with the following script, which reduces the amount of data to process from 50MB to 1MB:

from lib.core.enums import PRIORITY

__priority__ = PRIORITY.NORMAL

def tamper(payload, **kwargs):
    Try reducing the time-based payload delay to below the
    minumum, so that we don't keep dropping connections...
    return payload.replace("RANDOMBLOB(100000000/2)", "RANDOMBLOB(2000000/2)")

I save this as tamper/, and also add an empty tamper/ file to make the tamper directory a Python package. (Sqlmap requires this structure due to the way it tries to import the script.)

As a final step, I add a small delay between each request, to fix a problem with sqlmap intermittently dropping connections during the database-dumping phase. This leaves me with the following command:

sqlmap '' \
  --data='search=a&order=atomic_number' \
  -p order \
  --dbms sqlite \
  --technique=T \
  --level 3 \
  --risk 2 \
  --time-sec 1 \
  --delay=0.5 \
  --tamper=tamper/ \
  --dump -T flag -C flag

Our tamper script and delay do the trick, and sqlmap is able to do its thing without any dropped connections. It is kind of captivating watching it find each character one by one, slowly printing out the flag.

[18:17:35] [INFO] retrieved: flag{order_by_blind}
Database: <current>
Table: flag
[1 entry]
| flag                 |
| flag{order_by_blind} |
comments powered by Disqus