Skip to main content

GEN.L1.DML_WITHOUT_WHERE — the predicate you meant to type

  • Category: safety
  • Level: 1
  • Confidence: deterministic
  • Downtime class: online
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18 and MySQL 8.4

An UPDATE or DELETE with no WHERE clause changes or removes every row. Nothing about the statement is malformed, so nothing stops it — a forgotten predicate turns a targeted fix into a whole-table sweep, and the first sign is usually the row count in the deploy log.

Two problems, one statement​

The obvious one is scope: the wrong rows were touched, and in a migration there is no undo.

The quieter one is lock volume. A predicate-free write takes a row lock on every row in the table and holds them all until the migration's transaction commits. On a large table that is enough to stall unrelated traffic that happens to touch the same rows — and on MySQL it is also enough to blow past the undo-log size the server was tuned for.

Flagged​

DB::statement('DELETE FROM audit_log');

Preferred​

// Scope the write, and for a large table delete in bounded batches:
DB::table('audit_log')->where('created_at', '<', now()->subDays(30))->limit(1000)->delete();

Batching is not decoration. A bounded write takes bounded locks, produces a bounded amount of undo, and — when something goes wrong — leaves a bounded amount of mess.

When you really do mean every row​

Say so, and say why:

#[SqlensAllowDestructive('audit_log is rebuilt from the event stream on every import')]
final class ResetAuditLog extends Migration { /* … */ }

That reads differently from a missing WHERE, which is exactly the point — to a reviewer and to the next person to touch the file.

  • PG.L1.TRUNCATE / MY.L1.TRUNCATE — emptying a table with a statement that cannot take a predicate. Different rule, because there is no forgotten WHERE to point at.
  • GEN.L3.DML_ON_SCHEMA_CHANGED_TABLE — the neighboring mistake of writing data in the same migration that changed the schema.

Sources​

The fix material this rule carries​

A finding from this rule carries a payload whose strategy is none: this rule has looked, and there is no safe standard sequence. That is a conclusion rather than an omission — a finding with no payload at all says only that nobody wrote one.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.