Text-to-SQL Without DROP TABLE Surprises

Text-to-SQL is seductive. Business users type plain English; the model writes SQL; everyone gets charts without waiting on the data team. Until someone asks an innocent-sounding question that compiles to DELETE FROM orders WHERE 1=1, or an attacker embeds instructions in a column name the model reads as context.

The industry knows LLM-generated SQL is risky. The gap is where teams check it. Parsing SQL in the application after execution is an autopsy. Checking only the user's question misses attacks in retrieved schema metadata. Trusting "read-only roles" without pre-execution validation still allows over-broad SELECT exfiltration and wastes analyst trust when connections are misconfigured.

This guide covers failure modes beyond obvious injection, what teams try and where it stops, and how the Text-to-SQL Safety Gate playbook separates generation from permission. For a production incident walkthrough, write access on a reporting connection, real rows deleted, read safe SQL before it hits production.

The Question That Looked Like Analytics

An illustrative internal tool had run for months: "how many orders shipped late," "which accounts renewed below list price." Ordinary business questions. One afternoon someone asked for "a cleanup query to remove test orders and refresh the report."

The model generated something close to:

DELETE FROM orders WHERE customer_email LIKE '%test%'

The pattern matched internal test accounts. It also matched real customers whose emails contained "test," the consultancy's whole domain, and personal addresses with "attester" in the local part. The reporting connection had write access it did not need. Thousands of rows were gone before anyone noticed the report looked short.

The model did exactly what it was asked in a narrow technical sense. Dangerously broad is not a SQL syntax property. It describes the query's effect on real data. A system needs checks before execution to catch that risk.

Failure Modes Beyond "Don't Run Raw SQL"

Destructive statements dressed as analytics. Models may emit DROP, TRUNCATE, or unscoped DELETE in response to requests to clean up old data or refresh a report.

Injection via natural language. A request to ignore filters and show deleted rows is a social attack on the query generator. Schema metadata and sample cell values can carry instruction-like strings that the model treats as context.

Over-broad SELECT exfiltration. Read-only queries can leak PII through incorrect joins, cross-tenant rows, or unnecessary columns. Syntax validation is a floor, not a ceiling.

Trusting ORM-style "it's just SELECT". Generated SQL is a string. Treat it like any untrusted user input.

Post-execution parsing. Logging slow queries helps capacity planning. It does not undo a DELETE.

Be honest about limits: the table shows what a pre-execution guard can detect once you configure it, not what the starter template does. Heuristic guards complement database permissions; they do not replace least-privilege DB users or semantic ACLs on table names.

What Teams Try (and Where It Stops)

Read-only database roles, essential; not sufficient when misconfiguration grants write access, or when SELECT alone exfiltrates.

Allowlisted tables in prompts, drifts when schemas change; models hallucinate table names outside the list.

Human approval for every query, safe; does not scale to self-serve analytics culture.

SQL parsers and lint rules, the scalable middle layer when applied before execution.

Post-hoc query log review, incident discovery, not prevention.

ValGuard's starter covers a small part of the fourth layer: it runs light syntax and injection-pattern checks, then hands off to your executor only on pass. Stronger statement and predicate rules are yours to add.

The Playbook Structure

The ValGuard Text-to-SQL Safety Gate playbook separates generation from permission.

Stage 1: Generate SQL. Natural language becomes a SQL string, the creative, untrusted step. Context may include schema snippets, sample rows, prior queries. Treat all of it as untrusted input surface. The shipped step reuses the general customer_support_reply rule set, which is not SQL-specific. It blocks prompt-injection patterns, PII-like strings, and two literal phrases, and it only warns on short output or markdown format.

Stage 2: SQL guard. The starter template checks generated SQL with valid_sql_syntax and sql_injection. It also warns on unbalanced code fences.

• valid_sql_syntax is a light check, not a SQL parser. After stripping comments, it requires a recognizable statement keyword (such as SELECT, DELETE, or DROP) and balanced parentheses. It does not check tables, columns, or whether a WHERE clause exists.
• sql_injection matches four fixed patterns: OR followed by a numeric comparison such as OR 1=1, DROP TABLE, UNION SELECT, and a trailing -- comment at the end of the output.

That means the starter does not block a plain DELETE, TRUNCATE, UPDATE, or WHERE 1=1 on its own. The DELETE examples earlier in this guide would pass these two rules. They do not authorize tables, evaluate row scope, estimate query cost, or decide whether a statement is appropriate for a particular user.

Add explicit statement, table, tenant, predicate, and row-limit policies in your executor or custom validators. Do not assume the starter rejects every destructive or over-broad query.

The SQL guard is not a semantic ACL. Keep database permissions least-privileged and enforce row-level access in the database.

