Text to SQL Explained: Schema Context, Read-Only Roles and SQL Checks

It writes SQL that looks right and runs, but it can return a wrong number, run for an hour, read private columns, or change data if its login allows. The model cannot tell you which of these happened.

This guide shows how to build text to SQL so that each failure is either refused by the database or caught by a check you can test. Every error message quoted below came from a real run.

Picture a common setup. An online grocery business has stores in Pune and Jaipur and a PostgreSQL database of orders. The operations team gets a chat box in its internal dashboard. Someone types "How much did Pune sell in September?", a large model writes the SQL, the app runs it and shows a table. The business is made up, but the database, queries and errors in this guide are real test runs.

Four things can go wrong, and they need different fixes.

• Wrong answer. The model sums every order, including cancelled ones, because nothing told it that status matters. The query runs fine, so nobody notices.
• Runaway query. A join with no join condition pairs every order with every other order. On our test table of 2 lakh (200,000) orders, PostgreSQL estimated 40 billion rows.
• Data leak. The model selects the phone column because it exists. The numbers land in a chat window and in your logs.
• Unwanted write. A user, or text stored in the data, asks for a delete. If the login can delete, the database obeys.

The last one is not hypothetical. The OWASP Top 10 for LLM Applications 2025 lists Improper Output Handling as LLM05. One of its example scenarios is model-written SQL that, if not checked, could delete all tables.

A 2023 research paper on web apps that pass model output to a database studied prompt-to-SQL injection. That means crafted questions that make the model write harmful SQL. It found these attacks widespread across the seven models it tested.

You need seven terms. Each one is used again later, so it is worth a minute here.

• Schema: the list of tables, their columns and the type of each column, such as orders.amount_rupees being a number.
• Role and privileges: a role is a PostgreSQL login identity. Privileges are what it may do, such as SELECT (read) on one table but not UPDATE (change).
• Transaction: a group of statements that succeed or fail together. A read-only transaction refuses most writes.
• Parser and syntax tree: a parser reads SQL text and turns it into a tree of parts, like a sentence diagram in grammar class. Checking the tree is far more reliable than searching the text.
• Allow-list: a list of what is permitted, where everything else is refused. A deny-list does the opposite and misses whatever nobody thought of.
• CTE, or WITH clause: a named sub-query written at the top of a query, starting with WITH. PostgreSQL also allows INSERT, UPDATE or DELETE inside one.
• Statement timeout: a limit on how long one SQL statement may run before PostgreSQL cancels it.

Text to SQL is a pipeline. A question comes in, your app adds schema context, the model writes SQL, your code checks it, and the database runs it as a limited role. The user sees the rows and the SQL that produced them.

The key idea is simple. Model output is untrusted input, exactly like text typed into a form by a stranger. OWASP's guidance for LLM05 says the same thing: treat the model like any other user and validate its output before it reaches backend systems.

An everyday picture helps. Think of a new intern sent to a records room with a request slip.

• The visitor pass is the database role. It opens only some cabinets, and some drawers stay locked.
• The clerk who reads every slip before fetching anything is the parser check.
• The closing bell is the statement timeout. When it rings, the search stops.
• The tray that holds at most 1,000 pages is the row limit.

Where the picture breaks: a clerk would notice that a request makes no sense. A parser checks only the shape of the SQL. A well-formed query with the wrong logic passes every check, which is why evaluation exists.

Show the model only the tables and columns it may query, with a short note on every column whose meaning is not obvious. Build that text while logged in as the bot's own role, so hidden columns can never reach the prompt.

How you show the schema matters. A 2022 study gave a large code model several prompt formats for the same questions on a public benchmark. CREATE TABLE statements plus three sample rows per table scored best. Its execution accuracy, the share of questions whose SQL returned the right rows, was 67 percent. The question alone, with no schema, scored 8.3 percent.

PostgreSQL makes the "only what it may see" part easy. Its information_schema.columns view, a built-in saved query that you read like a table, shows only the columns the current user has access to. Run the builder below as report_reader, and the phone column simply never appears.

