GHSA-pmch-g965-grmr Langroid: SQLChatAgent _validate_query blocklist misses pg_read_file family enabling arbitrary file read
Summary`SQLChatAgent` in `langroid` ships a `_validate_query` defense-in-depth layer
whose `_DANGEROUS_SQL_PATTERNS` regex blocklist enumerates dangerous SQL
primitives by specific function name. The list misses the canonical
PostgreSQL filesystem-disclosure family `pg_read_file()`, `pg_stat_file()`,
`pg_ls_logdir()`, `pg_ls_waldir()`, `pg_current_logfile()` (and similar
`SELECT`-shaped functions in the same family). It also leaves SQL Server
`OPENDATASOURCE` and SQLite `ATTACH '<file>' AS x` (DATABASE keyword
omitted) unblocked.An attacker able to shape the LLM's generated SQL (directly via prompt input
or transitively via prompt-injection in data the LLM ingests) can read
arbitrary files from the PostgreSQL host through ordinary `SELECT` queries,
even with the agent's strict default configuration
(`allow_dangerous_operations=False`, `allowed_statement_types=['SELECT']`).
The payloads survive the statement-type allowlist (each is a `SELECT`) and
pass through the regex blocklist (none of the function names match), then
reach the live SQLAlchemy engine via `SQLChatAgent.run_query`.Affected versions`langroid` `<= 0.63.0` (latest at the time of this report; PyPI release
2026-05-27). The vulnerable code path is
`langroid/agent/special/sql/sql_chat_agent.py::_validate_query`, which
consults the module-level `_DANGEROUS_SQL_PATTERNS` literal at
`sql_chat_agent.py:113-141`.Privilege requiredAny caller able to influence the LLM-generated `RunQueryTool.query` string
that reaches `SQLChatAgent.run_query`. In a typical deployment this is any
client of a SQLChatAgent-backed service, or any upstream data source whose
content the LLM is asked to read and summarise. No PostgreSQL credentials
are required from the attacker; the agent holds them.Vulnerable code`langroid/agent/special/sql/sql_chat_agent.py:113-141` (the
`_DANGEROUS_SQL_PATTERNS` literal) and `sql_chat_agent.py:546-615` (the
`_validate_query` method that consults it):The blocklist is a list of `\b<exact-token>\b` literals. PostgreSQL ships
several near-name functions on the same primitive that none of these match:| Function | What it returns | Matched by blocklist? |
|---|---|---|
| `pg_read_server_file('/path')` | file contents | yes (`pg_read_server_files?`) |
| `pg_read_binary_file('/path')` | binary contents | yes |
| `pg_ls_dir('/path')` | directory listing | yes |
| `pg_read_file('/path')` | file contents | **no** (no `_server_` infix) |
| `pg_stat_file('/path')` | size, mtime, ctime, atime, isdir | **no** |
| `pg_ls_logdir()` | filenames in PostgreSQL log dir | **no** |
| `pg_ls_waldir()` | WAL filenames and sizes | **no** |
| `pg_ls_tmpdir()` | temp-dir listing | **no** |
| `pg_ls_archive_statusdir()` | archive-status directory listing | **no** |
| `pg_current_logfile()` | active server log path | **no** |Each of these is a `SELECT`-shaped function call. They pass the
`sqlglot_exp.Select`-only statement-type allowlist applied at
`sql_chat_agent.py:583-614`, then evade the regex blocklist (their names
contain no token the blocklist enumerates), then reach the SQLAlchemy
`session.execute(text(query))` sink inside `SQLChatAgent.run_query` (line
631 onwards).Two non-PostgreSQL secondary gaps with the same regex-enumeration shape:The SQLite pattern `\battach\s+database\b` requires the literal
`DATABASE` keyword. Per the SQLite grammar
(https://www.sqlite.org/lang_attach.html) the keyword is optional:
`ATTACH '/path/to/db' AS x` is valid syntax and matches no entry in the
blocklist. Whether the agent rejects this via the statement-type
allowlist depends on how the configured `sqlglot` dialect parses it; on
PostgreSQL dialect parsing fails (sqlglot returns no `Select`) and the
statement-type check rejects, but a SQLite-dialect SQLChatAgent
(`database_uri="sqlite:///..."`) returns the statement as
`sqlglot_exp.Attach`, which is not in the agent's `kind_map`, so the
generic `type(stmt).__name__.upper()` branch produces `"ATTACH"`. That
string is not in `_DEFAULT_ALLOWED_STATEMENTS` so the allowlist saves it
here; however any deployment that extends `allowed_statement_types` to
include `"ATTACH"` (e.g. to permit cross-schema connectivity) loses
this fallback and the regex misses.The MSSQL pattern `\bopenrowset\b` blocks `OPENROWSET` but not the
closely-related `OPENDATASOURCE` function. Both can read
remote/UNC files and execute remote queries via an ad-hoc connection
string, e.g. a `SELECT` against
`OPENDATASOURCE('SQLNCLI11','Server=remote;Trusted_Connection=yes')`
qualified down to `master.sys.tables`.Attack scenario`SQLChatAgent.run_query` (line 617 of `sql_chat_agent.py`) calls
`self._validate_query(query)` (line 631) on the LLM-generated SQL. The
LLM-generated SQL is shaped by upstream prompt content that crosses the
trust boundary: the user message, any tool result the LLM is asked to
summarise, any document the agent retrieves, and any row the agent reads
back from its own database (the `RunQueryTool` result is fed back into the
LLM history at `sql_chat_agent.py:712-720` of the same release).The default config in `SQLChatAgentConfig` (lines 183-184) sets
`allow_dangerous_operations=False` and `allowed_statement_types=["SELECT"]`,
which is the configuration `_validate_query` was added to support. The
bypass primitives below are reachable under this default config because
each is a syntactic `SELECT` whose function-call argument is the
disclosure vector.Proof of concept`poc.py` (single-file, no external services beyond a transient PostgreSQL
spawned via `testing.postgresql`):End-to-end reproductionRun against the latest published `langroid` release from PyPI; no external
LLM provider, no API key, no Docker, just a transient `pg_ctl`-managed
PostgreSQL spawned in-process by `testing.postgresql`. Captured transcript
of the run is below.Observed transcript (abridged to bypass results; the run also verifies
that the four primitives the current blocklist already covers
(`COPY ... TO PROGRAM`, `pg_read_server_file`, `pg_read_binary_file`,
`pg_ls_dir`) continue to be REJECTED, confirming the proposed fix is
strictly broader, not narrower):The headline payload `SELECT pg_read_file('langroid_bypass_victim.txt')`
returns the marker string verbatim from the file on disk. The same SQL,
issued by an LLM under prompt-injection through any data source the agent
reads, would land identically — the validator is purely a function of the
SQL string and is consulted before the SQLAlchemy execute.`_validate_query` is invoked directly rather than through a fully
initialised `SQLChatAgent` because the agent's `__init__` builds the LLM
stack and demands a working LLM API key (or a stub). The security
control under test is purely a function of `(query, patterns,
allowed_statements, dialect)`, so the direct call is observationally
equivalent to a call via `run_query`. Patterns and allowed-statements are
loaded by reading the pinned `sql_chat_agent.py` source out of the venv,
guaranteeing no drift between PoC and shipped binary.Impact**Arbitrary file read** from the PostgreSQL host: `pg_read_file()` reads
files from PGDATA-relative paths by default and can take absolute paths
when the DB role holds `pg_read_server_files` (or equivalent in
managed-Postgres setups). For self-managed PostgreSQL deployments the
DB role is frequently a superuser, in which case absolute paths are
always accepted and the impact extends to `postgresql.conf`,
`pg_hba.conf`, `~/.pgpass`, TLS keys, and any other file readable by
the PostgreSQL OS user.**Filesystem reconnaissance** via `pg_stat_file()` (file existence,
size, mtime, isdir), `pg_ls_logdir()`, `pg_ls_waldir()`,
`pg_ls_tmpdir()`, `pg_ls_archive_statusdir()`,
`pg_current_logfile()`.**MSSQL extension:** `OPENDATASOURCE` reaches remote SQL Servers and
UNC paths, providing arbitrary outbound read + intranet pivot on MSSQL
deployments.**SQLite extension:** `ATTACH '<path>' AS schemaname` (DATABASE keyword
omitted) allows reading/writing arbitrary SQLite files on deployments
whose `allowed_statement_types` include `"ATTACH"`.Suggested fixPatch `_DANGEROUS_SQL_PATTERNS` to cover the full family rather than
individual function names. Two compatible approaches; either is enough.Approach 1 — family-prefix regex (minimal change, simplest to review):Approach 2 — `sqlglot` AST walk in addition to regex. `sqlglot` is already
imported by `sql_chat_agent.py`; iterate every function-call node
(`sqlglot_exp.Anonymous` / `sqlglot_exp.Func`) inside the parsed
statements and reject when the lower-cased name starts with `pg_read`,
`pg_stat`, `pg_ls`, `pg_current_logfile`, `lo_`, or matches the MSSQL
extended-procedure prefixes (`xp_`, `sp_oa`). AST matching is robust to
whitespace, comments, and case games inside identifiers, at the cost of
broader per-dialect maintenance. For closing the immediate gap, Approach
1 is sufficient.Regression-test the additions in
`tests/main/sql_chat/test_sql_chat_security.py` alongside the existing
security tests. A natural 7-case extension covers the 5 PostgreSQL
bypass payloads, the SQLite `ATTACH ... AS x` form, and the MSSQL
`OPENDATASOURCE` form.Fix PRA private temp-fork PR applying the **Suggested fix** Approach 1 diff,
plus the regression tests described above, accompanies this advisory:
https://github.com/langroid/langroid-ghsa-pmch-g965-grmr/pull/1CreditReported by tonghuaroot.
CVSS v4.0 score: 8.7