Let the agent write SQL inside guards
閱讀約需 111 分鐘 分鐘,12 節 中共有 11 個範例。
本頁內容
- Give the agent a database with only what it may read
- Give each role a database of views
- 在執行前先剖析 SQL
- Allow only read-only queries on known tables
- Bound the rows and the cost
- Enforce the deadline by killing a process
- 對防護層重放攻擊
- Repair once from the error
- Transpile to the warehouse's dialect
- 何時應優先建立新指標而非使用 SQL
- 正式環境中會出什麼問題
- 本章做出的決定
此部分尚未翻譯,因此以英文呈現。
By the end of this chapter, you can handle the questions no metric covers by letting the agent write SQL that runs read-only on a database holding only the rows and columns its role may read, and that is parsed, checked against an allowlist of tables, bounded in rows, size, cost and time, repaired once from an error, and transpiled to your warehouse's dialect. You can also tell from the golden questions when such a question should become a metric instead.
Chapter 6 sent the questions of the shop, which sells outdoor and camping gear across three regions, to governed metrics first, and its semantic layer holds ten metrics that cover the monthly finance questions. The questions no metric covers are the long tail: how much revenue came from orders that included a tent, or what share of sessions converted. Chapter 6's agent declines those, which is honest and frustrating. Ten of the shop's golden questions are in the long tail, listed in the section on promoting them.
For the long tail, the agent has to write SQL. Chapter 4 already had it do so, and ran the SQL as written on a connection that can't write. This chapter puts guards around it. DuckDB's own documentation says to "Treat SQL in DuckDB like code in Bash or Python. Do not execute SQL from untrusted sources without proper sandboxing" (DuckDB). A model's SQL is SQL from an untrusted source in that sense, whoever typed the question.
So the security boundary is the database: the agent connects read-only, to a database that holds only views limiting the rows and columns its role may see. The parser's checks in lib/sql_guard.py, the timer and the cost check are a teaching layer and defence in depth. They catch mistakes, give the model refusals to repair from, and bound cost and time, but they don't keep data from a role. Drafts of the parser were bypassed, as the replay section shows, and any parser can be.
此部分尚未翻譯,因此以英文呈現。
Give the agent a database with only what it may read
Least privilege means that the agent's SQL runs on a connection that can reach what the agent may query and nothing else. It comes before any check on the SQL, because a check is a judgement about text, and when the judgement is wrong, the database decides what the query reaches.
Chapter 1's warehouse.connect() is read-only, but it sees everything: the raw schema, the legacy mart and the semantic views. So agent_warehouse() in lib/warehouse.py copies the seven marts the agent may read, named one by one in AGENT_TABLES, into a second database file, and opens it read-only. File access and extensions are off, memory is limited to 1 GB and each query to two threads, and the settings are locked, so a query can't switch any of it back on. A setting is only as good as the attempts that test it, so the example sends six attempts straight to the agent's database, with no guard in front, and then reads DuckDB's catalog.
1"""The agent's own database holds copies of the seven allowed marts, opened read-only with files, extensions and settings2locked. The attempts show what those locks refuse, and the catalog lines show what they can't hide."""3import duckdb45from lib.warehouse import agent_warehouse, connect67shop = connect()8agent = agent_warehouse(shop) # a second file: the seven allowed marts, copied, and nothing else9(shop_tables,), = shop.execute("SELECT count(*) FROM duckdb_tables()").fetchall()10print("tables in the shop's warehouse:", shop_tables)11tables = agent.execute("SELECT schema_name || '.' || table_name FROM duckdb_tables() ORDER BY 1").fetchall()12print("tables in the agent's database:", len(tables))13print("the agent's tables:", ", ".join(name for (name,) in tables))1415(source,), = shop.execute("SELECT path FROM duckdb_databases() WHERE database_name = 'shop'").fetchall()16ATTEMPTS = { # sent straight to the agent's database, with no guard in front17 "raw table": "SELECT count(*) FROM raw.customers",18 "legacy mart": "SELECT sum(revenue) FROM analytics.orders_legacy",19 "attach the shop's file": f"ATTACH '{source}' AS shop (READ_ONLY)",20 "read a file": "SELECT * FROM read_csv('data/golden/questions.toml')",21 "load an extension": "LOAD httpfs",22 "turn file access back on": "SET enable_external_access = true",23}24for label, sql in ATTEMPTS.items():25 try:26 agent.execute(sql).fetchall()27 print(f"{label}: allowed")28 except duckdb.Error as e:29 kind, message = str(e).splitlines()[0].split(": ", 1) # the error's kind, then its message30 print(f"{label}: {kind}, {message.split(' - ')[-1]}") # the file's path is left out of the output3132# What no setting hides: DuckDB's own catalog, which every database has.33(listed,), = agent.execute("SELECT count(*) FROM information_schema.tables WHERE table_schema = 'analytics'").fetchall()34print("catalog, tables it lists:", listed)35(settings,), = agent.execute("SELECT count(*) FROM duckdb_settings()").fetchall()36print("catalog, settings it lists:", settings)37(paths,), = agent.execute("SELECT count(*) FROM duckdb_databases() WHERE path IS NOT NULL").fetchall()38print("catalog, database files it names:", paths)
python agent-warehouse.pytables in the shop's warehouse: 16
tables in the agent's database: 7
the agent's tables: analytics.dim_customers, analytics.dim_date, analytics.dim_products, analytics.fct_order_lines, analytics.fct_orders, analytics.fct_refunds, analytics.fct_sessions
raw table: Catalog Error, Table with name "raw.customers" does not exist because schema "raw" does not exist.
legacy mart: Catalog Error, Table with name orders_legacy does not exist!
attach the shop's file: Permission Error, file system operations are disabled by configuration
read a file: Permission Error, file system operations are disabled by configuration
load an extension: Permission Error, Loading external extensions is disabled through configuration
turn file access back on: Invalid Input Error, the configuration has been locked
catalog, tables it lists: 7
catalog, settings it lists: 170
catalog, database files it names: 1
The shop's warehouse has 16 tables, and the agent's database keeps 7 of them. A query on raw.customers gets Catalog Error, Table with name "raw.customers" does not exist because schema "raw" does not exist.. Nothing refused it, because the schema isn't there, and the legacy mart isn't there either. Attaching the shop's file, reading any other file and loading an extension all fail. A SET that would turn file access back on fails too, because the configuration is locked. DuckDB's documentation calls such settings "defense-in-depth" and adds that they "are not a substitute for proper sandboxing" (DuckDB).
What no setting hides is DuckDB's catalog, such as information_schema and duckdb_settings(), which describes the database itself. In the agent's database the catalog lists 7 tables, all of them ones the agent may read, and 170 settings, and it names 1 database file with its path on disk. The database can't refuse those reads, so the allowlist section refuses them in the parser.
Working rule: Run the agent's SQL on a connection that can reach only what the agent may query: a role with only those grants, or, in DuckDB, a read-only database file that holds only the allowed tables, with file access and extension loading off and its settings locked. Name the allowed tables one by one. Then list what the database can't hide, such as its catalog, and refuse those reads in the guard.
此部分尚未翻譯,因此以英文呈現。
Give each role a database of views
The agent's database leaves out whole tables, but the shop's people don't all see the same rows and columns of the rest. Five roles ask the agent questions, written down in examples/data/shop/roles.toml, which chapter 9 explains. EMEA's regional lead sees EMEA's rows only, finance sees customers with names and emails masked, and support sees orders without revenue. A role's view of a table returns the rows that role may see, with a masked column holding a constant and a denied column left out. The view, not the parser, should decide what the role reads.
Real warehouses do this with grants. PostgreSQL's documentation shows a view that hides a column, granted to a role that can't read the table beneath it, and warns that such a view can't reliably conceal rows unless its security_barrier flag is set (PostgreSQL). Snowflake's secure views exist to limit access to sensitive data (Snowflake). Its row access policies and masking policies attach the same rules to the table itself, so the mask applies wherever the column appears (Snowflake). So in production, the agent's warehouse user has a read-only role with SELECT on the role's secure views and nothing else.
DuckDB has no roles or GRANT, and a view can be read only by a connection that can read the tables under it. So agent_warehouse(con, policy=...) builds each role its own database: every table in it is the role's view of a mart, written out as a table of the same name, and opened read-only with the same locked settings. The views come from chapter 9's Policy.subquery(), so each rule is written once. The example sends three queries the guard accepts, since it knows tables and columns but not roles, straight to each role's database, then replays a catalog bypass that a draft of the guard let through.
1"""A role's database, not the guard, decides what the role reads: queries the guard accepts return less from each role's2database than from every mart, and the catalog bypass lists no net_revenue_eur for support. The assert after the bypass's3tables-listed line settles that none of support's tables has the net_revenue_eur column the sum needs."""4import duckdb56from lib.guard_attacks import ATTEMPTS7from lib.policies import policy_for8from lib.sql_guard import SqlGuard9from lib.warehouse import agent_warehouse, connect1011shop = connect()12marts = agent_warehouse(shop) # every allowed mart, whole: no role's limits13guard = SqlGuard(marts) # chapter 7's guard knows tables and columns, not roles14roles = {"EMEA's regional lead": policy_for("regional_lead", "EMEA"), "finance": policy_for("finance"),15 "support": policy_for("support")}16QUERIES = {"EMEA's regional lead": "SELECT count(DISTINCT region) AS regions FROM analytics.fct_orders",17 "finance": "SELECT count(*) AS n FROM analytics.dim_customers WHERE email LIKE 'customer-0004%'",18 "support": "SELECT sum(net_revenue_eur) AS revenue FROM analytics.fct_orders"}192021def first_value(con, sql):22 try:23 return con.execute(sql).fetchall()[0][0] # the first column of the first row24 except duckdb.Error as e:25 return str(e).splitlines()[0]262728for role, sql in QUERIES.items():29 database = agent_warehouse(shop, policy=roles[role]) # the role's database: its views of the marts only30 guard.check(sql) # raises if the guard refuses; it doesn't31 print(f"{role}, guard accepts: yes")32 print(f"{role}, every mart: {first_value(marts, sql)}")33 print(f"{role}, role's database: {first_value(database, sql)}")3435# A parser bypass a draft of the guard let through: a CTE named like DuckDB's catalog, which lists every table's SQL.36support = agent_warehouse(shop, policy=roles["support"])37# the bypass's rows as text: each table's CREATE statement, as DuckDB's catalog shows it38listed = " ".join(str(row) for row in support.execute(ATTEMPTS["CTE named duckdb_tables"]).fetchall())39print(f"support, catalog bypass, tables listed: {listed.count('CREATE TABLE')}")40assert "net_revenue_eur" not in listed # the catalog lists no net_revenue_eur for support, which its sum needs41print(f"support, catalog bypass, names a revenue column: {'net_revenue_eur' in listed}")42print(f"support, a write: {first_value(support, 'DELETE FROM analytics.fct_orders')}")
python role-database.pyEMEA's regional lead, guard accepts: yes
EMEA's regional lead, every mart: 3
EMEA's regional lead, role's database: 1
finance, guard accepts: yes
finance, every mart: 10
finance, role's database: 0
support, guard accepts: yes
support, every mart: 4019333.37
support, role's database: Binder Error: Referenced column "net_revenue_eur" not found in FROM clause!
support, catalog bypass, tables listed: 6
support, catalog bypass, names a revenue column: False
support, a write: Invalid Input Error: Cannot execute statement of type "DELETE" on database "agent" which is attached in read-only mode!
Each query passed the guard, and each got only what the role's views hold. Counting regions in the orders table gives 3 on every mart and 1 on the lead's database. Finance's search for emails that start customer-0004 matches 10 customers on every mart and 0 on finance's, because every email in finance's database is (masked).
Support's sum of net revenue fails with Binder Error: Referenced column "net_revenue_eur" not found in FROM clause!, because the column isn't there. The bypass, a CTE named like DuckDB's catalog, lists 6 tables in support's database, and none of them names net_revenue_eur, the column the sum needs, which the example asserts. A write fails as read-only.
Working rule: Make the database the boundary: connect the agent read-only, as a role that can read only views limiting the rows and columns that role may see, through secure views, row access policies and masking policies where the warehouse has them. In DuckDB, give each role its own read-only database of those views. Generate the views from the same policy the rest of your stack uses, and test them with queries the parser accepts.
在執行前先剖析 SQL
防護機制只能檢查它看得到的內容,而以文字呈現的 SQL 幾乎什麼都看不出來:只檢查 DELETE 這個字,會漏掉註解、加引號的名稱,以及同一個陳述式的十幾種寫法。剖析(parsing)會把文字轉成一棵樹,每個陳述式、資料表、欄位與函式各有一個節點,因此之後的每項檢查針對的是節點,而不是字元。sqlglot(版本為 30.31.0)可以剖析 DuckDB 方言與許多其他方言的 SQL,同一棵樹之後也能再轉回 SQL。它的 README 清楚說明了剖析不做什麼:「SQLGlot is a transpiler, not a validator. A query that parses successfully may still fail at execution time」(sqlglot)。因此防護機制先剖析,再依據查詢將要執行的資料倉儲來檢查這棵樹。
以 gq-017(商店各國家在 2025 年的淨營收)為例,第 4 章中包含四層脈絡的紀錄裡,有一則回覆是依日曆表的國家欄位分組。為了了解防護機制如何看待這則回覆,本範例先剖析該回覆,列出這棵樹,並對它執行防護機制的 check();接著剖析一個含有兩個陳述式的字串和一個有語法錯誤的查詢,以呈現剖析在何處停止。
1"""Parse the SQL the model wrote for gq-017 before it runs: its tree, the tables it reads, a column the guard refuses, and2two inputs that don't parse as one query. The prose's examples of each come from these printed lines."""3import duckdb4import sqlglot5from sqlglot import exp6from sqlglot.errors import ParseError78from lib.context import LAYERS, build_context9from lib.golden import load_golden10from lib.model import RecordedModel11from lib.prompts import layered_sql_messages12from lib.sql_guard import GuardError, allowed_schema, check13from lib.tools import extract_sql14from lib.warehouse import agent_warehouse, connect1516con = connect()17question = next(q for q in load_golden() if q.question_id == "gq-017")18context = build_context(con, question.question, LAYERS) # chapter 4's context, every layer19sql = extract_sql(RecordedModel("context-layers").complete(layered_sql_messages(context.text, question.question)))20print(f"question: {question.question}\n{sql}")2122tree = sqlglot.parse_one(sql, read="duckdb")23print("root:", type(tree).__name__)24print("tables read:", ", ".join(f"{t.db}.{t.name} AS {t.alias}" for t in tree.find_all(exp.Table)))25print("columns named:", ", ".join(sorted({f"{c.table}.{c.name}" for c in tree.find_all(exp.Column) if c.table})))2627try:28 check(sql, allowed_schema(agent_warehouse(con))) # the allowlist: the agent's database's tables29except GuardError as e:30 print("guard refuses:", e.kind)31 # the refusal, then the columns of each table it reads, after a fixed phrase32 message, columns = str(e).split(". The tables it reads have only these columns: ")33 print("guard message:", message)34 print("message then lists the columns of:", ", ".join(part.split(" has ")[0] for part in columns.split("; ")))35try:36 con.execute(sql)37except duckdb.Error as e:38 print("DuckDB, had it run:", str(e).splitlines()[0])3940statements = sqlglot.parse("SELECT 1; DROP TABLE analytics.fct_orders", read="duckdb")41print("statements in one string:", ", ".join(type(s).__name__ for s in statements))42try:43 sqlglot.parse_one("SELECT region, sum(net_revenue_eur FROM analytics.fct_orders GROUP BY region", read="duckdb")44except ParseError as e:45 error = e.errors[0] # the parser's first problem; line and col count from 146 print("parse error fields:", ", ".join(sorted(error)))47 print(f"parse error: {error['description']}\nparse error line: {error['line']}\nparse error column: {error['col']}")
python parse-agent-sql.pyquestion: What was net revenue in each of our countries in 2025?
SELECT d.country, round(sum(o.net_revenue_eur), 2) AS net_revenue
FROM analytics.fct_orders AS o
JOIN analytics.dim_date AS d ON d.date_day = o.order_date_local
WHERE o.status <> 'cancelled'
AND d.calendar_year = 2025
GROUP BY d.country
ORDER BY net_revenue DESC
root: Select
tables read: analytics.fct_orders AS o, analytics.dim_date AS d
columns named: d.calendar_year, d.country, d.date_day, o.net_revenue_eur, o.order_date_local, o.status
guard refuses: column
guard message: Unknown column: country
message then lists the columns of: analytics.fct_orders, analytics.dim_date
DuckDB, had it run: Binder Error: Table "d" does not have a column named "country"
statements in one string: Select, Drop
parse error fields: col, description, end_context, highlight, into_expression, line, start_context
parse error: Expecting )
parse error line: 1
parse error column: 39
這棵樹的根節點是 Select,讀取的資料表為 analytics.fct_orders AS o, analytics.dim_date AS d。單靠剖析器,d.country 會被放行。check() 會依據查詢可能讀取的資料表之欄位,以 sqlglot 的 qualify 對每個欄位進行限定;遇到無法歸屬的欄位時,它會引發錯誤。
在此防護機制以 column 拒絕該查詢:Unknown column: country。訊息接著列出 analytics.fct_orders, analytics.dim_date 的欄位,模型需要這些資訊來修正查詢。若照原樣執行,這個查詢在 DuckDB 中會失敗,並出現 Binder Error: Table "d" does not have a column named "country"。
SELECT 1; DROP TABLE analytics.fct_orders 是兩個陳述式,即 Select, Drop,因此防護機制只接受恰好一個陳述式。
缺少右括號的查詢會引發 sqlglot 的 ParseError,其 errors 清單會給出每個問題的欄位,即 col, description, end_context, highlight, into_expression, line, start_context。對於範例中有錯誤的查詢,描述為 Expecting ),位於第 1 行、第 39 欄。防護機制會把這些資訊交給模型,修復一節便是這樣使用它們。
實用原則: 在任何東西執行之前,先以將要執行該查詢的資料倉儲之方言,把代理程式的 SQL 剖析成一棵樹,並且只接受恰好一個陳述式。檢查節點,絕不檢查文字。能被剖析的查詢,只代表它的結構現在可以被檢視,並不代表它有效;在送往資料倉儲之前,先依據它可能讀取的資料表解析其欄位。
此部分尚未翻譯,因此以英文呈現。
Allow only read-only queries on known tables
With the agent's SQL parsed into a tree, the guard can ask two questions of it: does it only read, and does it read only what it may? Checking the statement at the root isn't enough. In DuckDB a common table expression, a CTE, can hold a DELETE with RETURNING inside a SELECT. So the guard accepts only a query at the root, then walks every node of the tree and refuses any statement that writes, changes settings or reaches outside the warehouse.
One hostile query, sent through the guard and straight to the agent's read-only database, and to a writable copy of the shop in a temporary folder, shows what a write would do. Its CTE deletes every refund over 50 euros, and its outer SELECT counts them.
1"""A DELETE hidden in a CTE: the root says SELECT, the tree walk finds the DELETE, and the read-only warehouse refuses it.23The attack runs for real only on a throwaway copy of the shop, built from the same seed in a temporary folder.4"""5import tempfile6from pathlib import Path78import duckdb9import sqlglot10from sqlglot import exp1112from lib.sql_guard import GuardError, SqlGuard13from lib.warehouse import agent_warehouse, build, connect1415ATTACK = """WITH gone AS (DELETE FROM analytics.fct_refunds WHERE amount_eur > 50 RETURNING refund_id)16SELECT count(*) AS refunds_over_50 FROM gone"""1718tree = sqlglot.parse_one(ATTACK, read="duckdb")19print("root:", type(tree).__name__)20print("statements in the tree:", ", ".join(type(n).__name__ for n in tree.walk() if isinstance(n, (exp.Query, exp.DML)))) # every node, not only the root2122agent = agent_warehouse(connect()) # the agent's read-only database23try:24 SqlGuard(agent).run(ATTACK)25except GuardError as e:26 print(f"guard refuses ({e.kind}): {e}")27try:28 agent.execute(ATTACK) # past the guard, straight to the read-only database29except duckdb.Error as e:30 print("read-only database:", str(e).splitlines()[0])3132with tempfile.TemporaryDirectory() as folder: # a writable copy, deleted when the block ends33 path = Path(folder) / "shop.duckdb"34 build(path)35 with duckdb.connect(str(path)) as writable:36 (before,), = writable.execute("SELECT count(*) FROM analytics.fct_refunds").fetchall()37 (answer,), = writable.execute(ATTACK).fetchall()38 (after,), = writable.execute("SELECT count(*) FROM analytics.fct_refunds").fetchall()39print("writable copy, refunds before:", before)40print("writable copy, the query's answer:", answer)41print("writable copy, refunds after:", after)
python block-a-delete.pyroot: Select
statements in the tree: Select, Delete
guard refuses (write): the query contains a Delete statement; only reads may run
read-only database: Invalid Input Error: Cannot execute statement of type "SELECT" on database "agent" which is attached in read-only mode!
writable copy, refunds before: 1068
writable copy, the query's answer: 931
writable copy, refunds after: 137
The root is a Select, so a root check would pass it. The walk finds Select, Delete, and the guard refuses it. The read-only database refuses too, and its message names the statement it saw as a SELECT.
On the writable copy, the query returned 931, a plausible count of refunds, and analytics.fct_refunds went from 1068 rows to 137. Nothing in the answer says rows were deleted. The read-only database is what makes that impossible; the parse check turns it into a refusal the model can read.
The second question is which tables. The agent's database already leaves out raw and analytics.orders_legacy, the old definition of revenue that chapter 2 warned about. The guard checks the same list in the parser too, because its refusal names the tables a repair needs, and because it must refuse DuckDB's catalog. The allowlist is every table in the agent's database, read by allowed_schema() with each table's columns, and the same mapping is the schema qualify resolves columns against. The allowlist is its own check, because qualify doesn't check that a table exists.
A name without a schema can name a CTE, which is visible only in the query whose WITH defines it, in that query's subqueries, and to the CTEs after it in the same WITH. The guard's refers_to_cte() follows those rules, and refuses any other name without a schema. To test the allowlist, the example tries six ways around it, the last a CTE named like DuckDB's catalog, and then runs every golden question's golden SQL through the guard.
1"""The table allowlist: the marts the agent may read, six ways around it that the guard refuses, and the two golden2queries it can't answer. The last two lines are the cost the prose describes."""3from lib.golden import load_golden4from lib.sql_guard import GuardError, SqlGuard5from lib.warehouse import agent_warehouse, connect67con = agent_warehouse(connect()) # the agent's database: copies of the allowed marts, nothing else8guard = SqlGuard(con) # allowlist: the tables in it, read with their columns9print("allowed tables:", ", ".join(f"{db}.{t}" for db, tables in guard.schema.items() for t in tables))1011ATTEMPTS = {12 "raw table": "SELECT count(*) AS customers FROM raw.customers",13 "legacy mart": "SELECT sum(revenue) AS revenue FROM analytics.orders_legacy",14 "no schema": "SELECT count(*) AS orders FROM fct_orders",15 "file as a table": "SELECT * FROM 'data/golden/questions.toml'",16 "table function": "SELECT * FROM read_csv('data/golden/questions.toml')",17 "CTE named like the catalog": """SELECT * FROM (WITH duckdb_tables AS (SELECT 1 AS x) SELECT x FROM duckdb_tables) AS a,18duckdb_tables""",19}20for label, sql in ATTEMPTS.items():21 try:22 guard.check(sql)23 print(f"{label}: allowed")24 except GuardError as e:25 print(f"{label}: refused ({e.kind}), {str(e).split(';')[0]}") # the message's first clause2627golden = [q for q in load_golden() if q.golden_sql]28outside = []29for q in golden:30 try:31 guard.check(q.golden_sql)32 except GuardError as e:33 outside.append(f"{q.question_id} ({str(e).split(' ')[0]})") # the message starts with the table it refused34print(f"golden queries the allowlist refuses: {len(outside)} of {len(golden)}")35print("refused golden queries:", ", ".join(outside))
python block-raw-tables.pyallowed tables: analytics.dim_customers, analytics.dim_date, analytics.dim_products, analytics.fct_order_lines, analytics.fct_orders, analytics.fct_refunds, analytics.fct_sessions
raw table: refused (table), raw.customers isn't a table you may read
legacy mart: refused (table), analytics.orders_legacy isn't a table you may read
no schema: refused (table), fct_orders isn't a table you may read
file as a table: refused (table), data/golden/questions.toml isn't a table you may read
table function: refused (table), READ_CSV('data/golden/questions.toml') reads data through a function
CTE named like the catalog: refused (table), duckdb_tables is DuckDB's catalog, not a table you may read
golden queries the allowlist refuses: 2 of 34
refused golden queries: gq-034 (raw.marketing_spend), gq-037 (raw.customers)
All six are refused, and three of them aren't table names at all. A file path in the FROM clause parses as a table named after the path, so the allowlist refuses it. read_csv() parses as a function in the table's place, and the guard refuses anything in FROM other than a table, a subquery or a VALUES list, which covers range(), unnest() and query_table() too. The CTE named duckdb_tables is defined inside a subquery, so the duckdb_tables after it names DuckDB's catalog view, and the guard says so: refused (table), duckdb_tables is DuckDB's catalog, not a table you may read. It refuses every catalog name, such as those starting duckdb_ or pg_, and every function that reads the instance, such as current_setting(). Any other function must be on ALLOWED_FUNCTIONS, the functions the shop's analytics need.
The last two lines show what the allowlist costs. It refuses 2 of 34 golden queries: gq-034 (raw.marketing_spend), gq-037 (raw.customers). Marketing spend has no mart, and duplicate sign-ups exist only in raw.customers, because the mart removed them.
Working rule: Accept only a query at the root, walk every node for a statement that writes, and allowlist tables by schema and name, with nothing but tables and subqueries in FROM. Resolve a name without a schema as a CTE only where a CTE of that name is visible, refuse the database's catalog by name, and allow only the functions your analytics need. Check the same list the agent's database holds. When a golden question needs a table off the list, give it a mart or a metric, not a wider list.
此部分尚未翻譯,因此以英文呈現。
Bound the rows and the cost
A query that only reads the allowed tables can still hurt: it can return every row of a fact table into the agent's context, or join three fact tables with no join condition and run for hours. The guard bounds both, and the time. For rows, it adds a LIMIT, and after the run it refuses a result longer than 50,000 characters, since one row can hold a whole column. For cost, the guard asks DuckDB's EXPLAIN for the plan, which plans the query without running it and gives each step an estimated number of rows (DuckDB). For time, it needs its own timer, because DuckDB has no statement timeout, and its documentation says to "Implement strict query timeouts at the application level" (DuckDB).
The costly mistake is chapter 2's fan-out taken to its end: orders, lines and refunds in one FROM clause with no keys. The plan-size limit refuses the keyless join before anything runs. The example sets that plan beside the same sum joined on order_id, checks the limit against the golden queries, and then tries three more things: a LIMIT the agent wrote, a query for too many rows, and the keyless join under the timer alone.
1"""Bound the rows and the cost: EXPLAIN a join that forgot its keys and refuse it, keep the agent's LIMIT, refuse a result2of every order, and stop the join by timer alone. The printed estimates and limits are the figures the prose quotes."""3import sqlglot45from lib.golden import load_golden6from lib.sql_guard import GuardError, SqlGuard, check, explain, run_with_timeout7from lib.warehouse import agent_warehouse, connect89guard = SqlGuard(con := agent_warehouse(connect())) # the agent's database, where the guard runs its checks10NO_KEYS = """SELECT sum(o.net_revenue_eur) AS net_revenue FROM analytics.fct_orders AS o,11analytics.fct_order_lines AS l, analytics.fct_refunds AS r"""12KEYED = """SELECT sum(o.net_revenue_eur) AS net_revenue FROM analytics.fct_orders AS o13JOIN analytics.fct_order_lines AS l USING (order_id)"""14for label, sql in (("no join keys", NO_KEYS), ("joined on order_id", KEYED)):15 cost = explain(con, check(sql, guard.schema).sql)16 print(f"{label}, largest step: {cost.largest_step}\n{label}, estimated rows: {cost.largest_estimate:,}") # one per query17try:18 guard.check(NO_KEYS)19except GuardError as e:20 print(f"guard refuses ({e.kind}): {e}")21costs = {q.question_id: explain(con, check(q.golden_sql, guard.schema).sql)22 for q in load_golden() if q.golden_sql and q.question_id not in ("gq-034", "gq-037")} # both need tables off the list23print("golden queries estimated:", len(costs))24print(f"largest estimate among golden queries: {max(c.largest_estimate for c in costs.values()):,} rows")25print("golden plans with a cross product:", ", ".join(i for i, c in costs.items() if c.cross_products))26print("golden plans with a step that has no estimate:", sum(1 for c in costs.values() if c.unestimated))27print(f"limit on estimated rows: {guard.max_estimated_rows:,}")2829TOP_FIVE = """SELECT product_id, sum(net_amount_eur) AS net_revenue FROM analytics.fct_order_lines30GROUP BY product_id ORDER BY net_revenue DESC LIMIT 5"""31print("sqlglot's .limit(1001):", sqlglot.parse_one(TOP_FIVE, read="duckdb").limit(1001).sql("duckdb").split("ORDER BY")[1].strip()) # the text after ORDER BY32print("guard's limit on the top five:", check(TOP_FIVE, guard.schema).limit)33try:34 guard.run("SELECT order_id, net_revenue_eur FROM analytics.fct_orders")35except GuardError as e:36 print(f"every order ({e.kind}): {e}")3738# The join without keys, through the time limit alone, as if the estimate had let it through. It reads its rows in39# batches, and DuckDB looks for the timer's interrupt between them.40try:41 run_with_timeout(con, check(NO_KEYS, guard.schema).sql, seconds=1.0)42except GuardError as e:43 print(f"no join keys, timer alone ({e.kind}): {e}")44print("connection still answers:", con.execute("SELECT count(*) FROM analytics.fct_orders").fetchall()[0][0])
python explain-before-running.pyno join keys, largest step: CROSS_PRODUCT
no join keys, estimated rows: 604,042,404,768
joined on order_id, largest step: SEQ_SCAN
joined on order_id, estimated rows: 38,071
guard refuses (cost): one step of the plan (CROSS_PRODUCT) is estimated to produce 604,042,404,768 rows, over the limit of 10,000,000; join each table on its key
golden queries estimated: 32
largest estimate among golden queries: 38,071 rows
golden plans with a cross product: gq-031
golden plans with a step that has no estimate: 0
limit on estimated rows: 10,000,000
sqlglot's .limit(1001): net_revenue DESC LIMIT 1001
guard's limit on the top five: 5
every order (rows): the query returns more than 1000 rows; aggregate or filter it
no join keys, timer alone (timeout): the query ran past the 1-second time limit and was stopped
connection still answers: 14856
The keyed join's largest step is a SEQ_SCAN of 38,071 rows. Without keys, the largest step is a CROSS_PRODUCT of 604,042,404,768 rows, and the guard refuses it with a message that tells the model to join on keys. The guard also refuses a plan with any step that has no estimate, such as an UNNEST that makes rows from a list, because it can't price such a plan.
Of the 32 golden queries the allowlist accepts, 0 have a step without an estimate, and the largest estimate among them is 38,071 rows, against a limit of 10,000,000 rows, a rule of thumb for the shop's size.
A cross product isn't always a mistake, either: DuckDB's plan for the golden query of gq-031, units per order, has one, and that query is correct.
The LIMIT needs care. sqlglot's .limit() replaces the LIMIT a query already has, so a top-five query given .limit(1001) ends in net_revenue DESC LIMIT 1001 and stops being a top five. The guard keeps the smaller limit, which is 5, and sets its own limit one row more than the 1,000 it allows, so it can tell a result that was cut from one that fits. A query for every order is refused: the query returns more than 1000 rows; aggregate or filter it. The first 1,000 rows would look like an answer, so a refusal makes the model aggregate.
The time limit is the backstop for estimates that are wrong. Under the timer alone, the join without keys shows whether the backstop holds. run_with_timeout() calls DuckDB's interrupt(), which DuckDB acts on between the batches of rows a query reads, so this query stops: the query ran past the 1-second time limit and was stopped. The connection then answers the next query, and counts 14856 orders.
Working rule: Bound every query four ways: a row limit that keeps a smaller one the agent wrote, a limit on the result's size, a plan-size limit from EXPLAIN checked before the query runs, and one deadline over every step of the guard, planning included. Refuse a result over either limit rather than cut it, and refuse a plan with a step that has no estimate. Set the plan limit from your own known-good queries. Never use EXPLAIN ANALYZE, which runs the query, and rely on the deadline and the result limits, not the estimate.
此部分尚未翻譯,因此以英文呈現。
Enforce the deadline by killing a process
The bounds section stopped a slow query with an interrupt, but DuckDB acts on interrupt() only where it checks for one, and it can compute a constant while planning, before any such check. So the timer can't be relied on, but a killed process stops wherever it is.
The guard plans and runs the agent's query in a worker, a separate process started by Worker in lib/sql_guard.py that opens the agent's database file read-only with the guard's settings, and kills it when the 5-second deadline passes. At most 1,001 rows come back through a pipe, and an oversized result is refused inside the worker.
The query below compares each order ID with a constant sum over 30 million generated numbers, through list functions the guard's list refuses, which the example lets past so the plan is reached. Its plan is taken first with a small constant, 18, on two connections, and then a worker plans the full query under a half-second deadline.
1"""Stop a slow plan: DuckDB computes a constant while it plans, before any row is read, so a timer's interrupt has2nothing to stop; a killed worker stops wherever it is. The prose's claim that the plan never returns and the worker is3stopped rests on the assert after the worker's run."""4import json56from lib.sql_guard import GuardError, SqlGuard, Worker, check7from lib.warehouse import agent_warehouse, connect89shop = connect() # chapter 1's connection: constant folding on10agent = agent_warehouse(shop) # the agent's database: constant folding off11guard = SqlGuard(agent, timeout=0.5)12SLOW_PLAN = """SELECT count(*) AS n FROM analytics.fct_orders13WHERE order_id < list_sum(list_transform(range(30000000), x -> list_sum(range(40))))"""14# Checked as if the function list allowed the list functions, as sqlglot names them (range() is GENERATE_SERIES and15# list_transform() is TRANSFORM), so the plan reaches DuckDB.16LIST_FUNCTIONS = {"list_sum", "transform", "generate_series"}17planned = check(SLOW_PLAN, guard.schema, functions=guard.functions | LIST_FUNCTIONS).sql18# The same query with a small constant, 3 lists summing to 6 each: 18, under every order ID. A plan with no rows to19# read means DuckDB computed the constant while planning.20small = planned.replace("30000000", "3").replace("RANGE(0, 40)", "RANGE(0, 4)")21for label, connection in (("shop's connection, folding on", shop), ("agent's database, folding off", agent)):22 (_, plan), = connection.execute(f"EXPLAIN (FORMAT json) {small}").fetchall()23 steps, nodes = [], json.loads(plan) # the plan's tree, followed down its first child at each step24 while nodes:25 steps.append(nodes[0]["name"])26 nodes = nodes[0]["children"]27 print(f"EXPLAIN with a small constant, {label}: {' > '.join(steps)}")28worker = Worker(shop) # a process with its own connection to the shop's file29try:30 worker.explain(planned, 0.5)31 outcome = "planned"32except GuardError as e:33 outcome = e.kind # timeout, memory or worker_failed: the machine decides which34print("EXPLAIN in a worker with a half-second deadline, shop's connection, plan returned:", outcome == "planned")35print("EXPLAIN in a worker, still running afterwards:", worker.running)36assert outcome != "planned" and not worker.running # the half-second deadline stops the plan, and the kill leaves no worker37try:38 guard.run(SLOW_PLAN)39except GuardError as e:40 print(f"slow plan through the guard ({e.kind}): {e}")
python stop-a-slow-plan.pyEXPLAIN with a small constant, shop's connection, folding on: UNGROUPED_AGGREGATE > EMPTY_RESULT
EXPLAIN with a small constant, agent's database, folding off: UNGROUPED_AGGREGATE > EMPTY_RESULT
EXPLAIN in a worker with a half-second deadline, shop's connection, plan returned: False
EXPLAIN in a worker, still running afterwards: False
slow plan through the guard (function): list_sum() isn't a function the guard allows; use aggregates, window functions, casts, and date, number and text functions
With the small constant, the plan reads UNGROUPED_AGGREGATE > EMPTY_RESULT on the shop's connection, where DuckDB folds constants, and UNGROUPED_AGGREGATE > EMPTY_RESULT on the agent's database, where it doesn't (DuckDB). In both, DuckDB computed the constant while planning and found no order ID under it. For the full constant that is the slow step, so folding off is only a second line of defence, and the guard relies on the kill. In a worker with a half-second deadline, the plan doesn't come back on the shop's connection, and the worker is stopped afterwards.
Working rule: Start the deadline when the agent's SQL arrives, and run EXPLAIN under it as well as the query. Enforce it by killing a separate process with its own read-only connection, not with an interrupt. Check on your own database that a stopped plan and a stopped query really stop.
對防護層重放攻擊
防護層的初稿信任語法剖析檢查與各項上限,並且運行在第 1 章的連線上,而該連線能看到所有 schema。一位審查者以三種方式繞過了剖析器。第一種,以目錄檢視的名稱命名的 CTE,回傳了每個資料表的 CREATE 陳述式,連 raw 也包含在內。第二種,規劃階段折疊的常數,執行時間超過了計時器的限制。第三種,與 unnest(range(200000)) 做的笛卡兒積通過了計畫大小的上限,原因是沒有估計值的步驟被視為規模很小。這些都是防護層對 SQL 的模型與資料庫的理解不一致之處,而實際執行的是資料庫的解讀。兩次安全審查在強化後的防護層上又找到了更多繞過的方法。
將審查中的各次嘗試,加上三個較小的嘗試,對每一層重放,就能看出哪一層守得住。其中有些直接送到代理程式的資料庫,每一個也都會經過防護層,並受 5 秒的限制。
1"""The critics' attempts to get past the guard (lib/guard_attacks.py), replayed on a temporary copy of the marts: what the2database alone allows and what the guard does. The asserts back the claims the prose makes without a number."""3import duckdb45from lib.guard_attacks import ALONE, ATTEMPTS, alone, attempt, macro_then_sum6from lib.sql_guard import SqlGuard, explain7from lib.warehouse import agent_warehouse, connect89agent = agent_warehouse(connect())10guard = SqlGuard(agent) # the 5-second limit: the queries that run here finish far inside it11for label, sql in ATTEMPTS.items():12 if label in ALONE: # straight to the agent's database, with no guard in front13 print(f"{label}, database alone: {alone(agent, label)}")14 print(f"{label}, guard: {attempt(guard.run, sql)[0]}")15print("upper-case names, guard:", attempt(guard.run, "SELECT count(*) AS n FROM ANALYTICS.FCT_ORDERS")[0])1617# A temporary macro named sum, on a cursor of the database alone, then sent straight to the guard's worker.18alone_sum, real = macro_then_sum(agent, guard.worker)19print("temp macro named sum, database alone, the next sum:", alone_sum)20print("temp macro named sum, guard's worker, the next sum is the real one:", real)21assert real # the worker's fresh cursor dropped the macro, so the next sum is real2223# The regex attempt. In-process, a timer's interrupt waits for the end of a batch of rows, and one batch is this much24# text. With repeat() and regexp_matches() (sqlglot's REGEXP_LIKE) allowed, the guard's worker runs it under the25# review's 2-second limit, and stops it by the deadline or the memory cap, whichever the machine reaches first.26REGEX, BATCH = ATTEMPTS["regex on a repeated email"], duckdb.__standard_vector_size__27(text,), = agent.execute(f"SELECT sum(length(email)) * 200000 FROM (SELECT email FROM analytics.dim_customers "28 f"ORDER BY customer_id LIMIT {BATCH})").fetchall()29print(f"regex, characters to match in one batch of {BATCH:,} rows: {text:,}")30wider = SqlGuard(agent, timeout=2.0, functions=guard.functions | {"repeat", "regexp_like"})31print("regex, guard without the function list:", attempt(wider.run, REGEX)[1])32print("regex, worker still running afterwards:", wider.worker_running)33assert not wider.worker_running # the worker is gone, however it was stopped34print("regex, the guard's next query:", attempt(wider.run, "SELECT count(*) AS n FROM analytics.fct_orders")[0])3536# The inequality join ran. Its estimate varies a little between runs, so only its size is printed.37JOIN = ATTEMPTS["join on inequality"]38estimate, joined = explain(agent, JOIN).largest_estimate, guard.run(JOIN).rows[0][0] # the count is the only row and column39print("join on inequality, estimate under the limit:", estimate < guard.max_estimated_rows)40print(f"join on inequality, rows it joined: {joined:,}")41print("join on inequality, rows joined more than 100 times the estimate:", joined > 100 * estimate)42assert estimate < guard.max_estimated_rows and joined > 100 * estimate # under the limit, yet far more rows than estimated
python replay-guard-bypasses.pyCTE named duckdb_tables, database alone: 7 rows, one per table, all in analytics: True
CTE named duckdb_tables, guard: refused (table), duckdb_tables is DuckDB's catalog, not a table you may read
CTE named pg_settings, database alone: 170 rows of settings
CTE named pg_settings, guard: refused (table), pg_settings is DuckDB's catalog, not a table you may read
slow plan, guard: refused (function), list_sum() isn't a function the guard allows
unnest in FROM, guard: refused (table), UNNEST(RANGE(0, 200000)) AS t(x) makes rows from a function, not a table
unnest in a subquery, guard: refused (function), unnest() isn't a function the guard allows
every email in one row, database alone: 1 row of 106,670 characters
every email in one row, guard: refused (size), the result is 106,670 characters long, over the limit of 50,000
a setting read by a function, database alone: 1 row, a folder's path
a setting read by a function, guard: refused (table), current_setting() reads the database's settings or catalog
a table named with its database, database alone: 14,856 orders
a table named with its database, guard: refused (table), agent.analytics.fct_orders names a database
regex on a repeated email, guard: refused (function), regexp_matches() isn't a function the guard allows
database name and version, database alone: agent, v2.0.0
database name and version, guard: refused (table), current_database() reads the database's settings or catalog
600 nested subqueries, guard: refused (parse), the SQL is nested too deeply to check (the limit is 200 levels)
SIMILAR TO on an email, guard: refused (function), SIMILAR TO isn't an operator the guard allows
temp macro named sum, guard: refused (statement), only queries may run, and this is a Create statement
join on inequality, guard: ran
upper-case names, guard: ran
temp macro named sum, database alone, the next sum: 42
temp macro named sum, guard's worker, the next sum is the real one: True
regex, characters to match in one batch of 2,048 rows: 10,649,600,000
regex, guard without the function list: stopped, no answer
regex, worker still running afterwards: False
regex, the guard's next query: ran
join on inequality, estimate under the limit: True
join on inequality, rows it joined: 45,147,384
join on inequality, rows joined more than 100 times the estimate: True
除了 join 之外,每個嘗試都被拒絕。名為 duckdb_tables 的 CTE 若單獨送到代理程式的資料庫,會回傳 7 rows, one per table, all in analytics: True:目錄無法讓代理程式看到它不得讀取的資料表,因為那裡根本沒有這張表。慢速計畫與 unnest() 因為是不在清單上的函式而被拒絕,如 refused (function), list_sum() isn't a function the guard allows 所示。單一資料列中包含每位客戶的姓名與電子郵件,也就是 1 row of 106,670 characters,則因過大而被拒絕。
第二次審查的第一個嘗試,是將一個樣式與每位客戶重複 200,000 次的電子郵件做比對。它通過了剖析器與計畫上限,並且超過了中斷的時限;DuckDB 只在各批資料列之間檢查中斷,而這裡一批包含 10,649,600,000 個字元。函式清單現在會拒絕它:refused (function), regexp_matches() isn't a function the guard allows。若在允許 repeat() 與 regexp_matches() 的情況下,以審查的 2 秒限制執行,結果是 stopped, no answer,由期限或工作程序的記憶體上限先到者終止。工作程序也一併被停止,而防護層的下一個查詢 ran。
current_database() 與 version() 單獨使用時會回傳 agent, v2.0.0,它們曾經通過;防護層現在會檢查每一個函式。六百層巢狀子查詢曾使 sqlglot 的剖析器當機,現在會被拒絕:refused (parse), the SQL is nested too deeply to check (the limit is 200 levels)。
最後一次審查又加入兩項。防護層現在拒絕 SIMILAR TO 這個以運算子形式出現的正規表達式:refused (function), SIMILAR TO isn't an operator the guard allows。名為 sum 的暫存巨集只有資料庫單獨運作時才會接受,它會使下一次的加總變成 42。剖析器會拒絕這個巨集;即使它通過了,工作程序每個查詢都以全新的游標啟動,也會將它丟棄,所以下一次加總得到的是真正的結果。
最後一個嘗試,是將訂單與每位未下這些訂單的客戶做 join,而它確實執行了,這符合規則的允許範圍。它的估計值低於上限,實際 join 了 45,147,384 列,超過估計值的 100 倍。因此,計畫上限只能攔下它所針對的那些錯誤。其餘的情況由終止機制、結果上限與記憶體上限來約束,而正式環境一節列出了仍然存在的風險。
**實用原則:**依靠資料庫的限制,並將剖析器視為縱深防禦。把每一個被人發現的攻擊都保留為測試:分別單獨對代理程式的資料庫以及透過防護層重放,並確認每一個都由你預期的那一層拒絕。當攻擊繞過剖析器時,應先問資料庫為什麼讓它能碰到任何東西,然後再修正剖析器。
此部分尚未翻譯,因此以英文呈現。
Repair once from the error
Repair means giving the model the guard's refusal or the warehouse's error, once, and running what it writes next. The research says when repair helps. MAC-SQL's Refiner, which corrects SQL from the error message, added 4.6 points on BIRD's dev set with GPT-4 (Wang et al., Table 3). Repair without an outside signal is weaker: models "struggle to self-correct their responses without external feedback" (Huang et al.), and in DIN-SQL a prompt that told GPT-4 its SQL was buggy lowered its Spider score from 73.3 to 70.0 (Pourreza and Rafiei, Table 5). So the guard repairs only on an outside signal, its own refusal or DuckDB's error, and the repair prompt asks for a correction only when the message shows how to make one.
The repair prompt is GUARD_REPAIR in lib/prompts.py: the model's own reply, the message, and the instruction to reply with corrected SQL or a line that starts cannot answer:. answer_with_repair() in lib/sql_guard.py runs it at most once per question. The recording guarded-sql-runs holds only the repair calls, made with the book's pinned model, Qwen3-4B-Instruct-2507 in a 4-bit file, chosen as chapter 1 explains. Each first reply is replayed from chapter 1's schema-only recording or chapter 4's layered one, and the model was called only where the guard refused that reply or DuckDB failed: 6 times for schema-only SQL and 1 for layered SQL on the dev split. The same run repaired the test split's replies, which the long-tail section uses. Scoring the repairs on their own shows whether they help: the example scores both kinds of first reply on the dev split's 17 answer questions, and prints two repairs in full: the layered run for gq-017 from the parse section, and the schema-only run for gq-001, net revenue in fiscal year 2026.
1"""Repair once from the guard's message: how often one repair turns a refusal into an answer on the dev split, and two2repairs in full. The counts are the figures the prose quotes; the repaired SQL is printed for the reader to check."""3from collections import Counter45from lib.context import LAYERS, build_context6from lib.golden import golden_result, grade, load_golden7from lib.model import RecordedModels8from lib.prompts import layered_sql_messages, schema_only_messages9from lib.sql_guard import SqlGuard, answer_with_repair10from lib.warehouse import agent_warehouse, connect, describe_schema1112con = connect()13guard, schema = SqlGuard(agent_warehouse(con)), describe_schema(con)14# First replies from chapters 1 and 4's recordings; each repair reply from this chapter's.15model = RecordedModels("schema-only-sql", "context-layers", "guarded-sql-runs")16MESSAGES = {"schema-only": lambda q: schema_only_messages(schema, q.question),17 "layered": lambda q: layered_sql_messages(build_context(con, q.question, LAYERS).text, q.question)}18dev = [q for q in load_golden() if q.split == "dev" and q.kind == "answer"]19shown = {}2021for context, messages in MESSAGES.items():22 counts = Counter()23 for q in dev:24 run = answer_with_repair(guard, model, messages(q), q.question, role=q.role, repairs=1)25 outcome = grade(q, run.answer, golden_result(con, q))26 when = "after repair" if run.repaired else "first time"27 counts[f"ran {when}"] += run.attempts[-1].outcome == "ran"28 counts[f"correct {when}"] += outcome == "correct"29 counts[f"wrong {when}"] += outcome == "wrong"30 counts["refused or failed first"] += run.repaired31 counts["declined after repair"] += run.attempts[-1].outcome == "declined"32 shown[context, q.question_id] = run33 print(f"{context}, questions: {len(dev)}")34 for key in ("ran first time", "correct first time", "wrong first time", "refused or failed first",35 "ran after repair", "correct after repair", "wrong after repair", "declined after repair"):36 print(f"{context}, {key}: {counts[key]}")3738for context, question_id in (("layered", "gq-017"), ("schema-only", "gq-001")):39 run = shown[context, question_id]40 print(f"{question_id} {context}, refused: {run.attempts[0].message.split('. The tables')[0]}") # the refusal, without its list of tables41 print(run.attempts[1].sql) # attempt 0 is the first reply, attempt 1 the repair42 rows = len(run.answer.rows)43 print(f"{question_id} {context}, after repair: {run.attempts[1].outcome} and returned {rows} row{'s' * (rows != 1)}")
python repair-from-error.pyschema-only, questions: 17
schema-only, ran first time: 11
schema-only, correct first time: 2
schema-only, wrong first time: 9
schema-only, refused or failed first: 6
schema-only, ran after repair: 5
schema-only, correct after repair: 0
schema-only, wrong after repair: 5
schema-only, declined after repair: 0
layered, questions: 17
layered, ran first time: 16
layered, correct first time: 14
layered, wrong first time: 2
layered, refused or failed first: 1
layered, ran after repair: 1
layered, correct after repair: 0
layered, wrong after repair: 1
layered, declined after repair: 0
gq-017 layered, refused: Unknown column: country
SELECT o.country, round(sum(o.net_revenue_eur), 2) AS net_revenue
FROM analytics.fct_orders AS o
JOIN analytics.dim_date AS d ON d.date_day = o.order_date_local
WHERE o.status <> 'cancelled'
AND d.calendar_year = 2025
GROUP BY o.country
ORDER BY net_revenue DESC
gq-017 layered, after repair: ran and returned 10 rows
gq-001 schema-only, refused: Column 'fiscal_year' could not be resolved. Line: 3, Col: 17
SELECT SUM(net_revenue_eur) AS net_revenue
FROM analytics.fct_orders
WHERE EXTRACT(YEAR FROM placed_at) = 2026
gq-001 schema-only, after repair: ran and returned 1 row
Repair worked as a mechanism. For gq-017, the guard's message named the unknown column and listed the orders table's columns, and the model moved country from the calendar to the orders table. The repaired query ran and returned 10 rows. For schema-only SQL, 5 of the 6 refused or failed queries ran after one repair.
It didn't make answers right. Schema-only SQL got 0 repaired answers correct, and layered SQL 0. The repaired gq-017 returns every country, and the golden question is asked by EMEA's regional lead, who should see EMEA's countries only: a rule that the lead's own database enforces, and that no SQL error reveals.
For gq-001, the guard refused fiscal_year, which the model had looked for on the orders table, and the model's fix filtered on the calendar year of the UTC timestamp instead. That query ran and returned 1 row, but it answers a different question. Meanwhile layered SQL ran first time on 16 questions, with 2 of them wrong, and repair never sees those, because nothing failed. On the shop's dev split, repair turned visible errors into answers that run and are wrong, harder to catch than an error.
Working rule: Repair at most once, and only from an outside signal: the guard's refusal or the warehouse's error, passed to the model with its own SQL and a prompt that doesn't assume the SQL is wrong. Let the model decline in the repair. Score repaired answers on their own, apart from first-time answers, and if your golden questions show they are rarely right, decline on a refusal instead of repairing.
此部分尚未翻譯,因此以英文呈現。
Transpile to the warehouse's dialect
The shop's warehouse is DuckDB, and most readers' isn't. The same tree can be written in another dialect, as chapter 5 did for the semantic layer's SQL. What is transpiled is the guard's SQL, with every identifier quoted. sqlglot's transpiler translates what it knows and, by default, warns and makes a "best-effort translation" of what it can't. With unsupported_level=ErrorLevel.RAISE, which transpile() in lib/sql_guard.py uses, it raises instead (sqlglot). Neither tells you that the result means the same thing on the target.
A long-tail query, net revenue by weekday and month in 2025, shows the quoting problem: the example writes it for Snowflake twice, with the guard's quotes and then without them. A query with DuckDB's histogram() aggregate, which is on the guard's function list, shows what the transpiler does with a function it doesn't know.
1"""Transpile the guard's SQL to Snowflake: the quoted table name Snowflake reads, a weekday function that answers differently,2and a histogram that passes through unchanged. The printed lines are the output the prose quotes."""3from lib.sql_guard import SqlGuard, transpile4from lib.warehouse import agent_warehouse, connect56con = connect()7guard = SqlGuard(agent_warehouse(con))8WEEKDAYS = """SELECT dayname(order_date_local) AS weekday, strftime(order_date_local, '%Y-%m') AS month,9 round(sum(net_revenue_eur), 2) AS net_revenue10FROM analytics.fct_orders11WHERE status <> 'cancelled' AND year(order_date_local) = 202512GROUP BY ALL ORDER BY month, weekday"""13checked = guard.check(WEEKDAYS)14print("kept quoted, Snowflake reads:", transpile(checked.sql, "snowflake", quoted=True).sql.split(" FROM ")[1].split(" WHERE")[0]) # the FROM clause only1516snowflake = transpile(checked.sql, "snowflake", pretty=True)17print(snowflake.sql)18print("functions passed through unchanged:", ", ".join(snowflake.passed_through) or "none")19print("DuckDB's first weekday:", guard.run(WEEKDAYS).rows[0][0])2021MIX = """SELECT region, histogram(channel) AS orders_by_channel FROM analytics.fct_orders22WHERE year(order_date_local) = 2025 GROUP BY region"""23mix = transpile(guard.check(MIX).sql, "snowflake")24print("channel mix in Snowflake:", mix.sql.split(" FROM")[0]) # the SELECT list, which holds the function25print("functions passed through in the channel mix:", ", ".join(mix.passed_through) or "none")
python transpile-to-snowflake.pykept quoted, Snowflake reads: "analytics"."fct_orders" AS "fct_orders"
SELECT
DAYNAME(fct_orders.order_date_local) AS weekday,
TO_CHAR(fct_orders.order_date_local, 'yyyy-mm') AS month,
ROUND(SUM(fct_orders.net_revenue_eur), 2) AS net_revenue
FROM analytics.fct_orders AS fct_orders
WHERE
fct_orders.status <> 'cancelled' AND YEAR(fct_orders.order_date_local) = 2025
GROUP BY ALL
ORDER BY
month,
weekday
LIMIT 1001
functions passed through unchanged: none
DuckDB's first weekday: Friday
channel mix in Snowflake: SELECT fct_orders.region AS region, HISTOGRAM(fct_orders.channel) AS orders_by_channel
functions passed through in the channel mix: histogram
Kept quoted, the table becomes "analytics"."fct_orders" AS "fct_orders". In Snowflake that names a different table, because Snowflake folds unquoted names to upper case and treats quoted names as case-sensitive (Snowflake). So transpile() drops the quotes unless asked to keep them, and the second version reads analytics.fct_orders. sqlglot rewrote strftime() as TO_CHAR() with Snowflake's format, and it knew every function in the query, so none passed through.
That clean result still changes the answer. dayname() came out as DAYNAME(), and the two names don't mean the same thing. DuckDB's dayname() gives the English name of the weekday, which here is Friday, while Snowflake's gives a three-letter day-of-week name. The same rows get different labels, so a golden result that says Friday fails on Snowflake. The channel mix is the other failure: histogram is a function sqlglot doesn't know, so it was written out unchanged, SELECT fct_orders.region AS region, HISTOGRAM(fct_orders.channel) AS orders_by_channel, with no error. transpile() lists such functions, here histogram, so you can refuse them or check them by hand.
Working rule: Let the agent write the dialect your golden questions are checked in, guard that SQL, and transpile the guard's output, not the agent's text, with errors raised for what the transpiler knows it can't translate. Drop the guard's quotes for warehouses that fold unquoted names to upper case, refuse functions the transpiler passed through, and run the golden questions on the target warehouse before you trust a transpiled answer, because a function with the same name can return different values.
何時應優先建立新指標而非使用 SQL
資料庫與各道防護讓代理程式的 SQL 可以安全執行:它只能使用其角色可用的檢視(檢視一節),執行時有範圍限制,並且只修復一次。但這些機制都不能保證結果正確。指標則是依其建構方式而正確,因為它由人定義一次。因此,受防護的 SQL 適用於只問一次的問題;反覆出現的問題,或其數字會放進報告的問題,應該成為指標。要區分兩者,可以在黃金問題的長尾上為受防護的路徑評分,並逐一檢視每個錯誤答案。
商店的長尾是十道 examples/data/semantic/ 中沒有任何指標或維度涵蓋的問答題,例如包含帳篷的訂單、工作階段轉換率與折扣。這十題中,有 2 題在 dev 切分,8 題在保留的 test 切分。兩題太少,無法評分,所以長尾的數字取自 test。防護與修復提示是依 dev 固定的,而且在我們讀過一次 test 之後就沒有再更動;第 4 章已經在那裡為最初的回覆評分。我們先為該路徑評分,再在其 YAML 的副本中提升一道問題。
1"""Guarded SQL on the long tail, the answer questions no metric in the semantic layer covers; then one promoted."""2import shutil3import tempfile4from collections import Counter5from pathlib import Path67from lib.answers import format_value8from lib.context import LAYERS, build_context9from lib.golden import golden_result, grade, load_golden, results_match10from lib.model import RecordedModels11from lib.prompts import layered_sql_messages12from lib.semantic import SEMANTIC_DIR, MetricRequest, load13from lib.sql_guard import SqlGuard, answer_with_repair14from lib.warehouse import agent_warehouse, connect1516LONG_TAIL = {"gq-007": "a filter on what an order contains", "gq-016": "customers with an order",17 "gq-031": "units per order", "gq-032": "gross margin", "gq-033": "session conversion",18 "gq-034": "marketing spend", "gq-036": "one order's refunds", "gq-037": "duplicate sign-ups",19 "gq-038": "a weekday dimension", "gq-041": "discount"}20con, golden = connect(), {q.question_id: q for q in load_golden()}21guard, model = SqlGuard(agent_warehouse(con)), RecordedModels("context-layers", "context-layers-test", "guarded-sql-runs")22counts = Counter()23for question_id, needs in LONG_TAIL.items():24 q = golden[question_id]25 run = answer_with_repair(guard, model, layered_sql_messages(build_context(con, q.question, LAYERS).text,26 q.question), q.question, role=q.role)27 outcome = grade(q, run.answer, gold := golden_result(con, q))28 counts[q.split, outcome] += 129 print(f"{question_id} ({q.split}, {needs}): {outcome}, after {' then '.join(a.outcome for a in run.attempts)}")30 if outcome == "wrong":31 row = ", ".join(map(format_value, min(run.answer.rows))) if run.answer.rows else "no rows"32 print(f"{question_id} first row: {row} (golden: {', '.join(map(format_value, min(gold)))})")33print(*(f"long tail questions on {s}: {sum(golden[i].split == s for i in LONG_TAIL)}" for s in ("dev", "test")), sep="\n")34for split, outcome in [(s, o) for s in ("dev", "test") for o in ("correct", "wrong", "declined")]:35 print(f"long tail {split} {outcome}: {counts[split, outcome]} of {sum(n for (s, _), n in counts.items() if s == split)}")3637with tempfile.TemporaryDirectory() as folder: # promote gq-038: one dimension added to a copy of the YAML38 shutil.copytree(SEMANTIC_DIR, copy := Path(folder) / "semantic")39 with open(copy / "orders.yml", "a") as orders:40 orders.write(" - name: weekday\n type: categorical\n expr: dayname(order_date_local)\n")41 layer = load(copy)42answer = layer.query(con, MetricRequest(("net_revenue", "orders"), ("order__weekday",), {"metric_time__year": 2025}))43print("gq-038 as a metric request matches golden:", results_match(answer.rows, golden_result(con, golden["gq-038"])))44assert results_match(answer.rows, golden_result(con, golden["gq-038"])) # the promotion gives the golden answer
python long-tail-score.pygq-007 (dev, a filter on what an order contains): wrong, after ran
gq-007 first row: NULL (golden: 377,497.40)
gq-016 (dev, customers with an order): correct, after ran
gq-031 (test, units per order): correct, after ran
gq-032 (test, gross margin): wrong, after ran
gq-032 first row: 773,723.34 (golden: 847,864.71)
gq-033 (test, session conversion): wrong, after ran
gq-033 first row: (none), 13.46 (golden: (none), 0.13)
gq-034 (test, marketing spend): declined, after table then declined
gq-036 (test, one order's refunds): correct, after ran
gq-037 (test, duplicate sign-ups): wrong, after ran
gq-037 first row: no rows (golden: 54)
gq-038 (test, a weekday dimension): wrong, after ran
gq-038 first row: Friday, 221,172.11, 822 (golden: Friday, 221,172.11, 869)
gq-041 (test, discount): correct, after ran
long tail questions on dev: 2
long tail questions on test: 8
long tail dev correct: 1 of 2
long tail dev wrong: 1 of 2
long tail dev declined: 0 of 2
long tail test correct: 3 of 8
long tail test wrong: 4 of 8
long tail test declined: 1 of 8
gq-038 as a metric request matches golden: True
在 dev 上,受防護的 SQL 答對 1 of 2 題;在 test 上答對 3 of 8 題,另有 1 of 8 題被拒答。被拒答的是 gq-034,也就是行銷支出報酬率:防護拒絕了 raw.marketing_spend,模型在修復時回答沒有任何允許的資料表存有支出,這是正確的。解決方式是建立一個 mart。
每個錯誤答案都順利通過了每一道防護。在 gq-007 中,模型犯了第 4 章發現的兩個錯誤:它寫的類別是 tents,而商店儲存的是 Tents,所以加總結果為 NULL (golden: 377,497.40);而且它只加總帳篷的明細行,但問題問的是包含帳篷之訂單的全部營收。在 gq-032,也就是毛利中,模型用毛營收減去淨營收,得到的是稅額,又以 order_id 連接訂單明細,使每筆訂單依明細行數重複計算,這就是第 2 章的扇出(fan-out):773,723.34 (golden: 847,864.71)。計畫大小限制抓不到以鍵產生的扇出。在 gq-033 中,它以百分比表示轉換率,而商店以小數表示:(none), 13.46 (golden: (none), 0.13)。在 gq-038,也就是依星期幾統計淨營收與訂單數時,營收正確,訂單數則不然:Friday, 221,172.11, 822 (golden: Friday, 221,172.11, 869)。模型在計數時排除了已取消的訂單,而商店的 orders 指標則保留它們。
gq-038 是應該提升的那一題。商店已定義它的兩個指標,只缺少星期幾維度:在 orders 語意模型上加三行 YAML,運算式為 dayname(order_date_local)。有了它,這個問題就成為依 2025 年的 order__weekday 分組的指標請求,結果與黃金結果相符。這不算保留集的結果:我們是根據 gq-038 在 test 上的失敗而選出它的。本書的語意層保持不變,所以 gq-038 仍留在 test;在你自己的語意層中,應把它移到 dev。gq-034 與 gq-037 需要先建立 mart,因為代理程式理當不能讀取 raw。像 gq-036 這類查詢,也就是單一訂單的退款及其原因,正是受防護的 SQL 能夠妥善回答的問題。
實用原則: 只有在沒有任何指標涵蓋某個問題時,才把它交給受防護的 SQL,並在該路徑自己的黃金問題長尾上為它評分。當問題反覆出現、答案會放進報告,或受防護的 SQL 在定義上答錯時(例如什麼算是一筆訂單),就把它提升到語意層。單次查詢則留在受防護的路徑上。當問題需要不在允許清單上的資料表時,應建立 mart 及其指標,而不是放寬清單。
正式環境中會出什麼問題
通過測試的防護機制,在實際使用時仍可能出錯,因為資料倉儲、角色與函式清單會不斷變動;以下每一種失敗,都附有能讓人發現它的徵兆。
**錯誤的答案會通過所有防護。**資料庫與防護機制限制的是查詢讀取的內容與成本,而不是查詢結果的數字代表什麼意義。以執行結果為依據的修正,無法修復能執行卻是錯誤的 SQL,因此 Hong et al. 在修復之前加入錯誤偵測(Hong et al.),Chaturvedi et al. 則以錯誤分類法引導修正(Chaturvedi et al.)。唯有以黃金問題替受防護的路徑評分,並檢視結果為空、為 NULL,或是在預期小數時卻得到百分比的答案,才會察覺這類錯誤。
**代理程式的連線位於檢視之上。**只有在代理程式的資料倉儲使用者無法讀取檢視底下的資料表時,這道界線才成立。每當資料倉儲的角色變動,就要檢查它的授權。
**角色的檢視有誤或已過期。**檢視會回傳其定義所寫的內容,因此缺少列篩選或遮罩,會讓所有路徑同時洩漏資料。在 DuckDB 中,每個角色的資料庫是建置時產生的副本,因此資料集市重建時,它也必須重建。請由同一份政策產生檢視,用剖析器會接受的查詢測試每個角色,並逐一列出每個資料表的名稱,這樣新的資料集市在有人加入之前不會被納入。
**函式與目錄清單是判斷的結果。**分析師需要的函式可能不在清單上,清單上的函式仍可能很慢,DuckDB 新增的目錄函式也可能超出清單的範圍。請記錄每一次 function 的拒絕,並在每次 DuckDB 發行新版時檢視這兩份清單。
**防護機制拒絕了正確的 SQL。**防護機制會拒絕 sqlglot 無法建模的 DuckDB 語法。請記錄每次拒絕及其類型,並如允許清單那一節所做的,在 CI 中讓每個黃金查詢都通過防護機制。
估計值不等於成本。重播顯示有一個連結(join)產生的資料量超過其估計值的 100 倍。請把每個查詢的估計值與其執行時間一併記錄。
**唯讀的資料庫仍會保留狀態。**唯讀的 DuckDB 連線接受暫存資料表、檢視、巨集與變數,因此通過剖析器的陳述式可能改變之後的答案,如重播中的巨集所示。工作程序會為每個查詢提供全新的游標;在任何共用的連線池中也應如此。
**DuckDB 的記憶體上限並未涵蓋所有的值。**它「只適用於緩衝區管理器」(DuckDB),因此運算式所建構的字串可以突破它的 1 GB。資料庫開啟後,工作程序會把自己的行程限制在比其大小多 1.5 GB 的範圍內。工作程序若記憶體不足、中止或根本無法啟動,會引發 GuardError(memory 或 worker_failed)而不回傳任何資料列:防護機制採失敗即拒絕(fail closed)的方式運作。剖析檢查在防護機制自己的行程中執行,只受 20,000 字元的上限與深度上限約束。請讓每次代理程式執行都有自己的防護機制,因為一個工作程序一次只回答一個查詢。
**資料倉儲本身的上限太遠。**Snowflake 的 STATEMENT_TIMEOUT_IN_SECONDS 預設為「172800(2 天)」(Snowflake)。請在防護機制中設定代理程式的時間上限,並在代理程式的資料倉儲角色上也設定較低的上限。
**修復會掩蓋錯誤。**在商店的開發集上,每個經修復後能執行的查詢都是錯的。請把各次嘗試與每個答案一併記錄,如 answer_with_repair() 在其 GuardedRun 中所做的,並在每一次評分中計入經修復的答案。
**防護機制的訊息會描述資料庫。**拒絕訊息會列出允許的資料表及其欄位。請以角色自己的資料庫建立防護機制,讓它只列出該角色可以看到的內容。
**實用原則:**把角色的唯讀檢視資料庫視為控制手段,把防護機制視為將錯誤轉成拒絕並限制成本的一層。檢查代理程式的授權並測試每個角色的檢視,在 CI 中讓每個黃金查詢都通過防護機制,在防護機制與資料倉儲上都設定時間上限,記錄每一次拒絕、估計與修復及其答案,並在每次變更後,用該路徑自己的長尾黃金問題為它評分。
本章做出的決定
- 受防護的 SQL 是後備方案,而非預設做法,用於沒有任何指標涵蓋的問題,並以第 4 章的四層脈絡來撰寫。
- 資料庫就是安全邊界。 代理程式的 SQL 在
agent_warehouse()上以唯讀方式執行:那是一個存放七個允許使用之資料集市的檔案;若指定了角色的政策,則是該角色所能看到的這些資料集市的檢視表。在具備角色機制的資料倉儲中,這就是一個對安全檢視表只有SELECT權限的唯讀角色,或是列存取與遮罩政策。 - 剖析器、計時器與成本檢查屬於教學層與縱深防禦,並不是邊界。
- 實際執行的是防護機制產出的 SQL,由 sqlglot 剖析並重新產生,每個欄位都加上限定名稱,並加上列數上限:只有一個陳述式、根節點是查詢,而且整棵語法樹中沒有任何寫入型陳述式。
- 允許清單就是資料庫的資料表,逐一列名,CTE 名稱依作用域解析,拒絕 DuckDB 的目錄,只允許列出的函式,拒絕
SIMILAR TO與GLOB,限制巢狀層數,且FROM中只能出現資料表與子查詢。有兩個黃金問題無法在此範圍內回答。 - 每個查詢有四項限制: 最多 1,000 列與 50,000 個字元,超過時拒絕而不截斷;計畫中最大的步驟經
EXPLAIN估計不超過 1,000 萬列,這是經驗法則,且不得有任何步驟未被估計;以及涵蓋檢查、計畫與執行的單一 5 秒期限,透過終止工作程序來強制執行,該程序以全新的游標執行每個查詢。 - 只修復一次,依據防護機制的拒絕或 DuckDB 的錯誤,提示詞允許模型拒絕作答。修復後的答案另外計分。
- 轉譯防護機制的輸出,不含其引號,遇到 sqlglot 無法翻譯的內容就拋出例外,並列出直接通過的函式。
- 在下列情況將問題升級為指標: 問題反覆出現、其數字會被對外報告,或受防護的 SQL 弄錯了其定義。
本章,於本版次中
版次 2026.11.15範例於 2026年11 月 19 通過測試本章 11 個範例 個範例中有 11 個通過
譯自英文書,版次 2026.11.15。