Production-Ready Text-to-SQL: Secure Governance from Restricted Semantics to Revocable Execution

1. Correct the Goal: Not "Let the Model Write SQL" but "Controlled Answers to Data Questions"

An operations user asks: "Summarize last month's successful refund amounts by region." Even if the model generates syntactically correct SELECT, the system may still: miss tenant conditions, read phone numbers, mix currencies, scan all historical partitions, or continue occupying the database after client timeout.

Therefore, trustworthy Text-to-SQL must simultaneously satisfy four goals:

Operational Safety : Can it modify data, lock data, or invoke external capabilities? Primary control points: SQL subset, function/attribute allowlist, read-only account.

Data Authorization : Which rows and columns can the current principal use for what purpose? Primary control points: Server-side identity, semantic catalog, row/column permissions, result permissions.

Resource Safety : Will it exhaust CPU, connections, memory, or network? Primary control points: Shape restrictions, plan signals, concurrency and total deadline.

Analytical Correctness : Do returned values match the metric, time, currency, and granularity? Primary control points: Metric definitions, time interpretation, result metadata.

Model output is a candidate program; parsing success does not equal approval, execution success does not equal correct answer.

2. Threat Model: Attack Surface Not Only from User Input

Direct prompt injection asks to "ignore rules, drop tables." Indirect injection hides in uploaded documents, schema comments, retrieval examples, ticket notes, and historical query results. All such content must be treated as data, not instructions to the executor.

Prompts can improve generation quality but do not carry authorization. Especially schema retrieval: similarity only indicates relevance, not that the current user has the right to know the existence of tables, columns, or metrics. PreparedStatement remains necessary but only protects "values" from rewriting the fixed structure. The model's choice of tables, columns, sort directions, and functions belongs to structure and cannot be remedied by parameterization; they must be produced by a controlled compiler or authorized AST. Values like time, region, status must be type-bound.

Even if the top-level is SELECT, reject multi-statements, locking reads, file exports, user variable assignments, executable comments, model-provided hints, arbitrary stored functions, and expressions not belonging to analytical capabilities such as SLEEP, BENCHMARK, LOAD_FILE. Unknown functions default to deny; do not maintain an eternally incomplete dangerous-function blacklist.

3. Choose Capability Boundary First, Then Technical Implementation

"Free SQL" is not a sign of maturity. Most business analytics are better served by letting the model choose business semantics while the server generates SQL.

Fixed metrics, dimensions, filters, and sorting → Recommended: Restricted Query Spec (DSL) + Trusted Compiler. Avoid: Pass model raw SQL directly to JDBC.

Few vetted analysts need flexible expression → Recommended: Explicit SQL subset + AST binding, authorization, budget. Avoid: Keyword blacklist or regex replacement.

High sensitivity, multi-tenant, strong isolation → Recommended: DSL + dedicated views/physical isolation; native row policies if needed. Avoid: Shared account with "convention tenant_id".

Window functions, complex joins, long reports → Recommended: Analytical engine or controlled async worker, incrementally extend syntax. Avoid: Only increase online timeouts and connection pools.

3.1 Default Approach: Restricted Semantic Spec

The model only fills business intent, not tenant, raw table names, or expression concatenation:

{
  "dataset": "refunds",
  "dimensions": ["region", "currency"],
  "metrics": ["refund_amount_sum"],
  "timeRange": {
    "start": "2026-09-01T00:00:00+08:00",
    "endExclusive": "2026-10-01T00:00:00+08:00"
  },
  "filters": [
    {"field": "status", "op": "eq", "value": "SUCCESS"}
  ],
  "orderBy": [
    {"metric": "refund_amount_sum", "direction": "DESC"}
  ],
  "limit": 100
}

The compiler maps dataset to vetted views, maps metrics to fixed expressions, turns values into typed parameters, and forcibly injects tenant scope from server-side identity. Still must validate field enums, combinations, time spans, data types, permissions, and limit; but the problem shrinks from "review arbitrary program" to "compile restricted query."

