Prevent SQL Injection Attacks

Overview

SQL injection is one of the most common and dangerous web application vulnerabilities. It occurs when an attacker injects malicious SQL code into application queries through user input, potentially exposing, modifying, or deleting entire databases. Injection attacks consistently rank in the OWASP Top 10 because they are easy to exploit and devastating in impact.

The root cause is almost always the same: concatenating untrusted user input directly into SQL strings. The fix is equally straightforward: use parameterized queries or an ORM that handles escaping automatically. The pattern below demonstrates the secure way to access databases in Python, JavaScript, and Java.

When to Use

Use this recipe when:

• Writing any code that executes SQL queries with live values
• Migrating legacy code that uses string concatenation for SQL
• Auditing existing applications for injection vulnerabilities
• Training developers on secure database access patterns
• Setting up code review checklists for database-related changes

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();

// 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]
);
const rows = 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 database driver treats user input as data, not as executable SQL. Placeholders (?, $1, :name) are replaced safely by the driver, preventing any injected SQL from being interpreted as commands.
• Prepared statements: The database compiles the query plan once and executes it with different parameters. This is both a security and performance win.
• ORMs: Object-relational mappers like SQLAlchemy, Sequelize, and Hibernate automatically parameterize queries. They are the safest choice for most applications because they abstract SQL entirely.
• Stored procedures: Can add a layer of abstraction, but they do not prevent injection if they themselves concatenate input inside live SQL.

Variants

Approach	Security	Flexibility	Best For
Raw parameterized queries	Excellent	High	Complex queries, reporting
ORM	Excellent	Medium	CRUD-heavy applications
Stored procedures	Good	Low	Legacy systems, strict DBAs
Query builders (Knex, jOOQ)	Good	High	Live 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.
• Use an ORM by default: it eliminates entire categories of injection bugs with minimal performance cost.
• Validate input before it reaches the database: input validation and parameterized queries are complementary defenses.
• Use least-privilege database accounts: the application user should not have DROP TABLE or GRANT permissions.
• Log and monitor for injection attempts: failed queries containing SQL keywords or unusual characters can signal probing.
• Keep database drivers updated: security patches for drivers and ORMs fix known bypasses.

Common Mistakes

• Using f-strings or template literals for SQL: this is the most common cause of SQL injection in modern code.
• Partial parameterization: parameterizing the WHERE clause but concatenating ORDER BY columns or table names.
• Trusting client-side validation: attackers bypass frontend validation entirely. All validation must be server-side.
• Using LIKE without escaping wildcards: % and _ in user input can cause unexpected matches even in parameterized queries.
• Assuming stored procedures are safe: procedures that build live SQL internally are still vulnerable unless they use parameterized queries themselves.

Advanced Solutions

ORDER BY with allowlist (Python)

Table and column names cannot be parameterized. Use an allowlist to safely handle dynamic sorting:

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."""
    # 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 are parameterized
    query = f"""
        SELECT id, name, email, created_at
        FROM users
        WHERE active = TRUE
        ORDER BY {column} {direction}
        LIMIT %s OFFSET %s
    """

    with db.cursor() as cursor:
        cursor.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