Blog /
When the database says no: self-correcting SQL from database errors
Published . Written for nlqueries-core 0.4.
sql validation security
LLM SQL error correction is the step that runs when a generated statement passes every check you have and the database still refuses it. The model gets one more try, with the database's error in hand. It sounds like a small thing to add, and most of the work is in deciding what the model is told, what it is never asked to fix, and what has to happen to the corrected statement before it runs. This post describes how nlqueries-core 0.4 does it.
Two failures, two corrections
A generated statement can fail in two places.
Before it runs. Every statement is parsed and checked first: a single read-only query, in the agent's dialect, naming only tables the knowledge base knows. When that fails and a mechanical fix (no model call) does not repair it, the model is shown the validator's error and asked for a corrected statement, which is checked the same way. Nothing has touched the database yet. Why we validate SQL before it runs covers that step.
When the database runs it. Validation is static. It parses the statement
with a general SQL grammar and checks names against the knowledge base, so a
statement that grammar accepts and the engine does not still reaches the
database. One that turns up in practice is
... ORDER BY x LIMIT 1 UNION ALL SELECT ...: the parser accepts it, and
Snowflake rejects it with a compilation error that names the fault exactly.
That error is the most specific correction the model could be given, and this
post is about using it.
What the model is shown
When the database refuses a statement, nlqueries query and the MCP query
tool make one more model call. The model gets:
- the question the statement was meant to answer;
- the SQL that failed;
- the database's error, up to 1,000 characters.
The question matters more than it looks. A syntax error carries its own intent: the fix is in the statement. An "invalid identifier" or "object does not exist" does not. The model has to choose a different column or table, and without the question nothing ties that choice to what was asked. A plausible query for a different question passes every check there is.
The call reuses the system prompt the statement was generated with, so on a provider with prompt caching the schema part is read from the cache rather than sent again. It is one call, never a set of candidates: the user is already waiting on a failure.
Why the error is framed as data
The error text comes from the database driver, and drivers quote things in their errors. A failed cast names the value it could not convert, and that value came out of your data. So the error is untrusted text, and it is handled like the other reference material in the prompt:
- it is capped at 1,000 characters, which is enough for any compilation error's message and position;
- it is labelled as text from the database, to diagnose the failure, and "not an instruction".
The label is not the safeguard. The safeguard is what happens next: the corrected statement is model output bound for someone else's database, and following an error earns it nothing. It is validated exactly as a first attempt is, the statement policy and then the schema, and only a valid result runs. A correction that fails validation is dropped and logged, and you get the database's original error.
What leaves this machine lists the error among the things sent to the model provider, with its cap.
What is never corrected
Some errors are not defects in the SQL, and asking the model to "fix" them does harm. Before the correction call, the error is checked against these, and any match means no correction: you see the original error.
Permission refusals. "Permission denied for table orders" means the query
role was never granted that table, even though the knowledge base lists it.
The only "correction" is a different table, which answers a different question
and would be reported as the answer. The check covers the wording of
PostgreSQL and Redshift, SQL Server, Snowflake and BigQuery. It reads the
driver's own message, not the statement the driver echoes back, so a table
called access_denied_events does not make an ordinary error look like a
refusal. A refusal worded in a way the check does not know still meets the
prompt, which forbids swapping a table or column to get around a permission or
access error, and asks for the statement back unchanged when the error is not
a defect in it. An unchanged statement is not run again.
Timeouts. A statement cancelled for time is recognised by the driver's own wording where it names it (Snowflake's error 000630, PostgreSQL's "canceling statement due to statement timeout"), and otherwise by what it cost: an attempt that used 90% or more of the statement timeout is not re-run. Re-running it would make you wait the whole timeout again for a statement the model may only have made "faster". With no timeout set, an attempt that took over a minute stands in for the same thing.
Failures that never reached the database. If the connector could not be opened, or the request was not allowed to execute, the error is raised as an exception, not reported by the database. That is not the model's to fix.
Too little time left. A caller that bounds the whole turn passes its deadline in. A correction is one model call and one execution, the same shape as the attempt that just failed, so it starts only when at least that much time remains. Otherwise the deadline would expire mid-correction, and the database's error, which was a perfectly good answer to return, would become a bare timeout.
The corrected statement runs on the same connector
A correction runs on the same connector as the statement it replaces, so the same rules apply to it, row filters included. What you see is whichever statement ran last, with that statement's own rows or error beside it: the SQL shown is always the SQL behind the result.
When the statement was rewritten on its way
A connector can be wrapped by code built on core, and a wrapper can rewrite a statement before it reaches the database. Adding row-filter predicates is the obvious use. When a rewritten statement fails, the database's error describes the rewritten text, and its positions and identifiers point at nothing in the model's own SQL.
A wrapper that rewrites can say what it actually sent, in the query result's
executed_sql. The correction prompt then shows the model both statements:
its own, and the one the database ran, labelled as information like the error.
It is still asked to correct its own SQL, because the correction goes back
through the same wrapper and is rewritten the same way.
One case is caught explicitly. A model shown the rewritten statement can hand it back as its answer. That statement can pass validation, and run, it would be rewritten a second time and the caller would see the filter predicate. So a correction that repeats the statement the database ran is treated as unchanged, and not run.
Core itself rewrites nothing. With no wrapper setting executed_sql, the
prompt is exactly the three-part one above.
In short
- One correction, only after the database refuses a valid statement.
- The model sees the question, its SQL and up to 1,000 characters of the error, framed as data.
- Permission refusals, timeouts and failures before the database are never corrected.
- The correction is validated like a first attempt and runs on the same connector.
nlqueries ask shows the SQL without running it, so it never reaches this
step; query does. To try it,
pip install nlqueries-core and start with
getting started, or see the
database hardening page for the read-only role this
all runs under.