AI SQL Agent Security: Running Generated Queries Safely

AI SQL agent security fails when a model can turn a natural-language request directly into a production query. A syntactically valid SELECT can still expose another tenant's rows, call an unsafe function, lock a table, scan billions of records, or return fields the caller is not allowed to see. An empty result can also become a confident invented answer. Put a query broker between the model and the database instead. The broker must bind trusted caller identity to database policy, constrain one proposed statement, execute it with limited authority and resources, and return a typed result that the model cannot reinterpret as success.

This guide defines that broker, the order of its checks, and the negative tests needed before an internal database assistant reaches production.

Why prompt rules cannot secure generated SQL

A system prompt can tell a model to issue only read queries. That instruction has no authority at the database boundary. Models can produce malformed SQL, misunderstand a schema, follow hostile text copied from stored records, or generate a query that is read-only in syntax but dangerous in cost or scope.

The current LangChain SQL-agent tutorial states that executing model-generated SQL has inherent risks and recommends scoping connection permissions as narrowly as possible. Its agent checks a query before execution and can add human review. Those controls help, but a checker powered by the same class of model is not a security boundary. Database permissions, parser rules, transaction mode, row policies, and resource limits must reject unsafe work without relying on the model's cooperation.

The common direct path looks like this:

1. The application sends the full schema and user question to a model.
2. The model produces a SQL string.
3. The application runs the string through a shared database account.
4. Rows are pasted into another model prompt for summarization.

That path gives the model more authority at each step. The schema may reveal sensitive tables. The shared account loses the caller's identity. The SQL string controls execution. The result set can exceed the user's permissions or the model's context. The final model may then hide a timeout, mistake an empty set for missing evidence, or invent a value.

A secure design treats proposed SQL as untrusted input and the returned rows as sensitive output.

Define the query contract before choosing an agent

Start with a small contract that describes the only database questions the assistant may answer. Write it in terms of business data, caller roles, maximum freshness, and acceptable latency. Do not begin with every table in the production schema.

For each assistant capability, record:

• approved users or application roles;
• approved views, columns, and aggregate functions;
• tenant and regional boundaries;
• whether individual records may be returned;
• maximum rows, bytes, execution time, and concurrent queries;
• whether the source can be a delayed read replica;
• which denials require human review;
• how empty, partial, stale, and failed results appear to the caller.

Prefer curated views over broad table access. A view can rename cryptic columns, omit personal data, prejoin stable relationships, and expose approved aggregates. It also keeps model-facing schema changes separate from the operational schema.

Use a read replica when the freshness requirement permits it. Read-only mode does not stop a large scan from competing with customer traffic. A replica plus a separate connection pool limits that blast radius. If the assistant needs second-level freshness, keep the same broker and resource controls against the primary rather than granting a direct connection.

Carry trusted caller identity into database policy

The browser, chat message, and generated SQL must not choose the tenant or acting user. Resolve identity from the authenticated application session before calling the model. The broker should receive a trusted context object containing the user, tenant, application role, and request identifier.

Map that context to a dedicated database role or transaction-local policy variables. PostgreSQL row-security policies can restrict which rows a user may read or modify. When row security is enabled and no applicable policy exists, PostgreSQL uses default deny. Table owners, superusers, and roles with BYPASSRLS can bypass those policies, so the agent connection must not use any of them.

Row security should duplicate, not replace, application authorization. The application decides whether the caller may use a sales-reporting capability. The database independently restricts that capability to the caller's tenant and allowed rows. A mistake in either layer does not automatically cross the tenant boundary.

Test the database role directly, outside the agent. Connect as the runtime role, set tenant context through the same code path, and attempt to query another tenant by primary key. The database must return no row or a denial even when the SQL explicitly names that record.

Validate structure, not keywords

Keyword filters such as sql.startswith("SELECT") are easy to bypass and too coarse to be useful. Comments, common table expressions, nested statements, database-specific functions, and alternate encodings make string checks unreliable. Parse the statement with a SQL parser that understands the database dialect.

The validator should require exactly one complete statement and an approved statement class. Walk its abstract syntax tree and reject:

• writes, schema changes, transaction commands, and session changes;
• unapproved schemas, tables, views, columns, and functions;
• file, network, extension, or administrative functions;
• locking clauses and advisory locks;
• unbounded recursive queries;
• system catalogs unless explicitly needed;
• comments or trailing bytes that produce a second interpretation;
• a missing server-enforced row limit for record-returning queries.

A parser allowlist is still not enough. SQL identifiers usually cannot be bound as parameters. Map approved business concepts to known identifiers in application code. The OWASP SQL Injection Prevention Cheat Sheet recommends parameterized queries for values and allowlist validation when dynamic table or column names cannot be bound.

Keep values separate from the generated query plan where possible. For example, let the model select an approved report template and propose typed filter values. The application then builds the final parameterized SQL. Free-form SQL should be reserved for questions that cannot fit a maintained template library.

Execute inside a constrained transaction

Validation predicts what a statement means. The database decides what it can do. Use both.

Run each accepted query through a dedicated connection pool and a fresh transaction. PostgreSQL documents a READ ONLY transaction mode that disallows ordinary writes and many data-definition operations. Set that mode at the start of the transaction, not through model-generated text.

