DELETE with WHERE 1=1 deleted every row: how to recover

VERICTO-003 Critical In CI (offline CLI): BLOCKED

DELETE FROM users WHERE 1 = 1; deletes every row of users: a condition that is true for every row filters nothing, so the statement is a DELETE without WHERE. If the transaction is still open, run ROLLBACK now. If it was committed, the rows are only in your backups or point-in-time recovery: stop writes and follow the steps below.

How to recover the data

  • If the DELETE ran inside a transaction that is still open, run ROLLBACK. After COMMIT (or with autocommit on), it cannot be rolled back.
  • Stop the writers (application, jobs, agents) so new rows do not mix with the ones you are about to restore.
  • Restore a point-in-time copy from just before the statement (PostgreSQL PITR, or the managed service's point-in-time restore on RDS, Aurora or Cloud SQL) into a separate instance, never over production.
  • Copy the missing rows back by primary key with INSERT ... SELECT from the restored copy. On MySQL without PITR, restore the last backup elsewhere and replay the binary log with mysqlbinlog --stop-datetime up to just before the DELETE.

Why it happens

Query builders often start with WHERE 1=1 and append AND ... for each filter. When the list of filters is empty (a missing parameter, a filter dropped by a refactor, an agent that did not fill it in), only 1=1 is left. Other always-true forms do the same: WHERE id = id, WHERE TRUE, WHERE 1.

How to fix it

Make the code that builds a DELETE refuse to run without at least one real condition, instead of falling back to 1=1. Run destructive statements inside a transaction and check the affected row count before COMMIT:

BEGIN;
DELETE FROM users WHERE id = 42;
-- expect DELETE 1, otherwise ROLLBACK
COMMIT;

How to stop it from happening again

VERICTO-003 · DELETE with always-true WHERE (1=1, true) · Severity: Critical · AST node: DeleteStmt > WhereClause = ALWAYS_TRUE

The statement the rule reports

-- cleanup.sql
DELETE FROM users WHERE 1 = 1;

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-003 critical
    DeleteStmt > WhereClause = ALWAYS_TRUE
    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-003 critical
      DeleteStmt > WhereClause = ALWAYS_TRUE
      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-003] <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-003" => find(stmts, |s| {
    s.kind == StatementKind::Delete && s.where_presence == WherePresence::AlwaysTrue
})
.map(|s| violation(rule, s, suggest_delete(s))),

Frequently asked questions