SQL injection prevention

Severity: Critical

Database queries must use parameterized queries or ORM methods - never string concatenation with user input.

Check automatically:

1. Check for ORM usage (inherently safe when used properly):

# Node.js ORMs
grep -E "\"prisma\"|\"@prisma/client\"|\"typeorm\"|\"sequelize\"|\"drizzle-orm\"|\"knex\"|\"objection\"" package.json 2>/dev/null

# Python ORMs
grep -E "sqlalchemy|django|peewee|tortoise-orm" requirements*.txt pyproject.toml 2>/dev/null

# Go ORMs
grep -E "gorm|sqlx|ent" go.mod 2>/dev/null

# Ruby ORMs
grep -E "activerecord|sequel" Gemfile 2>/dev/null

2. Check for dangerous raw query patterns:

# Template literals in SQL (JavaScript) - HIGH RISK
grep -rE "\`SELECT.*\\\$\{|\`INSERT.*\\\$\{|\`UPDATE.*\\\$\{|\`DELETE.*\\\$\{" src/ app/ lib/ --include="*.ts" --include="*.js" 2>/dev/null

# String concatenation in SQL - HIGH RISK
grep -rE "\"SELECT.*\" \+|\"INSERT.*\" \+|\"UPDATE.*\" \+|\"DELETE.*\" \+" src/ app/ lib/ 2>/dev/null

# Python f-strings in SQL - HIGH RISK
grep -rE "f\"SELECT|f\"INSERT|f\"UPDATE|f\"DELETE|f'SELECT|f'INSERT" src/ app/ lib/ --include="*.py" 2>/dev/null

# .format() in SQL - HIGH RISK
grep -rE "\"SELECT.*\.format\(|\"INSERT.*\.format\(" src/ app/ lib/ --include="*.py" 2>/dev/null

3. Check for raw query methods (need manual review):

# Raw query execution that MIGHT be safe (if parameterized)
grep -rE "\.raw\(|\.execute\(|\$queryRaw|\$executeRaw|rawQuery|executeSql" src/ app/ lib/ 2>/dev/null

# Prisma raw queries (check for parameterization)
grep -rE "Prisma\.\\\$queryRaw|Prisma\.\\\$executeRaw|\\\$queryRawUnsafe|\\\$executeRawUnsafe" src/ app/ lib/ 2>/dev/null

4. Check for safe parameterized patterns:

# Parameterized queries (safe patterns)
grep -rE "\\\$1|\\\$2|:param|:name|\?.*\?|\[.*\]" src/ app/ lib/ 2>/dev/null | grep -iE "select|insert|update|delete" | head -10

# Prepared statements
grep -rE "prepare\(|prepared|parameterized" src/ app/ lib/ 2>/dev/null

Ask user:

• "Do you use an ORM or raw SQL queries?"
• "Are there any raw SQL queries in the codebase?"
• "How do you handle dynamic query conditions?"

Cross-reference with:

• API-003 (input validation - first line of defense)
• DB-004 (database users - limited permissions reduce impact)

Pass criteria:

• Uses ORM for all queries, OR
• Raw queries use parameterized placeholders ($1, ?, :name)
• No string interpolation/concatenation with user input in SQL
• $queryRawUnsafe / $executeRawUnsafe not used (or justified and audited)

Fail criteria:

• String concatenation in SQL queries
• Template literals with ${variable} in SQL
• Python f-strings or .format() in SQL
• *Unsafe raw query methods with user input

Evidence to capture:

• ORM used (if any)
• Any raw SQL found and whether it's parameterized
• Any dangerous patterns detected
• Use of unsafe raw query methods

---