Apply limits before execution:

• a short statement timeout;
• a lock timeout shorter than the statement timeout;
• an idle-in-transaction timeout;
• a maximum result row count;
• a maximum serialized result size;
• per-user and per-tenant concurrency limits;
• cancellation when the client request ends.

PostgreSQL's client connection defaults document statement_timeout, which aborts a statement after its configured duration. Set limits on the runtime role or transaction. Do not rely only on a web request timeout, because the database query may continue after the client has gone away.

Treat the row cap as an output policy, not a prompt suggestion. Fetch at most the cap plus one row. If the extra row exists, mark the result truncated and require the user to narrow the request. Do not send an arbitrary partial data set to the model as if it were complete.

Use a typed result envelope

The broker must not return either raw rows or an error string without context. Return a machine-readable state that separates database facts from workflow status.

class QueryResult:
    state: Literal[
        "complete", "empty", "truncated", "denied", "timed_out", "failed"
    ]
    columns: list[str]
    rows: list[list[Scalar]]
    row_count: int
    executed_query_id: str
    policy_reason: str | None


def execute_question(question, trusted_context):
    plan = generate_query_plan(question, approved_schema(trusted_context.role))
    checked = validate_plan(plan, trusted_context)
    if not checked.allowed:
        return QueryResult(state="denied", policy_reason=checked.reason)

    with broker_pool.transaction(read_only=True) as tx:
        apply_caller_context(tx, trusted_context)
        apply_query_limits(tx)
        return fetch_bounded(tx, checked.sql, checked.parameters)

The answer generator receives the state, approved columns, and bounded rows. Give it explicit behavior for each state. empty means the query completed and found no matching records. timed_out means no conclusion can be drawn. truncated means it may summarize the returned page only if it labels the result incomplete. denied must not trigger a rewritten query that attempts to bypass policy.

The typed states also cover a documented correctness failure. In LangChain issue 13802, an author reported that an empty SQL result could still produce a hallucinated answer. Treat that thread as a practitioner report, not a universal product guarantee. The same regression case belongs in any framework: zero rows must never become a made-up business value.

Decide when human approval belongs in the path

Human review is useful for exceptional access, not as a substitute for database policy. A reviewer cannot reliably inspect every generated statement under normal workload, and a readable query can hide an expensive plan or a tenant-context bug.

Require review when a request asks for a sensitive aggregate, unusually broad time range, export-sized result, or capability outside the caller's ordinary role. Show the reviewer the normalized query plan, data classes, tenant, row estimate, and enforced limits. If any material field changes after approval, request approval again.

Never let approval upgrade the runtime connection to an owner or superuser role. It may select a narrowly defined elevated capability whose database permissions and limits were configured in advance.

Test the boundary with hostile and ordinary queries

Unit tests for the SQL parser are necessary but insufficient. Run integration tests against the same database version, policies, roles, and connection settings used in production.

Build fixtures for these cases:

• a valid aggregate for the caller's tenant;
• an explicit predicate naming another tenant;
• a join that reaches an unapproved table or hidden column;
• a write hidden inside a common table expression;
• multiple statements separated by comments or unusual whitespace;
• an approved function wrapping an unapproved function;
• a query that waits on a lock;
• a large Cartesian join and a recursive query;
• a result one row above the cap;
• an empty result followed by answer generation;
• a client disconnect during execution;
• missing tenant context or an unavailable policy service.

Assert effects, not only messages. The cross-tenant query returns no protected rows. The write changes no records. The timeout cancels work in the database. The oversized result never reaches the model in full. Missing identity fails closed. Empty results produce a direct “no matching records” response without invented detail.

Record the normalized plan hash, caller identity, policy decision, database role, duration, row count, result state, and cancellation outcome. Do not log raw sensitive rows merely to prove the query ran.

Roll out with narrow capabilities

Start with one read-only reporting capability backed by curated views and a replica. Shadow the agent's proposed plans without executing them, then compare those plans with queries written by analysts. Add live execution for a small internal group only after the negative test suite passes.

Track denied plans, timeouts, truncations, empty-result answers, and row-policy denials. A rising denial rate may mean the schema contract is too narrow or the prompt is drifting. It does not justify weakening the broker. Change the approved capability deliberately, add tests, and deploy it as a policy version.

Your next action is to pick one internal question, create the smallest view that answers it, and run the cross-tenant, timeout, oversized-result, and empty-result tests through the real runtime role. Do not connect the model until those four cases fail safely without prompt instructions.

References

1. LangChain SQL-agent tutorial: model-generated SQL risks, narrowly scoped permissions, query checking, and optional human review.
2. OWASP SQL Injection Prevention Cheat Sheet: parameterized values, allowlisted identifiers, and least-privilege accounts.
3. PostgreSQL row-security policies: per-user row restrictions, default deny, and bypass conditions.
4. PostgreSQL SET TRANSACTION: database-enforced read-only transaction behavior.
5. PostgreSQL client connection defaults: statement timeouts and row-security handling.
6. LangChain issue 13802: an author report about hallucinated answers after empty SQL results.

---