"""sql-injection-for-people-who-write-queries.py — a self-contained regression test for parameterised queries.

What it does: builds a throwaway SQLite database in memory, then runs the SAME lookup two
ways: once with string concatenation (the defect, CWE-89) and once with a bound parameter
(the fix). It feeds both a handful of hostile-looking inputs and asserts that the bound
version returns only the row whose name exactly equals the input. It is a unit test you can
drop into your own suite: point `lookup_safe` at your real query function and keep the
assertions. It never touches a network or any database but the in-memory one it creates.

Inputs:  none.
Output:  a pass/fail line per input, and a non-zero exit code if the bound query ever
         returns anything other than an exact match.

Run:     python code/sql-injection-for-people-who-write-queries.py
Standard library only (Python 3.9+).
"""
from __future__ import annotations

import sqlite3
import sys

ROWS = [("alice", "alice@example.org", 120), ("bob", "bob@example.org", 80), ("carol", "carol@example.org", 5)]

# Inputs that are legitimate-looking but contain SQL metacharacters. A safe query treats every
# one of them as an opaque string and returns nothing (no user has these names).
PROBES = [
    "alice",                     # the honest case: must return exactly alice
    "o'brien",                   # a real surname with a quote in it
    "alice' OR '1'='1",          # the textbook tautology
    "alice'; --",                 # comment terminator
    "%",                         # LIKE wildcard, harmless in an = comparison
    "alice\x00",                 # embedded NUL
]


def make_db() -> sqlite3.Connection:
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE users (name TEXT PRIMARY KEY, email TEXT NOT NULL, balance INTEGER NOT NULL)")
    con.executemany("INSERT INTO users VALUES (?, ?, ?)", ROWS)
    return con


def lookup_concat(con: sqlite3.Connection, name: str) -> list[tuple]:
    """The defect: the input becomes part of the SQL text. Shown only so the test can prove the difference."""
    sql = "SELECT name, email FROM users WHERE name = '" + name + "'"
    try:
        return con.execute(sql).fetchall()
    except sqlite3.Error as e:  # a syntax error is the *good* outcome for a hostile probe
        return [("<sql error>", str(e).splitlines()[0])]


def lookup_safe(con: sqlite3.Connection, name: str) -> list[tuple]:
    """The fix: the SQL text is fixed; the value travels separately as a bound parameter."""
    return con.execute("SELECT name, email FROM users WHERE name = ?", (name,)).fetchall()


def main() -> int:
    con = make_db()
    failures = 0
    print(f"{'input':<22}{'concatenated':<40}{'parameterised'}")
    for p in PROBES:
        shown = repr(p)
        c = lookup_concat(con, p)
        s = lookup_safe(con, p)
        expected = [(p, f"{p}@example.org")] if p in {r[0] for r in ROWS} else []
        ok = s == expected
        failures += 0 if ok else 1
        print(f"{shown:<22}{str(c)[:38]:<40}{s}  {'ok' if ok else 'FAIL'}")
    # The regression assertion you keep: a bound query must never widen a match.
    hostile = [p for p in PROBES if p not in {r[0] for r in ROWS}]
    for p in hostile:
        assert lookup_safe(con, p) == [], f"bound query returned rows for {p!r}"
    print(f"\n{len(PROBES)} probes, {failures} failures in the parameterised path.")
    return 1 if failures else 0


if __name__ == "__main__":
    sys.exit(main())