Prevent SQL Injection Attacks

Overview

SQL injection has topped vulnerability rankings for two decades and it still shows up in new code. The attack itself is simple: hostile input lands inside a query, and the database runs it as if your application wrote it. Depending on the account’s permissions, the damage ranges from a leaked table to a wiped database. Injection sits near the top of every OWASP Top 10 release because it’s cheap to exploit and brutal in impact.

The root cause is almost always the same: untrusted input concatenated straight into SQL strings. The fix is just as direct — bind values with parameterized queries, or let an ORM do the escaping for you. The examples below cover Python, JavaScript, and Java at the driver level; if you work specifically with SQLAlchemy, the SQLAlchemy injection recipe goes deeper into its ORM and Core APIs.

When to Use

Reach for this recipe when you’re:

• Shipping code that builds SQL from runtime values
• Modernizing a legacy codebase that concatenates SQL strings
• Auditing an application for injectable parameters
• Teaching a team safe database access patterns
• Building a code-review checklist for anything that touches the database

Solution

Python

import sqlite3

# VULNERABLE — never do this
# query = f"SELECT * FROM users WHERE email = '{user_input}'"

# SAFE — parameterized query
conn = sqlite3.connect("app.db")
cursor = conn.cursor()

cursor.execute(
    "SELECT * FROM users WHERE email = ? AND active = ?",
    (email, True)
)
rows = cursor.fetchall()

JavaScript (Node.js with pg)

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

async function getUserByEmail(email) {
  // VULNERABLE — never do this
  // const query = `SELECT * FROM users WHERE email = '${email}'`;

  // SAFE — parameterized query
  const result = await pool.query(
    'SELECT * FROM users WHERE email = $1 AND active = $2',
    [email, true]
  );
  return result.rows;
}

Java (JDBC)

import java.sql.*;

