Secure NL2SQL against LLM SQL injection

A system prompt that says "only generates SELECT queries" is a preference, not a barrier. The model respects it most of the time because it has been trained to follow the instructions, not because a technical constraint physically prevents it from writing anything else. Two classes of failure make this confidence insufficient in production.

The first comes from the question itself. A user, ill-intentioned or simply creative in their formulation, can direct the question in such a way as to push the model towards SQL that they should not have written: a join to a sensitive table, a filter which circumvents the expected logic, a system function call. The model does not distinguish between a legitimate question and one designed to manipulate it.

The second requires no malice. A model can hallucinate a table name, forget the LIMIT that the prompt asked for, or generate an SELECT * without any restrictions on a large table. The result is the same in both cases: potentially expensive or intrusive SQL, which has passed the prompt filter and is about to be executed against a real database.

The first line of defense is to parse the SQL produced by the model with a real parser for the target dialect, then validate the resulting structure, not the raw text. A search for prohibited words in the character string (“DROP”, “DELETE”, “;”) is trivially bypassed: different case, comment inserted in the middle of a keyword, typed quotation marks. A syntax tree unambiguously describes what the query actually does.

Aurabase implements this step with the Rust crate sqlparser and its dialect PostgreSqlDialect. Even before parsing, a first lexical filter rejects two constructions that are difficult to reason correctly once in the tree: dollar-quoting ($$...$$), which can hide arbitrary content in a string, and multi-line comments (/* */), which can hide the real end of an instruction.

let dialect = PostgreSqlDialect {};
let mut statements = Parser::parse_sql(&dialect, sql)
    .map_err(|e| AuraError::BadRequest(format!("Erreur de parsing SQL: {}", e)))?;

if statements.len() > 1 {
    return Err(AuraError::BadRequest("Multi-statements non autorisés".to_string()));
}

This rejection of multi-statement alone blocks the most well-known form of SQL injection by stacking: SELECT * FROM users; DROP TABLE users;--. The parser only returns one actionable instruction, the second is simply never reached, no matter how it is worded in the original question.

Once the tree is obtained, the broadest validation is to accept only one type of root node, a query (Statement::Query), and reject everything else: INSERT, UPDATE, DELETE, DROP, CREATE, ALTER. It is no longer a prompt instruction, it is a condition on the type of the parsed object, which no skillful formulation of the question can circumvent.

Even within a SELECT, several constructs remain dangerous and deserve their own explicit rejection:

A test case taken from the repository concretely illustrates the last point: SELECT * INTO backup FROM users is rejected, even though it contains neither visible writing keyword nor suspicious function. The form of the request is enough to disqualify it.

A blacklist of prohibited functions (pg_sleep, pg_read_file, dblink...) requires anticipating each dangerous function one by one, while Postgres exposes several hundred of them. A whitelist reverses the burden of proof: only ten functions are authorized, count, sum, avg, min, max, lower, upper, coalesce, date_trunc, now. Everything else is disallowed by default, including one legitimate feature that no one has thought to add yet.

A single validation pass is not always enough. A structural traversal of the tree lists its entry points one by one (projection, WHERE, JOIN, GROUP BY...), and it is easy to forget one: a forbidden function can be hidden in an FILTER (WHERE pg_sleep(10) IS NOT NULL)clause, in an intra-aggregate ORDER BY (sum(id ORDER BY pg_sleep(10))), in WITHIN GROUP, DISTINCT ON, or OFFSET.

#[test]
fn test_rejette_fonction_interdite_dans_filter() {
    assert!(validate(
        "SELECT count(*) FILTER (WHERE pg_sleep(10) IS NOT NULL) FROM users",
        None, None
    ).is_err());
}

The Aurabase validator therefore adds a second exhaustive pass, which goes through all the expressions in the tree wherever they are, independently of the structural path. It’s an assumed defense in depth: if the first pass misses a case, the second catches up.

