エージェントにガード付きでSQLを書かせる

読了まで約87 分分、12 節 節の 11 例 例。

第2026.11.15版 2026年11月19日時点で例が通過 78 例中 78 例が合格 変更点

AIによる翻訳。英語版は人による検証済みである。 当社の書籍の作り方

英語版の書籍(版2026.11.15)から翻訳した。 原文を読む

この章を終えると、どの指標にも当てはまらない質問を、次のような条件でエージェントにSQLを書かせて処理できるようになる。そのSQLは、ロールが読める行と列だけを保持するデータベース上で読み取り専用で実行される。また、構文解析され、テーブルの許可リストと照合され、行数・サイズ・コスト・時間に上限が設けられ、エラーからは一度だけ修復され、使用するウェアハウスの方言に変換される。さらに、ゴールデンクエスチョンを見れば、そのような質問をいつ指標に昇格させるべきかも判断できるようになる。

第6章では、3つの地域でアウトドア用品とキャンプ用品を販売する店の質問を、まず管理された指標に振り分けた。その意味層には、月次の財務に関する質問をカバーする10個の指標がある。どの指標にも当てはまらない質問がロングテールである。たとえば、テントを含む注文からの売上がいくらか、セッションのうちコンバージョンした割合はどれだけか、といった質問である。第6章のエージェントはこれらを断るが、それは誠実な対応である一方、もどかしい。この店のゴールデンクエスチョンのうち10問がロングテールにあり、昇格を扱う節に一覧がある。

ロングテールに対しては、エージェントがSQLを書かなければならない。第4章ではすでにエージェントにSQLを書かせ、書き込みのできない接続で、書かれたとおりに実行した。この章ではそのSQLにガードを設ける。DuckDB自身のドキュメントは、「DuckDBのSQLは、BashやPythonのコードと同じように扱うこと。適切なサンドボックス化なしに、信頼できない出所のSQLを実行してはならない」と述べている(DuckDB)。モデルが書いたSQLは、誰が質問を入力したかにかかわらず、この意味で信頼できない出所のSQLである。

そのため、セキュリティ上の境界はデータベースである。エージェントは読み取り専用で接続し、接続先のデータベースには、ロールが参照できる行と列を制限するビューだけを置く。lib/sql_guard.pyにある構文解析器の検査、タイマー、コストの検査は、学習用の層であり、多層防御の一部である。これらは誤りを検出し、モデルが修復の手がかりにできる拒否を返し、コストと時間に上限を設けるが、ロールにデータを読ませないことまでは保証しない。構文解析器の草稿は、再生を扱う節で示すように回避されたことがあり、どの構文解析器でも回避される可能性がある。

この部分はまだ翻訳されていないため、英語で表示している。

Give the agent a database with only what it may read

最終変更:版2026.11.12

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.

agent-warehouse.py Python 3.12.15 · duckdb 2.0.0 合格
"""The agent's own database holds copies of the seven allowed marts, opened read-only with files, extensions and settingslocked. The attempts show what those locks refuse, and the catalog lines show what they can't hide."""import duckdbfrom lib.warehouse import agent_warehouse, connectshop = connect()agent = agent_warehouse(shop)                 # a second file: the seven allowed marts, copied, and nothing else(shop_tables,), = shop.execute("SELECT count(*) FROM duckdb_tables()").fetchall()print("tables in the shop's warehouse:", shop_tables)tables = agent.execute("SELECT schema_name || '.' || table_name FROM duckdb_tables() ORDER BY 1").fetchall()print("tables in the agent's database:", len(tables))print("the agent's tables:", ", ".join(name for (name,) in tables))(source,), = shop.execute("SELECT path FROM duckdb_databases() WHERE database_name = 'shop'").fetchall()ATTEMPTS = {                                  # sent straight to the agent's database, with no guard in front    "raw table": "SELECT count(*) FROM raw.customers",    "legacy mart": "SELECT sum(revenue) FROM analytics.orders_legacy",    "attach the shop's file": f"ATTACH '{source}' AS shop (READ_ONLY)",    "read a file": "SELECT * FROM read_csv('data/golden/questions.toml')",    "load an extension": "LOAD httpfs",    "turn file access back on": "SET enable_external_access = true",}for label, sql in ATTEMPTS.items():    try:        agent.execute(sql).fetchall()        print(f"{label}: allowed")    except duckdb.Error as e:        kind, message = str(e).splitlines()[0].split(": ", 1)   # the error's kind, then its message        print(f"{label}: {kind}, {message.split(' - ')[-1]}")   # the file's path is left out of the output# What no setting hides: DuckDB's own catalog, which every database has.(listed,), = agent.execute("SELECT count(*) FROM information_schema.tables WHERE table_schema = 'analytics'").fetchall()print("catalog, tables it lists:", listed)(settings,), = agent.execute("SELECT count(*) FROM duckdb_settings()").fetchall()print("catalog, settings it lists:", settings)(paths,), = agent.execute("SELECT count(*) FROM duckdb_databases() WHERE path IS NOT NULL").fetchall()print("catalog, database files it names:", paths)
出力python agent-warehouse.py
tables 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.

各ロールにビューのデータベースを与える

最終変更:版2026.11.15

エージェントのデータベースはテーブルごと除外するが、ショップの担当者全員が残りのテーブルの同じ行と列を見るわけではない。エージェントに質問するロールは5つあり、examples/data/shop/roles.tomlに記述されている(第9章で説明する)。EMEAの地域責任者はEMEAの行だけを見る。財務部門は氏名とメールアドレスをマスクした顧客を見る。サポート部門は売上を除いた注文を見る。あるロールのテーブルのビューは、そのロールが見てよい行を返し、マスクした列には定数を入れ、拒否した列は除く。ロールが何を読めるかは、パーサーではなくビューが決めるべきである。

