SQL guards

Every submission is parsed and inspected before it can enter the approval queue. A request that violates a guard is rejected at submission with an explicit message — it never reaches a DBA, let alone a database.

The rules

UPDATE / DELETE require a WHERE

DELETE FROM users;                    -- ✗ rejected
DELETE FROM users WHERE id = 42;      -- ✓ allowed (still needs approval)

This applies inside transactions too. A WHERE clause that is statically always true counts as missing:

DELETE FROM users WHERE 1 = 1;        -- ✗ rejected
DELETE FROM users WHERE true;         -- ✗ rejected
DELETE FROM users WHERE id = 1 OR 1 = 1;  -- ✗ rejected

The check evaluates literals (numbers, plain strings, TRUE, FALSE, NULL), comparisons, IS [NOT] NULL|TRUE|FALSE, AND, OR, NOT and parentheses. An operand it cannot evaluate (a column, a function) is never counted as true: the detection is static and best-effort. A WHERE clause nested too deeply or too large to evaluate is rejected too (… with a WHERE clause too complex to verify is not allowed). There is no override for this rule. Because the detection is static, it stops the common mistakes; it does not prove that a write touches only a few rows.

The rule also applies to the statement inside EXPLAIN ANALYZE and MariaDB ANALYZE, inside CTE bodies, T-SQL batches and dynamic SQL. PostgreSQL DO blocks are the exception: they are not inspected for this rule, are classified as a write, and always go through human approval, where the reviewer sees the full body. Dynamic SQL inside a DO body (PL/pgSQL EXECUTE) is the other side of that bargain: the statement it runs is built at run time and cannot be read, so it is rejected outright (see Judged by the dangerous part).

SELECT gets a LIMIT

• No LIMIT? The default (1000) is injected automatically, on its own line so a trailing line comment can never swallow it.
• LIMIT above the hard cap (10000)? It is clamped down.
• LIMIT ALL (PostgreSQL) counts as unlimited and is clamped.
• Offsets are preserved: LIMIT 10, 20000 becomes LIMIT 10, 10000, LIMIT 99999 OFFSET 4 becomes LIMIT 10000 OFFSET 4.
• FETCH FIRST n ROWS ONLY and T-SQL TOP n are clamped the same way.
• SQL Server targets get no injected LIMIT (it is not valid T-SQL); the guard works on TOP instead. An existing TOP n / TOP (n) is clamped to the hard cap (10000), and a top-level SELECT without one gets TOP (1000) (the default) after any DISTINCT / ALL. TOP … PERCENT, TOP (@v), expressions and decimals are rejected. For WITH, OFFSET, UNION, EXCEPT and INTERSECT no TOP is added; the worker’s row ceiling (the hard cap) bounds those, with the result marked as truncated.
• A LIMIT the guard cannot understand as a plain row count (arithmetic like LIMIT 100*100000, expressions, placeholders) is rejected, not left unclamped — a limit that can’t be verified can’t be enforced.
• Subquery LIMITs are left alone; only the outer query is guarded. Locking clauses are respected — the injected LIMIT lands before FOR UPDATE.

On top of this, the worker enforces an absolute row ceiling (the hard cap) while streaming results, independent of the SQL — so even a query that dodges the clause-level guard (dialect quirks, SHOW, EXPLAIN) can never return more than the cap; the stored result is flagged as truncated.

Both numbers are configurable — see Configuration. The prepared SQL (with the injected LIMIT) is shown to the developer before submission and to the DBA at review; what you approve is exactly what runs.

Multiple statements need an explicit transaction

UPDATE a SET x = 1 WHERE id = 1;
UPDATE b SET y = 2 WHERE id = 2;      -- ✗ rejected: two bare statements

BEGIN;
UPDATE a SET x = 1 WHERE id = 1;
UPDATE b SET y = 2 WHERE id = 2;
COMMIT;                               -- ✓ allowed, runs atomically

The worker wraps the statements in a real transaction: if any statement fails, everything rolls back. ROLLBACK in a submission is rejected — rollback on failure is automatic, not something to hand-write. Nested transactions are not supported.

Always blocked

These statements hand out server-level code execution or file access, and have no everyday equivalent through a query portal. They are refused outright, whatever the role:

• DROP DATABASE / DROP SCHEMA
• GRANT, REVOKE, DENY
• CREATE USER / ALTER USER / DROP USER (and ROLE / LOGIN variants)
• CREATE FUNCTION … SONAME — loadable UDFs (and the CREATE AGGREGATE FUNCTION form)
• SHUTDOWN
• Server-side file / program IO: SELECT … INTO OUTFILE / INTO DUMPFILE, LOAD DATA, PostgreSQL COPY (including COPY … TO PROGRAM), the LOAD_FILE() function, and PostgreSQL’s file functions pg_read_file(), pg_read_binary_file(), pg_ls_dir(), lo_import() and lo_export() — schema-qualified calls (pg_catalog.pg_read_file(…)) included. Only the call form is caught; a column or alias with the same name is not blocked.
• PostgreSQL functions that run SQL handed to them as a string, which the guard cannot read: query_to_xml, query_to_xmlschema, query_to_xml_and_xmlschema, xpath_table, ts_stat, ts_rewrite, crosstab* and connectby.

