INSERT INTO ... SELECT copied the whole table: what to check

VERICTO-040 High In CI (offline CLI): BLOCKED

INSERT INTO users_backup SELECT * FROM users; copies every row and every column of users into users_backup. The source is untouched, so nothing was lost, but you now have a second copy of the data, possibly with personal data in a table that has weaker access rules, and a long write that can hold locks and grow storage and backups. Decide whether the copy should exist at all.

How to recover the data

  • If the target was created only for this copy and nothing reads it, drop it (or empty it) once you have confirmed that.
  • If the rows were appended to a table that already had data, identify them (by an id range or a timestamp column) and delete them inside a transaction, checking the count before COMMIT.
  • If the copy holds sensitive columns, check who can read the target table and its schema, and remember that backups taken since then also contain it.

Why it happens

Backfills, ad-hoc "backups" before a risky change and data moves between tables are written quickly, often as SELECT * with no condition. With SELECT * the statement also depends on both tables having the same columns in the same order, so it breaks, or puts values in the wrong columns, as soon as one schema changes.

How to fix it

Name the columns on both sides and filter the source to the rows you need; on a large table, copy in batches by key.

INSERT INTO users_backup (id, email)
SELECT id, email FROM users WHERE created_at < '2026-01-01';

For a real backup before a change, use the database's backup tooling (pg_dump, a snapshot) rather than a copy inside the same database.

How to stop it from happening again

VERICTO-040 · INSERT INTO … SELECT without filter · Severity: High · AST node: InsertStmt > source = SelectStmt (unfiltered)

The statement the rule reports

-- backfill.sql
INSERT INTO users_backup SELECT * FROM users;

Real CLI output

$ npx @vericto/vericto-cli@2.3.0 check --offline backfill.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.
✖ backfill.sql  BLOCKED  VERICTO-040 high
    InsertStmt > source = SelectStmt (unfiltered)
    fix: INSERT INTO users_backup SELECT ... WHERE <condition> LIMIT N
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))
  ✖ backfill.sql  BLOCKED  VERICTO-040 high
      InsertStmt > source = SelectStmt (unfiltered)
      fix: INSERT INTO users_backup SELECT ... WHERE <condition> LIMIT N

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-040] <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

-- backfill.sql
INSERT INTO users_backup (id, email) SELECT id, email FROM users WHERE created_at < '2026-01-01';
$ npx @vericto/vericto-cli@2.3.0 check --offline backfill.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-040" => find(stmts, |s| {
    s.kind == StatementKind::Insert && s.insert_has_select && !s.insert_select_has_filter
})
.map(|s| {
    let mut v = violation(
        rule,
        s,
        Some(format!(
            "INSERT INTO {} SELECT ... WHERE <condition> LIMIT N",
            s.relation.as_deref().unwrap_or("{table}")
        )),
    );
    v.ast_node_path = "InsertStmt > source = SelectStmt (unfiltered)".to_string();
    v
}),

Frequently asked questions