実際のウェアハウスはこれを権限付与で実現する。PostgreSQLのドキュメントは、列を隠すビューを、その下のテーブルを読めないロールに付与する例を示し、security_barrierフラグを設定しないかぎり、そのようなビューは行を確実には隠せないと警告している(PostgreSQL)。Snowflakeのセキュアビューは、機密データへのアクセスを制限するために存在する(Snowflake)。その行アクセスポリシーとマスキングポリシーは同じ規則をテーブル自体に結び付けるので、その列が現れる場所ではどこでもマスクが適用される(Snowflake)。したがって本番環境では、エージェントのウェアハウスユーザーには、そのロール用のセキュアビューに対するSELECTだけを持つ読み取り専用ロールを与え、ほかには何も与えない。

DuckDBにはロールもGRANTもなく、ビューは、その下のテーブルを読める接続でしか読めない。そこでagent_warehouse(con, policy=...)は各ロールに専用のデータベースを作る。その中のテーブルはすべて、マートに対するそのロールのビューを同名のテーブルとして書き出したものであり、同じロック済みの設定で読み取り専用として開かれる。ビューは第9章のPolicy.subquery()から生成されるので、各規則は一度だけ書けばよい。この例では、ガードが受理する3つのクエリを各ロールのデータベースに直接送る。ガードはテーブルと列は知っているがロールは知らないためである。続いて、ガードの草案が通してしまったカタログ迂回を再現する。

role-database.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""A role's database, not the guard, decides what the role reads: queries the guard accepts return less from each role'sdatabase than from every mart, and the catalog bypass lists no net_revenue_eur for support. The assert after the bypass'stables-listed line settles that none of support's tables has the net_revenue_eur column the sum needs."""import duckdbfrom lib.guard_attacks import ATTEMPTSfrom lib.policies import policy_forfrom lib.sql_guard import SqlGuardfrom lib.warehouse import agent_warehouse, connectshop = connect()marts = agent_warehouse(shop)                 # every allowed mart, whole: no role's limitsguard = SqlGuard(marts)                       # chapter 7's guard knows tables and columns, not rolesroles = {"EMEA's regional lead": policy_for("regional_lead", "EMEA"), "finance": policy_for("finance"),         "support": policy_for("support")}QUERIES = {"EMEA's regional lead": "SELECT count(DISTINCT region) AS regions FROM analytics.fct_orders",           "finance": "SELECT count(*) AS n FROM analytics.dim_customers WHERE email LIKE 'customer-0004%'",           "support": "SELECT sum(net_revenue_eur) AS revenue FROM analytics.fct_orders"}def first_value(con, sql):    try:        return con.execute(sql).fetchall()[0][0]          # the first column of the first row    except duckdb.Error as e:        return str(e).splitlines()[0]for role, sql in QUERIES.items():    database = agent_warehouse(shop, policy=roles[role])     # the role's database: its views of the marts only    guard.check(sql)                                         # raises if the guard refuses; it doesn't    print(f"{role}, guard accepts: yes")    print(f"{role}, every mart: {first_value(marts, sql)}")    print(f"{role}, role's database: {first_value(database, sql)}")# A parser bypass a draft of the guard let through: a CTE named like DuckDB's catalog, which lists every table's SQL.support = agent_warehouse(shop, policy=roles["support"])# the bypass's rows as text: each table's CREATE statement, as DuckDB's catalog shows itlisted = " ".join(str(row) for row in support.execute(ATTEMPTS["CTE named duckdb_tables"]).fetchall())print(f"support, catalog bypass, tables listed: {listed.count('CREATE TABLE')}")assert "net_revenue_eur" not in listed              # the catalog lists no net_revenue_eur for support, which its sum needsprint(f"support, catalog bypass, names a revenue column: {'net_revenue_eur' in listed}")print(f"support, a write: {first_value(support, 'DELETE FROM analytics.fct_orders')}")
出力python role-database.py
EMEA'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!

どのクエリもガードを通過し、どれもそのロールのビューが持つものだけを返した。注文テーブルのリージョンを数えると、すべてのマートでは3、責任者のデータベースでは1になる。財務部門がcustomer-0004で始まるメールアドレスを検索すると、すべてのマートでは10件の顧客が一致し、財務部門のデータベースでは0件になる。財務部門のデータベースではすべてのメールアドレスが(masked)だからである。

サポート部門の純売上の合計は、列が存在しないためBinder Error: Referenced column "net_revenue_eur" not found in FROM clause!で失敗する。迂回は、DuckDBのカタログと同じ名前のCTEを使うもので、サポート部門のデータベースの6個のテーブルを列挙するが、そのどれにも、合計に必要な列であるnet_revenue_eurは含まれない。例はこれをアサートしている。書き込みは読み取り専用として失敗する。

実践ルール: データベースを境界にする。エージェントは読み取り専用で接続し、そのロールが見てよい行と列を制限するビューだけを読めるロールとして動かす。ウェアハウスにあれば、セキュアビュー、行アクセスポリシー、マスキングポリシーを使う。DuckDBでは、各ロールにそれらのビューからなる読み取り専用の専用データベースを与える。ビューはスタックの他の部分が使うのと同じポリシーから生成し、パーサーが受理するクエリでテストする。

実行前にSQLを構文解析する

最終変更:版2026.11.12

ガードが検査できるのは、見えるものだけである。テキストとしてのSQLからはほとんど何も見えない。DELETE という語を探す検査は、コメントや引用符で囲んだ名前を見逃し、同じ文を書く何通りもの方法も見逃す。構文解析はテキストを木に変換する。木では文、テーブル、列、関数のそれぞれが1つのノードになるので、以降の検査は文字ではなくノードを対象にできる。30.31.0 の sqlglot は、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章の4層すべての文脈を与えた記録には、カレンダーテーブルの国の列でグループ化する応答が含まれている。ガードがこの応答をどう扱うかを見るために、この例ではその応答を構文解析して木を一覧し、ガードの check() を実行する。続いて、2つの文を含む1つの文字列と、壊れたクエリを構文解析し、構文解析がどこで止まるかを示す。

parse-agent-sql.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""Parse the SQL the model wrote for gq-017 before it runs: its tree, the tables it reads, a column the guard refuses, andtwo inputs that don't parse as one query. The prose's examples of each come from these printed lines."""import duckdbimport sqlglotfrom sqlglot import expfrom sqlglot.errors import ParseErrorfrom lib.context import LAYERS, build_contextfrom lib.golden import load_goldenfrom lib.model import RecordedModelfrom lib.prompts import layered_sql_messagesfrom lib.sql_guard import GuardError, allowed_schema, checkfrom lib.tools import extract_sqlfrom lib.warehouse import agent_warehouse, connectcon = connect()question = next(q for q in load_golden() if q.question_id == "gq-017")context = build_context(con, question.question, LAYERS)          # chapter 4's context, every layersql = extract_sql(RecordedModel("context-layers").complete(layered_sql_messages(context.text, question.question)))print(f"question: {question.question}\n{sql}")tree = sqlglot.parse_one(sql, read="duckdb")print("root:", type(tree).__name__)print("tables read:", ", ".join(f"{t.db}.{t.name} AS {t.alias}" for t in tree.find_all(exp.Table)))print("columns named:", ", ".join(sorted({f"{c.table}.{c.name}" for c in tree.find_all(exp.Column) if c.table})))try:    check(sql, allowed_schema(agent_warehouse(con)))       # the allowlist: the agent's database's tablesexcept GuardError as e:    print("guard refuses:", e.kind)    # the refusal, then the columns of each table it reads, after a fixed phrase    message, columns = str(e).split(". The tables it reads have only these columns: ")    print("guard message:", message)    print("message then lists the columns of:", ", ".join(part.split(" has ")[0] for part in columns.split("; ")))try:    con.execute(sql)except duckdb.Error as e:    print("DuckDB, had it run:", str(e).splitlines()[0])statements = sqlglot.parse("SELECT 1; DROP TABLE analytics.fct_orders", read="duckdb")print("statements in one string:", ", ".join(type(s).__name__ for s in statements))try:    sqlglot.parse_one("SELECT region, sum(net_revenue_eur FROM analytics.fct_orders GROUP BY region", read="duckdb")except ParseError as e:    error = e.errors[0]                     # the parser's first problem; line and col count from 1    print("parse error fields:", ", ".join(sorted(error)))    print(f"parse error: {error['description']}\nparse error line: {error['line']}\nparse error column: {error['col']}")
出力python parse-agent-sql.py
question: 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 は2つの文、すなわち Select, Drop であるため、ガードはちょうど1つの文だけを受け付ける。

