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 never guarded, 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. 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.

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); MySQL, MariaDB and SQL Server have not been run against live servers.

PostgreSQL

MySQL / MariaDB

SQL Server

SQLite

All dialects

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.