Skip to content
ENNO
← Back to blog
·5 min read·

Two Checks Between an LLM and Your Warehouse

An apostrophe in a SQL comment was enough to hide a subquery that read 584,332 raw rows. The two checks a model-written query now has to pass before it runs, and why neither is enough on its own.

Two Checks Between an LLM and Your Warehouse

A client wanted their own customers to ask questions of their marketing data in plain English. The obvious build is to hand a model the schema, let it write SQL and run whatever comes back. That works in a demo after an afternoon. The part that takes the time is making sure a question from one customer can never read another customer's rows.

Why the prompt can't be the guard

The first instinct is to put the rule in the prompt: only query the tables for this client. That holds until someone asks a question the model reads creatively, or pastes in text telling it to do something else. A prompt is a request, so I don't treat anything the model writes as trusted, however good the instructions were.

So the model can write whatever it likes, and everything it writes goes through two checks before it gets near the warehouse. If either one says no, the query is refused.

Diagram of the two gates. A question goes to the model, which writes SQL. Gate one reads the text: one SELECT, nothing that writes, and only this client's mart and dimension tables by name. Gate two is a BigQuery dry run: every table it would really read, resolved through views, must be in this client's dataset or a source dataset the client has declared. Only then does the query run, capped at 2 GB scanned and 500 rows. Either gate refusing stops the assistant instead of letting the model try again.
Two gates, and each one sees something the other can't.

The first check reads the query

The first gate looks at the SQL text. It has to be a single SELECT, with no insert, update, delete, merge or DDL anywhere in it. Every table it names has to be one of this client's modelled tables, the mart and dimension tables the dashboard itself reads. Not another client's dataset, not another project, and not the raw staging tables the marts are built from.

It sounds simple and I still got it wrong the first time. The check strips comments and string literals before looking for table names, and it did that with two regular expressions, strings first and comments second. An apostrophe inside a comment, something as ordinary as -- it's fine, opened a string match that ran to the next quote and swallowed the SQL in between. A subquery sitting there was invisible to the check, and a query shaped that way read 584,332 rows straight out of a raw export table. The model wouldn't even have to be trying. It writes contractions in its comments like everyone else.

The fix was to stop using regex for it. Whether a quote opens a string depends on whether a comment opened first, and the other way round, so the only correct way is one pass from left to right:

ts
while (i < n) { const c = sql[i]; const two = sql.slice(i, i + 2); if (two === "--" || c === "#") { // comment: skip to end of line while (i < n && sql[i] !== "\n") i++; out += " "; continue; } if (c === "'" || c === '"') { // string: blank it out i++; while (i < n && sql[i] !== c) { if (sql[i] === "\\") i++; i++; } i++; out += "''"; continue; } out += c; i++; }

That's trimmed. The real one also handles block comments, triple-quoted and raw strings, and backticked names with escaped backticks in them, and several of those came out of a review whose only job was to break it. There's a script with 36 cases now that I run after every change to that folder. I'd guess a text check always has a hole like that somewhere, which is why it isn't the only one.

It also fails the other way. EXTRACT(DAYOFWEEK FROM d.event_date) has a FROM in it, and for a while the check read d.event_date as a table in a dataset called d and refused a perfectly good question. The client noticed before I did.

The second check asks BigQuery what it would read

BigQuery has a dry run. You submit the query, it plans it without running it, and it tells you which tables it would read, resolved through every view underneath.

ts
const [job] = await bigquery.createQueryJob({ query, dryRun: true, useLegacySql: false, maximumBytesBilled: String(MAX_BYTES), }); const referenced = job.metadata?.statistics?.query?.referencedTables; // Absent is not the same as empty. Empty means the query reads no table. // Absent means BigQuery hasn't said, and "I don't know" has to mean no. if (referenced === undefined && namedTables > 0) throw refused(); for (const ref of referenced ?? []) { if (ref.projectId !== PROJECT || !allowed.has(ref.datasetId)) throw refused(); }

The second gate checks that every one of those tables sits in this client's own dataset or in a source dataset the client is declared to own. I trust it more because it's BigQuery's own parser and not something I wrote, and it sees through whatever the SQL does to hide a reference. The query then runs with a hard cap of 2 GB scanned and 500 rows back, so a runaway scan gets stopped instead of billed.

The declared part matters. The first version worked out a client's source datasets from its own view definitions and filtered out anything that looked like another client's. Raw exports aren't named like client datasets, though, so one copy-pasted view reference while setting up a second client would have handed the first client the second one's Search Console data. Now the list is typed in deliberately per client, and anything another client has claimed is dropped from it even if someone types it by mistake.

Why I kept both

The dry run is blind in one specific way. Because it resolves through views, a mart disappears from the list and you only see what it's built from. A query that reads the raw source table directly looks exactly like a query that reads a mart built on it. The text check catches that, and the dry run catches whatever a view reads that the text can't see. They cover different gaps, and both fail closed. A query that can't be proved safe is refused, never run.

One detail turned out to matter more than I expected. When a query is refused for reaching outside the client, the assistant stops. It doesn't hand the error back to the model and let it have another go in a different shape.

Who the user is never comes from the model

The declared table list comes from the session, not from anything in the request. A user signs in, the session says which company they belong to, and that decides whose marts are in scope. There's no client id in the URL to edit, and nothing in the question can change it. The model never gets told who it's serving in a way it could be talked out of. The only tables it can reach just happen to belong to one company.

Each question costs three or four cents, almost all of it the model call. The dry run itself is free.

None of this is BigQuery specific. In Postgres, EXPLAIN shows the plan with views expanded, and a role per tenant with row-level security gets you the same guarantee from the database itself. What I'd push back on is any setup where the only thing between a question and the wrong customer's data is a sentence in a system prompt.

If you're building something like this, it's the kind of work I do.

#AI#LLM#text-to-SQL#BigQuery#security#multi-tenant