閉じ括弧が欠けたクエリは sqlglot の ParseError を送出する。その errors リストは、各問題のフィールド col, description, end_context, highlight, into_expression, line, start_context を与える。この例の壊れたクエリでは、説明は Expecting )、位置は 1 行目、39 列目である。ガードはこれらをモデルに渡す。修復の節でも、これらをそのように用いる。

実践ルール: エージェントのSQLは、何かを実行する前に、実行するウェアハウスの方言で木へと構文解析し、ちょうど1つの文だけを受け付ける。検査するのはノードであり、テキストではない。構文解析できたクエリは、形を検査できるようになったクエリであって、有効なクエリではないと扱う。ウェアハウスに送る前に、そのクエリが読み取りうるテーブルに照らして列を解決する。

この部分はまだ翻訳されていないため、英語で表示している。

Allow only read-only queries on known tables

最終変更:版2026.11.12

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.

block-a-delete.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""A DELETE hidden in a CTE: the root says SELECT, the tree walk finds the DELETE, and the read-only warehouse refuses it.The attack runs for real only on a throwaway copy of the shop, built from the same seed in a temporary folder."""import tempfilefrom pathlib import Pathimport duckdbimport sqlglotfrom sqlglot import expfrom lib.sql_guard import GuardError, SqlGuardfrom lib.warehouse import agent_warehouse, build, connectATTACK = """WITH gone AS (DELETE FROM analytics.fct_refunds WHERE amount_eur > 50 RETURNING refund_id)SELECT count(*) AS refunds_over_50 FROM gone"""tree = sqlglot.parse_one(ATTACK, read="duckdb")print("root:", type(tree).__name__)print("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 rootagent = agent_warehouse(connect())                      # the agent's read-only databasetry:    SqlGuard(agent).run(ATTACK)except GuardError as e:    print(f"guard refuses ({e.kind}): {e}")try:    agent.execute(ATTACK)                                 # past the guard, straight to the read-only databaseexcept duckdb.Error as e:    print("read-only database:", str(e).splitlines()[0])with tempfile.TemporaryDirectory() as folder:             # a writable copy, deleted when the block ends    path = Path(folder) / "shop.duckdb"    build(path)    with duckdb.connect(str(path)) as writable:        (before,), = writable.execute("SELECT count(*) FROM analytics.fct_refunds").fetchall()        (answer,), = writable.execute(ATTACK).fetchall()        (after,), = writable.execute("SELECT count(*) FROM analytics.fct_refunds").fetchall()print("writable copy, refunds before:", before)print("writable copy, the query's answer:", answer)print("writable copy, refunds after:", after)
出力python block-a-delete.py
root: 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.

block-raw-tables.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""The table allowlist: the marts the agent may read, six ways around it that the guard refuses, and the two goldenqueries it can't answer. The last two lines are the cost the prose describes."""from lib.golden import load_goldenfrom lib.sql_guard import GuardError, SqlGuardfrom lib.warehouse import agent_warehouse, connectcon = agent_warehouse(connect())          # the agent's database: copies of the allowed marts, nothing elseguard = SqlGuard(con)                     # allowlist: the tables in it, read with their columnsprint("allowed tables:", ", ".join(f"{db}.{t}" for db, tables in guard.schema.items() for t in tables))ATTEMPTS = {    "raw table": "SELECT count(*) AS customers FROM raw.customers",    "legacy mart": "SELECT sum(revenue) AS revenue FROM analytics.orders_legacy",    "no schema": "SELECT count(*) AS orders FROM fct_orders",    "file as a table": "SELECT * FROM 'data/golden/questions.toml'",    "table function": "SELECT * FROM read_csv('data/golden/questions.toml')",    "CTE named like the catalog": """SELECT * FROM (WITH duckdb_tables AS (SELECT 1 AS x) SELECT x FROM duckdb_tables) AS a,duckdb_tables""",}for label, sql in ATTEMPTS.items():    try:        guard.check(sql)        print(f"{label}: allowed")    except GuardError as e:        print(f"{label}: refused ({e.kind}), {str(e).split(';')[0]}")   # the message's first clausegolden = [q for q in load_golden() if q.golden_sql]outside = []for q in golden:    try:        guard.check(q.golden_sql)    except GuardError as e:        outside.append(f"{q.question_id} ({str(e).split(' ')[0]})")   # the message starts with the table it refusedprint(f"golden queries the allowlist refuses: {len(outside)} of {len(golden)}")print("refused golden queries:", ", ".join(outside))
出力python block-raw-tables.py
allowed 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

