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 required
Any 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):
```python
# sql_chat_agent.py:113
_DANGEROUS_SQL_PATTERNS: List["re.Pattern[str]"] = [
re.compile(r"\bcopy\b[\s\S]*\bprogram\b", re.IGNORECASE),
re.compile(r"\bpg_read_server_files?\b", re.IGNORECASE),
re.compile(r"\bpg_read_binary_file\b", re.IGNORECASE),
re.compile(r"\bpg_ls_dir\b", re.IGNORECASE),
re.compile(r"\blo_(import|export)\b", re.IGNORECASE),
re.compile(r"\binto\s+(outfile|dumpfile)\b", re.IGNORECASE),
re.compile(r"\bload_file\s*\(", re.IGNORECASE),
re.compile(r"\bload\s+data\b", re.IGNORECASE),
re.compile(r"\bload_extension\s*\(", re.IGNORECASE),
re.compile(r"\battach\s+database\b", re.IGNORECASE),
re.compile(r"\bxp_cmdshell\b", re.IGNORECASE),
re.compile(r"\bsp_oacreate\b", re.IGNORECASE),
re.compile(r"\bsp_oamethod\b", re.IGNORECASE),
re.compile(r"\bopenrowset\b", re.IGNORECASE),
re.compile(r"\bbulk\s+insert\b", re.IGNORECASE),
re.compile(
r"\bcreate\s+(or\s+replace\s+)?(function|procedure|trigger)\b",
re.IGNORECASE,
),
re.compile(r"\bcreate\s+extension\b", re.IGNORECASE),
]
```
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-di
