SQL Guardrail Recipes

Simple patterns catch simple mistakes. Real queries span multiple lines, hide writes behind CTEs, and scope deletes with subqueries. This page gives you tested patterns for those harder cases, plus safe queries to verify each rule before it reaches production. Every pattern here was validated against Python’s re module, the engine that evaluates guardrails, using both queries that must be blocked and legitimate queries that must pass.

For rule creation, actions, and pattern syntax basics, see Guardrails Configuration.

Two pitfalls that break most SQL rules

Real queries span multiple lines. Real scripts contain more than one statement. These two regex details cause most false positives and false negatives: 1. . does not cross line breaks. Without the s flag, a lookahead like (?!.*\bWHERE\b) only inspects the first line. This rule blocks a multi-line UPDATE even though it has a WHERE clause:

UPDATE users
SET active = false
WHERE id = 123;  -- blocked anyway: the lookahead never sees line 3

Fix: scope the lookahead by statement with [^;]* instead of .*. It crosses line breaks and stops at the statement boundary. 2. ^ only matches the start of the payload. Without the m flag, a rule anchored on ^UPDATE misses the UPDATE in SELECT 1; UPDATE t SET a = 1;. Prefer \b word boundaries plus per-statement scoping over anchors. The recipes below apply both fixes. They share a common shape: (?is) at the start (case-insensitive, dot crosses lines) and [^;]* to stay inside one statement.

Recipes

Each recipe is one input rule with the Block action, unless noted. Rule names follow the block-* convention; use them as-is or rename to match your setup.

block-tautology-where

Blocks statements where the WHERE clause exists but does not restrict anything: WHERE 1=1, WHERE TRUE, WHERE '1'='1'.

(?is)\bwhere\s+\(*\s*(?:1\s*=\s*1|'1'\s*=\s*'1'|true)\s*\)*\s*(?:;|$|\breturning\b)

Query	Result
`UPDATE users SET active = false WHERE 1 = 1;`	Blocked
`SELECT * FROM users WHERE TRUE;`	Blocked
`UPDATE t SET a = 1 WHERE (1=1) RETURNING id;`	Blocked
`UPDATE users SET active = false WHERE id = 10;`	Allowed
`SELECT * FROM users WHERE 1=1 AND tenant_id = 5;`	Allowed
`SELECT * FROM flags WHERE enabled = true;`	Allowed

The pattern only matches when the tautology is the last or only condition, followed by ;, end of input, or RETURNING. That keeps the common query-builder idiom WHERE 1=1 AND <real condition> working. To block that idiom too, remove the ;|$ alternatives.

block-write-with-subquery

Blocks UPDATE and DELETE statements whose scope comes from a subquery: IN (SELECT ...), EXISTS (SELECT ...), = (SELECT ...). These writes are hard to review because the affected rows are invisible until execution.

