# How to stop UPDATE and DELETE without WHERE in production > How to block UPDATE and DELETE statements without a WHERE clause in production, from MySQL safe updates to parsing, approval and database privileges. - Published: 2026-10-13 - Tags: sql-guards, access-control - Source: https://queryproxy.com/blog/prevent-update-delete-without-where/ - Language: en-US - Author: Muhammet Şafak --- 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? ```sql 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](/what-is-database-access-control/). ## Does MySQL safe updates solve it? Partly. The MySQL 8.4 manual [describes `--safe-updates`](https://dev.mysql.com/doc/refman/8.4/en/mysql-tips.html) 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](/compare/dbeaver-datagrip/) 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](/docs/sql-guards/) 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: ```sql 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](/sql-query-approval-workflow/). The third layer is the database itself. QueryProxy's [production hardening guide](/docs/production-hardening/) 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](/docs/sql-guards/#coverage) 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](/postgresql-access-control/) and [MySQL and MariaDB access control](/mysql-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](https://www.postgresql.org/docs/18/continuous-archiving.html): "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](/docs/quickstart/) runs QueryProxy with a single `docker run`.