A DELETE or UPDATE without a WHERE clause applies to every row of the table. If the transaction is still open, run ROLLBACK now and nothing is lost. If it was committed (psql, most drivers and MySQL Workbench autocommit each statement), SQL has no undo: the rows come back only from a backup, point-in-time recovery or, on MySQL, the binary log. Stop the writes to that table first, so what you restore is not overwritten again.
How to recover the data
- Still in a transaction?
ROLLBACK;in the same session undoes it (psql shows an asterisk in the prompt,mydb=*#, while a transaction is open). On a committed statement it changes nothing. - PostgreSQL: restore a base backup with WAL archiving to a separate instance, with
recovery_target_timeset just before the statement, then copy the rows back (pg_dump --data-only -t tablefrom the restored instance). Managed services such as Amazon RDS and Google Cloud SQL do point-in-time restore into a new instance. - MySQL: restore the last full backup to a separate server and replay the binary log with
mysqlbinlog --stop-datetime(or--stop-position) up to just before the statement. With row-based logging (binlog_format=ROW, the default since MySQL 5.7.7) the binary log also holds the changed rows. - No backup at all? On PostgreSQL, deleted rows stay on disk until
VACUUMreclaims them; extensions such aspg_dirtyreadcan read them as a last resort, so do not vacuum that table until you have decided.
Why it happens
The statement had no row filter. It is usually one of: a script whose WHERE was commented out or lost in a copy-paste, a query built from a template where the condition came out empty, an ORM call such as Model.delete() or update_all on an unfiltered scope, or SQL written by an AI agent that took "clean up the old rows" literally. An always-true condition (WHERE 1=1) has the same effect, and is its own rule.
How to fix it
Write the filter first, check how many rows it selects, and run the change inside a transaction you only commit when the count is right:
BEGIN;
SELECT count(*) FROM users WHERE last_login < NOW() - INTERVAL '2 years';
DELETE FROM users WHERE last_login < NOW() - INTERVAL '2 years';
-- the DELETE reports the same count? COMMIT; otherwise ROLLBACK;
COMMIT;For large deletes, delete in batches by primary key. On MySQL, keep sql_safe_updates on for interactive sessions (below). And give the application and any AI agent a database role that only has the privileges it needs.
MySQL Error 1175: safe update mode
MySQL Workbench enables safe update mode by default, so an unfiltered statement fails with:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.That error is the protection working: the statement would have touched every row. The fix is to name the rows by a key column (WHERE id = 42, or WHERE id IN (…)), not to switch the mode off. Safe update mode also accepts a LIMIT, which bounds the damage but does not choose the right rows. It only exists in MySQL sessions that turn it on (SET sql_safe_updates = 1, or mysql --safe-updates); PostgreSQL has no equivalent, and neither applies to the application's or an agent's own connection unless it sets it.
UPDATE without WHERE
An UPDATE products SET price = 0 without WHERE rewrites every row, and recovery is the same as for a DELETE: ROLLBACK before the commit, a backup or point-in-time recovery after it. The rows are still there, so the damage is easy to miss until someone reads them. Vericto reports it as VERICTO-030 (and with the same predicate as VERICTO-042); the CLI output below is for the DELETE, and the 030 page has the UPDATE.
How to stop it from happening again
VERICTO-001 · DELETE without WHERE clause · Severity: Critical · AST node: DeleteStmt > WhereClause = NULL
The statement the rule reports
-- cleanup.sql
DELETE FROM users;
Real CLI output
$ npx @vericto/vericto-cli@2.3.0 check --offline cleanup.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
✖ cleanup.sql BLOCKED VERICTO-001 critical
DeleteStmt > WhereClause = NULL
fix: DELETE FROM users WHERE id = $1
1 blocked · 0 allowed · 0 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
✖ Vericto blocked 1 statement (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
✖ cleanup.sql BLOCKED VERICTO-001 critical
DeleteStmt > WhereClause = NULL
fix: DELETE FROM users WHERE id = $1
This is Vericto enforcing a rule — the exit code is intentional, not a crash.
To allow a finding on purpose, add an inline comment on the statement:
-- vericto:ignore[VERICTO-001] <reason> (a reason is required)
The CLI blocks it and exits with code 1, so the CI job fails before the merge.
The corrected version
-- cleanup.sql
DELETE FROM users WHERE id = 42;
$ npx @vericto/vericto-cli@2.3.0 check --offline cleanup.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
0 blocked · 1 allowed · 0 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
Try it free, no account
The CLI checks your .sql files on your machine with the 42 standard rules: no sign-up, no API key, and no SQL leaves your computer. It is open source (AGPL-3.0-only).
npx @vericto/vericto-cli@2.3.0 check --offline migrations/
On every pull request
The vericto/check-sql GitHub Action (Apache-2.0) runs the same offline check on the .sql files a PR changes and annotates the findings on the PR. Save it as .github/workflows/vericto.yml:
on: pull_request
jobs:
vericto:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v7
with: { fetch-depth: 0 }
- uses: vericto/check-sql@v1
with: { offline: true, upload-sarif: false }
At runtime, for AI agents
The SQL an AI agent writes never goes through a PR. The Vericto TCP proxy checks each statement between the application or agent and the database, and the MCP check_sql tool lets the agent check SQL before it runs it. Both use the runtime profile, which also blocks schema changes.
The AST condition in the engine
The rule is a deterministic predicate over the AST, in src/rules/evaluator.rs of the open source engine (AGPL-3.0-only), at this page's commit:
"VERICTO-001" => find(stmts, |s| {
s.kind == StatementKind::Delete && s.where_presence == WherePresence::Absent
})
.map(|s| violation(rule, s, suggest_delete(s))),
Frequently asked questions
Related rules
- VERICTO-030: UPDATE without WHERE on main table
- VERICTO-003: DELETE with always-true WHERE (1=1, true)
- VERICTO-011: TRUNCATE TABLE
- VERICTO-010: DROP TABLE or DROP DATABASE
More context
- Why an allowlist is not enough: the UPDATE without WHERE case
- An AI agent deleted the production database: how to recover
- When an AI agent runs destructive SQL on production
- Stack Overflow: MySQL error code 1175 during UPDATE in MySQL Workbench
- MySQL manual: using safe-updates mode
- PostgreSQL docs: continuous archiving and point-in-time recovery