(?is)\b(?:update|delete)\b[^;]*\bwhere\b[^;]*\(\s*select\b

Query	Result
`UPDATE users SET active = false WHERE id IN (SELECT id FROM temp_users);`	Blocked
`UPDATE users SET active = false WHERE EXISTS (SELECT 1 FROM sessions s WHERE s.user_id = users.id);`	Blocked
`DELETE FROM users WHERE id = (SELECT max(id) FROM audit);`	Blocked
`SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders LIMIT 10);`	Allowed
`UPDATE users SET active = false WHERE id = 10;`	Allowed

Reads with subqueries stay allowed. Column names like updated_at do not trigger the rule: \b requires the exact word.

block-cte-write

Blocks writes hidden behind a CTE: WITH ... UPDATE, WITH ... DELETE, WITH ... INSERT, WITH ... MERGE. A rule that only inspects the first keyword of a query sees WITH and lets the write through.

(?is)(?:^|;)\s*with\b[^;]*\b(?:update|delete|insert|merge)\b

Query	Result
`WITH recent AS (SELECT * FROM orders) UPDATE orders SET processed = true FROM recent WHERE orders.id = recent.id;`	Blocked
`SELECT 1; WITH x AS (SELECT id FROM t) DELETE FROM t USING x WHERE t.id = x.id;`	Blocked
`WITH recent AS (SELECT * FROM orders) SELECT * FROM recent;`	Allowed
`WITH x AS (SELECT last_update FROM t) SELECT * FROM x;`	Allowed

Read-only CTEs pass. The rule requires WITH at the start of a statement, so table hints like WITH (NOLOCK) in the middle of a query do not trigger it.

block-excessive-joins

Matches statements with 3 or more JOINs. Regex cannot parse query structure, but it can count occurrences. Adjust {2} to N-1 for a different threshold.

(?is)\bjoin\b(?:[^;]*\bjoin\b){2}

Query	Result
`SELECT * FROM a JOIN b ON ... JOIN c ON ... LEFT JOIN (SELECT ...) x ON ...;`	Matched (4 joins)
`SELECT * FROM a JOIN b ON a.id = b.id LEFT JOIN c ON b.id = c.id;`	Allowed (2 joins)
`SELECT ... 2 joins ...; SELECT ... 2 joins ...;`	Allowed (per statement)

Use Require Approval instead of Block for this rule. A complex join is not always wrong, but it usually deserves a second pair of eyes. See Actions.

block-write-without-where

Blocks UPDATE and DELETE statements that have no WHERE clause at all. This is the corrected, multi-line-safe version of the classic rule.

(?is)\b(?:update|delete)\b(?![^;]*\bwhere\b)[^;]*(?:;|$)

Query	Result
`UPDATE users SET active = false;`	Blocked
`DELETE FROM tmp_sessions` (no trailing `;`)	Blocked
`SELECT 1; UPDATE t SET a = 1;`	Blocked (second statement)
Multi-line `UPDATE ... WHERE id = 123;`	Allowed
`UPDATE t SET a = 1 WHERE b IN (SELECT id FROM x);`	Allowed

This rule and block-tautology-where are complementary and never overlap: UPDATE ... WHERE 1=1; triggers the tautology rule, not this one. Assign both to the same resource role for full coverage.

Test before you deploy

Never test a blocking rule with a query that would be destructive if the block fails. A typo in the pattern, or a guardrail assigned to the wrong resource role, means the query runs. The test queries below are safe on any database, including production:

“Must block” queries never reach the database when the rule works. They also reference tables that do not exist (gr_test_zz, gr_tmp_zz, gr_orders_zz), so even if the rule fails, Postgres returns relation does not exist and nothing changes. For an extra layer, wrap writes in BEGIN; ...; ROLLBACK; (neither keyword triggers these rules).
“Must pass” queries execute for real. They only read Postgres catalogs (pg_tables, pg_class) with a LIMIT, and the single write targets a temp table created in the same payload, discarded when the session ends. On a read-only resource role, the temp table control fails on permissions, which is harmless.

With all 5 rules active on the same resource role, each blocking query below triggers only its own rule, so the error message tells you which rule fired. block-tautology-where

-- must block:
UPDATE gr_test_zz SET active = false WHERE 1 = 1;
-- must pass:
SELECT * FROM pg_tables WHERE 1=1 AND schemaname = 'public';

block-write-with-subquery

-- must block:
UPDATE gr_test_zz SET active = false WHERE id IN (SELECT id FROM gr_tmp_zz);
-- must pass:
SELECT * FROM pg_tables WHERE tablename IN (SELECT tablename FROM pg_tables LIMIT 5);

block-cte-write

-- must block:
WITH recent AS (SELECT * FROM gr_orders_zz) UPDATE gr_orders_zz SET processed = true FROM recent WHERE gr_orders_zz.id = recent.id;
-- must pass:
WITH t AS (SELECT tablename FROM pg_tables) SELECT * FROM t LIMIT 5;

block-excessive-joins

-- must block (4 joins):
SELECT * FROM pg_class a JOIN pg_namespace b ON a.relnamespace = b.oid JOIN pg_attribute c ON c.attrelid = a.oid LEFT JOIN (SELECT oid FROM pg_type) d ON d.oid = c.atttypid LIMIT 1;
-- must pass (1 join):
SELECT a.relname FROM pg_class a JOIN pg_namespace b ON a.relnamespace = b.oid LIMIT 5;

block-write-without-where

-- must block:
UPDATE gr_test_zz SET active = false;
-- must pass (single payload, temp table):
CREATE TEMP TABLE gr_test_ok (id int, active bool); INSERT INTO gr_test_ok VALUES (1, true); UPDATE gr_test_ok SET active = false WHERE id = 1;

Two extra checks worth running:

An UPDATE ... WHERE id = 1 split across several lines must pass block-write-without-where. Multi-line queries are the main source of false positives in older rule styles.
UPDATE ... WHERE 1 = 1; must trigger block-tautology-where, not block-write-without-where.

What regex cannot catch

A LIMIT that only exists in a subquery is not detectable with regex. Consider:

SELECT *
FROM customers
WHERE id IN (
  SELECT customer_id FROM orders LIMIT 10
);
-- the outer SELECT is still unbounded

Two reasons no pattern can catch this reliably:

Deciding whether the LIMIT belongs to the outer query or the subquery requires parsing nested parentheses. That is grammar, and regex does not parse structure.
A “SELECT without LIMIT” rule does not help either: the word LIMIT is present in the text, so any textual check considers the query bounded.

The same limit applies to string literals and comments: a guardrail sees plain text, so SET note = 'where true' can trip a tautology rule, and /* where */ can satisfy an absence check. Rare in practice, but real. A guardrail is a textual safety belt, not a SQL parser. For unbounded reads, layer your defenses instead:

Require Approval: route mass-read verbs on production resource roles through approval. The approver sees the query structure that regex cannot.
Database-level controls: give the resource role’s database user a statement_timeout, a read-only role, or views with built-in limits on sensitive tables. The guardrail blocks the text; the database limits the actual cost.
Runbooks: parameterized queries for day-to-day operations. Users fill in parameters instead of writing free-form SQL.

Guardrails Configuration

Rule creation, actions, pattern syntax, and troubleshooting

Guardrails Overview

What guardrails do and how they fit with other features

Action Access Requests

Configure the Require Approval action

Runbooks

Parameterized queries instead of free-form SQL

Introduction

Features

Clients

Quickstart

Setup & Administration

Concepts

Integrations

Two pitfalls that break most SQL rules

Recipes

block-tautology-where

block-write-with-subquery

block-cte-write

block-excessive-joins

block-write-without-where

Test before you deploy

What regex cannot catch

Guardrails Configuration

Guardrails Overview

Action Access Requests

Runbooks

​Two pitfalls that break most SQL rules

​Recipes

​block-tautology-where

​block-write-with-subquery

​block-cte-write

​block-excessive-joins

​block-write-without-where

​Test before you deploy

​What regex cannot catch

​Related

Guardrails Configuration

Guardrails Overview

Action Access Requests

Runbooks

Two pitfalls that break most SQL rules

Recipes

block-tautology-where

block-write-with-subquery

block-cte-write

block-excessive-joins

block-write-without-where

Test before you deploy

What regex cannot catch

Related