3.2 When to Allow Restricted Free SQL

Only when DSL cannot cover real needs and the team can maintain parser versions, dialect difference tests, authorization binding, and regression suites, should an explicit SQL subset be released. First version may support only single query block, explicit columns, vetted INNER JOIN, limited aggregation, and fixed pagination; CTE, UNION, window functions, and correlated subqueries are all rejected. They are not inherently dangerous, but current policy cannot prove their authorization and cost semantics.

4. Trust Boundaries: Generation, Approval, and Execution Must Be Unbypassable

Authenticated Identity & Request
Filtered Semantic Catalog by Permissions
Model Candidate Spec/SQL
Parse, Bind, Authorize & Semantic Validation
Trusted Compile / Relational Isolation Rewrite
Final Statement Re-validation & Plan Admission
Issue Short-lived ApprovedQuery
Dedicated Read-only Executor
Bounded Results & Business Interpretation
Audit, Cost Feedback, Cancellation Status

Key invariants:

User, tenant, role, data source, and permissions come from auth/authz service; model must not declare or override.

Rewritten final SQL must be re-parsed, bound, and authorized; must not validate string A but execute string B. EXPLAIN and actual execution use same SQL, parameters, session settings, and remaining deadline.

Executor only receives approved object, not arbitrary string SQL.

Auth service, plan parser, or policy version anomalies default to deny; no fallback to write DB, default tenant, or unlimited budget.

An implementable approval object:

record ApprovedQuery(
  String finalSql, List<TypedParameter> parameters,
  PrincipalSnapshot principal, AuthorizationScope scope,
  ExecutionBudget budget, String policyVersion,
  String catalogVersion, Instant expiresAt, String fingerprint,
  byte[] integrityTag){}

integrityTag

is signed or MAC'd by approval side over normalized fields; execution side verifies integrity, expiry, policy/catalog versions, and current permissions. It is not a "security token replacing DB permissions" but prevents inter-service passing just a true that gets replaced, expires, or drifts from approval context.

5. AST Validation: Parser Is Not a Security Sandbox

Pin JSqlParser and DB version, compile in-project first, then run dialect regression tests. Parser may accept syntax the DB rejects, or incompletely express DB dialect attributes; therefore must do "app-allowed statements interpreted as expected on target DB" differential testing.

Validation flow:

enforce byte limit, parse deadline, AST depth/node limits
parse entire input with fixed dialect; require exactly one allowed SELECT form
reject comments, hints, unconsumed input and unsupported attributes
bind every relation, alias and column against authorized catalog
authorize every reference and expression location
validate functions, types, join graph and query shape
compile/rewrite isolation and budget; reparse and repeat final validation

Do not judge safety by semicolon count, string replacement, or regex comment stripping. Executable comments, quotes, sql_mode, default schema, identifier case, and escaping rules all make "looks filtered" differ from DB's actual interpretation.

Must traverse SELECT, FROM/JOIN, WHERE, ON, HAVING, GROUP BY, ORDER BY, CASE, subqueries, CTE, set operations, window definitions, LIMIT/OFFSET, and all dialect attributes. Ununderstood nodes always reject. E.g., ON SLEEP(5)=0, sorting by sensitive column, or accessing unauthorized table in subquery cannot pass because of top-level SELECT and table allowlist.

Column permissions at least split: display, filter, group, sort, aggregate; SELECT * default deny, if supported must expand per current permissions. Unqualified columns must bind to unique relation else reject. COUNT(*) is independent aggregation semantics, not "authorized to read all columns." JOINs only on catalog-approved relationship edges with recorded cardinality expectations; wrong one-to-many joins both inflate cost and double-count.

6. Multi-tenancy: Protect Every Base Table Read, Not Just Append a WHERE

Tenant ID comes from server. The following concatenation leaks other tenants:

WHERE status = 'SUCCESS' OR region = 'CN' AND tenant_id = ?