Stage 3: Pass or block. A passing query reaches the playbook's approved terminal. A blocked query ends the live run with a blocked status, and the graph names a blocked terminal for it. The graph defines routes only for pass and block. A warning-only result, such as unbalanced code fences, matches neither route and the run ends as failed, so treat anything other than a clean pass as not approved. Your application must apply any additional authorization before sending SQL to a database.

Fork patterns from playbook templates; attach org table allowlists where your governance team requires them.

Layered Defense (Not Either/Or)

Production text-to-SQL stacks combine:

1. Least-privilege DB user, reporting role read-only on approved views.
2. Pre-execution SQL guard, every NL question, not only "sensitive" ones.
3. Row limits and timeouts, executor caps blast radius of wrong SELECT.
4. Human confirm queue, flagged broad predicates, first-time table access, high estimated row counts.

Read-only roles failed in the incident above because configuration drifted. A pre-execution guard adds another control, but it does not replace correct database permissions.

Same principle generalizes to AI-generated artifacts that execute: JSON that passes schema but authorizes wrong actions, shell commands from coding assistants, API calls with valid syntax and wrong scope. Syntax was never where the risk lived.

For retrieval-side leaks, agents pulling fields the requester should not see, see guarded RAG policy Q&A.

Measure the Guard at the Right Boundary

The benchmark snapshot reports a proxy request p50 of 0.57 ms with a mocked upstream. It does not measure SQL validation alone, model inference, or database execution. See the latency methodology.

Measure your model and database latency separately. Keep the validation check before execution, and enforce database permissions independently.

Wiring the Guard in Your Stack

Analyst UI → API → model → guard → executor.

Never expose executor credentials to the model directly. The application holds the connection; the model proposes strings; your code disposes.

LangChain / agent tools. run_sql tool should call guard internally; agent cannot invoke raw executor. Validate tool arguments, SQL string, with same rules if model passes query as param.

ValGuard middleware. Validation step on completion output before your app forwards to JDBC/ODBC driver.

Shadow mode. Log would-block for two weeks; compare to DBA manual review sample; then enforce. Track false positives on legitimate admin queries routed to separate role.

Store { query_id, rules[], pass | block } for compliance audits, who asked, what was generated, what ran.

Anti-Patterns in Text-to-SQL Deployments

"We use read-only, we're fine." Misconfiguration and broad SELECT exfiltration disagree.

Human review only for admins. Self-serve culture means every analyst is admin of their own mistake.

Prompt-only "never DELETE." Instructions drift; validators version with git.

Parsing SQL after execution for "monitoring." Autopsy, not gate.

Semantic trust in model reasoning. "It understood the question" is not authorization.

Skipping guard on cached queries. Cache keys on NL text; regenerated SQL still untrusted if context changed.

Testing SQL Guard Playbooks

• Fixture queries per rule, DROP TABLE, UNION SELECT, OR 1=1, and any destructive-statement rules you add; assert the block and the rule name. A bare DELETE passes the starter rules.
• Golden SELECT paths, valid analytics queries pass; no live warehouse required for unit tests.
• Role matrix, destructive block on reporting connection; allowed on explicit admin path with human confirm.
• Schema injection fixtures, column names resembling instructions; assert guard + logging even when output is SELECT.

Integration tests with recorded cassettes for full NL→SQL→guard path. Chaos: simulate guard failure → assert fail-closed (no execute), not fail-open.

Observability Data Teams Use

• Block rate by rule, a spike in your custom broad-predicate rule may mean analysts asking dangerous cleanup questions; train + add views instead of disabling guard.
• Pass-then-incident, row count or runtime anomaly after pass means heuristic gap; add rule, not "turn off SQL AI."
• Question text retention, balance audit need with PII in logged NL questions; hash or truncate per policy.

Pair with agent observability when text-to-SQL is one tool in a multi-step analyst agent.

Minimum Production Text-to-SQL Checklist

1. Least-privilege executor credentials, reporting views, not base tables where possible.
2. SQL guard on every generated string, before JDBC/ODBC/API call.
3. Destructive statement block, default deny on reporting paths. Add this rule; the starter does not include it.
4. Broad predicate flag, human confirm queue, not auto-run. This is also a rule you add.
5. Fail closed on guard error, unreachable validator blocks execute.
6. Audit log, NL question hash, generated SQL, rule results, execution ID.
7. Shadow → enforce, tune with DBAs before blocking analysts.

ValGuard sits between your analyst UI and your warehouse: the model proposes, the rules dispose. Your data team keeps self-serve speed; your security team keeps a veto that does not require reading every SELECT at 11 p.m.

Explore the Text-to-SQL Safety Gate playbook →

Text-to-SQL systems judged on correct answers must also be judged on safe answers. A valid query that destroys or exfiltrates data failed, regardless of syntax.

Book a technical walkthrough with our team → Start free in shadow mode →