The Statement Allowlist: Only `SELECT`/`WITH`, a Single One

Description

The previous lesson shielded the connection: even if a DELETE arrives, the engine rejects it. This lesson builds the layer that acts before that, at the text level: the statement allowlist, a filter that looks at the SQL string and decides whether it has permission to run without even touching the connection. It's the first door in the system, and the one that gives clear rejection messages ("statement not allowed: starts with DROP") instead of a bare engine OperationalError.

The rule is deliberately strict: exactly one statement is allowed, and that statement must start with SELECT or WITH. Everything else gets rejected: DROP, DELETE, UPDATE, INSERT, ALTER, ATTACH, a write PRAGMA, and any attempt to smuggle two statements with a ;. It's not a list of forbidden things (that would be a blocklist, and you'll see why it's dangerous); it's a list of the only things allowed (an allowlist), and everything not explicitly allowed gets denied.

We'll reuse the strip_sql you already built in Module 4 — cleaning comments and whitespace — and turn it, from a shape check (is it a read?), into a full-blown security guardrail.

Connection to the module

In M4, looking at the first word was a correctness validation: a DELETE isn't a read query. Here that same check is safety: a DELETE must not be able to execute. It's the same line of code with a different intent and a different backup (the read-only connection from lesson 03, behind it). The allowlist and the read-only connection are two layers for the same danger: if an edge case fools one, the other catches it. That redundancy is lesson 07's theme.

Analogy: the guest list, not the banned list

There are two ways to control who gets into a party.

The first is a banned list: "don't let So-and-so or What's-their-name in." The problem is obvious: it only works against the names you anticipated. Someone shows up who isn't on your banned list — because you didn't know them, or because they changed their name — and they walk in. Every time you discover a new gatecrasher, you add their name... and you're always one step behind.

The second is a guest list: "only let these three names in, no one else." Now it doesn't matter how many strangers show up or what they're called: if they're not on the list, they don't get in. You don't have to anticipate every possible gatecrasher; you anticipated the allowed ones, who are few and known.

In security, the banned list is called a blocklist and the guest list an allowlist. For an LLM's SQL, the allowlist is the only sensible defense: the dangerous verbs are many and vary across dialects (DROP, DELETE, TRUNCATE, REPLACE, ATTACH, VACUUM…), but the allowed verbs are just two, SELECT and WITH. You enumerate the good, which is short, and deny everything else by default.

Why a blocklist is a mistake

Before building the allowlist, let's see why its alternative — blocking a list of bad verbs — fails. Suppose you write:

# ANTI-PATTERN: blocklist. Do NOT do this.
BLOCKED = {"DROP", "DELETE", "UPDATE", "INSERT", "ALTER", "TRUNCATE"}

def is_dangerous_blocklist(sql):
    first = sql.strip().split(None, 1)[0].upper()
    return first in BLOCKED

It looks reasonable, and it blocks the obvious suspects. But things slip through, and it only takes one:

• ATTACH DATABASE 'evil.db' AS evil — not on your list, and it lets you mount another database: a data leak and a write path.
• PRAGMA query_only = OFF — not on your list, and — as you saw in lesson 03 — it turns off the read-only guardrail.
• REPLACE INTO members … — an alias for INSERT OR REPLACE that doesn't start with INSERT; it slips through.
• VACUUM, REINDEX, and any verb that exists in your dialect or in the engine's next version that you didn't anticipate.

Each of these is a gatecrasher your banned list didn't see coming. And it only takes one slipping through for the defense to fail. That's the structural flaw of the blocklist: it forces you to know everything bad in advance, and bad is open-ended and unlimited. The allowlist flips the burden: you enumerate the good (two verbs) and deny the rest by default, without having to know it.

Building the allowlist

The allowlist reuses M4's strip_sql (cleaning comments and whitespace, so a model that prepends -- explanation doesn't fool the check) and applies three rules: not empty, a single statement, starts with SELECT/WITH.

import re

def strip_sql(sql):
    """(From M4) Removes line and block comments, and leading/trailing whitespace."""
    sql = re.sub(r"--[^\n]*", "", sql)
    sql = re.sub(r"/\*.*?\*/", "", sql, flags=re.DOTALL)
    return sql.strip()

