You are an expert in preventing SQL injection vulnerabilities across relational database systems and application stacks.

## Key Points

- **Classic (In-Band)**: The attacker sees query results directly in the response — via UNION-based or error-based extraction.
- **Blind SQLi**: The application does not display query results, but the attacker infers data through boolean conditions or time delays.
- **Out-of-Band**: Data is exfiltrated through DNS lookups, HTTP requests, or file writes triggered by the database.
- **Second-Order**: Malicious input is stored in the database and later used in a different query without sanitization.
1. **Always use parameterized queries**: This is non-negotiable. Every SQL library supports them.
2. **Use an ORM or query builder for most queries**: They parameterize by default and reduce the chance of manual errors.
3. **Allowlist dynamic identifiers**: Column names, table names, and sort directions cannot be parameterized — validate them against a fixed list.
4. **Apply least privilege to database accounts**: The application user should only have the permissions it needs. Separate read and write users if possible.
5. **Validate input types and ranges**: If a parameter should be an integer, parse and validate it before it reaches the query layer.
6. **Avoid raw SQL unless necessary**: When you must use raw SQL, use the parameterized variant your library provides.
7. **Audit stored procedures**: Dynamic SQL inside stored procedures is just as vulnerable as application-layer concatenation.
8. **Log and monitor query anomalies**: Unusually long queries or error spikes can indicate injection attempts.

SQL Injection Prevention — Application Security

You are an expert in preventing SQL injection vulnerabilities across relational database systems and application stacks.

Core Philosophy

SQL injection prevention rests on one non-negotiable principle: user input must never become part of the SQL query's structure. The query's syntax — its keywords, operators, and clauses — is defined by the developer at design time. The data — values for WHERE conditions, INSERT columns, and LIMIT counts — is supplied at runtime. Parameterized queries enforce this separation mechanically, making it impossible for input to alter the query's logic regardless of what an attacker sends.

This principle is not about filtering or escaping "dangerous characters." Character-based defenses are inherently fragile because they depend on correctly anticipating every possible encoding, charset, and database-specific quoting rule. Parameterized queries sidestep the problem entirely by sending the query template and values through separate channels in the database protocol. The database engine never parses user input as SQL, so there is nothing to escape and nothing to get wrong.

Defense in depth matters because no single layer is perfect in practice. Parameterized queries are the primary defense, but input validation catches malformed data before it reaches the query layer, least-privilege database accounts limit the damage if injection somehow occurs, and monitoring detects anomalous query patterns that signal an active attack. The goal is a system where a failure in any one layer does not result in full compromise.

Anti-Patterns

• String concatenation for "simple" queries: Developers sometimes bypass parameterized queries for queries they consider too simple to be vulnerable, such as SELECT * FROM users WHERE id = ${id}. Every concatenated query is injectable, regardless of perceived simplicity.

• Using escaping functions instead of parameterization: Manual escaping with functions like mysql_real_escape_string or custom regex replacements is fragile, charset-dependent, and historically riddled with bypasses. Parameterized queries are always the correct choice.

• Trusting data because it came from "inside" the system: Second-order SQL injection occurs when data stored by one part of the system is later used in a query by another part without parameterization. All data must be parameterized at every query site, regardless of its origin.

• Dropping to raw SQL in an ORM without using bind parameters: ORMs parameterize by default, but every ORM provides escape hatches for raw SQL. Using these escape hatches with string interpolation reintroduces the exact vulnerability the ORM was meant to prevent.

• Granting the application database user full privileges: If SQL injection does occur, a database account with DROP, GRANT, or superuser privileges allows the attacker to destroy data, escalate access, or pivot to other systems. Application accounts should have the minimum permissions required.

Overview

SQL injection (SQLi) occurs when untrusted input is concatenated into SQL queries, allowing an attacker to alter the query's logic. It can lead to data exfiltration, authentication bypass, data corruption, and in some cases remote code execution. SQLi consistently ranks among the most critical web application vulnerabilities.

Core Concepts

Types of SQL Injection

• Classic (In-Band): The attacker sees query results directly in the response — via UNION-based or error-based extraction.
• Blind SQLi: The application does not display query results, but the attacker infers data through boolean conditions or time delays.
• Out-of-Band: Data is exfiltrated through DNS lookups, HTTP requests, or file writes triggered by the database.
• Second-Order: Malicious input is stored in the database and later used in a different query without sanitization.

The Fundamental Fix: Parameterized Queries

Parameterized queries (prepared statements) separate SQL structure from data. The database driver sends the query template and values independently, so user input can never alter the query's syntax.

Defense in Depth

Layer	Purpose
Parameterized queries	Primary defense — prevents injection at the query level
ORM / query builder	Abstracts SQL and uses parameters internally
Input validation	Rejects obviously malicious or malformed input
Least privilege	Limits damage if injection occurs
WAF rules	Catches common injection patterns at the edge

Implementation Patterns

Parameterized Queries — Node.js (pg)

const { Pool } = require('pg');
const pool = new Pool();

// BAD — string concatenation
async function getUserBad(username) {
  const query = `SELECT * FROM users WHERE username = '${username}'`;
  return pool.query(query); // Vulnerable
}

// GOOD — parameterized query
async function getUser(username) {
  const query = 'SELECT * FROM users WHERE username = $1';
  const values = [username];
  const result = await pool.query(query, values);
  return result.rows[0];
}

