How to stop UPDATE and DELETE without WHERE in production

You stop a WHERE-less UPDATE or DELETE in production by refusing the statement before it reaches the database, not by hoping the person typing it notices. That takes three layers, because each one fails in a different way: a parse step that rejects the statement, a human who reads what is left, and database privileges that limit the damage when the first two miss.

What does the accident look like?

DELETE FROM orders;

The editor in a database client sends this exactly as it sends the statement with WHERE id = 42. The database sees a valid statement from a user with the right privilege and runs it. A role that may DELETE cannot tell a careful one-row change from a full-table one; that is the gap between “who may act” and “what the statement does”, described in what database access control is.

Does MySQL safe updates solve it?

Partly. The MySQL 8.4 manual describes --safe-updates as helpful when “you might have issued an UPDATE or DELETE statement but forgotten the WHERE clause”. With sql_safe_updates enabled, such statements “produce an error if they do not specify a key constraint in the WHERE clause, or provide a LIMIT clause, or both”. (Read on 2026-10-13.)

Three limits matter in production:

• It is a per-session setting. It protects the person whose session has it enabled, not a connection that a script or another tool opens without it.
• A LIMIT satisfies it. DELETE FROM orders LIMIT 100000 passes the rule and still removes a hundred thousand rows.
• The manual notes that a statement can raise an error even with a key in the WHERE clause, if the optimizer decides not to use the index. A rule that fires on correct statements gets switched off.

Use it for people working in a terminal. Do not make it the only control. The same is true of production settings in GUI clients; the DBeaver and DataGrip comparison covers what those guard and what they leave open.

What does parsing the statement add?

A guard that sits in front of the connection can apply the rule to every statement, whichever tool wrote it. QueryProxy parses each submission before it can enter the approval queue. As the SQL guards reference puts it, an UPDATE or DELETE without a WHERE is rejected at submission, and a WHERE clause that is statically always true counts as missing:

DELETE FROM users;                        -- rejected
DELETE FROM users WHERE 1 = 1;            -- rejected
DELETE FROM users WHERE true;             -- rejected
DELETE FROM users WHERE id = 1 OR 1 = 1;  -- rejected
DELETE FROM users WHERE id = 42;          -- allowed, still needs approval

The rule also applies inside transactions, CTE bodies, EXPLAIN ANALYZE and MariaDB ANALYZE. There is no override: a statement with no WHERE, or an always-true one, never reaches a reviewer (PostgreSQL DO blocks, below, are the exception).

Two details are worth knowing before you rely on it:

• Several statements need an explicit transaction. Two bare UPDATE statements in one request are rejected. Inside BEGIN … COMMIT they run atomically, and if any statement fails everything rolls back.
• The check is static. It evaluates literals, comparisons, AND, OR, NOT and parentheses. An operand it cannot evaluate, such as a column or a function, is never counted as true. And PostgreSQL DO blocks are exempt from this rule: they are classified as a write and always go to a DBA, who sees the full body.

Why is a guard not enough?

Because a denylist is incomplete by nature. The same reference says the list is “structurally incomplete” and that what authorizes a statement is the DBA approval, a person who sees the prepared SQL and decides whether it should run at all. That is the second layer. Read how a request moves in the SQL query approval workflow.

The third layer is the database itself. QueryProxy’s production hardening guide recommends a separate connection with SELECT-only privileges for people who only read, and notes that read-only execution is not available yet (planned for v0.3.0). The SQL guards coverage table lists a database-side statement timeout as not covered yet either, also planned for v0.3.0. Until then, privileges are your database-side bound. Engine specifics are in PostgreSQL access control and MySQL and MariaDB access control.

What if it happens anyway?

Then it is a recovery problem. PostgreSQL 18.6 documents continuous archiving and point-in-time recovery: “it is possible to restore the database to its state at any time since your base backup was taken.” That only works if archiving was set up before the mistake. (Read on 2026-10-13.)

When is this overkill?

If one person owns a small database and every change goes through reviewed migrations, a parse step in front of the connection adds little. The case for it is many people with production access and statements typed by hand. For getting started, the quick start runs QueryProxy with a single docker run.