These checks run against a comment-normalized form of the statement, so tricks like DROP/**/DATABASE or a leading /* … */ comment cannot smuggle a blocked statement past them.

DROP TABLE, TRUNCATE and ALTER are not blocked outright — they run as approval-gated writes, but a request containing DDL is flagged at submission: a DDL badge shows in the approval queue and on the request detail page, and the Slack and Teams submission notifications carry a DDL marker (the later approved/rejected notifications do not). A request without DDL shows no flag.

Database administration belongs in your infrastructure tooling, not in a query portal.

Judged by the dangerous part, not the keyword

Three statements are refused only for what is inside them, because their syntax also covers ordinary DBA work that the guard must not take away.

CREATE EXTENSION is judged by the extension name. Untrusted procedural languages run as the database superuser by definition and are refused: plpythonu, plpython2u, plpython3u, plperlu, pltclu, plsh, plr, plluau, plrubyu, plphpu, pljavau — plus anything matching PostgreSQL’s pl…u naming convention for an untrusted language, so a binding nobody has listed yet (plpython4u) is refused on sight. Everything else installs normally: pg_stat_statements, pgcrypto, uuid-ossp, postgis, plv8, plpgsql. A CREATE EXTENSION whose name cannot be read is refused.

DO blocks and routine bodies. A PostgreSQL DO block, CREATE FUNCTION or CREATE PROCEDURE carries a body in another language, so the guard reads the body with PostgreSQL’s own lexical rules (comments, '…', E'…', U&'…', quoted identifiers and nested dollar-quoted strings), not MySQL’s, and applies the same blocked-function and guarded-setting rules as to top-level SQL. So DO $$ BEGIN DROP DATABASE prod; END $$ and a pg_read_file(…) call hidden behind a backslash or a comment do not get through. On top of that:

• Only the plpgsql and sql languages are accepted. A DO block without a LANGUAGE clause runs in PostgreSQL’s default, plpgsql; a CREATE FUNCTION / PROCEDURE needs an explicit LANGUAGE plpgsql or sql unless it has a SQL-standard RETURN body. Any other language is refused, trusted or not, and so is one the guard cannot read as a plain name; a quoted name keeps its case, so LANGUAGE "PLPGSQL" is refused. CREATE LANGUAGE and ALTER LANGUAGE … RENAME are refused as well.
• A body that uses dynamic SQL (EXECUTE) is refused, trigger functions included, because the statement it runs is built at run time. Trigger EXECUTE FUNCTION|PROCEDURE clauses are not affected.
• BEGIN ATOMIC bodies, and CREATE FUNCTION / CREATE PROCEDURE without a body, are refused.
• A SET clause on a routine (CREATE/ALTER FUNCTION|PROCEDURE … SET), a database (ALTER DATABASE … SET) or a role (ALTER ROLE|USER … SET) goes through the guarded-setting rule below. If the setting cannot be resolved, the statement is refused.

SET is judged by the variable name. Refused are the variables that name a file path, load code into the server process or switch a protection off.

• MySQL and MariaDB: general_log, general_log_file, slow_query_log, slow_query_log_file, log_error, init_file, init_connect, plugin_dir, plugin_load, plugin_load_add, secure_file_priv, local_infile. Only the persistent scopes are judged — GLOBAL, PERSIST and PERSIST_ONLY, in both the keyword form (SET GLOBAL x = …) and the variable form (SET @@GLOBAL.x = …). A SESSION write is not guarded, except sql_mode and the read-only switch (see below), and @name is a user variable, never a server setting. SET STATEMENT … FOR <statement> (MariaDB) is judged twice: its assignments by the rule above, and the statement after FOR as if it had been submitted on its own.
• PostgreSQL: session_preload_libraries, local_preload_libraries, dynamic_library_path, default_transaction_read_only, transaction_read_only, session_replication_role, allow_system_table_mods, lo_compat_privileges, zero_damaged_pages, ignore_checksum_failure, role, session_authorization. These are refused in every scope — SET x, SET LOCAL x, SET SESSION x, SET x TO … — because a session-level change is exactly what they are abused for. The same applies when the target database is unknown.
• Every driver: set_config('<name>', …) is refused for any listed name, with either value of the third argument.
• Every driver, every scope: sql_mode. NO_BACKSLASH_ESCAPES and ANSI_QUOTES change what a backslash and a double quote mean, which the guard cannot follow, so SET sql_mode, SET SESSION sql_mode, SET @@sql_mode and set_config('sql_mode', …) are refused. Reading it passes. See the next section for the matching check at execution time.