最終変更:版2026.11.15

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.

explain-before-running.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""Bound the rows and the cost: EXPLAIN a join that forgot its keys and refuse it, keep the agent's LIMIT, refuse a resultof every order, and stop the join by timer alone. The printed estimates and limits are the figures the prose quotes."""import sqlglotfrom lib.golden import load_goldenfrom lib.sql_guard import GuardError, SqlGuard, check, explain, run_with_timeoutfrom lib.warehouse import agent_warehouse, connectguard = SqlGuard(con := agent_warehouse(connect()))   # the agent's database, where the guard runs its checksNO_KEYS = """SELECT sum(o.net_revenue_eur) AS net_revenue FROM analytics.fct_orders AS o,analytics.fct_order_lines AS l, analytics.fct_refunds AS r"""KEYED = """SELECT sum(o.net_revenue_eur) AS net_revenue FROM analytics.fct_orders AS oJOIN analytics.fct_order_lines AS l USING (order_id)"""for label, sql in (("no join keys", NO_KEYS), ("joined on order_id", KEYED)):    cost = explain(con, check(sql, guard.schema).sql)    print(f"{label}, largest step: {cost.largest_step}\n{label}, estimated rows: {cost.largest_estimate:,}")   # one per querytry:    guard.check(NO_KEYS)except GuardError as e:    print(f"guard refuses ({e.kind}): {e}")costs = {q.question_id: explain(con, check(q.golden_sql, guard.schema).sql)         for q in load_golden() if q.golden_sql and q.question_id not in ("gq-034", "gq-037")}   # both need tables off the listprint("golden queries estimated:", len(costs))print(f"largest estimate among golden queries: {max(c.largest_estimate for c in costs.values()):,} rows")print("golden plans with a cross product:", ", ".join(i for i, c in costs.items() if c.cross_products))print("golden plans with a step that has no estimate:", sum(1 for c in costs.values() if c.unestimated))print(f"limit on estimated rows: {guard.max_estimated_rows:,}")TOP_FIVE = """SELECT product_id, sum(net_amount_eur) AS net_revenue FROM analytics.fct_order_linesGROUP BY product_id ORDER BY net_revenue DESC LIMIT 5"""print("sqlglot's .limit(1001):", sqlglot.parse_one(TOP_FIVE, read="duckdb").limit(1001).sql("duckdb").split("ORDER BY")[1].strip())   # the text after ORDER BYprint("guard's limit on the top five:", check(TOP_FIVE, guard.schema).limit)try:    guard.run("SELECT order_id, net_revenue_eur FROM analytics.fct_orders")except GuardError as e:    print(f"every order ({e.kind}): {e}")# The join without keys, through the time limit alone, as if the estimate had let it through. It reads its rows in# batches, and DuckDB looks for the timer's interrupt between them.try:    run_with_timeout(con, check(NO_KEYS, guard.schema).sql, seconds=1.0)except GuardError as e:    print(f"no join keys, timer alone ({e.kind}): {e}")print("connection still answers:", con.execute("SELECT count(*) FROM analytics.fct_orders").fetchall()[0][0])
出力python explain-before-running.py
no 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

最終変更:版2026.11.15

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.