Even rewritten as (original condition) AND tenant_id=? only covers current query block. Subqueries, CTE, UNION branches, and every base table read must be included in isolation. For LEFT JOIN, putting right-table tenant condition in top-level WHERE turns outer join into inner join; filtering must be injected into correct input relation or ON condition. If business key is unique only within tenant, join condition must also include tenant boundary.

First version prefers "restricted compiler + dedicated analytical views." MySQL 8.0 lacks PostgreSQL native RLS equivalent; views on shared account don't automatically know app user. Session variable context simulation involves mutability, connection reuse, and cleanup — must be tested. High-sensitivity scenarios should prefer physical isolation, per-tenant routing, or native DB policies, not bet correctness entirely on AST rewrite.

7. Performance Admission: EXPLAIN Is Risk Signal, Not Execution Guarantee

Only run EXPLAIN FORMAT=JSON on final approved statement with same parameters as real execution. Don't use unauthorized raw SQL to "peek cost"; online admission must not switch to EXPLAIN ANALYZE which actually runs the statement.

Plan JSON traversal must cover objects and arrays, recording all table accesses, join outputs, access types, indexes, temporary tables, sorts, and materialized relations. Cannot stop at first rows field; single-table scan estimate, join output estimate, and total work are different quantities; simple sum, max, or multiply are unreliable.

Large table ALL / index access : Judge with filter range, indexes, historical samples, and data source thresholds.

Unapproved join edges or too many tables : Direct reject.

Large join output, temporary tables, sorts : Tighten input ranges or convert to fixed metrics/async jobs.

Deep OFFSET : Switch to cursor pagination or set max offset.

Key estimates missing, anomalous, or plan exceeds limits : Mark UNKNOWN, default deny. LIMIT 10 only limits output; COUNT(*), large aggregations, and GROUP BY ... ORDER BY ... LIMIT 10 may still scan or sort massive data. Cost governance must simultaneously limit time range, query shape, concurrency, runtime, and result bytes. Optimizer cost is relative, not milliseconds; thresholds must be calibrated per data source, data scale, query category, and version.

8. Database Backstop, Budget, and Cancellation Loop

Executor holds only dedicated data source ai_reader; DB grants only SELECT on vetted views or columns, no write, FILE, PROCESS, admin, or arbitrary stored procedure EXECUTE. Spring read-only transaction, Connection.setReadOnly(true), routing data source, and YAML names are additional constraints, not substitutes for separate credentials and DB permissions. SQL SECURITY DEFINER views can reduce column exposure but definer rights, dependencies, and functions must be reviewed; they cannot dynamically implement app tenant isolation. information_schema, mysql, performance_schema, sys must not enter model catalog; metadata visibility tested with actual account.

MySQL's max_execution_time is session/global variable, example: SET SESSION max_execution_time = 3000. Model hints must be rejected to avoid overriding budget. Don't assume connection pool restores arbitrary session variables: test with current driver and pool version, restore baseline before return or evict contaminated connections.

Budget must be layered and share total deadline: queue wait, connection acquisition, EXPLAIN, DB, JDBC, socket, read, and serialization all check remaining time. Save executing Statement; on timeout, client disconnect, or revocation call Statement.cancel(); failed or uncertain-state connections evicted from dedicated pool. HTTP timeout, Future cancelled, or thread interrupted do not prove DB statement stopped. Need KILL QUERY by independent control component verifying connection ownership and lifecycle to prevent mis-kill after reuse.

8.1 Runtime Semantics of Permission Revocation

Must define explicit policy, not default ignore:

Queued tasks not started: cancel after revocation.

Cache hits and async result reads: re-check current permissions each time.

Running short interactive queries: at least block result delivery; high-sensitivity data should actively cancel.

Delivered results: handle per data classification, session, and export policy; cannot promise "recall already downloaded content after revocation."

Write revocation events to audit linked with fingerprint, task ID, and execution connection to verify this promise.

9. Results, Caching, and Async: Last Mile Is Still Security Boundary