ALLOWED_FIRST = ("SELECT", "WITH")

def check_allowlist(sql):
    """Allows ONE statement starting with SELECT or WITH. Returns (ok, reason)."""
    clean = strip_sql(sql)
    if not clean:
        return False, "empty query"
    body = clean.rstrip(";").strip()          # removes a legitimate trailing ;
    if ";" in body:                           # a ; left in the body? -> 2 statements
        return False, "more than one statement (possible injection)"
    first = body.split(None, 1)[0].upper()    # first word, uppercased
    if first not in ALLOWED_FIRST:
        return False, f"statement not allowed: starts with {first}"
    return True, "single-statement SELECT"

Three rules, in order from cheap to less cheap: non-empty string, a single statement, allowed verb. Notice that the allowlist doesn't query the database — it only looks at the text — it's the cheapest layer and the first to act. Let's test it with a range of inputs, good and bad:

tests = [
    "SELECT name FROM rooms",                          # simple read
    "WITH c AS (SELECT * FROM rooms) SELECT name FROM c",  # CTE, also a read
    "DELETE FROM bookings",                            # write
    "DROP TABLE rooms",                                # destructive DDL
    "UPDATE rooms SET name='x'",                       # write
    "SELECT 1; DROP TABLE rooms",                      # multi-statement (injection)
    "SELECT * FROM rooms; DELETE FROM bookings",       # multi-statement
    "PRAGMA query_only = OFF",                          # turning off the guardrail
    "ATTACH DATABASE 'evil.db' AS evil",              # mounting another database
    "-- comment\nSELECT 1",                            # comment + legitimate read
]
for s in tests:
    ok, why = check_allowlist(s)
    tag = "PASS  " if ok else "REJECT"
    print(f"[{tag}] {why:45} <- {s[:40]!r}")

What to expect:

[PASS  ] single-statement SELECT                       <- 'SELECT name FROM rooms'
[PASS  ] single-statement SELECT                       <- 'WITH c AS (SELECT * FROM rooms) SELECT n'
[REJECT] statement not allowed: starts with DELETE      <- 'DELETE FROM bookings'
[REJECT] statement not allowed: starts with DROP        <- 'DROP TABLE rooms'
[REJECT] statement not allowed: starts with UPDATE      <- "UPDATE rooms SET name='x'"
[REJECT] more than one statement (possible injection)    <- 'SELECT 1; DROP TABLE rooms'
[REJECT] more than one statement (possible injection)    <- 'SELECT * FROM rooms; DELETE FROM booking'
[REJECT] statement not allowed: starts with PRAGMA      <- 'PRAGMA query_only = OFF'
[REJECT] statement not allowed: starts with ATTACH      <- "ATTACH DATABASE 'evil.db' AS evil"
[PASS  ] single-statement SELECT                       <- '-- comment\nSELECT 1'

Ten inputs, ten verdicts. Both reads (SELECT and WITH) pass. Everything else is rejected — and notice what the allowlist caught that a naive blocklist would have let through: the PRAGMA query_only = OFF and the ATTACH. We didn't have to "know" them: since they don't start with SELECT/WITH, they fall by default. The last entry is the most instructive: a SELECT with a comment prepended passes, because strip_sql cleaned the comment before looking at the first word. The allowlist is strict with the dangerous and fair with the legitimate.

The two defenses against multi-statements

The "single statement" rule deserves a closer look, because it's the one that catches the classic stacked queries injection — smuggling a DROP behind a SELECT. The allowlist hunts it by counting ; in the body (after stripping a legitimate trailing ;). But, as you'll recall from M4, there's a second net: sqlite3's con.execute() method refuses to run more than one statement. Let's verify it again, now as a safety guardrail:

import sqlite3
con = sqlite3.connect("reservo.db")
try:
    con.execute("SELECT 1; DROP TABLE rooms")
except sqlite3.Error as e:
    print(f"{type(e).__name__}: {e}")
con.close()

What to expect:

ProgrammingError: You can only execute one statement at a time.

