Spooky Query Leaks

INSERT Query SQLi

We are presented with a simple webpage with a few functionalities.

app.py

from flask import Flask, render_template, request, redirect, session, g
from werkzeug.security import generate_password_hash, check_password_hash
import sqlite3

app = Flask(__name__)
app.secret_key = 'REDACTED'

DATABASE = 'challenge.db'

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row
    return db

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        cursor = get_db().cursor()
        try:
            cursor.execute(f"INSERT INTO users (username, password) VALUES ('{username}', '{generate_password_hash(password)}')")
            get_db().commit()
            return redirect('/login')
        except sqlite3.IntegrityError:
            return "Username already taken."

    return render_template('register.html')

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        cursor = get_db().cursor()

        cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
        user = cursor.fetchone()

        if user and check_password_hash(user['password'], password):
            session['username'] = user['username']
            return redirect('/dashboard')
        else:
            return "Invalid credentials."

    return render_template('login.html')

@app.route('/dashboard')
def dashboard():
    if 'username' not in session:
        return redirect('/login')

    username = session['username']
    cursor = get_db().cursor()

    if username == 'admin':
        cursor.execute("SELECT flag FROM flags")
        flag = cursor.fetchone()['flag']
        return render_template('dashboard.html', username=username, flag=flag)

    return render_template('dashboard.html', username=username, flag=None)

if __name__ == '__main__':
    app.run(debug=False)

setup_challenge.py

import sqlite3
from werkzeug.security import generate_password_hash

DATABASE = 'challenge.db'
FLAG = "REDACTED"

def setup():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        username TEXT UNIQUE NOT NULL,
                        password TEXT NOT NULL
                    )''')

    cursor.execute('''CREATE TABLE IF NOT EXISTS flags (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        flag TEXT NOT NULL
                    )''')

    try:
        cursor.execute("INSERT INTO users (username, password) VALUES (?, ?)",
                       ('admin', generate_password_hash('REDACTED')))
        cursor.execute("INSERT INTO flags (flag) VALUES (?)", (FLAG,))
        conn.commit()
        print("Database setup complete.")
    except sqlite3.IntegrityError:
        print("Admin user or flag already exists.")
    
    conn.close()

if __name__ == '__main__':
    setup()

We see a obvious SQLi vulnerability in the /register route where it is using format strings to form the query instead of parameterized queries in /login.

We need access to the admin user account created during setup, as it will enable us access to /dashboard which is our win function.

We can try to concatenate a simple SELECT * query but we are faced with an error.

Since execute() can only carry out one statement at once, we need to base our exploit of the initial INSERT query.

We also have the constraint of the username field needing to be unique.

Stumbling across this article, we find a way to execute our INSERT query even on a duplicate entry with the use of ON CONFLICT DO UPDATE SET field='value'

We can thus try to execute this query to update the password of admin to 123 with the following query.

#password is the generate_password_hash('123') output
username=admin', 'whoami') ON CONFLICT DO UPDATE SET password='pbkdf2:sha256:260000$lK7miSvf7xXGQW6h$24ec38babe72fa588b841c351d50de909ce77217dccd81619ceccc1f40c1c891';--&password=