INJECTION DEFENSESINTERMEDIATE8 min readCWE-89ZX-SQLI-001

Preventing SQL Injection in Raw Queries and Modern ORMs

Why string concatenation in raw queries and unsafe ORM raw clauses ($queryRawUnsafe) lead to database compromise, and how to use parameterized queries.

Why AI coding tools introduce this vulnerability

When building complex filters (search, sort, pagination), AI coding tools frequently resort to string interpolation: `SELECT * FROM users WHERE name LIKE '%${searchTerm}%'` or ORM raw escapes like prisma.$queryRawUnsafe(...), exposing the application to classic SQL injection.

Technical Vulnerability Analysis

SQL injection occurs when user-supplied input is directly concatenated into SQL statement text rather than passed as isolated parameters to the database driver. Attackers can execute arbitrary queries, extract entire database tables, alter records, and in some database configurations, execute system commands.

VULNERABLE IMPLEMENTATION (DO NOT USE)

// AI-Generated Search Endpoint (VULNERABLE)
export async function searchProducts(searchTerm: string) {
  // String interpolation into raw query:
  return await db.query(
    `SELECT * FROM products WHERE name ILIKE '%${searchTerm}%'`
  );
}

Input like "' OR 1=1 --" alters SQL structure and extracts all rows.

Remediation Playbook & Code Fixes

SECURE HARDENED PATCH (src/core/db/products.ts)

// Correct Parameterized Query ($1, $2)
export async function searchProducts(searchTerm: string) {
  return await db.query(
    'SELECT id, name, price FROM products WHERE name ILIKE $1',
    [`%${searchTerm}%`]
  );
}

Why this fix works: Always pass values as positional parameters. The database driver escapes and sanitizes them.

Local CLI Verification Commands

Verify injection payload is treated as literal text and does not dump all records.

curl -s 'https://example.com/api/search?q=%27%20OR%201=1%20--'

Expected result: {"products":[]}

Pre-Deployment Self-Audit Checklist

Never concatenate variables into SQL strings using template literals (${...}) or +.Always use parameterized placeholders ($1, $2 in pg, or ? in MySQL/SQLite).Avoid raw unescaped methods ($queryRawUnsafe) in ORMs like Prisma or Drizzle.

Knowledge Check: Verify Your Understanding

What is the primary architectural cause of preventing sql injection in raw queries and modern orms?

Test your website for ZX-SQLI-001

Run our 14 deterministic engines to inspect your live deployment.

Launch scan for this flaw