stop-a-slow-plan.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""Stop a slow plan: DuckDB computes a constant while it plans, before any row is read, so a timer's interrupt hasnothing to stop; a killed worker stops wherever it is. The prose's claim that the plan never returns and the worker isstopped rests on the assert after the worker's run."""import jsonfrom lib.sql_guard import GuardError, SqlGuard, Worker, checkfrom lib.warehouse import agent_warehouse, connectshop = connect()                                      # chapter 1's connection: constant folding onagent = agent_warehouse(shop)                         # the agent's database: constant folding offguard = SqlGuard(agent, timeout=0.5)SLOW_PLAN = """SELECT count(*) AS n FROM analytics.fct_ordersWHERE order_id < list_sum(list_transform(range(30000000), x -> list_sum(range(40))))"""# Checked as if the function list allowed the list functions, as sqlglot names them (range() is GENERATE_SERIES and# list_transform() is TRANSFORM), so the plan reaches DuckDB.LIST_FUNCTIONS = {"list_sum", "transform", "generate_series"}planned = check(SLOW_PLAN, guard.schema, functions=guard.functions | LIST_FUNCTIONS).sql# The same query with a small constant, 3 lists summing to 6 each: 18, under every order ID. A plan with no rows to# read means DuckDB computed the constant while planning.small = planned.replace("30000000", "3").replace("RANGE(0, 40)", "RANGE(0, 4)")for label, connection in (("shop's connection, folding on", shop), ("agent's database, folding off", agent)):    (_, plan), = connection.execute(f"EXPLAIN (FORMAT json) {small}").fetchall()    steps, nodes = [], json.loads(plan)              # the plan's tree, followed down its first child at each step    while nodes:        steps.append(nodes[0]["name"])        nodes = nodes[0]["children"]    print(f"EXPLAIN with a small constant, {label}: {' > '.join(steps)}")worker = Worker(shop)                                 # a process with its own connection to the shop's filetry:    worker.explain(planned, 0.5)    outcome = "planned"except GuardError as e:    outcome = e.kind                                  # timeout, memory or worker_failed: the machine decides whichprint("EXPLAIN in a worker with a half-second deadline, shop's connection, plan returned:", outcome == "planned")print("EXPLAIN in a worker, still running afterwards:", worker.running)assert outcome != "planned" and not worker.running   # the half-second deadline stops the plan, and the kill leaves no workertry:    guard.run(SLOW_PLAN)except GuardError as e:    print(f"slow plan through the guard ({e.kind}): {e}")
出力python stop-a-slow-plan.py
EXPLAIN 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.

この部分はまだ翻訳されていないため、英語で表示している。

Replay the attacks on the guard

最終変更:版2026.11.12

A first draft of the guard trusted its parse checks and bounds, on chapter 1's connection, which could see every schema, and a critic got past the parser three ways. A CTE named like a catalog view returned the CREATE statement of every table, raw included. A constant folded while planning outran the timer. A cross product with unnest(range(200000)) passed the plan-size limit, because a step with no estimate counted as small. Each was a place where the guard's model of SQL and the database disagreed, and the database's reading is the one that runs. Two security reviews found more ways past the hardened guard.

The reviews' attempts and three smaller ones, replayed against each layer, show which one holds. Some go straight to the agent's database, and each goes through the guard with its 5-second limit.

replay-guard-bypasses.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""The critics' attempts to get past the guard (lib/guard_attacks.py), replayed on a temporary copy of the marts: what thedatabase alone allows and what the guard does. The asserts back the claims the prose makes without a number."""import duckdbfrom lib.guard_attacks import ALONE, ATTEMPTS, alone, attempt, macro_then_sumfrom lib.sql_guard import SqlGuard, explainfrom lib.warehouse import agent_warehouse, connectagent = agent_warehouse(connect())guard = SqlGuard(agent)                       # the 5-second limit: the queries that run here finish far inside itfor label, sql in ATTEMPTS.items():    if label in ALONE:                        # straight to the agent's database, with no guard in front        print(f"{label}, database alone: {alone(agent, label)}")    print(f"{label}, guard: {attempt(guard.run, sql)[0]}")print("upper-case names, guard:", attempt(guard.run, "SELECT count(*) AS n FROM ANALYTICS.FCT_ORDERS")[0])# A temporary macro named sum, on a cursor of the database alone, then sent straight to the guard's worker.alone_sum, real = macro_then_sum(agent, guard.worker)print("temp macro named sum, database alone, the next sum:", alone_sum)print("temp macro named sum, guard's worker, the next sum is the real one:", real)assert real                                   # the worker's fresh cursor dropped the macro, so the next sum is real# The regex attempt. In-process, a timer's interrupt waits for the end of a batch of rows, and one batch is this much# text. With repeat() and regexp_matches() (sqlglot's REGEXP_LIKE) allowed, the guard's worker runs it under the# review's 2-second limit, and stops it by the deadline or the memory cap, whichever the machine reaches first.REGEX, BATCH = ATTEMPTS["regex on a repeated email"], duckdb.__standard_vector_size__(text,), = agent.execute(f"SELECT sum(length(email)) * 200000 FROM (SELECT email FROM analytics.dim_customers "                         f"ORDER BY customer_id LIMIT {BATCH})").fetchall()print(f"regex, characters to match in one batch of {BATCH:,} rows: {text:,}")wider = SqlGuard(agent, timeout=2.0, functions=guard.functions | {"repeat", "regexp_like"})print("regex, guard without the function list:", attempt(wider.run, REGEX)[1])print("regex, worker still running afterwards:", wider.worker_running)assert not wider.worker_running                # the worker is gone, however it was stoppedprint("regex, the guard's next query:", attempt(wider.run, "SELECT count(*) AS n FROM analytics.fct_orders")[0])# The inequality join ran. Its estimate varies a little between runs, so only its size is printed.JOIN = ATTEMPTS["join on inequality"]estimate, joined = explain(agent, JOIN).largest_estimate, guard.run(JOIN).rows[0][0]   # the count is the only row and columnprint("join on inequality, estimate under the limit:", estimate < guard.max_estimated_rows)print(f"join on inequality, rows it joined: {joined:,}")print("join on inequality, rows joined more than 100 times the estimate:", joined > 100 * estimate)assert estimate < guard.max_estimated_rows and joined > 100 * estimate   # under the limit, yet far more rows than estimated
出力python replay-guard-bypasses.py
CTE 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

Each attempt but the join is refused. The CTE named duckdb_tables, sent to the agent's database alone, returns 7 rows, one per table, all in analytics: True: the catalog can't show the agent a table it may not read, because none is there. The slow plan and unnest() are refused as functions off the list, as in refused (function), list_sum() isn't a function the guard allows. One row holding every customer's name and email, 1 row of 106,670 characters, is refused as too large.

