WHERE Detection

Protect against accidental mass updates or deletes by detecting DELETE and UPDATE statements that are missing a WHERE clause.

DBCode automatically detects DELETE and UPDATE statements that are missing a WHERE clause before they execute. This prevents accidental mass data modifications that could affect every row in a table.

How It Works

When you execute a DELETE or UPDATE statement without a WHERE clause, DBCode intercepts the query and shows a warning dialog before execution. This gives you a chance to cancel the operation and add the appropriate filter.

For example, the following statements would trigger the warning:

-- Missing WHERE on DELETE - will affect ALL rows
DELETE FROM users;


-- Missing WHERE on UPDATE - will affect ALL rows
UPDATE orders SET status = 'cancelled';

While these statements execute normally without a warning:

-- Has WHERE clause - targets specific rows
DELETE FROM users WHERE id = 5;


-- Has WHERE clause - targets specific rows
UPDATE orders SET status = 'cancelled' WHERE order_id = 123;

Detection Behavior

StatementDetected
DELETE FROM tableYes
DELETE FROM table WHERE ...No
UPDATE table SET ...Yes
UPDATE table SET ... WHERE ...No
SELECT * FROM tableNo
INSERT INTO table ...No

Subquery Awareness

The detection is aware of subqueries and only checks for WHERE at the top level of the statement. A WHERE inside a subquery does not count:

-- Still flagged - the WHERE is inside the subquery, not on the UPDATE
UPDATE orders SET total = (SELECT SUM(amount) FROM items WHERE order_id = orders.id);


-- Not flagged - has a top-level WHERE clause
DELETE FROM users WHERE id IN (SELECT user_id FROM inactive);

CTE Support

Common Table Expressions (CTEs) are handled correctly:

-- Flagged - DELETE has no WHERE clause
WITH cte AS (SELECT id FROM temp) DELETE FROM users;


-- Not flagged - DELETE has a WHERE clause
WITH cte AS (SELECT id FROM temp) DELETE FROM users WHERE id IN (SELECT id FROM cte);

Availability

Pro Users

Missing WHERE detection runs automatically on every query execution. When combined with Connection Roles, the behavior is configurable per role:

PermissionBehavior
AllowNo detection; statements execute without checking
Ask (default)Warning dialog appears; you choose to proceed or cancel
DenyStatements are blocked entirely

The default settings for each role:

RoleMissing WHERE
DevelopmentAsk
TestingAsk
ProductionDeny

Core Users

Missing WHERE detection runs once per session as a preview of the feature. After the first detection, a notification will prompt you to upgrade to Pro for continuous protection.

Configuring Missing WHERE Detection

With Connection Roles

  1. Open Settings in VS Code
  2. Navigate to Extensions > DBCode > Connection Roles
  3. Find the role assigned to your connection
  4. Set the Missing WHERE permission to Allow, Ask, or Deny

Without Connection Roles

When no role is assigned, missing WHERE detection defaults to Ask behavior, showing a confirmation dialog when a potentially dangerous statement is detected.