// SAFE — PreparedStatement
String sql = "SELECT * FROM users WHERE email = ? AND active = ?";
try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql)) {

    stmt.setString(1, email);
    stmt.setBoolean(2, true);

    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

Using an ORM (Python/SQLAlchemy)

from sqlalchemy.orm import Session
from models import User

with Session(engine) as session:
    users = session.query(User).filter_by(
        email=email,
        active=True
    ).all()

Explanation

• Parameterized queries: the driver binds each value to its ?, $1, or :name placeholder, so injected text arrives as a string literal and nothing more — it never becomes part of the command.
• Prepared statements: the database parses and plans the statement once, then re-executes it with fresh parameters — cached plans for free on top of the security benefit.
• ORMs: SQLAlchemy, Sequelize, Hibernate — they all build parameterized calls under the hood. For most apps that’s the safest default — raw SQL simply never enters your codebase.
• Stored procedures: a useful layer, but no guarantee — a procedure that concatenates input into its own SQL is just as exposed.

Variants

Approach	Security	Flexibility	Best For
Bound-parameter queries	Excellent	High	Complex queries, reporting
ORM	Excellent	Medium	CRUD-heavy applications
Stored procedures	Good	Low	Legacy stacks, strict DBA control
Query builders (Knex, jOOQ)	Good	High	Runtime query construction

What Works

• Never concatenate user input into SQL strings: not even for ORDER BY columns or table names. Use allowlists if live identifiers are unavoidable.
• Default to an ORM: SQLAlchemy, Sequelize, and Hibernate bind parameters for you on every call, so the safe path is also the easy path.
• Keep validating input before the database: input validation catches garbage early; parameterization handles what gets through.
• Run the app on a least-privilege DB account: no DROP TABLE, no GRANT. If an injection slips through, least privilege is what keeps it a bug instead of a breach.
• Watch for injection probes in logs: failed queries packed with SQL keywords or stray quotes usually mean someone is testing your parameters.
• Update database drivers regularly: connector and ORM CVEs occasionally include injection bypasses.

Common Mistakes

• Building SQL with f-strings or template literals: still the fastest way to ship an injection bug.
• Parameterizing halfway: binding the WHERE values but interpolating ORDER BY columns or table names straight into the string.
• Trusting client-side checks: anything the browser validates can be skipped entirely — validation lives on the server or it doesn’t exist.
• Letting % and _ through in LIKE patterns: even a bound parameter can match far more rows than you intended.
• Assuming stored procedures are safe by default: ones that build dynamic SQL inside are vulnerable unless they bind parameters too.

Advanced Solutions

ORDER BY with allowlist (Python)

Since placeholders can’t bind identifiers, an allowlist is the way to sort dynamically:

ALLOWED_SORT_COLUMNS = {
    'name': 'users.name',
    'email': 'users.email',
    'created_at': 'users.created_at',
    'updated_at': 'users.updated_at',
}

def get_users(sort_by: str = 'created_at', sort_dir: str = 'desc',
              limit: int = 20, offset: int = 0):
    """Fetch users with safe dynamic sorting (sqlite3)."""
    # Validate sort column against allowlist
    column = ALLOWED_SORT_COLUMNS.get(sort_by)
    if column is None:
        raise ValueError(f'Invalid sort column: {sort_by}')

    # Validate sort direction
    direction = 'ASC' if sort_dir.upper() == 'ASC' else 'DESC'

    # Limit and offset stay parameterized; only vetted identifiers
    # are interpolated into the query text
    query = f"""
        SELECT id, name, email, created_at
        FROM users
        WHERE active = 1
        ORDER BY {column} {direction}
        LIMIT ? OFFSET ?
    """

    conn = sqlite3.connect("app.db")
    cursor = conn.execute(query, (limit, offset))
    return cursor.fetchall()

# Usage
users = get_users(sort_by='name', sort_dir='asc', limit=10, offset=0)
# An attacker passing sort_by="name; DROP TABLE users--" gets ValueError

LIKE wildcard escaping (JavaScript)

Even with parameterized queries, % and _ in user input can cause unexpected LIKE behavior:

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

async function searchUsers(searchTerm) {
  // Escape LIKE wildcards in user input
  const escapedTerm = searchTerm
    .replace(/\\/g, '\\\\')  // Escape backslashes first
    .replace(/%/g, '\\%')    // Escape percent
    .replace(/_/g, '\\_');   // Escape underscore

  const result = await pool.query(
    `SELECT id, name, email FROM users
     WHERE name LIKE $1 ESCAPE '\\'
     ORDER BY name
     LIMIT 20`,
    [`%${escapedTerm}%`]
  );

  return result.rows;
}

// Usage: searchTerm = "50%_off" becomes "50\%\_off" in the LIKE pattern
// This matches the literal string "50%_off" instead of "50<anything><any char>off"

Sequelize raw query safety (Node.js)

Sequelize’s query() method supports parameterized raw SQL:

const { Sequelize } = require('sequelize');
const sequelize = new Sequelize(process.env.DATABASE_URL);

// SAFE: Replacements
const users = await sequelize.query(
  'SELECT * FROM users WHERE email = :email AND active = :active',
  {
    replacements: { email: userEmail, active: true },
    type: Sequelize.QueryTypes.SELECT,
  }
);

// SAFE: Positional parameters
const orders = await sequelize.query(
  'SELECT * FROM orders WHERE user_id = $1 AND status = $2',
  {
    bind: [userId, 'completed'],
    type: Sequelize.QueryTypes.SELECT,
  }
);

// DANGEROUS: Never do this
// const result = await sequelize.query(
//   `SELECT * FROM users WHERE email = '${userEmail}'`
// );

jOOQ type-safe queries (Java)

jOOQ generates type-safe SQL from your schema, eliminating injection by construction:

import static org.jooq.impl.DSL.*;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;

// jOOQ generates classes from your database schema
import static com.example.generated.tables.Users.USERS;
import static com.example.generated.tables.Orders.ORDERS;

public class UserRepository {

    private final DSLContext ctx;

    public UserRepository(DSLContext ctx) {
        this.ctx = ctx;
    }

    public Result<Record> findActiveUsersByEmail(String email) {
        return ctx.select()
            .from(USERS)
            .where(USERS.EMAIL.eq(email))
            .and(USERS.ACTIVE.eq(true))
            .fetch();
    }

    public Result<Record> searchUsersByName(String namePattern) {
        // Type-safe LIKE with escaped pattern
        return ctx.select()
            .from(USERS)
            .where(USERS.NAME.like("%" + namePattern + "%"))
            .orderBy(USERS.CREATED_AT.desc())
            .limit(20)
            .fetch();
    }
}

Detecting injection with SQLMap

Test your application for SQL injection vulnerabilities:

#!/bin/bash
# Test a single endpoint
sqlmap -u "https://example.com/api/users?id=1" \
  --batch --level=3 --risk=2 \
  --random-agent \
  --output-dir=/tmp/sqlmap-results

# Test with authentication cookie
sqlmap -u "https://example.com/api/users?id=1" \
  --cookie="session=abc123" \
  --batch --level=5

# Test POST parameters
sqlmap -u "https://example.com/api/login" \
  --data="email=test@example.com&password=test" \
  --batch --level=3

See Also

• OWASP SQL Injection — attack patterns and prevention cheat sheets.
• CWE-89: SQL Injection — the weakness classification behind this recipe.
• node-postgres parameterized queries — how $1-style parameters work in the pg driver.
• sqlmap — point it at your own staging environment to find what manual review missed.
• Companion repository — runnable parameterized-query examples for Python, Node.js, and Java.