Names are matched exactly (case-insensitively, no wildcards), so max_connections, wait_timeout, time_zone and, on PostgreSQL, work_mem and search_path pass.

These two lists can be extended for your installation with QUERYPROXY_EXTRA_UNTRUSTED_LANGUAGES and QUERYPROXY_EXTRA_DANGEROUS_VARIABLES. The function, procedure and SQLite pragma lists have their own QUERYPROXY_EXTRA_* settings. See Configuration. They can only add: the built-in entries are the floor, and configuration cannot shorten them.

Identity, catalog and read-only protections

A session that can change who it is, rewrite the server’s own tables or lift its read-only state can undo every other rule here. These are refused in every position the guard reads SQL: top-level, inside DO and routine bodies, PREPARE and EXECUTE IMMEDIATE text, CTE bodies, EXPLAIN ANALYZE, MariaDB ANALYZE and SET STATEMENT … FOR. Known limits lists the statements this does not cover. The last item below is the exception: it is checked when the request runs, not when it is submitted.

• Role and user switches. SET [SESSION|LOCAL] ROLE, RESET ROLE, SET … SESSION AUTHORIZATION, RESET SESSION AUTHORIZATION (quoted names such as RESET "role" included), MySQL and MariaDB SET ROLE / SET DEFAULT ROLE, and set_config('role', …) / set_config('session_authorization', …). role and session_authorization are on the built-in list of guarded settings.
• ALTER SYSTEM on PostgreSQL, whatever it sets.
• Writes to the system catalog. INSERT, UPDATE, DELETE and MERGE against pg_catalog, pg_toast and pg_… tables on PostgreSQL; the same statements and REPLACE against the mysql schema on MySQL and MariaDB. Qualified and quoted spellings are covered. The rule judges the write target, so UPDATE t SET … FROM pg_proc and INSERT INTO t SELECT … FROM pg_proc pass. On MySQL and MariaDB a multi-table UPDATE / DELETE that only reads a mysql.* table is refused as well, because the guard cannot tell which table is written. Reading the catalog (SELECT * FROM pg_proc, SELECT rolname FROM pg_roles) is unaffected. On MySQL and MariaDB USE mysql is refused too (also inside PREPARE, EXECUTE IMMEDIATE and SET STATEMENT … FOR), so that a name without a schema cannot be resolved against the catalog.
• READ WRITE in SET TRANSACTION, SET SESSION CHARACTERISTICS, BEGIN and START TRANSACTION. READ ONLY passes.
• The MySQL / MariaDB read-only switch. Writing transaction_read_only or tx_read_only is refused in every scope and spelling (SESSION, GLOBAL, @@, @@session., backtick, inside a list, SET STATEMENT). transaction_isolation and SET TRANSACTION READ ONLY pass. Some statements are not inspected for it; see Known limits.
• PostgreSQL ALTER DATABASE|ROLE|USER … RESET of a guarded setting or ALL, and the action of a CREATE [OR REPLACE] RULE … DO [ALSO|INSTEAD] <action>, which is judged by the same rules as a statement submitted on its own. A rule whose DO action the guard cannot find is refused.
• SQL Server impersonation. EXECUTE AS, EXEC AS, SETUSER and REVERT, also inside EXEC('…') and sp_executesql, and WITH EXECUTE AS in object definitions. A column named execute_as and the same words inside a string literal are not affected.
• A MySQL / MariaDB write on a connection whose default database is mysql. A table name without a schema resolves against the session’s default database, so with mysql as the default an UPDATE user SET … would edit the account table. The executor reads DATABASE() on the connection before the first statement of a write and fails the request, without running anything, when it is mysql. Point the connection at the application database. A single-statement read and a connection with no default database are not checked.

Text the guard reads differently from the server

Some text is read differently by the guard’s lexer and by the database. Rather than guess, the guard rejects it or inspects it under both readings:

• SQL that is not valid UTF-8 (SQL must be valid UTF-8.), or in which the lexer finds no statement (QueryProxy could not read this SQL: …).
• A byte-order mark or Unicode whitespace (U+FEFF, U+0085, U+00A0, U+1680, U+180E, U+2000–U+200B, U+2028, U+2029, U+202F, U+205F, U+3000) outside string literals, quoted identifiers and comments, on every driver and inside dynamic SQL. The lexer glues such a character to the neighbouring word, while a server may treat it as a separator.
• MariaDB executable comments (/*M!100000 … */). On the mariadb driver the body is read as SQL and nesting deeper than 3 levels is rejected. On the mysql driver /*M! stays a plain comment, so connect a MariaDB server with the mariadb driver. When the target database is unknown, /*M! is rejected outright.
• Version-conditional comments (/*!NNNNN … */) on the mysql and mariadb drivers. A comment like this is checked twice, once as if the server runs the body and once as if it skips it; the statement is rejected if either reading is refused or if the two readings differ in statement count, transaction state, type or prepared text. Also rejected (a change from earlier releases): different versions in one request (SELECT /*!40101 1 */, /*!50503 2 */ is one), a version of other than 5 digits (5 or 6 on MariaDB), a versioned comment nested in or holding another comment, and one with */ inside a string or line comment in its body.
• sql_mode on MySQL and MariaDB. Besides refusing writes to it (above), the executor reads @@SESSION.sql_mode before it runs the SQL and fails the request, without running anything, when it contains NO_BACKSLASH_ESCAPES or ANSI_QUOTES. QueryProxy sets a strict sql_mode on every connection it opens, which replaces the server’s default, so today the check only fires if that setting changes or something in between alters the mode; a connection that does end up with either flag is refused until the flag is removed. Other drivers have no such check.
• PostgreSQL backslashes and #. A backslash is allowed only in comments, dollar-quoted bodies, E'…' strings and well-formed U& strings; anywhere else (including a plain string such as 'a\') it is rejected, because PostgreSQL and MySQL disagree on what it means. # is rejected outside literals, quoted identifiers and comments. The same rules apply when the target database is unknown; MySQL and MariaDB are unaffected.

What the denylist is not

This list is structurally incomplete, and always will be. It enumerates dangerous syntax that is known today across several dialects; a spelling it does not name — a vendor extension, a function alias, a future server feature — passes it. Every entry above exists because someone found a way around an earlier rule, and that process does not end.

So treat it as one layer of defence in depth, never as the authorization boundary. What actually authorizes a statement is the DBA approval: a human who can see the prepared SQL and decide whether it should run at all. The denylist only removes the most obviously abusive requests before they ever reach that person. (A new gap is still a valid security report; see the security policy.)

That goes double for the DO body scan. It reads SQL written out in plain text; it cannot read SQL the block assembles at run time, which is why dynamic SQL (EXECUTE) in a DO or routine body is refused rather than scanned. What stays outside its reach is the rest of what a block can do: a DO block in plpgsql is still arbitrary server-side code, and a plainly written DELETE without a WHERE in its body passes, because blocks are exempt from that rule and decided at approval. What the scan buys is the removal of the direct superuser-code-execution paths and of the plainly written forbidden statement; it is the last net under an approval that should not have been given, not a bound on what an approved block may do.

Coverage

What the guard does with each construct, per dialect. Blocked means the request is rejected. Classified as write means it is not rejected but runs as an approval-gated write (a DELETE / UPDATE still needs its WHERE). Read is a plain read. Not covered means the guard does nothing about it today. Behaviour is verified against the guard’s unit tests. For PostgreSQL, the verdict and its effect on the server are also checked against a live server (the live-pgsql test group). The repository’s Integration workflow runs checks of the read-only session, timeouts, TLS and result streaming against real PostgreSQL, MySQL, MariaDB and SQL Server servers. Rows about the database side describe what the executor sets; they do not make the guard a boundary on its own.

PostgreSQL

MySQL / MariaDB

SQL Server

SQLite

All dialects

Known limits

The guard does not cover everything. These cases are known and left as they are. The guard is no substitute for approval or for database privileges.

• PostgreSQL catalog names. Only pg_catalog, pg_toast and an unqualified pg_… name are judged as catalog tables. A table in pg_temp or in another schema (public.pg_x) is treated as a user table.
• SQLite and SQL Server. Writes to their catalog tables (sqlite_master, sys.…) are not covered by the catalog rule.
• Catalog DDL. The catalog rule judges INSERT, UPDATE, DELETE, MERGE and REPLACE. TRUNCATE, DROP and ALTER on a catalog table are outside it.
• MySQL and MariaDB stored code. The SET statements inside the body of a CREATE PROCEDURE, CREATE TRIGGER or CREATE EVENT … DO are not inspected.
• MySQL and MariaDB SET PASSWORD lists. None of the SET rules above (guarded variables, sql_mode, the read-only switch) is applied to the further assignments in a SET PASSWORD statement. The statement is not checked as a SET.

Because of these limits, the guard is not a security boundary on its own. Give the database user QueryProxy connects with only the privileges it needs (least privilege).

Dialects and the conservative fallback

The parser is MySQL-dialect-first. Statements it cannot fully parse (PostgreSQL casts like ::jsonb, driver-specific operators) are still guarded best-effort by keyword: an unparseable UPDATE without WHERE is still rejected, an unparseable SELECT still gets a LIMIT appended — and anything unclassifiable is treated as a write, which means full approval scrutiny. When in doubt, QueryProxy errs on the strict side.