SQL Injection Prevention: A Developer's Complete Guide

SQL injection occurs when untrusted data changes the structure of a database query. Its impact depends on the query, application behavior, and database privileges. This guide explains defensive patterns; illustrative examples are not production authentication designs.

What Is SQL Injection?

SQL injection occurs when user-supplied input is inserted directly into a SQL query without proper sanitization or parameterization. The attacker's input is interpreted as SQL code rather than data, allowing them to manipulate the query's logic.

A Classic Example

Consider this login query built with string concatenation:

-- Vulnerable query (DO NOT USE)
query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"

If an attacker enters this as the username:

' OR '1'='1' --

The resulting query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1' --' AND password = ''

In this simplified example, injected SQL can change the predicate and comment out the remaining condition. Exact comment syntax and outcomes depend on the database and application; a successful injection does not necessarily select an administrator or grant full database access.

Key Insight: SQL injection is fundamentally a confusion of code and data. The fix is to never mix user input directly into SQL query strings. Always keep data separate from code.

Types of SQL Injection

1. Classic (In-Band) SQL Injection

The attacker receives results directly in the application's response. This is the easiest to exploit and the most common in poorly written applications.

-- Union-based: extract data from other tables
' UNION SELECT username, password FROM admin_users --

-- Error-based: extract info from database error messages
' AND 1=CONVERT(int, (SELECT TOP 1 table_name FROM information_schema.tables)) --

2. Blind SQL Injection

The application doesn't show query results or error messages, but the attacker can infer information from the application's behavior.

-- Boolean-based: true/false responses reveal data
' AND (SELECT SUBSTRING(username,1,1) FROM users LIMIT 1) = 'a' --

-- Time-based: response delay reveals data
' AND IF(1=1, SLEEP(5), 0) --

3. Out-of-Band SQL Injection

The attacker uses database features to send data to an external server they control — using DNS lookups, HTTP requests, or file operations. Less common but effective when other channels are blocked.

Prevention Technique #1: Parameterized Queries

Parameterized queries separate query structure from bound values. The examples below illustrate binding only; do not store or compare plaintext passwords. A real login retrieves a stored password hash and verifies it with an appropriate password-hashing library.

Python (psycopg2)

# Parameterization example only — not password verification
cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password = %s",
    (username, password)
)

JavaScript (Node.js / pg)

// Parameterization example only — not password verification
const result = await pool.query(
  'SELECT * FROM users WHERE username = $1 AND password = $2',
  [username, password]
);

Java (JDBC)

// Parameterization example only — not password verification
PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE username = ? AND password = ?"
);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

PHP (PDO)

// Parameterization example only — not password verification
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$stmt->execute(['username' => $username, 'password' => $password]);
Important: Parameterized queries protect values only. You cannot parameterize table names, column names, or SQL keywords like ORDER BY directions. Those must be whitelisted.

Prevention Technique #2: Use an ORM

Object-Relational Mappers generate parameterized queries automatically. They add a layer of abstraction that makes SQLi much harder to introduce accidentally.

# Python — SQLAlchemy
user = session.query(User).filter_by(username=username).first()

# JavaScript — Prisma
const user = await prisma.user.findUnique({
  where: { username: username }
});

# Ruby — ActiveRecord
user = User.find_by(username: username)

ORMs are not bulletproof — raw query methods in ORMs can still be vulnerable if you concatenate strings. Always use the ORM's parameterization features even when writing raw SQL through the ORM.

Prevention Technique #3: Input Validation

Validate all user input against expected patterns before it reaches your query — even when using parameterized queries (defense in depth).

// Whitelist approach — only allow expected values
const validSortColumns = ['name', 'date', 'price', 'rating'];
const sortBy = validSortColumns.includes(userInput) ? userInput : 'name';

const validDirections = ['ASC', 'DESC'];
const direction = validDirections.includes(userDirection) ? userDirection : 'ASC';

// Now safe to use in query
const query = `SELECT * FROM products ORDER BY ${sortBy} ${direction}`;
Rule of Thumb: Whitelist over blacklist. It's much safer to define what is allowed than to try to block everything that isn't. Attackers are creative — blacklists always have gaps.

Prevention Technique #4: Least Privilege

Even if an attacker finds an injection point, limit the damage they can do:

  • Use separate database accounts for different parts of your application. The read-only blog page doesn't need DROP TABLE permissions.
  • Restrict access to only the tables and columns the application needs.
  • Never use the database admin account in your application's connection string.
  • Disable unneeded database features and restrict privileges to file, operating-system, and network operations. The configuration mechanism depends on the database; functions are not universally disabled through the same switch.

Prevention Technique #5: WAF & Runtime Protection

Web Application Firewalls (WAFs) provide an additional layer of defense by detecting and blocking common SQLi patterns in HTTP requests. They're not a replacement for secure code — but they catch attacks that slip through.

  • ModSecurity — Open-source WAF with OWASP Core Rule Set
  • Cloudflare WAF — Cloud-based, easy to deploy
  • AWS WAF — Integrates with ALB, CloudFront, API Gateway

Common Mistakes That Create Vulnerabilities

1. String Concatenation in Queries

// ? VULNERABLE — string interpolation
const query = `SELECT * FROM products WHERE category = '${category}'`;

// ? SAFE — parameterized
const query = 'SELECT * FROM products WHERE category = $1';
await pool.query(query, [category]);

2. Dynamic Table/Column Names Without Whitelisting

// ? VULNERABLE — user controls table name
const query = `SELECT * FROM ${tableName}`;

// ? SAFE — whitelist allowed tables
const allowedTables = ['products', 'categories', 'reviews'];
if (!allowedTables.includes(tableName)) throw new Error('Invalid table');
const query = `SELECT * FROM ${tableName}`;

3. Trusting Client-Side Validation

Client-side validation improves UX but provides zero security. Attackers bypass it in seconds using browser dev tools, curl, or Postman. Always validate on the server.

4. Exposing Database Error Messages

// ? BAD — leaks database structure to attacker
app.use((err, req, res, next) => {
  res.status(500).json({ error: err.message });
});

// ? GOOD — generic message, log details internally
app.use((err, req, res, next) => {
  console.error(err);  // Log for debugging
  res.status(500).json({ error: 'An internal error occurred' });
});

SQL Injection Testing Checklist

Use this checklist to audit your application:

  • Search your codebase for string concatenation in SQL queries
  • Check every user input path — forms, URL parameters, headers, cookies
  • Verify all ORM raw query calls use parameterization
  • Confirm dynamic identifiers (table/column names) are whitelisted
  • Test with automated tools: sqlmap, OWASP ZAP, Burp Suite
  • Review database user permissions — apply least privilege
  • Ensure error messages don't expose database details in production
  • Check stored procedures for dynamic SQL inside them

Quick Reference Table

Technique                   | Protects Against        | Priority
----------------------------|-------------------------|----------
Parameterized queries       | All value-based SQLi    | Critical
Input validation/whitelist  | Identifier injection    | High
ORM usage                   | Accidental concatenation| High
Least privilege             | Damage limitation       | High
WAF                         | Known attack patterns   | Medium
Error message suppression   | Information disclosure  | Medium
Code review & SAST          | Pre-deployment catches  | Medium

Working with SQL? Format and clean up your queries.

Open SQL Formatter →