The guarantee lives in the execution layer

I built a natural-language-to-SQL system for a client whose financial records are evidence. Analysts ask questions in plain English, a locally hosted model writes the SQL, and the results come back with the query attached. In that setting, a query that silently modified the records would not be a bug, it would be a disaster with a chain-of-custody problem attached.

So the design question was never “how do I make the model write safe SQL.” Models are stochastic. Prompts are suggestions. The question was: where can I put a guarantee that holds even when the model misbehaves, the prompt is adversarial, or I am wrong about something?

The tempting answer is a parser

The instinct everyone has first, including me, is to inspect the SQL before running it. Parse the query, walk the AST, allow SELECT, reject everything else. Maybe a keyword blocklist as a backstop.

Here is the problem: a SQL parser used as a security boundary is a second implementation of SQL that has to agree with the real one in every case that matters. SQLite’s grammar has CTEs, attached databases, comments in strange places, and dialect quirks. An attacker, or an unlucky model, needs to find one divergence between what your parser thinks a query does and what the engine actually does. You need to have zero. That is not a fight you sign up for if you have a choice.

And with SQLite, you have a choice.

Enforcement at the connection

The chokepoint in my system does not parse anything. The connection the queries run on is opened read-only at the database level, and an authorizer callback closes the remaining doors:

def _open_readonly(db_path):
    conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)

    def authorizer(action, *args):
        if action in (sqlite3.SQLITE_ATTACH, sqlite3.SQLITE_DETACH):
            return sqlite3.SQLITE_DENY
        return sqlite3.SQLITE_OK

    conn.set_authorizer(authorizer)
    return conn

mode=ro means the engine itself refuses writes. The authorizer denies ATTACH, so a query cannot connect a second, writable database and smuggle changes through it. There is nothing to fool because nothing is inspecting text. An INSERT does not get cleverly past the guard; it reaches the engine and the engine says no.

Every query where a user or the model influences the SQL text goes through one audited function on that connection. It caps results at 5,000 rows, and it logs the SQL string verbatim, where it came from, and what happened: row count on success, the full error text on failure. Refusals are evidence too.

One honest nuance: the application’s own bookkeeping writes use a separate writer connection with fixed, literal SQL. The guarantee is scoped precisely: any SQL an outside party can influence executes on a connection that cannot write. Guarantees you can state precisely are the only kind worth having.

The attack taxonomy collapses

Security reviews of NL-to-SQL systems usually walk a checklist: stacked statements, writes hidden inside CTEs, keyword games with comments. What I like most about connection-layer enforcement is what it does to that list.

Stacked statements: Python’s sqlite3 driver executes one statement per call, and the second statement would be read-only anyway. Writes inside CTEs: the write reaches the engine and the engine refuses it, no matter how it is dressed. Comment obfuscation: obfuscation only defeats things that read the text, and nothing here reads the text.

The test suite drives six write shapes through the chokepoint, INSERT, UPDATE, DELETE, DROP, CREATE TABLE, and ATTACH, and asserts each one fails at the connection. The tests read like an attacker’s checklist, which is what guardrail tests should look like. Not “does the happy path work,” but “here are the six ways I would try to hurt this, and proof that each one bounces.”

Being honest about the approval step

The workflow has a human in the loop: generated SQL lands in an editable text area, the analyst reviews it, and what they run is what runs. A security reviewer will notice immediately that there is no server-side binding between the approved string and the executed one. The query could change between approval and execution.

That is true, and it is by design, and the interface says so honestly: the label on the box reads, in effect, this exact text is what executes and what is logged. The guarantee was never “the string cannot change.” Editing the query is the analyst’s job. The guarantee is that whatever arrives at execution cannot write and is recorded verbatim, so the audit trail always reflects what actually ran.

This is the general principle hiding inside the specific one. Do not put the guarantee where you cannot enforce it. A signed hash binding approval to execution would defend a property this system does not need, while the property it does need, nothing writes and everything is logged, lives at a layer where enforcement is mechanical.

The same move, everywhere it matters

Once you start thinking this way, you make the same move all over the system.

The model endpoint must be loopback. Not as a deployment convention, as an enforced check, with tests asserting that hostile endpoint URLs, including a well-known cloud API, are rejected. Records never leave the machine because the software refuses to send them anywhere, not because the configuration happens to be right today.

The build itself enforces network isolation: a static scan of the source tree fails the build if any module other than the one model client file imports a network library. A future contributor, including future me, cannot casually add an HTTP call somewhere deep in the ingest code. The invariant “only one file talks to the network” is checked by machinery, not by review vigilance.

And every model call runs at temperature zero with a fixed seed, logging prompt, response, model identifier, parameters, and duration. When an analyst asks why the system produced a query last month, the answer is on disk, not in anyone’s memory.

Hope is not a control

Enterprise AI conversations are full of policy language: the model should not, users must not, the prompt instructs it to refuse. Every “should” in that sentence is a place where the system depends on good behavior, and stochastic systems plus adversarial inputs eventually visit every behavior they are capable of.

The pattern I keep coming back to is simple to state. Find the property you actually need. Find the lowest layer where that property can be enforced mechanically, the connection, the build, the network stack, the budget meter. Enforce it there, log everything, and then let the model be as clever or as wrong as it wants above that line.

If your assurance depends on the model behaving, you do not have assurance. You have hope with a dashboard.