Skip to content

Commit ef76102

Browse files
authored
fix(llm): close lexer-differential bypass in AI Assistant read-only guard
sqlparse's string-literal lexing can disagree with PostgreSQL's: under standard_conforming_strings = on (the default), a backslash before a quote is an ordinary character to PostgreSQL but sqlparse treats it as escaping the quote, so a payload like SELECT '\';COMMIT;CREATE TABLE pwn(x int);SELECT 1 --' passes _validate_readonly_query as a single SELECT while PostgreSQL executes it as four statements -- the smuggled COMMIT ends the wrapping BEGIN TRANSACTION READ ONLY and the trailing ROLLBACK is a no-op, reintroducing the write/RCE bypass the bf47924 fix was meant to close (reported by Kai Aizen / SnailSploit). Run the LLM-supplied query with prepare=True, forcing psycopg3's extended query protocol. PostgreSQL's own Parse step -- not a client-side approximation of it -- rejects any text containing more than one statement, independent of how it's lexed. Threaded through execute_2darray as an opt-in parameter (default None) so no other caller's behavior changes. Also set SESSION CHARACTERISTICS AS TRANSACTION READ ONLY as defense-in-depth against a smuggled COMMIT. prepare=True alone is not sufficient: psycopg3's PrepareManager.get() returns Prepare.NO -- checked before it even inspects the prepare argument -- whenever the connection's prepare_threshold is None, which is pgAdmin's per-server default ("Prepare threshold" is blank unless an administrator sets it). On a default-configured server the extended protocol never actually engaged, so the bypass stayed live. Force prepare_threshold=0 on the LLM's single-use connection in _connect_readonly() so the extended protocol -- and PostgreSQL's single-statement Parse-step guarantee -- is unconditional on this connection, without touching the server-wide setting or any other session/caller. Adds regression coverage at three levels: unit tests pinning that prepare=True is always passed and that prepare_threshold=0 is forced on the connection; and an end-to-end test driving the real /sqleditor/nlq/chat/<trans_id>/stream route (LLM client mocked, real tool-dispatch path) with the exact smuggled-COMMIT payload, confirming the guarantee holds from the HTTP entry point down to the driver call.
1 parent 362c202 commit ef76102

5 files changed

Lines changed: 509 additions & 32 deletions

File tree

‎web/pgadmin/llm/tests/test_database_tool_security.py‎

Lines changed: 187 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -10,21 +10,32 @@
1010
"""Security regression tests for execute_sql_query input validation.
1111
1212
These tests pin the multi-statement / leading-keyword guard added in
13-
``_validate_readonly_query``. The validator is the load-bearing
14-
defense against an attacker escaping the ``BEGIN TRANSACTION READ
15-
ONLY`` wrapper by emitting a transaction-control statement (COMMIT,
16-
END, ROLLBACK, ABORT) followed by writes -- the original Isaac Chen
17-
report from 2026-06-08.
18-
19-
The tests are pure unit tests (no DB, no Flask client) -- they exercise
20-
the validator directly. Anything that reaches the connection layer is
21-
already too late.
13+
``_validate_readonly_query``. It rejects the original Isaac Chen
14+
report from 2026-06-08 (a transaction-control statement such as COMMIT,
15+
END, ROLLBACK, ABORT followed by writes) whenever sqlparse and
16+
PostgreSQL agree on where the statement boundary is.
17+
18+
``_validate_readonly_query`` is a fast pre-filter, not the security
19+
boundary -- see ``ValidateReadonlyQueryLexerDifferentialTestCase``
20+
below and the module docstring in ``database.py`` for why sqlparse
21+
cannot be the load-bearing check, and ``ExecuteReadonlyQueryProtocolTestCase``
22+
for the protocol-level enforcement that actually is (Kai Aizen /
23+
SnailSploit report, 2026-07-23, on the bf4792444446 fix).
24+
25+
Most of these tests are pure unit tests (no DB, no Flask client) --
26+
they exercise the validator directly. Anything that reaches the
27+
connection layer is already too late for validator, which is exactly
28+
why the protocol-level fix exists.
2229
"""
2330

