Lab: Capture the Flag

Due: Wednesday, December 16 at 11:59pm (one week after it is assigned) Worth: 5 extra-credit points

This optional extra-credit capstone has no starter repository; a Gradescope grader checks the structured writeup. You will use SQL injection to break a login, capture the flag hidden behind it, and then patch the vulnerability so the same attack fails. The lab connects the offensive work from the password-cracking lab to the defensive SQL injection checks required for your Twitter clone.

Optional / extra credit. Nothing here is required, and skipping it costs you zero points. Bring your writeup and be ready to explain how your exploit works.

In security, a capture-the-flag challenge hides a secret string, the flag, somewhere only a working exploit can reach. You prove that the exploit worked by pasting the flag into your submission. Our flags look like CTF{...}, and there are two of them.

The target

The target is a login route that builds its SQL query by joining the user’s input directly into a string.

"SELECT * FROM users WHERE username='" + name + "' AND password='" + pw + "'"

The user controls what lands inside those quotes and can send SQL syntax instead of a username or password.

To attack it you need a copy of it running. Below is a tiny, self-contained target: a whole vulnerable web app in about thirty lines. It keeps a throwaway database in memory with two users, bob and admin, each guarding a flag, and each with a strong random password you are not meant to know. Save it as ctf_target.py.

# ctf_target.py -- a DELIBERATELY vulnerable login. Run it ONLY on your own machine.
import sqlite3
import uvicorn
from fastapi import FastAPI, Form
from fastapi.responses import HTMLResponse

# A throwaway in-memory database with two users, each hiding a secret flag.
# check_same_thread=False lets FastAPI's worker threads share this one connection;
# we only ever read from it, so that is safe here.
db = sqlite3.connect(':memory:', check_same_thread=False)
db.execute('CREATE TABLE users (username TEXT, password TEXT, flag TEXT)')
db.executemany('INSERT INTO users VALUES (?, ?, ?)', [
    ('bob',   'S6p!nwQ2zx8L', 'CTF{single_quotes_are_a_skeleton_key}'),
    ('admin', 'k9$Rf7vT1mAe', 'CTF{a_dash_dash_walked_past_the_password}'),
])
db.commit()

app = FastAPI()

FORM = '''
<h2>CS40 CTF login</h2>
<form method="post">
  <input name="username" placeholder="username">
  <input name="password" placeholder="password" type="password">
  <button>Log in</button>
</form>
'''

@app.get('/', response_class=HTMLResponse)
async def form():
    return FORM

@app.post('/', response_class=HTMLResponse)
async def login(username: str = Form(''), password: str = Form('')):
    # NEVER build a query like this. Gluing user input into SQL is the whole bug.
    sql = "SELECT username, flag FROM users WHERE username='" + username + "' AND password='" + password + "'"
    row = db.execute(sql).fetchone()
    if row:
        return FORM + f'<p>Logged in as <b>{row[0]}</b>. Flag: <code>{row[1]}</code></p>'
    return FORM + '<p>Wrong username or password.</p>'

if __name__ == '__main__':
    uvicorn.run(app, host='127.0.0.1', port=8000)

The app uses the FastAPI structure from the backend reading: two routes at /, a GET that shows the form and a POST that checks the login. The flag column is returned after a successful login. Install the three libraries and run it:

pip3 install fastapi uvicorn python-multipart
python3 ctf_target.py

Then open http://127.0.0.1:8000 in your browser. (If port 8000 is busy because your own Twitter clone is running, change the number in the last line.) Try logging in as bob with the password password. It fails, as it should, because you do not know Bob’s password. You will bypass the password through the query.

Warning. Run this only against your own copy on your own machine. Attacking a computer you do not own or have written permission to test is a crime in the US under the Computer Fraud and Abuse Act. Only test systems when the owner has given you permission.

Break in

Go to the login form and, in the password box, type exactly this, then submit:

' OR '1'='1

You are logged in, and a flag appears. Read what your input did to the query the server built:

SELECT username, flag FROM users WHERE username='bob' AND password='' OR '1'='1'

Your leading ' closed the password string early, and OR '1'='1' added a condition to the WHERE clause that is true for every row in the table. AND binds tighter than OR, so the database reads it as “(right username and empty password) or this-is-always-true” and returns every user. The server calls .fetchone(), grabs the first row, and logs you in as whoever that is: bob. You captured bob’s flag by changing the query rather than supplying the password.

The first injection logs you in as the first row returned. To choose the account, inject SQL through the username box. Log out, and this time type into the username field:

admin'--

Put anything at all in the password box. The login succeeds as admin and produces this query:

SELECT username, flag FROM users WHERE username='admin'--' AND password='...'

The -- begins a SQL comment, so the database ignores the rest of the line, including the password check. What runs is SELECT username, flag FROM users WHERE username='admin'. Swap in bob'-- to log in as bob; the comment bypass lets you choose the username.

Capture both flags. Also type a lone ' into either box and submit: the query becomes malformed, and you get a SQL error in the terminal (an Internal Server Error in the browser). The error shows that your input is reaching the query as code.

Patch it

Fix the login and confirm the repair with the same attacks. Never let user input enter the query’s structure. Hand the query and the values to execute separately, marking each slot with a ? placeholder and passing the actual values in a tuple. Change only the two lines inside the login route:

@app.post('/', response_class=HTMLResponse)
async def login(username: str = Form(''), password: str = Form('')):
    sql = "SELECT username, flag FROM users WHERE username=? AND password=?"
    row = db.execute(sql, (username, password)).fetchone()
    if row:
        return FORM + f'<p>Logged in as <b>{row[0]}</b>. Flag: <code>{row[1]}</code></p>'
    return FORM + '<p>Wrong username or password.</p>'

Restart the server and rerun both exploits. ' OR '1'='1 in the password box now fails. admin'-- in the username box now fails. sqlite3 treats each ? as a slot for one value and nothing else. The username is therefore the literal string admin'--, which does not match an account. The quotes and comment marker cannot change the query’s grammar; they are characters in a string that does not match a username. A login with a real password still works.

Parameterized queries are the standard fix for this injection bug. Use the ? placeholder from the SQL injection section of the backend reading in the final project: a Twitter clone that is vulnerable to this attack loses ten points. Every execute call in your project must use placeholders for user-supplied values.

Go further (optional)

The reading’s send-off was Stripe’s old Capture the Flag competition, a ladder of websites each with a planted vulnerability, where breaking one level opens the next. Level 3 of the 2012 CTF is a small Flask app hiding the exact SQL injection you just practiced. Download it, log in as bob without his password, and come explain your exploit. It is old code that expects Python 2, so setup may take some work. Current competitions are indexed at ctftime.org.

Companies run bug bounty programs that invite people to find a real vulnerability, report it privately, and receive a reward while the company fixes it. Stripe, which moves billions of dollars in payments, runs one on HackerOne, and platforms like it have routed hundreds of millions of dollars to ethical hackers over the years. Security testing requires the system owner’s permission.

Submitting

This capstone is optional extra credit. Create a file named answers.md containing:

1. the injection strings that worked, ' OR '1'='1 and admin'--, with a sentence each on why they worked,
2. both captured flags,
3. the patched query, and a line on why the ? placeholder defeats the same attack.

Commit answers.md to a GitHub repository and submit its repository and branch to the Gradescope Programming Assignment. The instructor-owned tests check the exact injection strings, both flags, the parameterized query, and that your explanations are present; their result is authoritative. Any screenshot is optional and any GitHub Action is preliminary feedback. If you completed more of the Stripe CTF, mention it.