Two layers for the same danger: the allowlist rejects the multi-statement by text (with a clear message), and execute() refuses to run it even if the ; check had missed it (for example, a ; hidden inside a literal). It's executescript() — not execute() — that runs several statements, and in a safe executor executescript is never used with model SQL. Keeping execute() as the only execution point is itself a guardrail.

The allowlist goes before the connection: why order matters

The allowlist acts on the text, before the SQL touches the database. The read-only connection (lesson 03) acts on the engine, once the SQL has already arrived. Why have both, if the read-only connection already blocks writes?

For three concrete reasons:

1. Clear messages. The allowlist says "statement not allowed: starts with DROP" — actionable, loggable, and can even be handed back to the model in the correction loop. The read-only connection says attempt to write a readonly database, a more opaque engine error.
2. Things the connection doesn't cover. mode=ro blocks writes, but a multi-statement (SELECT …; SELECT …) or a read PRAGMA aren't writes, and you still don't want to run them. The allowlist catches these; mode=ro doesn't.
3. Cheap before expensive. Rejecting on text doesn't spend a connection or an engine cycle. You filter the obviously bad before investing resources in opening and executing.

And conversely, the read-only connection covers what might slip past the allowlist (an edge case in the ; parsing, an unusual verb starting with something unexpected). Neither is sufficient alone; together, each covers the other's gap. That's the module's principle, again.

What if I want to allow more than reads?

Sometimes an assistant legitimately needs to write — logging a fact, flagging something. The allowlist doesn't forbid that philosophically; it forces you to be explicit. You expand the allowed set knowingly, verb by verb, aware of what you're opening up:

def check_allowlist_rw(sql, allowed=("SELECT", "WITH", "INSERT")):
    clean = strip_sql(sql)
    if not clean:
        return False, "empty query"
    body = clean.rstrip(";").strip()
    if ";" in body:
        return False, "more than one statement"
    first = body.split(None, 1)[0].upper()
    if first not in allowed:
        return False, f"verb not allowed: {first}"
    return True, f"allowed: {first}"

print(check_allowlist_rw("INSERT INTO members(name,tier) VALUES ('X','pro')"))
print(check_allowlist_rw("DELETE FROM members WHERE id=1"))

What to expect:

(True, 'allowed: INSERT')
(False, 'verb not allowed: DELETE')

The point is that expanding is an explicit, bounded decision (you add INSERT to the set, not everything else), and if you allow writing you must remove mode=ro/query_only — which reintroduces risk you need to compensate for with other measures (per-table least privilege, validating the values). For a query assistant, the correct set is the minimum: only SELECT and WITH. The shorter the allowlist, the smaller the attack surface.

Common mistakes

1. Using a blocklist. Enumerating forbidden verbs (DROP, DELETE…) always leaves gaps: ATTACH, PRAGMA, REPLACE, VACUUM, or the next verb you didn't anticipate. The allowlist enumerates what's allowed (two verbs) and denies the rest by default. It's the only robust option.

2. Forgetting to strip comments before looking at the first word. A model that prepends -- explanation or /* … */ would make the "first word" the comment, and you'd reject a legitimate SELECT. strip_sql (from M4) runs before the check.

3. Forgetting WITH. A CTE (WITH … SELECT) is a perfectly valid and common read. If the allowlist only accepts SELECT, you reject legitimate queries. Both verbs go in.

4. Trusting only the ; count. It's pragmatic and catches most multi-statements, but a ; inside a text literal can fool it. That's why the second net is con.execute(), which refuses to run two statements, and why executescript is never used with model SQL.

5. Believing the allowlist replaces the read-only connection. No: they complement each other. The allowlist acts on the text (clear messages, catches multi-statements and PRAGMA); the read-only connection acts on the engine (hard guarantee against writing). If one fails, the other catches it.

Exercises

Exercise 1: The lowercase verb with extra whitespace (Easy)

Does the allowlist accept " select name from rooms " (lowercase and with extra whitespace)? What about "\n\nSELECT 1"? Run them and explain why the check isn't fooled by upper/lowercase or by whitespace.

