sql-bulk-delete-warn

Warn when a destructive SQL command (DELETE / UPDATE / TRUNCATE) is about to run via psql, mysql, sqlite3, sqlcmd, cockroach sql, or duckdb without a row-count safeguard.

Motivated by claude-code issue #56738 (2026-05-06): a regex with capture groups in regexp_match returned NULL for nearly every row, and the subsequent DELETE FROM table WHERE _clean_title IS NULL wiped 24,472 of 24,475 rows. autovacuum cleaned the dead tuples within ~5 minutes, blocking pg_dirtyread recovery. Three days of scraping work permanently lost.

Event Configuration

• Event Type: PreToolUse
• Tool Matcher: Bash
• Category: security

Detected high-risk patterns

1. DELETE / UPDATE with no WHERE clause (full-table touch)
2. DELETE WHERE col IS NULL — the issue #56738 pattern: a NULL arising from a computed column populated by a prior UPDATE that may have failed silently
3. TRUNCATE TABLE (non-recoverable in some engines)
4. psql -c with DELETE/UPDATE/TRUNCATE and no BEGIN/START TRANSACTION (autocommit may persist before verification)

Environment Variables

• CC_SQL_BULK_DELETE_BLOCK — set to 1 to convert warnings into a hard block (exit 2) instead of advisory output. Defaults to advisory.

Requirements

• jq (for parsing the hook input JSON)

Script

COMMAND=$(cat | jq -r '.tool_input.command // empty' 2>/dev/null)
[ -z "$COMMAND" ] && exit 0

# Skip non-SQL invocations
if ! echo "$COMMAND" | grep -qiE '(^|[ \t/])(psql|mysql|sqlite3?|sqlcmd|cockroach\s+sql|duckdb)\b'; then
    exit 0
fi

# Skip pure SELECT / EXPLAIN / DESCRIBE invocations
if ! echo "$COMMAND" | grep -qiE '\b(DELETE|UPDATE|TRUNCATE|DROP)\b'; then
    exit 0
fi

WARN=""

# Pattern 1: DELETE / UPDATE without WHERE
if echo "$COMMAND" | grep -qiE '\b(DELETE\s+FROM|UPDATE)\s+[a-zA-Z_][a-zA-Z0-9_."]*\s*(;|$)'; then
    WARN="${WARN}WARNING: DELETE/UPDATE without a WHERE clause detected (full-table touch).
"
fi
if echo "$COMMAND" | grep -qiE '\b(DELETE\s+FROM|UPDATE)\s+[a-zA-Z_][a-zA-Z0-9_."]*\s+(SET\s+[^;]*\s*;|;|$)' && \
   ! echo "$COMMAND" | grep -qiE '\bWHERE\b'; then
    WARN="${WARN}WARNING: DELETE/UPDATE without WHERE detected (full-table touch).
"
fi

# Pattern 2: DELETE WHERE column IS NULL (Issue #56738 pattern)
if echo "$COMMAND" | grep -qiE 'DELETE\s+FROM\s+[a-zA-Z_][a-zA-Z0-9_."]*\s+WHERE\s+[a-zA-Z_][a-zA-Z0-9_.]*\s+IS\s+NULL'; then
    WARN="${WARN}WARNING: 'DELETE WHERE col IS NULL' pattern detected. If the column was just populated by a regex/UPDATE, a silent NULL on most rows wipes nearly the whole table (Issue #56738: 24,472 of 24,475 rows lost).
SUGGESTION: run 'SELECT COUNT(*) FROM <table> WHERE <col> IS NULL' first; if the count is unexpectedly high, the populating step failed.
"
fi

# Pattern 3: TRUNCATE
if echo "$COMMAND" | grep -qiE '\bTRUNCATE\s+TABLE\b'; then
    WARN="${WARN}WARNING: TRUNCATE TABLE detected. This bypasses transaction logs in some engines and is non-recoverable.
"
fi

# Pattern 4: psql -c inline with DELETE/UPDATE (no transaction wrapping)
if echo "$COMMAND" | grep -qiE 'psql\s+.*-c\s+["\047][^"\047]*\b(DELETE|UPDATE|TRUNCATE)\b' && \
   ! echo "$COMMAND" | grep -qiE '\b(BEGIN|START\s+TRANSACTION)\b'; then
    WARN="${WARN}WARNING: psql -c with DELETE/UPDATE/TRUNCATE has no explicit transaction. autocommit may persist the change before you can verify.
SUGGESTION: wrap in BEGIN; ... ROLLBACK; first to check row counts before COMMIT.
"
fi

if [ -n "$WARN" ]; then
    printf '%s' "$WARN" >&2
    if [ "${CC_SQL_BULK_DELETE_BLOCK:-0}" = "1" ]; then
        echo "BLOCKED by sql-bulk-delete-warn (strict mode). Unset CC_SQL_BULK_DELETE_BLOCK to convert to advisory." >&2
        exit 2
    fi
fi

exit 0

Source attribution

This hook was originally written for and ships in cc-safe-setup (MIT license). It is contributed here in single-file Markdown form so buildwithclaude users can install it standalone without taking the full cc-safe-setup dependency.