import os
import psycopg

COLUMNS_SQL = """
SELECT c.table_name, c.column_name, c.data_type,
       col_description(format('%I.%I', c.table_schema, c.table_name)::regclass,
                       c.ordinal_position) AS note
FROM information_schema.columns AS c
WHERE c.table_schema = 'public'
ORDER BY c.table_name, c.ordinal_position
"""


def schema_context(dsn):
    """Builds the schema text as the bot's own role, so hidden columns never appear."""
    tables = {}
    with psycopg.connect(dsn) as conn:
        for table, column, dtype, note in conn.execute(COLUMNS_SQL):
            line = f"  {column} {dtype}" + (f": {note}" if note else "")
            tables.setdefault(table, []).append(line)
    return "\n".join(f"Table {t}:\n" + "\n".join(cols) for t, cols in tables.items())


if __name__ == "__main__":
    print(schema_context(os.environ["BOT_DATABASE_URL"]))

Run it with the bot's connection string in BOT_DATABASE_URL. On the test database it printed this:

Table customers:
  id integer
  name text
  city text
Table orders:
  id bigint
  customer_id integer
  store_id integer
  amount_rupees numeric: Order value in rupees, after discounts, before delivery fee
  status text: One of delivered, cancelled, refunded
  created_at timestamp with time zone
Table stores:
  id integer
  city text
  name text

What just happened? The customers table lost its phone column because report_reader has no privilege on it. The two notes come from COMMENT ON COLUMN, which stores a description in the database next to the column.

Notes are where your business rules live. Here "revenue" means delivered orders only, after discounts. Without that note, the model has no way to know that cancelled orders should be left out. A note such as "joins stores.id" on orders.store_id also helps it find the right join.

Sample rows teach formats, such as status being stored as lower-case "delivered". Take them only from columns you would show any user. The prompt is sent to your model provider unless you host the model yourself.

When the schema is too big for one prompt

With hundreds of tables, the full schema will not fit, or it will distract the model. Pick the relevant tables first, a step research papers call schema linking. A 2023 paper did this by ranking tables and columns by relevance and passing only the top-ranked ones to the SQL generator.

Choosing between SQL and other sources is a different job. The SevyDevy guide "Semantic Routing: Sending Queries to the Right Source" covers how a question reaches the database at all. This guide starts once it has.

Create a separate login for the bot. Grant it CONNECT (log in to the database), USAGE on the schema (see what is inside) and SELECT on exactly the tables and columns it needs. Never let the bot connect as the owner of the tables.

This is standard database advice. The OWASP SQL Injection Prevention Cheat Sheet says accounts that only need read access should get read access only, to only the tables they need. OWASP's LLM06 entry, Excessive Agency, applies it to models: the database account the extension uses should enforce read-only access.

-- Run once as the table owner. Tested on PostgreSQL 16.15.
CREATE ROLE report_reader LOGIN;            -- add a password or certificate as your setup needs
GRANT CONNECT ON DATABASE shop TO report_reader;
GRANT USAGE ON SCHEMA public TO report_reader;
GRANT SELECT ON stores, orders TO report_reader;
GRANT SELECT (id, name, city) ON customers TO report_reader;   -- phone is left out
REVOKE TEMPORARY ON DATABASE shop FROM PUBLIC;                 -- no temp tables either
ALTER ROLE report_reader SET default_transaction_read_only = on;
ALTER ROLE report_reader SET statement_timeout = '3s';

Two lines deserve a note. Since PostgreSQL 15, new databases no longer let every role create tables in the public schema. Upgraded clusters keep the old permission, so check yours. Every new database still grants TEMPORARY, the right to create temporary tables, to all roles by default, so the REVOKE removes it. If other roles need temporary tables, grant TEMPORARY back to those roles by name. The 3-second role timeout is only a fallback; the app sets its own limit per transaction, as shown later.

We then sent the dangerous SQL as report_reader and recorded every refusal. The middle column is the role's normal state. For the right column we first switched read-only mode off, to prove that privileges alone still hold.