The second review's first attempt matches a pattern against each customer's email repeated 200,000 times. It passed the parser and the plan's limit, and outran an interrupt, which DuckDB checks for only between batches of rows; one batch here holds 10,649,600,000 characters. The function list now refuses it: refused (function), regexp_matches() isn't a function the guard allows. Run with repeat() and regexp_matches() allowed, under the review's 2-second limit, it is stopped, no answer, stopped by the deadline or the worker's memory cap, whichever came first. The worker is stopped too, and the guard's next query ran.

current_database() and version(), which alone return agent, v2.0.0, had passed; the guard now checks every function. Six hundred nested subqueries, which crashed sqlglot's parser, are now refused: refused (parse), the SQL is nested too deeply to check (the limit is 200 levels).

A final review added two. The guard now refuses SIMILAR TO, a regular expression as an operator: refused (function), SIMILAR TO isn't an operator the guard allows. A temporary macro named sum, which the database alone accepts, makes the next sum 42. The parser refuses the macro, and had it got through, the worker, starting each query on a fresh cursor, would drop it, so the next sum is the real one.

The last attempt joins orders to every customer who didn't place them, and it ran, as the rules allow. Its estimate was under the limit, and it joined 45,147,384 rows, which is more than 100 times the estimate. So the plan's limit catches only the mistakes it was set for. The kill, the result limits and the memory limit bound the rest, and the production section lists what remains.

Working rule: Rely on the database's limits, and treat the parser as defence in depth. Keep every attack anyone finds as a test: replay it against the agent's database alone and through the guard, and check that each is refused by the layer you expect. When an attack gets past the parser, ask first why the database let it reach anything, and then fix the parser.

この部分はまだ翻訳されていないため、英語で表示している。

Repair once from the error

最終変更:版2026.11.12

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.

repair-from-error.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""Repair once from the guard's message: how often one repair turns a refusal into an answer on the dev split, and tworepairs in full. The counts are the figures the prose quotes; the repaired SQL is printed for the reader to check."""from collections import Counterfrom lib.context import LAYERS, build_contextfrom lib.golden import golden_result, grade, load_goldenfrom lib.model import RecordedModelsfrom lib.prompts import layered_sql_messages, schema_only_messagesfrom lib.sql_guard import SqlGuard, answer_with_repairfrom lib.warehouse import agent_warehouse, connect, describe_schemacon = connect()guard, schema = SqlGuard(agent_warehouse(con)), describe_schema(con)# First replies from chapters 1 and 4's recordings; each repair reply from this chapter's.model = RecordedModels("schema-only-sql", "context-layers", "guarded-sql-runs")MESSAGES = {"schema-only": lambda q: schema_only_messages(schema, q.question),            "layered": lambda q: layered_sql_messages(build_context(con, q.question, LAYERS).text, q.question)}dev = [q for q in load_golden() if q.split == "dev" and q.kind == "answer"]shown = {}for context, messages in MESSAGES.items():    counts = Counter()    for q in dev:        run = answer_with_repair(guard, model, messages(q), q.question, role=q.role, repairs=1)        outcome = grade(q, run.answer, golden_result(con, q))        when = "after repair" if run.repaired else "first time"        counts[f"ran {when}"] += run.attempts[-1].outcome == "ran"        counts[f"correct {when}"] += outcome == "correct"        counts[f"wrong {when}"] += outcome == "wrong"        counts["refused or failed first"] += run.repaired        counts["declined after repair"] += run.attempts[-1].outcome == "declined"        shown[context, q.question_id] = run    print(f"{context}, questions: {len(dev)}")    for key in ("ran first time", "correct first time", "wrong first time", "refused or failed first",                "ran after repair", "correct after repair", "wrong after repair", "declined after repair"):        print(f"{context}, {key}: {counts[key]}")for context, question_id in (("layered", "gq-017"), ("schema-only", "gq-001")):    run = shown[context, question_id]    print(f"{question_id} {context}, refused: {run.attempts[0].message.split('. The tables')[0]}")   # the refusal, without its list of tables    print(run.attempts[1].sql)                       # attempt 0 is the first reply, attempt 1 the repair    rows = len(run.answer.rows)    print(f"{question_id} {context}, after repair: {run.attempts[1].outcome} and returned {rows} row{'s' * (rows != 1)}")
出力python repair-from-error.py
schema-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

最終変更:版2026.11.12

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.

