The read-only guard, measured against its own attacks

erp-report-engine 0.7.0 · a reproducible security benchmark

"Read-only" is a claim about a guard, and a guard that inspects a statement's shape while ignoring the functions it calls has not earned it. Every attack below is a perfectly well-formed SELECT — and every one reads a file, opens a socket, runs more SQL, or flips the session. A shape-only guard waves them all through.

28/28

attacks refused

8/8

legitimate reads allowed

SQL dialects

Every case behaves as claimed.

Run it yourself. The number above is computed from the guard, not asserted in prose — so you can reproduce it in one command:

pipx install erp-report-engine
erp-report-engine trust-benchmark

The same corpus is enforced by CI on every commit, so this page can never claim a result the tests don't hold. Or — no install — paste your own SQL into the guard, in your browser →

The same corpus, three ways to guard it

Most tools enforce "read-only" by reading a statement's shape (is the first word SELECT?) or scanning its text for write keywords. Here is how each fares against the exact cases below — computed in this same run, so the contrast is measured, not asserted.

Approach	Attacks refused	Reads allowed
starts-with-SELECT	6/28	8/8	lets 22 attacks through
write-keyword blocklist	9/28	7/8	lets 19 attacks through, and blocks 1 legitimate read
erp-report-engine — this guard	28/28	8/8	checks what each statement calls — refuses every attack, blocks no read

A keyword scan is the worst of both: it still misses the file-reading and socket-opening functions (they contain no write word), yet it blocks a legitimate SELECT 'please delete this note' because the word is in the string. The only guard that clears every attack and every read is the one that inspects what each statement actually calls.

Attacks — 28 well-formed SELECTs that are not reads

Severity	Function	Dialect	What it actually does	Guard
critical	commit_drop	postgres	THE famous one: the read-only transaction ESCAPE that got Anthropic's reference Postgres MCP server archived (Datadog, 2025) - refused as a second statement	refused
critical	cte_insert	postgres	a write hidden inside a CTE, wrapped in a SELECT	refused
critical	dblink	postgres	opens an outbound connection: SSRF and a data-exfiltration channel	refused
critical	drop	postgres	destroys a table	refused
critical	lo_export	postgres	WRITES a server file - a SELECT that mutates the filesystem	refused
critical	load_extension	sqlite	loads a shared library - arbitrary code execution	refused
critical	load_file	mysql	reads a server file into the result set	refused
critical	openrowset	tsql	reads a host file; sqlglot cannot even parse it, so the lexical net catches it	refused
critical	pg_read_file	postgres	reads an arbitrary server file straight off disk	refused
critical	set_config	postgres	switches the read-only session backstop OFF from inside a SELECT	refused
critical	sp_oacreate	tsql	OLE automation: another SQL Server path to arbitrary code	refused
critical	sys_exec	mysql	a MySQL UDF that shells out - command execution where the UDF is installed	refused
critical	trifecta_exfil	postgres	the Supabase 'lethal trifecta' write leg - injected content steering an agent to copy secrets into an attacker-readable row; a guard denies the write a read-only role might not	refused
critical	two_statements	postgres	a second statement smuggled behind a read	refused
critical	update	postgres	the obvious case - mutates rows	refused
critical	xp_cmdshell	tsql	SQL Server shell execution - and Logo, Netsis and Mikro all run on SQL Server	refused
high	comment_obfuscation	postgres	pg_read_file with a comment spliced in to dodge a name scan - the comment guard refuses it first	refused
high	copy_to	postgres	COPY ... TO writes a server file; it isn't a SELECT/WITH, so it never clears the head check	refused
high	into_outfile	mysql	SELECT ... INTO OUTFILE writes the result to a server file - a MySQL exfiltration primitive	refused
high	lock_hint	tsql	an exclusive lock hint escalates a read into a blocker	refused
high	opendatasource	tsql	ad-hoc connection to an attacker-named server	refused
high	openquery	tsql	runs SQL on a linked server - a pivot off the reporting box	refused
high	query_to_xml	postgres	re-enters the executor with an arbitrary SQL string	refused
high	select_into	tsql	SELECT ... INTO creates and populates a new table	refused
medium	benchmark	mysql	CPU-burning denial of service	refused
medium	comment	postgres	a trailing comment: refused as injection hygiene	refused
medium	pg_sleep	postgres	ties up a connection indefinitely - denial of service	refused
medium	sleep	mysql	denial of service; MySQL had no statement timeout to stop it	refused

Legitimate reads — 8 that must be allowed

A guard that blocks real work is useless, so these earn a place in the benchmark too.

	Query	Dialect	Why it's a read	Guard
read	aggregate	postgres	grouped aggregate - the bread and butter of a report	allowed
read	case_coalesce	mysql	CASE and COALESCE - recognised functions, allowed	allowed
read	cte_window	tsql	a CTE with a window function - reads, despite the machinery	allowed
read	date_bucket	postgres	date_trunc + COUNT - the time-bucketing at the heart of a weekly report	allowed
read	join	tsql	a two-table join	allowed
read	literal_keyword	postgres	a write keyword INSIDE a string literal is data, not code	allowed
read	top_ordered	tsql	TOP with ORDER BY - a ranked read, allowed	allowed
read	union	postgres	a UNION of two reads	allowed

Why this is the benchmark that matters

Pointing an autonomous tool — or an AI agent — at a production ERP database is the "lethal trifecta": untrusted input, a tool that can act, and a channel to exfiltrate through. Wrapping queries in a read-only transaction is not enough on its own: a session flag can be flipped (set_config), a transaction can be escaped, and a function can read a file or dial out without writing a single row. The only honest way to claim a guard holds is to measure it against the functions that defeat the shape-only version — which is what this page does, on every commit.

The guard here checks the functions a statement calls, by parse tree and lexically (some of these don't even parse), fails closed when it can't read a query, and — on the agent path — default-denies every function it doesn't recognise. Full model: SECURITY.md.