SQL LIMIT, JDBC maxRows, application read loop row/value caps, and serialization total byte cap must all exist. Trusted compiler can generate LIMIT 101 to express "display 100 rows and accurately mark truncation"; must not arbitrarily add LIMIT to subqueries as it changes aggregation semantics. setFetchSize does not automatically guarantee MySQL streaming read; must verify with Connector/J config and driver behavior.

Masking cannot replace read authorization: once phone number enters JDBC, heap, or logs, risk has occurred. Aggregation also leaks info; sensitive scenarios need minimum group size, dimension constraints, frequency control, and differential query audit.

Result cache key at minimum includes:

dataSource + finalQueryFingerprint + canonicalTypedParameters
+ tenantScope + authorizationScope + policyVersion
+ semanticCatalogVersion + freshnessWindow

On hit, still re-verify current permissions; permission changes, catalog/definition upgrades, and freshness expiry must invalidate result. Async worker does not add DB capacity: still needs cost, concurrency, and deadline, and maintains QUEUED/RUNNING/SUCCEEDED/FAILED/CANCELLED/EXPIRED states, idempotent deduplication, result retention, and dual permission checks.

Returned values should carry metric definition version, time range and timezone, data freshness, currency/unit, truncation status, and conclusion boundaries. Result text passed to summarization model is still data, not tool instruction.

10. Observability and Acceptance: Turn "Looks Safe" into Verifiable Evidence

Audit logs trace ID, principal/tenant reference, query fingerprint, data source, policy and catalog versions, approval conclusion, rejection reason, plan summary, phase timings, row/byte counts, cancellation status, and data freshness. Regular logs do not record full question, parameters, or results; for debugging use desensitized/encrypted storage, separate access rights, and retention.

Pre-launch must verify on isolated data source:

Syntax Escape : Multi-statement, comments, hints, file export, variables, locks, dangerous functions all rejected at admission.

Authorization & Scope : Unauthorized columns rejected even if only used for sorting/filtering; aliases, same-name columns, backticks correctly bound.

Tenant Isolation : OR, subqueries, UNION, LEFT JOIN, same-tenant key conflicts never cross tenants.

Plan & Capacity : Array-nested plans miss no nodes; UNKNOWN not passed as zero cost; deep pagination and small LIMIT large aggregation controlled.

Cancellation & Recovery : DB timeout, JDBC cancel, disconnect, shutdown, revocation cause active statements to disappear or have explicit failure evidence.

Results & Lifecycle : Oversized values/results limited; cache no cross-use; revoked, retried, expired tasks cannot continue reading or run indefinitely.

After upgrading parser, MySQL, Connector/J, changing sql_mode, catalog, permissions, or pool config, re-run key regression suites. For allowed statements do property/mutation tests: move same dangerous function or unauthorized column to every expression position to discover missed traversals.

11. Engineering Blueprint: Make Control Points Unbypassable in Code Structure

Scattering safety rules across Controller, Prompt, and DAO guarantees a bypass path. Suggest splitting modules by responsibility with unidirectional dependency flow:

IdentityContext : Input: Authenticated request. Output: Principal, tenant, role, purpose. Forbidden: Accept model-provided tenant or role.

SemanticCatalog : Input: Identity, version. Output: Filtered dataset/metric/field catalog. Forbidden: Expose full DB schema directly to model.

IntentParser : Input: User question, catalog. Output: Restricted QuerySpec. Forbidden: Access DB or decide permissions.

PolicyCompiler : Input: QuerySpec, identity, catalog. Output: Final SQL, typed parameters, budget. Forbidden: Construct identifiers by string concatenation.

SqlValidator : Input: Final SQL, catalog, policy. Output: Binding result, rejection reasons. Forbidden: Replace AST traversal with keyword scan.

PlanAdmission : Input: Approved SQL, parameters, budget. Output: ALLOW/REJECT/UNKNOWN. Forbidden: EXPLAIN on candidate raw text.

ReadonlyExecutor : Input: ApprovedQuery. Output: Bounded ResultSet / cancellation status. Forbidden: Execute arbitrary SQL or hold write credentials.