print(check_allowlist("  select name from rooms  "))
print(check_allowlist("\n\nSELECT 1"))

(True, 'single-statement SELECT')
(True, 'single-statement SELECT')

Exercise 2: The PRAGMA the blocklist wouldn't see (Medium)

Demonstrate the allowlist's superiority: write a naive blocklist that blocks {DROP, DELETE, UPDATE, INSERT} and show that it lets through PRAGMA query_only = OFF and ATTACH DATABASE 'evil.db' AS evil, while the allowlist rejects them.

BLOCKED = {"DROP", "DELETE", "UPDATE", "INSERT"}
def blocklist_ok(sql):
    first = strip_sql(sql).split(None, 1)[0].upper()
    return first not in BLOCKED   # True = "lets it through"

for s in ["PRAGMA query_only = OFF", "ATTACH DATABASE 'evil.db' AS evil"]:
    print(f"blocklist lets through: {blocklist_ok(s)!s:5} | allowlist: {check_allowlist(s)[1]}  <- {s!r}")

blocklist lets through: True  | allowlist: statement not allowed: starts with PRAGMA  <- 'PRAGMA query_only = OFF'
blocklist lets through: True  | allowlist: statement not allowed: starts with ATTACH  <- "ATTACH DATABASE 'evil.db' AS evil"

Exercise 3: Catching the stacked-query injection (Hard)

A malicious user got the model to return "SELECT * FROM rooms; DROP TABLE bookings". Show that this SQL is rejected by two independent layers: (a) the allowlist, by text; (b) con.execute, by engine. Explain why having both matters.

import sqlite3
attack = "SELECT * FROM rooms; DROP TABLE bookings"

# Layer (a): allowlist, on the text
print("allowlist ->", check_allowlist(attack))

# Layer (b): con.execute refuses to run two statements
con = sqlite3.connect("reservo.db")
try:
    con.execute(attack)
except sqlite3.Error as e:
    print("execute  ->", f"{type(e).__name__}: {e}")
print("bookings intact:", con.execute("SELECT COUNT(*) FROM bookings").fetchone()[0])
con.close()

allowlist -> (False, 'more than one statement (possible injection)')
execute  -> ProgrammingError: You can only execute one statement at a time.
bookings intact: 23

Summary and next step

• The allowlist acts at the text level, before touching the connection: it's the first door and the one that gives clear rejection messages.
• The rule is strict: a single statement, starting with SELECT or WITH. Everything else — DROP, DELETE, UPDATE, INSERT, ALTER, ATTACH, PRAGMA, multi-statement — gets rejected.
• Allowlist, not blocklist. Enumerating what's forbidden always leaves gaps (ATTACH, PRAGMA, REPLACE…); enumerating what's allowed (two verbs) and denying the rest by default is the only robust defense.
• M4's strip_sql is reused (cleaning comments/whitespace before looking at the first word), turning the shape check into a security guardrail.
• Multi-statements have two defenses: the ; count in the allowlist and con.execute refusing to run two statements. executescript is never used with model SQL.
• Allowlist (text) and read-only connection (engine) complement each other: clear messages and early catches vs. a hard guarantee of no writing.

Next lesson: Resource limits — A legitimate SELECT can also cause harm if it returns millions of rows or runs forever. You'll see how to force a LIMIT by wrapping the query, kill a slow query with a timeout via set_progress_handler (executed, aborting a heavy query), and cap the rows returned.

Additional resources

1. OWASP — Input Validation Cheat Sheet (Allow-list vs Deny-list) — Why enumerating what's allowed (allowlist) is more robust than enumerating what's forbidden (blocklist).
2. SQLite — Query Language: SELECT and WITH (CTE) — The only two forms of reading the allowlist permits.
3. Python — sqlite3.Connection.execute — Runs a single statement; the second net against multi-statements. Compare it to executescript, which does run several (and which is never used with model SQL).
4. OWASP — SQL Injection (Stacked Queries) — The "stacked queries" injection (SELECT …; DROP …) that the single-statement rule catches.
5. SQLite — ATTACH DATABASE — An example of the kind of statement a naive blocklist doesn't anticipate and the allowlist denies by default.