SELECT * remains authorized, it is useful for data mining. The risk is not the star, it is the absence of a ceiling on a query written by a model: a poorly formulated question can bring back an entire table, with the memory cost and response time that that implies.

Aurabase applies a simple and transparent rule. If the generated SQL does not have a LIMIT, the server adds one (100 lines by default, value announced to the model in the system prompt). If the SQL requests an LIMIT beyond a hard cap (1000 rows by default), the query is explicitly refused rather than silently reduced. Both values ​​are configurable on the server side (AI_NL2SQL_DEFAULT_LIMIT, AI_NL2SQL_MAX_LIMIT), and the server even refuses to start if the fault exceeds the ceiling.

{
  "sql": "SELECT count(*) FROM orders WHERE status = 'paid' LIMIT 100",
  "limit": 100,
  "limit_injected": true
}

Two distinct leaks threaten an NL2SQL engine connected to a real database: access to the Postgres system catalog, and access to a schema that does not belong to the caller. Both block upon validation, regardless of any RLS policy placed downstream.

pg_catalog is always part of search_path, which means that an unqualified name like pg_authid or pg_stat_activity accesses it directly, without a prefix. The Aurabase validator blocks any name starting with pg_, as well as information_schema and the internal schema aura_console, whether qualified or not.

On a two-component name (schema.table), only the schema of the calling project is allowed, any other value is rejected. A name with three or more components is automatically refused. This boundary at the generated query level is in addition to the database level isolation detailed in our article onmulti-tenant isolation: one prevents the generated SQL from targeting another schema, the other prevents the connection itself from reaching another database. Neither replaces the other.

A discrete trap lies in wait for any NL2SQL API that accepts a parameter describing the schema or tables allowed in the client's query. If this same parameter is used to construct the prompt and validate the output SQL, a caller can lie about what is allowed, and the validation then validates against this lie rather than against the reality of the database.

Aurabase introspects the actual project base schema on each call, with a short thirty-second cache for performance, and explicitly rejects (400 error) any schema, allowed_schema, or schema_context fields sent in the request body, rather than accepting and then silently overwriting it. The difference matters: a field accepted then ignored gives the illusion of control that does not exist; a refused field says it right away.

Whether you use Aurabase or build your own pipeline on top of a generic LLM, the following points cover what is most often missed.

If you write the validator yourself

• Parse SQL with a real parser for your exact dialect, never with pattern matching in a string.
• Adopt a default rejection: any type of node, any function not explicitly authorized must be refused, not just the dangerous cases already identified.
• Accept only one statement per query, this is the simplest rejection against stacking queries.
• Test the validator with real adversarial cases (function prohibited in FILTER, in an intra-aggregate ORDER BY, in OFFSET), not only with obvious cases.
• Despite everything, execute the validated SQL with a Postgres role with reduced privileges on the expected schema: the validator limits the form of the query, the role limits what it can physically achieve if a case has escaped you.

If you are evaluating a third-party NL2SQL framework

• Ask explicitly if the validation is structural (AST) or only a prompt instruction: the answer changes everything.
• Check that a line cap is applied by default, not just documented as a best practice at your expense.
• Check if the schema used for validation can be provided by the API client, which would reopen exactly the flaw described above.
• Compare several tools on this specific criterion before choosing: our comparison of NL2SQL tools details what distinguishes the approaches available in 2026.

A solid AST validator reduces risk at the source: the SQL that reaches your database already has a known and bounded form. However, it does not replace RLS policies on your sensitive tables, which decide which rows a given user has the right to see. The two layers answer different questions: the validator limits the form of the generated query, the RLS limits the data that it can return for a specific user. Keep both active, even if one seems redundant with the other.

NL2SQL covers structured questions about your tables. For questions about unstructured content, documents, notes, tickets, Aurabase's native RAG follows a comparable security logic, detailed in our RAG pipeline tutorial on pgvector.