SQL Injection Defense

# SQL Injection Defense

## The attack
```
User input: ' OR 1=1; DROP TABLE users; --

Vulnerable code:
  db.query(`SELECT * FROM users WHERE id = '${input}'`)
Becomes:
  SELECT * FROM users WHERE id = '' OR 1=1; DROP TABLE users; --'
```

## Parameterized queries (the fix)
```ts
// Node.js (pg)
const result = await pool.query(
  'SELECT * FROM users WHERE id = $1 AND status = $2',
  [userId, 'active']
)

// Python (psycopg2)
cursor.execute('SELECT * FROM users WHERE id = %s', (user_id,))

// Go (database/sql)
row := db.QueryRow('SELECT * FROM users WHERE id = ?', userID)
```
Parameters are **never** interpolated into the SQL string.

## ORM safety
```ts
// Prisma — safe by default
await prisma.user.findMany({ where: { email: userInput } })

// Drizzle — safe
await db.select().from(users).where(eq(users.email, userInput))

// DANGER: raw queries with interpolation
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE name = '${input}'`) // VULNERABLE!

// Safe raw query
await prisma.$queryRaw`SELECT * FROM users WHERE name = ${input}` // tagged template = safe
```

## Defense layers
1. **Parameterized queries** (primary defense).
2. **Input validation**: reject unexpected characters for non-text fields.
3. **Least privilege**: DB user should only have SELECT/INSERT/UPDATE on needed tables.
4. **WAF rules**: block common SQLi patterns at network edge.
5. **Allowlist**: for ORDER BY / column names (can't be parameterized).
```ts
const ALLOWED_SORT = ['name', 'created_at', 'email']
const sortCol = ALLOWED_SORT.includes(input) ? input : 'created_at'
const query = `SELECT * FROM users ORDER BY ${sortCol}`  // safe: allowlisted
```

## Testing
```bash
# sqlmap — automated SQLi testing
sqlmap -u "https://example.com/api/users?id=1" --batch --level=3
```