Constraining Generated SQL to an Allow-Listed Function Set

The difference between an allow list and a deny list is what happens to something nobody thought of. A deny list permits it; an allow list rejects it. For SQL shaped by text a stranger typed, that difference is the whole control — and it only holds if the check reads a parse tree rather than the statement’s text. This guide covers building and keeping that list, the enforcement half of prompt-to-spatial-SQL generation.

When to Use This Approach

Any system where a generated statement reaches a database. The size of the list varies enormously; its existence does not.

Implementation

Parse first. A statement is checked by walking its tree, never by searching its text, because whitespace, comments and casing all defeat text matching while changing nothing about what runs.

import sqlglot
from sqlglot import exp

ALLOWED_FUNCTIONS = {
    "st_area", "st_astext", "st_buffer", "st_centroid", "st_contains",
    "st_distance", "st_dwithin", "st_intersects", "st_intersection",
    "st_isvalid", "st_length", "st_makevalid", "st_transform", "st_union",
    "count", "sum", "avg", "min", "max",
}
ALLOWED_TABLES = {"parcels", "rivers", "protected_areas", "schools"}


def check(sql: str, dialect: str = "postgres") -> list[str]:
    try:
        statements = sqlglot.parse(sql, read=dialect)
    except sqlglot.ParseError as exc:
        return [f"unparseable: {exc}"]

    if len(statements) != 1:
        return ["more than one statement"]

    tree = statements[0]
    if not isinstance(tree, exp.Select):
        return [f"not a read: {type(tree).__name__.lower()}"]

    problems = []
    for node in tree.find_all(exp.Anonymous, exp.Func):
        name = (node.sql_name() or "").lower()
        if name and name not in ALLOWED_FUNCTIONS:
            problems.append(f"function not allowed: {name}")
    for table in tree.find_all(exp.Table):
        if table.name.lower() not in ALLOWED_TABLES:
            problems.append(f"table not allowed: {table.name}")
    return problems

Two structural checks matter as much as the lists. Rejecting anything that is not a single statement closes the case where a benign read is followed by something else. Rejecting anything that is not a SELECT means the list never has to reason about writes at all.

The rejection message returned to the model should name the specific item, because that is what makes the next attempt succeed rather than repeat.

def to_model_feedback(problems: list[str]) -> str:
    return (
        "The statement was rejected: " + "; ".join(problems) +
        ". Rewrite it using only the functions and tables described in the schema."
    )

Validation & Testing

Test with statements designed to slip past a text matcher, because those are what a text matcher would have missed.

BYPASS_ATTEMPTS = [
    "SELECT /* st_area */ pg_read_file('/etc/passwd')",
    "SELECT ST_Area(geom) FROM parcels; DROP TABLE parcels",
    "SELECT\n\tPG_SLEEP(10)",
    "select st_area(geom) from PARCELS_SECRET",
    "SELECT ST_Area(geom) FROM parcels UNION SELECT version()",
]


@pytest.mark.parametrize("sql", BYPASS_ATTEMPTS)
def test_rejected(sql):
    assert check(sql), f"should have been rejected: {sql}"


def test_ordinary_query_passes():
    assert check("SELECT ST_Area(geom) FROM parcels WHERE ST_DWithin(geom, %s, 500)") == []

Also assert that the parse failure path rejects. A malformed statement that produces a parse error must not fall through to execution on the theory that the database will reject it anyway — that reasoning is correct today and becomes wrong the first time somebody adds a fallback.

Gotchas & Edge Cases

Operators that are functions. Spatial operators like && and <-> do not appear as function nodes in most parsers. They need their own enumeration, and forgetting them leaves a gap that looks like thoroughness.

Schema-qualified names. public.st_area and st_area are the same function and different strings. Normalise qualified names before comparison, or the list will reject legitimate statements and someone will widen it in the wrong direction.

Common table expressions and subqueries. Tables referenced inside a WITH clause or a nested select are still tables. A checker that only inspects the top-level FROM misses them entirely, which is why walking the whole tree matters more than checking the obvious places.

The list broadening under pressure. Every addition is individually justified and the aggregate drifts toward permissiveness. Reviewing the whole list periodically, rather than each addition as it arrives, is the only review that sees what it has become.

Relying on the list alone. It is one layer. The connection underneath should be read-only and scoped so that a gap in the list is a bug rather than a breach, and the two together are what make the design survivable.

Generating the List Rather Than Writing It

A hand-written list drifts from the schema it describes. Generating it from the catalogue — the functions actually installed, the tables the connection can actually read — and then narrowing it by hand keeps the two in step and makes additions deliberate rather than accidental.

The generated starting point also answers a question that is otherwise guesswork: what is currently reachable. A connection with more privileges than anyone realised shows up immediately as a list far longer than expected, which is a finding worth having before it is a finding somebody else has. The narrowing pass is then a review of a real inventory rather than an attempt to imagine one.

The same generation should feed the schema description given to the model, so that what it is told about and what it is permitted to use are the same set. A model shown a table it cannot query will use it, get rejected, and try again — and the wasted round trips are entirely avoidable, since both descriptions come from the same source. Keeping them together also means that removing a table removes it from both places at once, which is the sort of change that otherwise gets done in one place and remembered in the other several weeks later.

Operating This Step Over Time

Count rejections by reason, and keep the counts separated by function and table rather than aggregated into a single total. A function rejected repeatedly is usually a legitimate capability the list has not caught up with, and the right response is to consider adding it; one rejected once is worth reading in full, because a single unusual attempt is the shape most worth understanding. Both are invisible without the counter and obvious with it, and the separation is what lets you tell a missing capability from an unusual request at a glance.

Re-generate the candidate list after every schema migration and diff it against the enforced one. New tables and functions appear silently otherwise, and the diff is a two-minute review that catches a table added for one purpose becoming reachable for all of them.

Keep the bypass test set growing. Every rejected statement that turned out to be interesting — an unusual construct, a nesting nobody had considered, a function reached through an operator — belongs in the fixtures, because a test written from a real attempt is worth several written from imagination. The set also documents what the check is known to handle, which is the question anyone reviewing it will ask first.

Watch for the check being skipped. The most common way an allow list stops working is not that it is broadened but that a new code path reaches the database without passing through it — a background job, an export, a debugging endpoint that was never meant to survive. Routing every statement through one function, and asserting in tests that no other call site exists, is the structural version of that discipline; a periodic search for direct execution calls is the cheap version.

Frequently Asked Questions

Related

• Up to the parent topic: Prompt-to-Spatial-SQL Generation
• Preventing SQL Injection in LLM-Generated Spatial Queries
• Generating Valid PostGIS Queries from Natural Language
• Related topic: Spatial Function-Calling Schemas