SQL injection prevention

Data-access safety flow (request → bind → execute)

  [ HTTP body / query string / headers ]
        │
        ▼
  ┌─────────────┐     Validate type, length, enums; block raw strings entering SQL
  │Normalize input│
  └─────────────┘
        │
        ▼
  ┌─────────────┐     Placeholders / named params; forbid quote + concat
  │Parameterized │──── Dynamic table/column: whitelist map only, never raw user text
  │    bind      │
  └─────────────┘
        │
        ▼
  ┌─────────────┐     Least-privilege account; audit + slow-query monitoring
  │Driver executes│──── Errors: uniform surface; logs include request id
  │     SQL      │
  └─────────────┘

Parameterized queries — 3-language implementations

The driver/protocol separates statement structure from data values: placeholders are parsed server-side; user input is passed only as bound values and never participates in keyword parsing.

Node.js — node-postgres (pg)

// ❌ Dangerous: string concatenation
const sql = `SELECT * FROM users WHERE email = '${req.body.email}'`;
await client.query(sql);

// ✅ Secure: $1 placeholder parameterization
const result = await client.query(
  'SELECT id, name FROM users WHERE email = $1 AND active = $2',
  [req.body.email, true]
);

// ✅ Bulk INSERT (avoid loop concatenation)
const values = users.map((_, i) => `($${i*2+1}, $${i*2+2})`).join(',');
await client.query(
  `INSERT INTO users (name, email) VALUES ${values}`,
  users.flatMap(u => [u.name, u.email])
);

Python — psycopg2

# ❌ Dangerous: % string formatting
cursor.execute("SELECT * FROM orders WHERE user_id = %s" % user_id)

# ✅ Secure: parameter tuple (second argument)
cursor.execute(
    "SELECT id, amount FROM orders WHERE user_id = %s AND status = %s",
    (user_id, "active")
)

# ✅ LIKE safe escaping
search = request.args.get("q", "")
safe_search = search.replace("%", "\%").replace("_", "\_")
cursor.execute(
    "SELECT * FROM products WHERE name ILIKE %s ESCAPE '\\'",
    (f"%{safe_search}%",)
)

Java — JDBC PreparedStatement

// ❌ Dangerous: Statement + concatenation
String sql = "SELECT * FROM accounts WHERE id = " + id;
Statement st = conn.createStatement();
st.executeQuery(sql);

// ✅ Secure: PreparedStatement
String sql = "SELECT balance FROM accounts WHERE id = ? AND owner_id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setLong(1, accountId);
    ps.setLong(2, currentUserId);
    ResultSet rs = ps.executeQuery();
    // ...
}

• Stored procedure parameters: if the procedure body still builds dynamic SQL internally, outer parameterization cannot save you—review the procedure body.
• Bulk work: use batch bind APIs; avoid building giant IN (...) strings in loops.

ORM dangerous usage and safe alternatives (Prisma example)

// ❌ Dangerous: Prisma $queryRaw with direct interpolation (template string)
const users = await prisma.$queryRaw`
  SELECT * FROM users WHERE role = '${userInput}'
`;
// Note: even with template literals, splicing JS variables is unsafe!
const unsafeQuery = `SELECT * FROM users WHERE name = '${name}'`;
await prisma.$queryRaw(Prisma.raw(unsafeQuery));  // ❌ Dangerous

// ✅ Secure: Prisma $queryRaw with Prisma.sql tag (auto-parameterized)
import { Prisma } from '@prisma/client';

const users = await prisma.$queryRaw(
  Prisma.sql`SELECT id, name FROM users WHERE role = ${userRole} AND active = ${true}`
);

// ✅ Safer: use Prisma type-safe API (avoids raw SQL entirely)
const users = await prisma.user.findMany({
  where: { role: userRole, active: true },
  select: { id: true, name: true },
});

// ✅ Safe dynamic sorting (allowlist mapping)
const ALLOWED_SORT_FIELDS = {
  'name': 'name',
  'createdAt': 'created_at',
  'score': 'score',
} as const;

const sortField = ALLOWED_SORT_FIELDS[req.query.sort as string];
if (!sortField) throw new Error('Invalid sort field');

const users = await prisma.$queryRaw(
  Prisma.sql`SELECT * FROM users ORDER BY ${Prisma.raw(sortField)} ASC`
);

• Raw fragments: only vetted static snippets plus bound parameters; do not let users or models emit whole queries.
• Pools and dialects: mind escaping and identifier quoting; migration scripts are not a substitute for app-layer injection defenses.

Least-privilege SQL configuration and injection vector defenses

Least-privilege database user configuration

-- Create dedicated app users (PostgreSQL)
CREATE USER app_readonly WITH PASSWORD 'strong-password-here';
CREATE USER app_writer WITH PASSWORD 'another-strong-password';

-- Read-only user: SELECT only
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

-- Write user: INSERT/UPDATE/SELECT only, no DDL
GRANT CONNECT ON DATABASE mydb TO app_writer;
GRANT USAGE ON SCHEMA public TO app_writer;
GRANT SELECT, INSERT, UPDATE ON TABLE orders, products TO app_writer;
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM app_writer;

-- Revoke high-risk privileges (MySQL)
REVOKE FILE ON *.* FROM 'app_user'@'%';
REVOKE SUPER ON *.* FROM 'app_user'@'%';

3 injection vectors and defense code

-- ① UNION injection defense: never dynamically build the SELECT field list
-- ❌ Dangerous: SELECT ${userFields} FROM users WHERE id = $1
-- ✅ Secure: fields hardcoded, id parameterized
SELECT id, name, email FROM users WHERE id = $1;

-- ② Time-based blind injection defense
-- Defense: statement timeout, block long-running queries
SET statement_timeout = '5s';  -- PostgreSQL per-session

-- ③ Second-order injection defense: parameterize both write and read paths
INSERT INTO profiles (user_id, username) VALUES ($1, $2);
SELECT * FROM audit_log WHERE username = $1;

• Operations: read replicas without DDL; app accounts without FILE-class privileges; pair SAST/CI rules with the data layer.

Dangerous-pattern highlight (SQL lab)

The editor stacks transparent text over a highlight layer: type or paste mixed code/SQL to flag common injection-shaped patterns (teaching aid—not a replacement for static analysis or human review).