Parameterized Queries Can't Help Here: 6 SQLite Traps When an LLM Writes Your SQL

The standard answer to SQL injection is "use parameterized queries". It's good advice, and it doesn't help at all once a language model writes the entire statement. There's no user value to bind. The query itself is the untrusted input.

I spent a week building the guard for an "ask your shop data" assistant on .NET 10 with SQLite. The big design decision came early: don't parse SQL in C#. SQLite already has a full parser for its own dialect, so the guard compiles each statement and asks the engine what it would do. The surprises came later, in the details. Here are six SQLite and Microsoft.Data.Sqlite behaviours that would each have let something through.

The project uses Microsoft.Data.Sqlite 10.0.13 (SQLite 3.53 via SQLitePCLRaw) and Microsoft.Extensions.AI 10.11.0 for the structured model output. Code below is trimmed to the shape; omitted parts are marked.

Tech Skill Builder is $5/month for a very limited time only (normally $10). Grab it now before it goes back to $10. The complete text-to-SQL guard from this post, with a step-by-step PDF, is inside Tech Skill Builder.

Trap 1: you read one row, and the DELETE still runs

I ran SELECT id FROM orders LIMIT 1; DELETE FROM orders through a plain SqliteCommand, read a single row, and let the using block dispose the reader. Afterwards the table had zero rows.

That's documented behaviour: Microsoft.Data.Sqlite supports batches, and disposing the reader executes any statements that haven't been consumed yet. So a guard that only looks at the first statement passes, and the second one runs anyway.

The fix is to compile the statement yourself with sqlite3_prepare_v2 and then compile the tail. If the tail produces a real statement handle, refuse:

var rc = raw.sqlite3_prepare_v2(db, sql, out sqlite3_stmt stmt, out string tail);
using (stmt)
{
    if (rc != raw.SQLITE_OK) { /* a SQL error, not a violation: eligible for repair */ }
    if (HasAnotherStatement(db, tail)) { /* refuse: multiple_statements */ }
    // read-only, EXPLAIN, bound parameters and result-column checks: in the complete project
}

Don't settle for "the tail isn't empty", by the way. Models love ending with ; -- done, and a comment-only tail compiles to an empty handle, which is fine.

Trap 2: "read-only" is narrower than it sounds

sqlite3_stmt_readonly looks like the perfect check. But per the SQLite docs, it reports true for BEGIN, ATTACH and DETACH, because they don't write to the database file. That's correct from SQLite's point of view, and it's not what you want for model output.

So I still use it, but it isn't the main lock. The main lock is the next one.

Trap 3: SQLITE_IGNORE turns a refusal into wrong data

sqlite3_set_authorizer registers a callback that SQLite calls while compiling: once per table/column read, per function, per statement type. That's the real allowlist. My callback allows SELECT and RECURSIVE, allows reads only of columns in the catalog, allows about 30 listed functions, and denies everything else:

internal PolicyViolation? Decide(int actionCode, string? p0, string? p1) => actionCode switch
{
    raw.SQLITE_SELECT or raw.SQLITE_RECURSIVE => null,
    raw.SQLITE_FUNCTION => DecideFunction(p1),       // allowlisted names only
    raw.SQLITE_READ     => DecideRead(p0 ?? "", p1 ?? ""), // catalog tables and columns only
    _ => new("statement_not_allowed", ActionName(actionCode)),
};
// DecideFunction, DecideRead and ActionName: full implementation in the complete project

The trap is the return value. For a column read, SQLITE_IGNORE makes SQLite return NULL instead of failing. Nothing errors, and that's exactly the problem: "show me customer emails" answers with a column of NULLs, and the user concludes you have no emails on file. I return SQLITE_DENY, so the statement fails to compile and the user gets an honest refusal.

Trap 4: count(*) reads a column with no name

Hiding a table from the catalog isn't enough if SELECT count(*) FROM payout_accounts still works. It tells an attacker how many rows exist.

The authorizer does fire for that query, just with an empty string as the column name (the SQLite docs describe exactly this). It's easy to write a rule that treats "no column" as "nothing to check". Mine allows the empty column only for catalog tables and denies it for any real table that isn't in the catalog. A test named Denies_hidden_tables_even_for_count_star keeps it that way.

Trap 5: pooled connections keep your hooks

Since version 6.0, Microsoft.Data.Sqlite pools connections by default. The authorizer and the progress handler (which I use for query deadlines) live on the native sqlite3 handle, not on your SqliteConnection object.

In a spike I installed a deny-all-reads authorizer, closed the connection, opened a new one with the same connection string, and ran a simple SELECT. It failed with "access to orders.id is prohibited". The hook had survived through the pool. With Pooling=False, the same second query worked.

Either direction is bad: unrelated code can inherit your guard, or your guarded path can get a handle without it. Every guarded connection in the project is unpooled and opened read-only, and both hooks are removed in a finally. There's a test for it, Hooks_do_not_leak_between_runs.

Trap 6: the repair loop becomes a search engine

Models get column names wrong, and feeding SQLite's error back ("no such column: category_name") usually fixes it on the next try. So a repair loop is worth having.

But only for honest mistakes. If you also send back "access to customers.email is prohibited", you've told the model what's off limits and asked it to try another way in. In my service, SQL errors get up to two repair rounds (MaxRepairAttempts = 2). Policy violations end the request as Refused immediately, the model never sees why, and timeouts aren't repaired either:

if (run.Kind == QueryRunKind.SqlError && calls < maxCalls)
{
    // add the draft and PromptBuilder.RepairMessage(run.Error) to the conversation, try again
}
// violations: Refused, codes only, no repair (full flow in the complete project)

The checklist

• [ ] Compile the SQL and the tail; exactly one statement or refuse
• [ ] Don't treat sqlite3_stmt_readonly as the main lock
• [ ] An authorizer allowlist for tables, columns, functions and statement types
• [ ] SQLITE_DENY, never SQLITE_IGNORE
• [ ] Hidden tables denied even for count(*)
• [ ] Unpooled, read-only connections, hooks removed in finally
• [ ] A deadline (progress handler) plus row and cell caps
• [ ] Repair SQL errors, never policy violations, with a hard limit

The project has 134 offline tests (121 unit, 13 integration with WebApplicationFactory) and a fake model that misbehaves on purpose: it writes DELETEs, stacks statements, goes after hidden columns and writes a recursive CTE that never ends.

All of this still assumes one shop per database. Put 40 shops in the same tables and a perfectly "allowed" SELECT SUM(total) FROM orders reads everyone's revenue. Where the tenant boundary should live is the hardest part, and I cover it in the project and the PDF.

Get the complete project

The snippets above are the outline. The complete .NET 10 solution is in Tech Skill Builder: the semantic catalog that drives both the prompt and the allowlist, the statement inspector, the authorizer, a per-tenant sandbox, deadlines and caps, the bounded repair loop, a privacy-aware audit trail, an ASP.NET Core minimal API, a 21-scene CLI demo and all 134 tests, plus an in-depth PDF guide. No API key needed to run it.

Tech Skill Builder gives you complete, working, tested .NET projects with full source code and step-by-step PDF guides, with new ones added regularly. Tech Skill Builder is $5/month for a very limited time only (normally $10). Grab it now before it goes back to $10. Join Tech Skill Builder

Which of these would your current text-to-SQL feature fall for?