ResultGovernor : Input: Row stream, policy. Output: Result, truncation, semantic metadata. Forbidden: Buffer entire result in memory first.

One dependency rule is critical: ReadonlyExecutor public methods only accept ApprovedQuery; its constructor does not inject business writer; raw SQL type is invisible in that module. Thus even if business team adds new interfaces, they cannot easily bypass the approver.

11.1 Semantic Catalog Must Be a Versioned Authorization Product

Catalog is not just "table and field list." Each item must contain: stable ID, physical relation/expression mapping, display name, data type, allowed roles, allowed actions, sensitivity level, metric definition version, allowed join edges, time field, default and max time range, and freshness SLA. Model receives a projection of this catalog, not raw information_schema dump.

Example: refund_amount_sum definition must specify: success status set, amount unit, negative meaning, refund timestamp field, whether includes fees, whether currency must be grouped, and whether can join on order dimension then aggregate. Missing these, safe SQL still produces wrong business conclusions.

11.2 Key Rules for Restricted Compiler

Compiler must have explicit "input normalization → authorization → generation → re-validation" order:

Normalize time, enums, numbers, list lengths, timezones; reject ambiguous dates and implicit type conversions.

Filter datasets, fields, metrics, and each usage action by current principal; do not generate then delete unauthorized columns.

Generate trusted identifiers from catalog mapping, all values as placeholder parameters; model cannot submit SQL fragments.

Inject tenant, purpose, and mandatory status conditions from server context; use AST to compose boolean expressions, not string append.

Generate stable ordering and controlled LIMIT; final SQL passes AST, parameter types, and plan admission again.

Example final statement. Tenant and status are not model's free filters but policy-generated; amount grouped by currency to avoid erroneous summation:

SELECT region, currency, SUM(refund_amount) AS refund_amount_sum
FROM analytics.ai_refunds
WHERE tenant_id = ?
  AND status = ?
  AND refunded_at >= ?
  AND refunded_at < ?
GROUP BY region, currency
ORDER BY refund_amount_sum DESC, region ASC, currency ASC
LIMIT 101;

11.3 When Input Is Indeed SQL: Minimal AST Policy

Free SQL entry also needs to separate "syntax allowed" from "semantics approved." After parsing, establish scope: each table alias points to unique real relation; each column reference points to unique catalog field; output aliases, CTE names, and DB objects separately named. Then check allowedness per node.

validateExpression(expr, scope):
  require expr node type in allowedNodes
  for each column reference: bind uniquely; authorize action at this location
  for each function: require exact name, arity, argument types and location
  for each literal/parameter: enforce type, size and source policy
  recurse into every child expression

This is not complete code but clarifies failure modes: nodes that cannot bind, understand, authorize, or estimate are rejected, not "try to execute anyway." For MySQL executable comments, lock syntax, INTO OUTFILE, variable assignment, and dialect extensions, handle explicitly in policy; never hope top-level node type masks them.

12. Budget Model: Translate Capacity Targets into Per-Request Executable Limits

Budget is not a single timeout=5s config but a set of interrelated caps. Can be fields of ExecutionBudget rather than scattered constants:

Queue wait : 500 ms — Prevent queuing from consuming interactive deadline.

Connection acquisition : 1 s — Fast fail when pool exhausted, avoid thread pileup.

Planning phase : 1 s — EXPLAIN, JSON read, and parsing also consume resources.

DB execution : 3 s — Constrain main compute, must be below caller deadline.

JDBC wait : 4 s — Reserve window for DB cancel and error return.

Single value/row count/total bytes : 64 KiB / 101 / 1 MiB — Prevent result phase from blowing memory and network.

End-to-end : 8 s — Covers queuing, execution, read, and serialization.

These numbers are not universal recommendations; they must be calibrated from target data source's historical latencies, P95/P99, peak concurrency, and error budget. Each step uses remainingDeadline() taking minimum, so 1+3+4+8 seconds are not incorrectly serialized into acceptable duration.