31+
from unittest.mock import patch, MagicMock
32+
2433
from pgadmin.utils.route import BaseTestGenerator
2534
from pgadmin.llm.tools.database import (
2635
DatabaseToolError,
2736
_validate_readonly_query,
37+
_connect_readonly,
38+
execute_readonly_query,
2839
)
2940

3041

@@ -404,3 +415,170 @@ def runTest(self):
404415
f"Validator accepted query that should have been "
405416
f"rejected: {self.query!r}"
406417
)
418+
419+
420+
class ValidateReadonlyQueryLexerDifferentialTestCase(BaseTestGenerator):
421+
"""Pins a KNOWN, ACCEPTED limitation of the sqlparse-based validator.
422+
423+
sqlparse's string-literal lexing does not always match PostgreSQL's.
424+
Under standard_conforming_strings = on (the default), a backslash
425+
inside a '...'-quoted literal is an ordinary character to PostgreSQL
426+
but sqlparse treats it as escaping the following quote, so it keeps
427+
reading past what PostgreSQL considers the end of the string. That
428+
lets a payload like the one below smuggle ``;COMMIT;<write>;`` past
429+
the "exactly one statement" check disguised as a single string
430+
literal, even though PostgreSQL executes it as four statements --
431+
the report from Kai Aizen / SnailSploit (2026-07-23) against the
432+
bf4792444446 fix.
433+
434+
This is NOT something ``_validate_readonly_query`` can be made to
435+
catch in general (it would require re-implementing PostgreSQL's
436+
lexer, including its dependence on server-side GUCs like
437+
standard_conforming_strings). The test below asserts the validator
438+
accepts the payload -- documenting that this is expected -- and
439+
exists only to point at where the real protection lives: see
440+
ExecuteReadonlyQueryProtocolTestCase, which pins that the query is
441+
always executed with prepare=True. That forces PostgreSQL's own
442+
Parse step -- not a client-side approximation of it -- to reject
443+
any text containing more than one statement, regardless of how it
444+
is lexed.
445+
"""
446+
447+
scenarios = [
448+
('Backslash-quote smuggled COMMIT + DDL', dict(
449+
query=(
450+
"SELECT '\\';COMMIT;CREATE TABLE pwn(x int);"
451+
"SELECT 1 --'"
452+
),
453+
)),
454+
('Backslash-quote smuggled COMMIT + COPY TO PROGRAM', dict(
455+
query=(
456+
"SELECT '\\';COMMIT;COPY (SELECT 1) TO PROGRAM 'id';"
457+
"SELECT 1 --'"
458+
),
459+
)),
460+
]
461+
462+
def setUp(self):
463+
pass
464+
465+
def runTest(self):
466+
# Documents current, accepted behavior -- must NOT raise.
467+
# If this ever starts raising, _validate_readonly_query has
468+
# changed in a way that may be worth understanding, but the
469+
# query is still safe only because of prepare=True downstream;
470+
# don't mistake a change here for the security fix itself.
471+
keyword = _validate_readonly_query(self.query)
472+
self.assertIsNone(keyword)
473+
474+
475+
class ExecuteReadonlyQueryProtocolTestCase(BaseTestGenerator):
476+
"""Pins that execute_readonly_query always runs the LLM's query with
477+
prepare=True.
478+
479+
This is the actual load-bearing defense against statement-boundary
480+
smuggling (see ValidateReadonlyQueryLexerDifferentialTestCase):
481+
prepare=True forces psycopg3's extended query protocol, whose Parse
482+
step is answered by PostgreSQL's own parser and rejects a query
483+
string containing more than one SQL statement -- independent of
484+
sqlparse, and independent of any particular payload shape. Verified
485+
end-to-end against a live PostgreSQL 16 instance during the
486+
SnailSploit report triage (2026-07-23): the smuggled-COMMIT payload
487+
that _validate_readonly_query accepts is rejected by PostgreSQL's
488+
Parse step with "cannot insert multiple commands into a prepared
489+
statement" once prepare=True is set, both before and after the
490+
max_rows LIMIT-wrapping applied to SELECT queries.
491+
492+
This test mocks the connection layer (no DB) and only pins the
493+
wiring: that prepare=True is passed on every call, not just for
494+
inputs that look suspicious. A future refactor that drops the
495+
keyword argument, or only sets it conditionally, must fail this
496+
test.
497+
"""
498+
499+
scenarios = [
500+
('Benign SELECT', dict(
501+
query='SELECT 1',
502+
)),
503+
('Backslash-quote smuggled COMMIT + DDL', dict(
504+
query=(
505+
"SELECT '\\';COMMIT;CREATE TABLE pwn(x int);"
506+
"SELECT 1 --'"
507+
),
508+
)),
509+
]
510+
511+
def setUp(self):
512+
pass
513+
514+
def runTest(self):
515+
mock_manager = MagicMock()
516+
mock_conn = MagicMock()
517+
mock_conn.execute_void.return_value = (True, None)
518+
mock_conn.execute_2darray.return_value = (
519+
True, {'columns': [], 'rows': []}
520+
)
521+
522+
with patch(
523+
'pgadmin.llm.tools.database._get_connection',
524+
return_value=(mock_manager, mock_conn)
525+
), patch(
526+
'pgadmin.llm.tools.database._connect_readonly',
527+
return_value=(True, None)
528+
):
529+
execute_readonly_query(sid=1, did=1, query=self.query)
530+
531+
mock_conn.execute_2darray.assert_called_once()
532+
_args, kwargs = mock_conn.execute_2darray.call_args
533+
self.assertTrue(
534+
kwargs.get('prepare') is True,
535+
"execute_readonly_query must run the LLM-supplied query "
536+
"with prepare=True so PostgreSQL's own Parse step -- not "
537+
"the sqlparse pre-filter -- enforces the single-statement "
538+
"guarantee."
539+
)
540+
541+
542+
class ConnectReadonlyForcesPrepareThresholdTestCase(BaseTestGenerator):
543+
"""Pins that _connect_readonly forces conn.conn.prepare_threshold = 0.
544+
545+
prepare=True on the execute_2darray call is necessary but NOT
546+
sufficient: psycopg3's PrepareManager.get() returns Prepare.NO --
547+
silently falling back to the multi-statement-capable simple query
548+
protocol -- whenever the connection's prepare_threshold is None,
549+
*before* it even inspects the prepare argument. pgAdmin's per-server
550+
"Prepare threshold" field defaults to blank/None, so on a default
551+
server the prepare=True guarantee never engages and the
552+
CVE-2026-12045 smuggled-COMMIT bypass stays live (Kai Aizen /
553+
SnailSploit follow-up, verified live against PostgreSQL on
554+
2026-07-24).
555+
556+
_connect_readonly() therefore forces prepare_threshold = 0 on the
557+
LLM's single-use connection. This test pins that override so a
558+
future refactor that drops it -- reopening the bypass -- fails here,
559+
without needing a live server.
560+
"""
561+
562+
def setUp(self):
563+
pass
564+
565+
def runTest(self):
566+
mock_manager = MagicMock()
567+
mock_conn = MagicMock()
568+
mock_conn.connected.return_value = True
569+
mock_conn.execute_void.return_value = (True, None)
570+
# Emulate pgAdmin's default: extended protocol disabled.
571+
mock_conn.conn.prepare_threshold = None
572+
573+
status, msg = _connect_readonly(
574+
mock_manager, mock_conn, 'llm_test_conn')
575+
576+
self.assertTrue(status, msg)
577+
self.assertEqual(
578+
mock_conn.conn.prepare_threshold, 0,
579+
"_connect_readonly must force prepare_threshold=0 on the "
580+
"LLM connection; otherwise psycopg3 ignores prepare=True "
581+
"(when the server's Prepare threshold is blank/None, the "
582+
"default) and falls back to the multi-statement-capable "
583+
"simple query protocol, reopening CVE-2026-12045."
584+
)