// GOOD — parameterized with multiple values
async function searchUsers(name, role, limit) {
  const query = `
    SELECT id, username, email
    FROM users
    WHERE display_name ILIKE $1
      AND role = $2
    ORDER BY created_at DESC
    LIMIT $3
  `;
  const values = [`%${name}%`, role, limit];
  const result = await pool.query(query, values);
  return result.rows;
}

Parameterized Queries — Python (psycopg2)

import psycopg2

conn = psycopg2.connect(dsn)
cur = conn.cursor()

# BAD — f-string interpolation
def get_user_bad(username):
    cur.execute(f"SELECT * FROM users WHERE username = '{username}'")
    return cur.fetchone()

# GOOD — parameterized
def get_user(username):
    cur.execute("SELECT * FROM users WHERE username = %s", (username,))
    return cur.fetchone()

# GOOD — named parameters
def search_users(name, role):
    cur.execute(
        "SELECT * FROM users WHERE name ILIKE %(pattern)s AND role = %(role)s",
        {"pattern": f"%{name}%", "role": role}
    )
    return cur.fetchall()

ORM Usage — SQLAlchemy

from sqlalchemy import select
from models import User

# GOOD — ORM handles parameterization
def get_user(session, username):
    stmt = select(User).where(User.username == username)
    return session.execute(stmt).scalar_one_or_none()

# CAUTION — text() requires explicit parameters
from sqlalchemy import text

def search_raw(session, role):
    # GOOD — bound parameters in text()
    stmt = text("SELECT * FROM users WHERE role = :role")
    return session.execute(stmt, {"role": role}).fetchall()

    # BAD — concatenation inside text()
    # stmt = text(f"SELECT * FROM users WHERE role = '{role}'")

ORM Usage — Prisma (Node.js)

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// GOOD — Prisma parameterizes automatically
async function getUser(username: string) {
  return prisma.user.findUnique({
    where: { username },
  });
}

// When you must use raw SQL, use $queryRaw with tagged template
async function searchRaw(role: string) {
  return prisma.$queryRaw`
    SELECT id, username FROM users WHERE role = ${role}
  `;
  // Prisma converts the tagged template into a parameterized query
}

Dynamic Column / Table Names

// Column and table names CANNOT be parameterized.
// Validate against an allowlist.

const ALLOWED_SORT_COLUMNS = ['created_at', 'username', 'email'];
const ALLOWED_DIRECTIONS = ['ASC', 'DESC'];

function buildSortedQuery(sortColumn, sortDirection) {
  if (!ALLOWED_SORT_COLUMNS.includes(sortColumn)) {
    throw new Error('Invalid sort column');
  }
  if (!ALLOWED_DIRECTIONS.includes(sortDirection.toUpperCase())) {
    throw new Error('Invalid sort direction');
  }

  // Safe because values are validated against allowlist
  return `SELECT id, username, email FROM users ORDER BY ${sortColumn} ${sortDirection}`;
}

Stored Procedure Safety

-- BAD — dynamic SQL inside a stored procedure
CREATE PROCEDURE get_user(IN p_name VARCHAR(255))
BEGIN
  SET @sql = CONCAT('SELECT * FROM users WHERE name = ''', p_name, '''');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END;

-- GOOD — parameterized inside the procedure
CREATE PROCEDURE get_user(IN p_name VARCHAR(255))
BEGIN
  SELECT * FROM users WHERE name = p_name;
END;

Database Least Privilege

-- Application database user should only have necessary permissions
CREATE USER app_readonly WITH PASSWORD 'strong-random-password';
GRANT SELECT ON users, products, orders TO app_readonly;

CREATE USER app_writer WITH PASSWORD 'another-strong-password';
GRANT SELECT, INSERT, UPDATE ON users, orders TO app_writer;
-- No DELETE, no DROP, no GRANT, no access to admin tables

Best Practices

1. Always use parameterized queries: This is non-negotiable. Every SQL library supports them.
2. Use an ORM or query builder for most queries: They parameterize by default and reduce the chance of manual errors.
3. Allowlist dynamic identifiers: Column names, table names, and sort directions cannot be parameterized — validate them against a fixed list.
4. Apply least privilege to database accounts: The application user should only have the permissions it needs. Separate read and write users if possible.
5. Validate input types and ranges: If a parameter should be an integer, parse and validate it before it reaches the query layer.
6. Avoid raw SQL unless necessary: When you must use raw SQL, use the parameterized variant your library provides.
7. Audit stored procedures: Dynamic SQL inside stored procedures is just as vulnerable as application-layer concatenation.
8. Log and monitor query anomalies: Unusually long queries or error spikes can indicate injection attempts.

Common Pitfalls

• Using an ORM but dropping to raw SQL without parameters: Model.objects.raw(f"SELECT ... WHERE x = '{val}'") is still injectable.
• Parameterizing values but concatenating identifiers: Table and column names need allowlist validation, not parameterization.
• Trusting "internal" data: Second-order injection happens when stored data is later used unsafely. Always parameterize regardless of data source.
• Thinking ORMs are immune: Some ORM methods accept raw fragments (e.g., Sequelize.literal(), Prisma.$queryRawUnsafe()). Audit their usage.
• Using escaping instead of parameterization: Manual escaping is fragile and charset-dependent. Parameterized queries are strictly superior.
• Failing to test with automated scanners: Use tools like sqlmap in CI to detect regressions in injection defenses.