transpile-to-snowflake.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""Transpile the guard's SQL to Snowflake: the quoted table name Snowflake reads, a weekday function that answers differently,and a histogram that passes through unchanged. The printed lines are the output the prose quotes."""from lib.sql_guard import SqlGuard, transpilefrom lib.warehouse import agent_warehouse, connectcon = connect()guard = SqlGuard(agent_warehouse(con))WEEKDAYS = """SELECT dayname(order_date_local) AS weekday, strftime(order_date_local, '%Y-%m') AS month,       round(sum(net_revenue_eur), 2) AS net_revenueFROM analytics.fct_ordersWHERE status <> 'cancelled' AND year(order_date_local) = 2025GROUP BY ALL ORDER BY month, weekday"""checked = guard.check(WEEKDAYS)print("kept quoted, Snowflake reads:", transpile(checked.sql, "snowflake", quoted=True).sql.split(" FROM ")[1].split(" WHERE")[0])   # the FROM clause onlysnowflake = transpile(checked.sql, "snowflake", pretty=True)print(snowflake.sql)print("functions passed through unchanged:", ", ".join(snowflake.passed_through) or "none")print("DuckDB's first weekday:", guard.run(WEEKDAYS).rows[0][0])MIX = """SELECT region, histogram(channel) AS orders_by_channel FROM analytics.fct_ordersWHERE year(order_date_local) = 2025 GROUP BY region"""mix = transpile(guard.check(MIX).sql, "snowflake")print("channel mix in Snowflake:", mix.sql.split(" FROM")[0])   # the SELECT list, which holds the functionprint("functions passed through in the channel mix:", ", ".join(mix.passed_through) or "none")
出力python transpile-to-snowflake.py
kept 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.

この部分はまだ翻訳されていないため、英語で表示している。

When to prefer a new metric over SQL

最終変更:版2026.11.12

The database and the guards make the agent's SQL safe to run: limited to its role's views (the views section), bounded and repaired once. None of them makes it right. A metric is right by construction, because a person defined it once. So guarded SQL is for questions asked once, and a question that keeps coming back, or whose number goes into a report, should become a metric. To tell them apart, score the guarded path on the long tail of golden questions and read every wrong answer.

The shop's long tail is the ten answer questions that no metric or dimension in examples/data/semantic/ covers, such as orders that include a tent, session conversion and discount. 2 of the ten are on the dev split and 8 on the held-out test split. Two questions are too few to score, so the long-tail number comes from test. The guard and the repair prompt were fixed on dev, and nothing changed after we read test once; chapter 4 already scored the first replies there. We score that path first, then promote one question in a copy of its YAML.

long-tail-score.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""Guarded SQL on the long tail, the answer questions no metric in the semantic layer covers; then one promoted."""import shutilimport tempfilefrom collections import Counterfrom pathlib import Pathfrom lib.answers import format_valuefrom lib.context import LAYERS, build_contextfrom lib.golden import golden_result, grade, load_golden, results_matchfrom lib.model import RecordedModelsfrom lib.prompts import layered_sql_messagesfrom lib.semantic import SEMANTIC_DIR, MetricRequest, loadfrom lib.sql_guard import SqlGuard, answer_with_repairfrom lib.warehouse import agent_warehouse, connectLONG_TAIL = {"gq-007": "a filter on what an order contains", "gq-016": "customers with an order",             "gq-031": "units per order", "gq-032": "gross margin", "gq-033": "session conversion",             "gq-034": "marketing spend", "gq-036": "one order's refunds", "gq-037": "duplicate sign-ups",             "gq-038": "a weekday dimension", "gq-041": "discount"}con, golden = connect(), {q.question_id: q for q in load_golden()}guard, model = SqlGuard(agent_warehouse(con)), RecordedModels("context-layers", "context-layers-test", "guarded-sql-runs")counts = Counter()for question_id, needs in LONG_TAIL.items():    q = golden[question_id]    run = answer_with_repair(guard, model, layered_sql_messages(build_context(con, q.question, LAYERS).text,                                                                 q.question), q.question, role=q.role)    outcome = grade(q, run.answer, gold := golden_result(con, q))    counts[q.split, outcome] += 1    print(f"{question_id} ({q.split}, {needs}): {outcome}, after {' then '.join(a.outcome for a in run.attempts)}")    if outcome == "wrong":        row = ", ".join(map(format_value, min(run.answer.rows))) if run.answer.rows else "no rows"        print(f"{question_id} first row: {row} (golden: {', '.join(map(format_value, min(gold)))})")print(*(f"long tail questions on {s}: {sum(golden[i].split == s for i in LONG_TAIL)}" for s in ("dev", "test")), sep="\n")for split, outcome in [(s, o) for s in ("dev", "test") for o in ("correct", "wrong", "declined")]:    print(f"long tail {split} {outcome}: {counts[split, outcome]} of {sum(n for (s, _), n in counts.items() if s == split)}")with tempfile.TemporaryDirectory() as folder:        # promote gq-038: one dimension added to a copy of the YAML    shutil.copytree(SEMANTIC_DIR, copy := Path(folder) / "semantic")    with open(copy / "orders.yml", "a") as orders:        orders.write("    - name: weekday\n      type: categorical\n      expr: dayname(order_date_local)\n")    layer = load(copy)answer = layer.query(con, MetricRequest(("net_revenue", "orders"), ("order__weekday",), {"metric_time__year": 2025}))print("gq-038 as a metric request matches golden:", results_match(answer.rows, golden_result(con, golden["gq-038"])))assert results_match(answer.rows, golden_result(con, golden["gq-038"]))   # the promotion gives the golden answer
出力python long-tail-score.py
gq-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

On dev, guarded SQL gets 1 of 2 right; on test, 3 of 8, with 1 of 8 declined. The decline is gq-034, return on marketing spend: the guard refused raw.marketing_spend, and in its repair the model answered that no allowed table holds spend, which is right. The fix is a mart.

Every wrong answer ran cleanly through every guard. For gq-007, the model made both mistakes chapter 4 found: it wrote the category tents where the shop stores Tents, so the sum came back NULL (golden: 377,497.40), and it sums only the tent lines, where the question asks for the whole revenue of orders that included one. For gq-032, gross margin, the model subtracted net revenue from gross revenue, which gives the tax, and joined the order lines on order_id, so each order counts once per line, chapter 2's fan-out: 773,723.34 (golden: 847,864.71). The plan-size limit doesn't catch a fan-out on a key. For gq-033, it gave conversion as a percentage where the shop reports a fraction: (none), 13.46 (golden: (none), 0.13). For gq-038, net revenue and orders by weekday, revenue is right and the order count isn't: Friday, 221,172.11, 822 (golden: Friday, 221,172.11, 869). The model left cancelled orders out of the count, where the shop's orders metric keeps them.

gq-038 is the one to promote. The shop defines both its metrics and lacks only a weekday dimension: three lines of YAML on the orders semantic model, with the expression dayname(order_date_local). With it, the question becomes a metric request grouped by order__weekday in 2025, and that matches the golden result. That isn't a held-out result: we chose it from gq-038's failure on test. The book's layer is unchanged, so gq-038 stays in test; in your own layer, move it to dev. gq-034 and gq-037 need a mart first, because the agent rightly can't read raw. Lookups such as gq-036, one order's refunds and their reasons, are what guarded SQL answers well.

