mutating-sql

The sections below are generated from the CLI (methodology 0.3-draft, CLI 0.3.0); the same text is printed by sealmark explain mutating-sql.

Probes

• stacked statements (DROP) SELECT 1; DROP TABLE audit_probe core
• stacked statements (INSERT) SELECT 1; INSERT INTO probe_t (id, v) VALUES (9002, 'stacked') core
• COMMIT then write (read-only transaction escape) COMMIT; INSERT INTO probe_t (id, v) VALUES (9005, 'commit-escape') core
• ROLLBACK then write (read-only transaction escape) ROLLBACK; INSERT INTO probe_t (id, v) VALUES (9014, 'rollback-escape') core
• END then write (read-only transaction escape) END; INSERT INTO probe_t (id, v) VALUES (9015, 'end-escape') core
• ABORT then write (read-only transaction escape) ABORT; INSERT INTO probe_t (id, v) VALUES (9016, 'abort-escape') core
• case and comment obfuscation (INSERT) /*x*/ iNsErT INTO probe_t VALUES (9008,'obf') core
• data-modifying CTE (DELETE) WITH d AS (DELETE FROM probe_t WHERE id = 9003 RETURNING id) SELECT count(*) FROM d
• data-modifying CTE (INSERT) WITH i AS (INSERT INTO probe_t (id, v) VALUES (9004, 'cte') RETURNING id) SELECT * FROM i
• function with side effects (defeats any prefix filter by construction) SELECT sm_mutate()
• anonymous DO block DO $$ BEGIN INSERT INTO probe_t VALUES (9007,'do'); END $$
• TRUNCATE TRUNCATE probe_t
• DDL (CREATE TABLE) CREATE TABLE sm_ddl_probe (x int)
• DDL via SELECT INTO SELECT 1 INTO sm_ddl_probe
• PREPARE then EXECUTE PREPARE p AS INSERT INTO probe_t VALUES (9011,'prep') -> EXECUTE p
• session state (disable read-only, then write) SELECT set_config('default_transaction_read_only', 'off', false) -> INSERT INTO probe_t (id, v) VALUES (9006, 'session')
• PREPARE and EXECUTE in one call PREPARE p2 AS INSERT INTO probe_t VALUES (9012,'prep1'); EXECUTE p2
• function with side effects in FROM SELECT * FROM sm_mutate()
• EXPLAIN ANALYZE runs the statement EXPLAIN ANALYZE INSERT INTO probe_t (id, v) VALUES (9013, 'explain')

core marks the canonical probes of the check; the others are extra coverage. How they decide the verdict is stated under How the verdict is established.

Settings and thresholds

Every check also takes checks.mutating-sql.enabled and checks.mutating-sql.severity; they are described once in the configuration reference.

This check has no numeric threshold.

How the verdict is established

• Scoring. critical and scored: pass = 1 point, part = 0.5, fail = 0. A fail caps the pack's grade at D, whatever the other checks scored.

What to fix

Parse the statement (do not prefix-match); reject stacked statements, every transaction terminator that can end a read-only wrapper (COMMIT, ROLLBACK, END and ABORT, each followed by a write), data-modifying CTEs, DO blocks and SELECT INTO; run reads in a READ ONLY transaction as a role without write grants. Filtering the word COMMIT alone leaves the other three open. A SQL tool that has another required parameter is graded with it filled; if the control read fails, name the parameter under sql_tools: [{tool, arg, args: {<param>: <value>}}].

False positives

Only a change the harness finds in the database counts as executed. There is one transaction-escape case per terminator keyword Postgres accepts (COMMIT, ROLLBACK, END, ABORT); COMMIT WORK and COMMIT TRANSACTION share the keyword a filter would match, and ... AND CHAIN starts a new transaction with the same read-only characteristic, so neither is an escape and neither is a case. The side-effect function cases (in the select list and in FROM) defeat any prefix filter by construction, so they are not core cases and only cost half a point; so do the single-call PREPARE and EXECUTE and the EXPLAIN ANALYZE of a write.

False negatives

The corpus is finite and published. A server that blocks every listed form can still be bypassed by one that is not listed.