PyPI · langroid
Langroid: SQLChatAgent _validate_query blocklist misses pg_read_file family enabling arbitrary file read
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.
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.
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.
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):
# 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:
\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.\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.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.
poc.py (single-file, no external services beyond a transient PostgreSQL
spawned via testing.postgresql):
"""
PoC: SQLChatAgent _validate_query bypass via PostgreSQL file-disclosure
family pg_read_file / pg_stat_file / pg_ls_logdir / pg_ls_waldir /
pg_current_logfile.
"""
import os
import re
import sys
from typing import List, Optional
PKG = "/tmp/poc-langroid-bypass/venv/lib/python3.12/site-packages/langroid"
SRC = f"{PKG}/agent/special/sql/sql_chat_agent.py"
assert os.path.exists(SRC), f"Missing pinned langroid source: {SRC}"
import sqlglot
from sqlglot import expressions as sqlglot_exp
def load_patterns_from_pinned_source():
"""Extract _DANGEROUS_SQL_PATTERNS + _DEFAULT_ALLOWED_STATEMENTS from
the pinned langroid 0.63.0 sql_chat_agent.py without instantiating the
full agent stack (which needs an LLM config)."""
with open(SRC) as f:
source = f.read()
block = re.search(
r"_DANGEROUS_SQL_PATTERNS:[^=]*=\s*\[(.*?)\]\s*\n", source, re.DOTALL,
)
ns = {"re": re, "List": list}
patterns = eval("[" + block.group(1) + "]", ns)
allowed = eval(
re.search(
r"_DEFAULT_ALLOWED_STATEMENTS:\s*List\[str\]\s*=\s*(\[.*?\])",
source, re.DOTALL,
).group(1)
)
return patterns, allowed
def validate_query(query, patterns, allowed_statements, dialect="postgres"
Is your project exposed to this? Stateward checks every dependency on every pull request and flags it only if your code actually reaches it.
Check my repoSources: CISA KEV (public domain), OSV.dev & GitHub Advisory Database (CC-BY-4.0), FIRST EPSS, NVD/CWE (public domain). Served live from the Stateward advisory database.