A03 — SQL Injection: The Attack

By the end of this lesson you can:

• Explain the root cause of SQL injection: mixing code and data.
• Demonstrate auth bypass and data extraction on a local vulnerable demo.
• Recognise the vulnerable code pattern instantly (string-built queries).
• Understand the impact — and why the defence (next lesson) is non-negotiable.

Imagine a login query built by gluing strings:

query = "SELECT * FROM users WHERE name = '" + username + "'"

normal:  username = "aisha"
  → SELECT * FROM users WHERE name = 'aisha'           ✓ fine

attack:  username = "' OR '1'='1"
  → SELECT * FROM users WHERE name = '' OR '1'='1'     ✗ always true!

The vulnerable pattern

import sqlite3
# VULNERABLE — input concatenated/formatted into the SQL string
def login(username, password):
    conn = sqlite3.connect("demo.db")
    query = f"SELECT * FROM users WHERE name = '{username}' AND pw = '{password}'"
    return conn.execute(query).fetchone()       # ← input is now SQL CODE

Any time you see an f-string, +, %, or .format() building a SQL string from input, that's the vulnerability. The database can't tell which parts came from you and which from the attacker.

Attack 1: authentication bypass

# attacker submits username = admin'--   (the -- comments out the rest)
# query becomes:
#   SELECT * FROM users WHERE name = 'admin'--' AND pw = '...'
#                                            ^^^^ everything after -- ignored
# → logs in as admin WITHOUT the password.

The -- starts a SQL comment, discarding the password check entirely. Or ' OR '1'='1 makes the WHERE always true. Either way: login without credentials.

Attack 2: data extraction with UNION

# a search box: SELECT name, email FROM users WHERE name LIKE '%{q}%'
# attacker sets q to:   ' UNION SELECT name, pw FROM users --
# → the query now returns every user's name and PASSWORD HASH.
# UNION lets the attacker append their OWN SELECT onto yours.

UNION SELECT appends attacker-chosen data to the results — letting them dump other tables (passwords, secrets) through an innocent search box. Blind/time-based variants extract data even when results aren't shown.

The impact

auth bypass        log in as anyone, including admin
data theft         dump entire tables (users, passwords, payment data)
data tampering     UPDATE/DELETE arbitrary rows
in severe cases    read/write files, run OS commands (full server takeover)

SQLi is consistently behind some of the largest breaches in history. It's an A03 (Injection) issue, and the cause — untrusted input crossing into the code context — is exactly the trust-boundary failure from Lesson 2.

Goal: build a tiny vulnerable login on a local SQLite DB seeded with fake users, and demonstrate auth bypass — to make the mechanism concrete. Your own machine, your own fake data, only.

import sqlite3

# --- set up a local demo DB with FAKE data ---
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, pw TEXT, role TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?, ?)", [
    ("admin", "super-secret-pw", "admin"),
    ("aisha", "aisha-pw", "user"),
])

# --- VULNERABLE login (string-built query) ---
def vulnerable_login(username: str, password: str):
    query = (f"SELECT name, role FROM users "
             f"WHERE name = '{username}' AND pw = '{password}'")
    print("  SQL:", query)
    return db.execute(query).fetchone()

# normal use:
print("normal login:")
print("  result:", vulnerable_login("aisha", "aisha-pw"))     # ('aisha','user')

# ATTACK: bypass auth with no password
print("\ninjection attack (auth bypass):")
print("  result:", vulnerable_login("admin'--", "anything"))  # logs in as admin!

normal login:
  SQL: SELECT name, role FROM users WHERE name = 'aisha' AND pw = 'aisha-pw'
  result: ('aisha', 'user')

injection attack (auth bypass):
  SQL: SELECT name, role FROM users WHERE name = 'admin'--' AND pw = 'anything'
  result: ('admin', 'admin')      ← logged in as admin with NO password

Read the result

Look at the printed SQL: the attacker's admin'-- closed the name string and commented out the entire password check, so the database happily returned the admin row to someone who never knew the password. Nothing was "hacked" in a fancy sense — the input simply became part of the query. This is the whole mechanism, and it's why string-built SQL is one of the most dangerous patterns in programming. Next lesson, the same demo with parameterised queries makes admin'-- just a (failing) literal username — the attack vanishes.

All against your own in-memory/local demo DB or OWASP Juice Shop's SQLi challenge (Lesson 27) — both safe, intentional targets.

def vuln_search(q):
    return db.execute(
        f"SELECT name FROM users WHERE name LIKE '%{q}%'").fetchall()
# attacker q:  ' UNION SELECT pw FROM users --
# → returns the pw column through the name search

execute(f"... WHERE id = {x}")              ✗ vulnerable (f-string)
execute("... WHERE id = " + str(x))         ✗ vulnerable (concat)
execute("... WHERE id = %s" % x)            ✗ vulnerable (% format)
execute("... WHERE id = ?", (x,))           ✓ safe (parameterised)
execute("... WHERE id = :id", {"id": x})    ✓ safe (named params)

SQL injection (A03) happens when user input is glued into a query string, letting input become code — the trust-boundary failure of Lesson 2. ' OR '1'='1 and admin'-- bypass logins; UNION SELECT dumps other tables; severe cases reach the OS. The vulnerable pattern is any f-string/+/%/.format() building SQL — learn to spot it instantly. We demonstrated it only on a local, deliberately-broken demo with fake data, because understanding the mechanism is what lets you defend it. The complete fix — parameterised queries (separate code from data) — is the entire next lesson.