SQL Injection Prevention Guide

How SQL Injection Works

SQL injection occurs when an application constructs SQL queries by concatenating user-supplied input directly into the query string. The attacker crafts input that changes the query's logical structure, allowing them to read unauthorized data, modify records, delete tables, or execute system commands. SQL injection has been the number one web application vulnerability for over two decades and remains in the OWASP Top 10.

Consider this vulnerable query: SELECT * FROM users WHERE username = '" + input + "'. If the attacker provides ' OR '1'='1 as input, the query becomes SELECT * FROM users WHERE username = '' OR '1'='1', which returns every row in the table. More destructive payloads can use UNION SELECT to extract data from other tables, ; DROP TABLE to delete data, or database-specific functions to read files and execute OS commands.

Parameterized Queries by Language

Parameterized queries are the primary defense against SQL injection. They work by sending the query structure and data values separately, so user input can never alter the SQL logic.

Node.js (mysql2):

const [rows] = await connection.execute(
  'SELECT * FROM users WHERE email = ? AND status = ?',
  [userEmail, 'active']
);

Python (psycopg2):

cursor.execute(
    "SELECT * FROM users WHERE email = %s AND status = %s",
    (user_email, "active")
)

PHP (PDO):

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status = :status');
$stmt->execute(['email' => $userEmail, 'status' => 'active']);

Java (JDBC):

PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE email = ? AND status = ?");
stmt.setString(1, userEmail);
stmt.setString(2, "active");
ResultSet rs = stmt.executeQuery();

ORM Safety and Pitfalls

Object-Relational Mappers use parameterized queries internally for standard operations, making them safe by default. However, every ORM provides escape hatches for raw SQL that reintroduce injection risk:

Sequelize (Node.js): Safe: User.findAll({ where: { email: input } }). Unsafe: sequelize.query("SELECT * FROM users WHERE email = '" + input + "'"). Fix: use sequelize.query("SELECT * FROM users WHERE email = ?", { replacements: [input] }).

SQLAlchemy (Python): Safe: session.query(User).filter_by(email=input). Unsafe: session.execute("SELECT * FROM users WHERE email = '" + input + "'"). Fix: use session.execute(text("SELECT * FROM users WHERE email = :email"), {"email": input}).

Eloquent (PHP): Safe: User::where('email', $input)->get(). Unsafe: DB::select("SELECT * FROM users WHERE email = '$input'"). Fix: use DB::select("SELECT * FROM users WHERE email = ?", [$input]).

Common Injection Patterns

Authentication bypass: ' OR '1'='1' -- in login forms to bypass password checks. Defense: parameterize both username and password fields, and hash passwords before comparison.

UNION-based extraction: ' UNION SELECT username, password FROM admins -- to read data from other tables. Defense: parameterized queries prevent this entirely.

Blind injection: The attacker uses conditional queries like ' AND SUBSTRING(password,1,1)='a' -- to extract data one character at a time based on true/false responses. Defense: parameterized queries and generic error messages.

Time-based blind injection: ' AND IF(1=1, SLEEP(5), 0) -- to infer information from response timing. This works even when error messages are suppressed.

Second-order injection: Malicious data is stored safely but later used in an unparameterized query. Defense: parameterize every query, including internal queries that use previously stored data.

Web Application Firewall (WAF) Rules

WAFs provide a defense-in-depth layer but should never be the sole protection. ModSecurity with the OWASP Core Rule Set (CRS) detects common injection patterns including UNION, SELECT, DROP, and comment sequences. Configure your WAF in blocking mode after testing with your application's traffic. Be aware that WAFs can be bypassed with encoding tricks, case variations, and database-specific syntax — parameterized queries remain the authoritative defense.

Least Privilege Principle

Even with parameterized queries, configure database accounts with minimal permissions. The application's database user should never have DROP, CREATE, ALTER, or GRANT privileges. Use separate accounts for read operations and write operations. Never connect to the database as root or sa. This limits the damage if an injection vulnerability is discovered in a code path you missed.