Concurrency governance also layered: per-user rate limit, per-tenant concurrency, global run slots, per-data-source connections, and bounded queue. Four app replicas each allowing five queries means DB may face twenty concurrent; EXPLAIN should also count in cost. Little's Law helps estimate average in-flight, but tail latency, burst traffic, and high-cost templates still need load testing.

13. Cancellation, Revocation, and Failure Recovery State Machine

Query lifecycle should explicitly record state, not rely only on HTTP request:

QUEUED → REJECTED (policy/plan/capacity denied)
QUEUED → RUNNING (acquired approved execution slot)
RUNNING → SUCCEEDED (bounded result complete)
RUNNING → CANCELLING (timeout/client disconnect/revocation)
CANCELLING → CANCELLED (database confirms stop)
CANCELLING → FAILED_UNCERTAIN (cancellation cannot be proven)
RUNNING → FAILED (database/driver error)
SUCCEEDED → EXPIRED (result retention elapsed)

FAILED_UNCERTAIN

must not pretend successful cancel: log connection ID, attempts, observation time, evict connection, alert, and query DB for active statements. High-sensitivity data: revocation triggers CANCELLING; general data: at least re-verify permissions before result delivery and refuse delivery. Client disconnect is only cancellation trigger, not proof of completion.

If using admin connection for KILL QUERY, control component must bind "approval ID — execution connection ID — query start epoch." Connection pool return may reuse same physical connection for another statement; without epoch or lifecycle verification causes mis-kill. AI read account should not be granted broad admin rights for this.

14. Phased Rollout: Expand Freedom by Evidence, Not Feature List

Phase 1: Fixed Analysis Scenarios. Publish only common metrics from semantic catalog, using dedicated views, read-only account, query spec, row/byte budgets, and audit. Acceptance focuses on definitions, column authorization, and tenant isolation.

Phase 2: Restricted Free SQL. Pin parser and dialect, publish enumerable AST subset, complete scope binding, expression authorization, plan adaptation, and property tests. Validate in isolated analytical environment with small traffic; shadow evaluation itself consumes DB, should not run production candidate statements indiscriminately.

Phase 3: Complex Analysis and Async Reports. Add CTE, window, or complex joins on demand, each syntax with corresponding authorization rules, cost model, and regression cases. High-cost tasks move to dedicated worker or analytical engine, verifying task cancellation, revocation, result retention, and expiry cleanup.

Every expansion must produce evidence package: supported syntax list, policy diffs, compiler and dialect test results, permission bypass cases, cancellation experiments, capacity load tests, cache isolation verification, and business definition sign-off. Without these, do not open capability just because "model can already generate."

15. One Request's Trusted Closed Loop

User requests: "Summarize last month's successful refund amounts by region, descending by amount." Auth provides tenant T; semantic catalog offers only allowed refund metrics and dimensions; time interpreter gets half-open interval per business timezone; model returns query spec; compiler injects tenant, success status, stable ordering, and result cap; final SQL re-validated and planned with same parameters; dedicated read-only executor reads within remaining budget; result carries currency, definition, freshness, and truncation status.

Cross-currency amounts cannot be directly summed into one ranking total; if unified currency needed, must define metric via vetted exchange rate source, conversion date, and rounding rules. This illustrates: safety guardrails must not only block DROP TABLE but also block a "safe but wrong" SELECT.

Conclusion

The mature form of Text-to-SQL is not a chat window that freely assembles SQL, but an explainable, authorizable, budgetable, cancellable, auditable data access pipeline. Start with restricted semantics to deliver value, then expand freedom with test evidence; let DB permissions be the final backstop; and fold revocation, caching, async, and result interpretation into the same governance. Only then can the model become a controlled entry point for data analysis, not a new attack surface for production databases.

References

MySQL 8.0 EXPLAIN

MySQL 8.0 EXPLAIN Output

MySQL 8.0 CREATE VIEW

MySQL 8.0 Server System Variables: max_execution_time

Connector/J Implementation Notes

JSqlParser migration 4.7 and 5.0