SQL Injection Prevention: A Developer's Complete Guide

SQL injection (SQLi) has remained in the OWASP Top 10 for over two decades. It's one of the oldest, most dangerous, and most preventable web vulnerabilities. A single unescaped input field can give an attacker full access to your database — reading, modifying, or deleting every record. This guide covers how SQL injection works, the different types, and the battle-tested techniques to prevent it.

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 = ''

The OR '1'='1' condition is always true, and the -- comments out the password check. The attacker is now logged in as the first user in the database — often the admin.

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

This is the single most effective defense. Parameterized queries (prepared statements) separate SQL code from data at the database driver level. User input is always treated as a value — never as SQL syntax.

Python (psycopg2)

# Safe — parameterized query
cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password = %s",
    (username, password)
)

JavaScript (Node.js / pg)

// Safe — parameterized query
const result = await pool.query(
  'SELECT * FROM users WHERE username = $1 AND password = $2',
  [username, password]
);

Java (JDBC)

// Safe — PreparedStatement
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)

// Safe — PDO prepared statement
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$stmt->execute(['username' => $username, 'password' => $password]);

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}`;

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 dangerous functions like xp_cmdshell (SQL Server) or LOAD_FILE() (MySQL).

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