Notice the DELETE inside WITH. In read-only mode, PostgreSQL 16.15 named the outer statement, SELECT, in its error, not the DELETE. If you search logs for the word DELETE, you will miss it.

Also notice that SELECT * FROM customers fails too, because the star includes phone. If the bot needs whole rows, give it a view that lists only the safe columns. OWASP's cheat sheet suggests views for exactly this.

Why read-only mode is a seat belt, not a wall

Pause and predict: the role has default_transaction_read_only set to on. Can SQL sent by that same role turn it off?

Yes. As report_reader, SET default_transaction_read_only = off returned SET, and the next SHOW said off. Inside BEGIN READ ONLY, SET TRANSACTION READ WRITE also succeeded, as long as no query had run yet. After a first query, it failed with "transaction read-write mode must be set before any query".

The PostgreSQL documentation itself calls this "a high-level notion of read-only that does not prevent all writes to disk." Keep read-only mode on, because it is a useful second layer. Just never rely on it alone.

A read-only transaction does catch writes hidden where a parser cannot look. We called a PL/pgSQL function (one written in PostgreSQL's built-in scripting language) containing a DELETE from a plain SELECT, as the table owner inside BEGIN READ ONLY. PostgreSQL refused with "cannot execute DELETE in a read-only transaction".

Parse the SQL with a real SQL parser, then walk the whole tree. Accept exactly one statement, a query at the top, no write anywhere inside, and only allow-listed tables and functions. Then add your own row limit.

The naive check, and why it is wrong

The naive version checks whether the text starts with SELECT. It is wrong in both directions. Here is its output on four queries, from a four-line Python function:

allowed | SELECT 1; TRUNCATE orders
blocked | WITH pune AS (SELECT id FROM stores WHERE city = 'Pune') SELECT count(*) FROM orders o JOIN pune p ON p.id = o.store_id
blocked | WITH gone AS (DELETE FROM orders WHERE id = 1 RETURNING *) SELECT count(*) FROM gone
allowed | SELECT * INTO copy_of_orders FROM orders

It allowed a second statement that empties a table, and a SELECT INTO that creates one. It blocked a harmless query just because it starts with WITH. Regular expressions that search for DROP or DELETE fail in similar ways, because SQL has comments, quoting and many ways to say the same thing.

Pause and predict: a smarter check parses the SQL and looks only at the top node of the tree. For WITH gone AS (DELETE FROM orders ... RETURNING *) SELECT count(*) FROM gone, what is the top node?

It is a Select. A top-only check would allow it. PostgreSQL's documentation says data-modifying statements in WITH are executed exactly once and always to completion, whether or not the main query reads their output. So the guard must walk every node.

A guard built on sqlglot

sqlglot is an open-source SQL parser for Python that understands PostgreSQL and many other dialects. Install the exact version tested here with pip install "sqlglot==30.20.0". Version 30.24.0 was the newest as of 24 October 2026, so pin whichever you test.

"""Parser-based guard for model-written SQL. Tested with sqlglot==30.20.0, Python 3.13."""
import sqlglot
from sqlglot import exp
from sqlglot.errors import ParseError
from sqlglot.optimizer.scope import traverse_scope

ALLOWED_TABLES = {"orders", "stores", "customers"}
ALLOWED_ANON_FUNCTIONS = set()   # functions sqlglot does not know; add only after review
WRITE_NODES = (exp.Insert, exp.Update, exp.Delete, exp.Merge,
               exp.Create, exp.Drop, exp.Alter, exp.Command)
MAX_ROWS = 1000


class Rejected(Exception):
    pass


def check_sql(sql):
    try:
        statements = [s for s in sqlglot.parse(sql, dialect="postgres") if s is not None]
    except ParseError as e:
        raise Rejected(f"could not parse: {str(e).splitlines()[0]}")
    if len(statements) != 1:
        raise Rejected(f"expected exactly 1 statement, got {len(statements)}")
    tree = statements[0]
    if not isinstance(tree, exp.Query):            # SELECT, UNION and friends
        raise Rejected(f"only queries are allowed, got {type(tree).__name__}")
    for node in tree.walk():                       # every node, not just the top
        if isinstance(node, WRITE_NODES):
            raise Rejected(f"write found inside the query: {type(node).__name__}")
        if isinstance(node, exp.Select) and node.args.get("into"):
            raise Rejected("SELECT INTO creates a table")
        if isinstance(node, exp.Select) and node.args.get("locks"):
            raise Rejected("row locks (FOR UPDATE / FOR SHARE) are not allowed")
    if any(not t.name for t in tree.find_all(exp.Table)):
        raise Rejected("table functions are not allowed")
    try:                                           # scopes tell real tables from CTE names
        scopes = traverse_scope(tree)
    except Exception as e:
        raise Rejected(f"could not resolve table names: {e}")
    for scope in scopes:
        for source in scope.sources.values():
            if not isinstance(source, exp.Table):  # a CTE or sub-query, checked in its own scope
                continue
            ident = source.this
            name = ident.name if getattr(ident, "quoted", False) else ident.name.lower()
            if name not in ALLOWED_TABLES or source.db.lower() not in ("", "public"):
                raise Rejected(f"table not on the allow-list: {source.sql(dialect='postgres')}")
    for fn in tree.find_all(exp.Anonymous):
        if fn.name.lower() not in ALLOWED_ANON_FUNCTIONS:
            raise Rejected(f"function not on the allow-list: {fn.name}")
    return tree


def limit_value(tree):
    node = tree.args.get("limit")
    if isinstance(node, exp.Limit):
        node = node.expression
    elif isinstance(node, exp.Fetch):
        node = node.args.get("count")
    if isinstance(node, exp.Literal) and node.is_int:
        return int(node.this)
    return None                                    # no limit, LIMIT ALL or an expression


def cap_rows(tree, max_rows=MAX_ROWS):
    n = limit_value(tree)
    if n is not None and min(n, max_rows) == n:
        return tree                                # already small enough
    tree = tree.copy()
    tree.set("limit", None)
    return tree.limit(max_rows)


def guard(sql, max_rows=MAX_ROWS):
    return cap_rows(check_sql(sql), max_rows).sql(dialect="postgres")

We ran this guard on 37 cases. The important ones are below, with the exact message each produced.

What just happened? Most rejections come from the shape of the tree, not from words in the text. The comment case passed because everything from -- to the end of the line is a comment, to sqlglot and to PostgreSQL alike. sqlglot rewrote it as a block comment, and PostgreSQL ran one statement.

Two details are easy to miss. SELECT INTO and FOR UPDATE are still Select nodes, so the guard checks their into and locks parts separately. And table names are resolved with sqlglot's scope module, which knows where each CTE name is visible.

Our first version simply skipped any name that some CTE defined. That let a real table through when the CTE lived inside a nested sub-query, or was defined after the CTE that used it. PostgreSQL treated both names as real tables, and the scope-aware version rejects both.

Functions sqlglot does not know, such as pg_sleep, set_config, nextval and pg_read_file, all parse as a generic Anonymous node. Refusing every Anonymous function by default is an allow-list. In our test it also refused query_to_xml, a built-in function that runs a query passed in as a string. Functions sqlglot knows, such as COUNT, DATE_TRUNC and generate_series, pass, so the timeout and row cap still matter.

Note that the guard returns SQL regenerated by sqlglot, not the model's original text. That way the SQL you checked is exactly the SQL you run. Keep a test that runs every passing case on PostgreSQL; all 14 of ours ran.

sqlglot's own documentation says it is "a transpiler, not a validator", and that its parser is intentionally lenient. That is fine for a filter. It is one more reason the database role, not the parser, has to be the wall.

statement_timeout makes PostgreSQL cancel any statement that runs longer than the limit, and a LIMIT added by your code caps how many rows come back. Set the timeout per transaction from your app, not only on the role.

The documentation says the timeout is measured from when a command arrives at the server until it completes. A value without units means milliseconds, and zero, the default, means no limit. With a 2-second limit, our cross join test took 2.01 seconds end to end and failed with "canceling statement due to statement timeout".

Why the role setting is not enough

Like read-only mode, a timeout is a setting the role can change. As report_reader, SET statement_timeout = 0 worked. So did calling set_config('statement_timeout', '0', false) inside an ordinary SELECT.

That set_config call did not stop the timer already running for its own statement; that test was still cancelled. But the new value stayed on the session. With a connection pool, a set of open connections the app reuses, the next question on that connection would then run with no limit at all.

The fix has two parts. The guard refuses set_config and SET, as shown above. And the app sets the timeout inside each transaction with set_config(..., true), which is the same as SET LOCAL and ends with the transaction. In our test, a transaction that set its own 2-second limit still cancelled pg_sleep(4), even after an earlier transaction had set the session value to zero.

import os
import psycopg
from guard import guard, Rejected, MAX_ROWS

DSN = os.environ["BOT_DATABASE_URL"]     # connects as report_reader, never as the owner


def run(model_sql, timeout="2s"):
    try:
        safe_sql = guard(model_sql, MAX_ROWS + 1)    # one extra row tells us we cut it
    except Rejected as e:
        return {"ok": False, "stage": "guard", "error": str(e)}
    with psycopg.connect(DSN) as conn:
        conn.read_only = True                         # every transaction starts read-only
        with conn.cursor() as cur:
            try:
                # Same as SET LOCAL: the timeout ends with this transaction.
                cur.execute("SELECT set_config('statement_timeout', %s, true)", (timeout,))
                cur.execute(safe_sql)
                rows = cur.fetchmany(MAX_ROWS + 1)
            except psycopg.Error as e:
                return {"ok": False, "stage": "database", "error": str(e).splitlines()[0]}
        conn.rollback()
    return {"ok": True, "sql": safe_sql, "rows": rows[:MAX_ROWS],
            "truncated": len(rows) == MAX_ROWS + 1}


if __name__ == "__main__":
    print(run("SELECT count(*) FROM orders a CROSS JOIN orders b"))
    print(run("SELECT id FROM orders")["truncated"])

The two test calls printed a timeout error from the database stage, then True. The second query had no LIMIT, so the guard added LIMIT 1001. Getting 1,001 rows back tells the app to show 1,000 and say the result was cut.

psycopg's read_only setting starts each new transaction as read-only, whatever the role default says. We confirmed it with SHOW transaction_read_only, which returned on inside the runner.

A LIMIT does not make a query cheap

A LIMIT caps rows returned, not work done. EXPLAIN for a GROUP BY over all orders with LIMIT 5 still planned a sequential scan, reading all 200,000 rows, before the limit. The timeout is what stops expensive work.

You can also reject obvious monsters before running them. EXPLAIN without ANALYZE plans a query but does not run it. For the cross join, PostgreSQL estimated 40,000,000,000 rows at a total cost of about 617 million. Cost is in planner units, not milliseconds, so tune any threshold on your own data.

Two more options help, as of 24 October 2026. PostgreSQL 17 added transaction_timeout, which ends the whole session when one transaction runs too long; it does not exist on 16. And pointing the bot at a read replica, a read-only copy of the database, keeps a heavy query away from your checkout traffic.

Keep a set of real questions, each with a hand-checked gold query. Run the model's SQL and the gold SQL, and compare the result rows, not the SQL text. Run both on more than one small database built to expose mistakes.

Comparing SQL text fails at once. "SELECT COUNT(o.id) FROM orders AS o WHERE o.status = 'cancelled'" and "SELECT count(*) FROM orders WHERE status = 'cancelled'" are different strings with the same answer.

Comparing results on one database has the opposite problem. A wrong query can match by luck. We built two tiny test databases: one with only delivered orders, and one with a cancelled order, a refund and a store with no orders. The candidate queries were hand-written stand-ins for model output.

What just happened? On the happy-path database, both wrong queries looked correct. Only the database with cancelled orders and an empty store caught them.

A 2020 research paper measured this effect at scale. In its results table, checking on a single database had a mean false-positive rate of 6.5 percent across 21 systems it re-scored. Its fix was a small suite of databases chosen so that wrong queries give different answers.

Compare rows as a multiset, meaning order is ignored but duplicates count, unless the gold query has ORDER BY. This helper runs as it stands:

from collections import Counter


def same_result(gold_rows, candidate_rows, gold_sql):
    """Compares result rows, not SQL text."""
    if "order by" in gold_sql.lower():
        return gold_rows == candidate_rows                # order matters only if asked for
    return Counter(gold_rows) == Counter(candidate_rows)  # otherwise a multiset: duplicates count


print(same_result([(5,)], [(5,)], "SELECT count(*) FROM orders"))                       # True
print(same_result([("Pune", 2), ("Jaipur", 1)], [("Jaipur", 1), ("Pune", 2)], "SELECT ..."))  # True
print(same_result([("Pune", 2), ("Jaipur", 1)], [("Jaipur", 1), ("Pune", 2)], "... ORDER BY 2 DESC"))  # False

1. 1

   Collect real questions

   Take them from your users or logs, not from your imagination. Include vague ones like "sales last week", where the gold query records your agreed meaning.

2. 2

   Write and check gold queries

   A person who knows the data writes each one and checks its answer by hand on a small database.

3. 3

   Build edge-case databases

   Add cancelled and refunded orders, empty stores, NULLs and timestamps near midnight in IST and UTC. Each row exists to make some wrong query visible.

4. 4

   Add refusal cases

   Include questions that ask to delete data or show phone numbers. The expected result is a guard rejection or a database error, never rows.

5. 5

   Score every change

   Rerun the set whenever the prompt, schema notes, model or guard changes. Track the match rate, guard rejections and timeouts separately.

Prompt injection is text that makes the model ignore your instructions. It can arrive in the user's question. It can also hide in data the model reads, such as a customer note shown back to the model in a follow-up step.

OWASP's 2025 entry for prompt injection says it is unclear whether fool-proof prevention exists. So design as if the model will sometimes be fooled. The role, the transaction and the guard hold whatever the model writes.

Some apps need more than one read-only role, because different users may see different rows. A store manager in Jaipur, say, should not see Pune orders. OWASP's LLM06 entry recommends running actions in the context of the specific user, with the minimum privileges necessary.

PostgreSQL row-level security policies can enforce that by restricting which rows each role can read. Be careful what the policy trusts. In our test, a policy that filtered on a session setting showed a different store's rows after a plain SELECT called set_config to change that setting. Base policies on the role itself, and keep set_config refused in the guard. Table owners normally bypass these policies, one more reason the bot must never connect as the owner.

Finally, never pass query results to a later model step that holds write tools. The SevyDevy guides "Prompt Injection Prevention: How to Defend AI Agents" and "LLM Guardrails Explained: Layered Checks Before and After the Model" cover those wider defences.

Frame it as untrusted code generation with layered defences, then walk the request from question to result. This order works well:

1. 1

   Pin down the use case

   For example, read-only analytics for internal staff, on data that includes personal details. Ask whether any user should see only part of the data.

2. 2

   Describe the schema context

   Allowed tables and columns only, notes on business meaning, a few sample rows from safe columns, and schema linking when there are too many tables.

3. 3

   Constrain the model's output

   Ask for one SQL query in a fixed format, such as a JSON field, so your code never has to guess where the SQL starts. The guide "LLM Structured Output: JSON Schemas That Don't Break" covers this.

4. 4

   Check before running

   Parse with a real parser, allow one query only, walk the tree for writes, allow-list tables and functions, and add a row cap.

5. 5

   Run with least privilege

   A SELECT-only role, a read-only transaction, a per-transaction timeout, a read replica, and per-user row scoping if needed.

6. 6

   Prove it and watch it

   Execution-based tests on several small databases, refusal cases in the same suite, and logs of every SQL query, rejection and timeout.