‎web/pgadmin/llm/tools/database.py‎

Lines changed: 79 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -44,10 +44,21 @@
4444
# effective if the LLM cannot exit that transaction. A multi-statement
4545
# payload such as `COMMIT; <write>; SELECT 1` would otherwise terminate
4646
# the read-only transaction (via COMMIT/END/ROLLBACK/ABORT) and run
47-
# subsequent statements in autocommit mode. The allowlist below, in
48-
# combination with the single-statement check, ensures the LLM cannot
49-
# emit transaction-control statements, SET/RESET, DML/DDL, CALL, COPY,
50-
# or anything else that could escape or weaken the read-only sandbox.
47+
# subsequent statements in autocommit mode.
48+
#
49+
# The allowlist below rejects transaction-control statements, SET/RESET,
50+
# DML/DDL, CALL, COPY, and anything else that could weaken the read-only
51+
# sandbox, *for queries sqlparse correctly recognises as a single
52+
# statement*. It is a fast, user-friendly pre-filter, not the boundary
53+
# enforcement: sqlparse's lexer can disagree with PostgreSQL's own
54+
# parser about where one statement ends and another begins (e.g. string
55+
# literal escaping under standard_conforming_strings), so a payload can
56+
# look like one safe statement to sqlparse while PostgreSQL executes it
57+
# as several. The actual guarantee that only one statement reaches the
58+
# server is enforced at the protocol level -- see _execute_readonly_query,
59+
# which runs the query with prepare=True so PostgreSQL's own Parse step
60+
# (not a client-side approximation of it) rejects multi-statement text.
61+
#
5162
# EXPLAIN ANALYZE on a SELECT remains supported; EXPLAIN ANALYZE on a
5263
# write statement is blocked by PostgreSQL itself inside the read-only
5364
# transaction.
@@ -126,6 +137,24 @@ def _connect_readonly(
126137
if not status:
127138
return False, msg
128139

140+
# Force the extended query protocol on this connection,
141+
# regardless of the server's configured "Prepare threshold"
142+
# (which defaults to blank/None, meaning "never prepare"). The
143+
# per-call prepare=True passed to execute_2darray() in
144+
# _execute_readonly_query() is silently ignored by psycopg3
145+
# whenever conn.prepare_threshold is None -- PrepareManager.get()
146+
# checks that before it looks at the prepare argument at all, and
147+
# falls back to the simple query protocol, which is exactly the
148+
# multi-statement-capable protocol this defense must avoid.
149+
# Setting the threshold to 0 ("always prepare") on this
150+
# single-use, per-query connection (see conn_id generation in
151+
# execute_readonly_query) makes the extended protocol -- and
152+
# therefore PostgreSQL's single-statement Parse-step guarantee --
153+
# unconditional here, without touching the server-wide setting or
154+
# any other connection.
155+
if getattr(conn, 'conn', None) is not None:
156+
conn.conn.prepare_threshold = 0
157+
129158
# Set application name via SQL - this is thread-safe and doesn't
130159
# require environment variables. The name will be visible in
131160
# pg_stat_activity to identify LLM connections.
@@ -139,6 +168,16 @@ def _connect_readonly(
139168
# Non-fatal - connection still works without custom app name
140169
pass
141170

171+
# Defense-in-depth: make READ ONLY the session default, not just a
172+
# property of the transaction started by BEGIN TRANSACTION READ
173+
# ONLY. If a statement ever managed to end that transaction early
174+
# (e.g. a smuggled COMMIT), the next transaction on this connection
175+
# -- implicit or explicit -- would still be read-only rather than
176+
# falling back to a writable default.
177+
conn.execute_void(
178+
"SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY"
179+
)
180+
142181
return True, None
143182

144183
except Exception as e:
@@ -166,20 +205,28 @@ def _first_real_keyword(statement) -> str:
166205

167206
def _validate_readonly_query(query: str) -> None:
168207
"""
169-
Ensure an LLM-supplied query is a single, read-only statement.
170-
171-
This is the load-bearing defense against an attacker escaping the
172-
read-only transaction wrapper by emitting multiple statements. The
173-
PostgreSQL simple-query protocol cheerfully runs a whole
174-
semicolon-separated batch in one round-trip, so a payload such as
175-
``COMMIT; <write>; SELECT 1`` would terminate the read-only
176-
transaction (via the leading ``COMMIT``) and execute the remaining
177-
statements in autocommit mode. The trailing ``ROLLBACK`` would then
178-
be a no-op.
208+
Reject LLM-supplied queries that are obviously not a single,
209+
read-only statement.
210+
211+
This is a fast pre-filter, not the security boundary: it rejects
212+
plainly-disallowed statement types and multi-statement input *as
213+
sqlparse's lexer sees it*. sqlparse's notion of where a string
214+
literal (and therefore a statement) ends can disagree with
215+
PostgreSQL's own parser -- e.g. a backslash before a quote is an
216+
escape to sqlparse but an ordinary character to PostgreSQL when
217+
standard_conforming_strings is on (the default) -- so a payload can
218+
pass this check as a single SELECT while PostgreSQL would actually
219+
execute it as several statements, including a COMMIT that ends the
220+
read-only transaction. That class of attack is closed at the
221+
protocol level, not here: see _execute_readonly_query, which
222+
executes the query with prepare=True so PostgreSQL's own Parse step
223+
-- the actual authority on statement boundaries -- rejects any text
224+
containing more than one statement.
179225
180226
Validation rules:
181227
182-
* The input must contain exactly one non-empty statement.
228+
* The input must contain exactly one non-empty statement (as far as
229+
sqlparse can tell).
183230
* The leading keyword must be in :data:`_ALLOWED_LEADING_KEYWORDS`.
184231
185232
PostgreSQL is left to enforce the rest -- ``EXPLAIN ANALYZE`` on a
@@ -242,6 +289,19 @@ def _execute_readonly_query(conn, query: str) -> dict:
242289
The query is wrapped in a read-only transaction to ensure
243290
no data modifications can occur.
244291
292+
The query is executed with prepare=True, and the connection's
293+
prepare_threshold is forced to 0 in _connect_readonly(), which
294+
together force it through PostgreSQL's extended query protocol.
295+
(prepare=True alone is not enough: psycopg3 ignores it whenever
296+
prepare_threshold is None -- pgAdmin's blank-by-default server
297+
setting -- and falls back to the multi-statement-capable simple
298+
query protocol.) The server's Parse step accepts only a single SQL
299+
statement in the extended protocol, so this is what actually
300+
guarantees the LLM cannot smuggle a second statement (e.g. a COMMIT
301+
to end the read-only transaction early) past
302+
_validate_readonly_query's sqlparse-based check -- see the note on
303+
_validate_readonly_query for why that check alone is not sufficient.
304+
245305
Args:
246306
conn: Database connection
247307
query: SQL query to execute
@@ -266,8 +326,10 @@ def _execute_readonly_query(conn, query: str) -> dict:
266326
)
267327

268328
try:
269-
# Execute the actual query
270-
status, result = conn.execute_2darray(query)
329+
# Execute the actual query. prepare=True, combined with the
330+
# prepare_threshold=0 forced in _connect_readonly(), is
331+
# load-bearing -- see the docstring above.
332+
status, result = conn.execute_2darray(query, prepare=True)
271333

272334
if not status:
273335
raise DatabaseToolError(

0 commit comments

Comments
 (0)