# PostgreSQL Query Safety Rules
## Rule
All queries MUST follow safety patterns to prevent data loss, performance degradation, and security vulnerabilities.
## Format
Every data-modifying query must include a WHERE clause. Every application query must use parameterized statements.
## Requirements
### 1. Never UPDATE/DELETE Without WHERE
```sql
-- DANGEROUS: Updates every row in the table
UPDATE users SET status = 'inactive';
-- SAFE: Explicit WHERE clause
UPDATE users SET status = 'inactive' WHERE last_login < now() - interval '1 year';
-- SAFEST: Verify with SELECT first, then use CTE
WITH to_deactivate AS (
SELECT id FROM users WHERE last_login < now() - interval '1 year'
)
UPDATE users SET status = 'inactive' WHERE id IN (SELECT id FROM to_deactivate);
```
### 2. No SELECT * in Application Code
```sql
-- BAD: Returns all columns, breaks when schema changes
SELECT * FROM users;
-- GOOD: Explicit columns — self-documenting, stable
SELECT id, email, name, status FROM users WHERE id = $1;
```
### 3. Always Use Parameterized Queries
```sql
-- DANGEROUS: SQL injection vulnerable
query = f"SELECT * FROM users WHERE email = '{user_input}'"
-- SAFE: Parameterized
query = "SELECT id, email, name FROM users WHERE email = $1"
-- Pass user_input as parameter
```
### 4. Use LIMIT on Exploratory Queries
```sql
-- DANGEROUS: Could return 100M rows
SELECT id, email FROM users WHERE status = 'active';
-- SAFE: Bounded result set
SELECT id, email FROM users WHERE status = 'active' LIMIT 100;
```
### 5. Wrap Multi-Statement Changes in Transactions
```sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If anything fails, ROLLBACK undoes both changes
```
## Anti-Patterns
- Using string concatenation to build SQL queries
- Running DELETE without first counting affected rows
- Using TRUNCATE instead of DELETE when you need to preserve triggers/logs
- Not using transactions for multi-step operations
## Enforcement
Use pg_stat_statements to monitor for queries without WHERE clauses. Configure statement_timeout to kill runaway queries.