Working rule: Send a question to guarded SQL only when no metric covers it, and score that path on its own long tail of golden questions. Promote a question to the semantic layer when it recurs, when its answer goes into a report, or when guarded SQL gets it wrong on a definition, as with what counts as an order. Leave one-off lookups on the guarded path. When a question needs a table off the allowlist, build the mart and its metric rather than widening the list.

本番環境で何がうまくいかなくなるか

最終変更:版2026.11.12

テストに通ったガードでも、実際の運用では誤りを含むことがある。ウェアハウス、ロール、関数リストが、ガードの下で変更され続けるからである。以下の各失敗には、それを明らかにする兆候を添えてある。

誤った答えがすべてのガードを通過する。 データベースとガードが制限するのは、クエリが何を読み、どれだけのコストがかかるかであり、その数値が何を意味するかではない。実行結果に基づく修正では、実行はできるが誤っている SQL を直せない。Hong et al. が修復の前に誤り検出を加え(Hong et al.)、Chaturvedi et al. が誤りの分類体系で修正を導く(Chaturvedi et al.)のは、このためである。気づく方法は、ガードを通る経路をゴールデン質問で採点することと、結果が空の答え、NULL の答え、割合ではなくパーセントになっている答えを読むことしかない。

エージェントの接続がビューより上にある。 境界が成り立つのは、エージェントのウェアハウスユーザーがビューの下にあるテーブルを読めない場合に限られる。ウェアハウスのロールが変わるたびに、そのユーザーの権限を確認する。

ロールのビューが誤っている、または古い。 ビューは定義どおりの内容を返すので、行フィルタやマスクが欠けていると、すべての経路で同時に情報が漏れる。DuckDB では、各ロールのデータベースは構築時に作られたコピーであるため、マートを再構築するときにそれも再構築しなければならない。ビューは一つのポリシーから生成し、パーサーが受け入れるクエリで各ロールをテストし、すべてのテーブルを一つずつ名前で指定する。そうすれば、新しいマートは誰かが追加するまで対象外のままになる。

関数とカタログのリストは判断である。 アナリストが必要とする関数が欠けていることがあり、リストにある関数が遅いこともあり、DuckDB の新しいカタログ関数がリストに載らないまま増えることもある。function の拒否をすべて記録し、DuckDB のリリースごとに両方のリストを見直す。

ガードが正しい SQL を拒否する。 ガードは、sqlglot がモデル化できない DuckDB の構文を拒否する。拒否はすべて種類とともに記録し、許可リストの節のように、すべての正解クエリを CI でガードに通す。

見積りはコストではない。 リプレイには、見積りの 100 倍を超える行数を生成した結合が示されている。各クエリの見積りを実行時間と並べて記録する。

読み取り専用のデータベースにも状態は残る。 読み取り専用の DuckDB 接続は、一時テーブル、ビュー、マクロ、変数を受け付ける。そのため、パーサーを通り抜けた文が以降の答えを変えうる。リプレイのマクロがその例である。ワーカーはクエリごとに新しいカーソルを使う。共有の接続プールでも同様にする。

DuckDB のメモリ制限はすべての値を対象にしない。 この制限は「バッファマネージャーにのみ適用される」(DuckDB)ため、式が構築する文字列は 1 GB の制限をすり抜ける。ワーカーは、データベースを開いた後、自身のプロセスを、データベースのサイズに 1.5 GB を加えた値で制限する。メモリ不足になったワーカー、終了したワーカー、起動しないワーカーは GuardError(memory または worker_failed)を送出し、行を返さない。つまりガードは安全側に閉じる。構文解析の検査はガード自身のプロセスで実行され、20,000 文字の上限と深さの上限だけが制約になる。ワーカーは一度に一つのクエリにしか答えられないので、エージェントの実行ごとに専用のガードを用意する。

ウェアハウス自身の制限は遠い。 Snowflake の STATEMENT_TIMEOUT_IN_SECONDS の既定値は「172800(2 日)」である(Snowflake)。エージェントの時間制限はガードで設定し、エージェントのウェアハウスロールにもそれより低い制限を設定する。

修復は誤りを隠す。 この店の開発用分割では、修復されて実行できたクエリはすべて誤りだった。answer_with_repair() が GuardedRun で行うように、試行を答えとともに記録し、すべてのスコアで修復された答えを数える。

ガードのメッセージはデータベースを説明してしまう。 拒否メッセージには、許可されたテーブルとその列が列挙される。ガードはそのロール自身のデータベースの上に構築し、ロールが見てよいものだけが列挙されるようにする。

実践ルール: ロールの読み取り専用のビューのデータベースを統制の本体とみなし、ガードは誤りを拒否に変えてコストを抑える層とみなす。エージェントの権限を確認して各ロールのビューをテストし、すべての正解クエリを CI でガードに通し、時間制限をガードとウェアハウスの両方に設定し、拒否、見積り、修復をすべて答えとともに記録し、変更のたびに、この経路専用のロングテールの正解付き質問で採点する。

この章で下した決定

最終変更:版2026.11.12
  • ガード付き 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)から翻訳した。

この節を引用する

形式

いずれの形式も、本訳書が従う英語版の書籍を出典として示す。

いずれの形式もこの版を明示している。そのリンクは現行版を開き、節の見出しには本文が最後に変更された日付が示される。アクセス日と取得日は、これを開いた日である。

このページの内容

全11章中の第7章: エージェントにガード付きでSQLを書かせる

  1. Give the agent a database with only what it may read
  2. 各ロールにビューのデータベースを与える
  3. 実行前にSQLを構文解析する
  4. Allow only read-only queries on known tables
  5. Bound the rows and the cost
  6. Enforce the deadline by killing a process
  7. Replay the attacks on the guard
  8. Repair once from the error
  9. Transpile to the warehouse's dialect
  10. When to prefer a new metric over SQL
  11. 本番環境で何がうまくいかなくなるか
  12. この章で下した決定

ページの先頭に戻るすべての章