SQL Injection Prevention: A Complete Developer's Guide

SQL injection is one of the oldest and most dangerous web vulnerabilities. Despite being well-known, it's still responsible for countless data breaches. This guide will teach you how to prevent it completely.

What is SQL Injection?

SQL injection occurs when an attacker manipulates database queries by injecting malicious SQL code through user input. This can allow them to:

• Access, modify, or delete data
• Bypass authentication
• Execute administrative operations
• Read sensitive files from the server
• In some cases, execute system commands

Real-World Example

Consider this vulnerable login code:

// ❌ VULNERABLE CODE - DO NOT USE
const username = req.body.username;
const password = req.body.password;

const query = "SELECT * FROM users WHERE username = '" + username + 
              "' AND password = '" + password + "'";
              
const result = db.execute(query);

An attacker could enter this as the username:

admin' --

This creates the query:

SELECT * FROM users WHERE username = 'admin' --' AND password = ''

The -- comments out the password check, allowing the attacker to log in as admin without a password!

Prevention Method 1: Prepared Statements (Parameterized Queries)

The most effective defense against SQL injection is using prepared statements.

Node.js (MySQL):

// ✅ SAFE - Using prepared statements
const mysql = require('mysql2');

const query = 'SELECT * FROM users WHERE username = ? AND password = ?';
const values = [username, password];

connection.execute(query, values, (err, results) => {
  // Handle results
});

Python (SQLite):

# ✅ SAFE - Using parameterized query
import sqlite3

query = "SELECT * FROM users WHERE username = ? AND password = ?"
cursor.execute(query, (username, password))
results = cursor.fetchall()

PHP (PDO):

// ✅ SAFE - Using PDO prepared statements
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ? AND password = ?');
$stmt->execute([$username, $password]);
$results = $stmt->fetchAll();

Prevention Method 2: ORM (Object-Relational Mapping)

ORMs like Sequelize, TypeORM, SQLAlchemy automatically handle parameterization.

Sequelize (Node.js):

// ✅ SAFE - ORM handles parameterization
const user = await User.findOne({
  where: {
    username: username,
    password: password
  }
});

Prisma (Node.js):

// ✅ SAFE - Prisma uses prepared statements
const user = await prisma.user.findFirst({
  where: {
    username: username,
    password: password
  }
});

Prevention Method 3: Stored Procedures

Stored procedures can provide an additional layer of security when properly implemented.

-- Create stored procedure
CREATE PROCEDURE GetUser(IN user_name VARCHAR(50), IN user_pass VARCHAR(50))
BEGIN
  SELECT * FROM users WHERE username = user_name AND password = user_pass;
END;

-- Call from application
CALL GetUser(?, ?);

What NOT to Do

These methods are NOT sufficient to prevent SQL injection:

• Escaping characters: Easy to bypass
• Blacklisting keywords: Can be circumvented
• Client-side validation: Easily bypassed by attackers
• String concatenation with sanitization: Still risky

// ❌ NOT SAFE ENOUGH
const sanitized = username.replace(/'/g, "''");
const query = "SELECT * FROM users WHERE username = '" + sanitized + "'";

// Still vulnerable to other attack vectors!

Advanced Attack Techniques

1. Union-based injection:

' UNION SELECT username, password FROM users--

2. Boolean-based blind injection:

' AND 1=1--  (returns results)
' AND 1=2--  (returns no results)

3. Time-based blind injection:

' AND SLEEP(5)--

Additional Security Layers

• Principle of Least Privilege: Database users should only have necessary permissions
• Input Validation: Validate data types and formats
• Web Application Firewall (WAF): Filter malicious requests
• Error Handling: Don't expose database errors to users
• Regular Security Audits: Scan code regularly

Testing for SQL Injection

Test your application with these inputs:

' OR '1'='1
' OR '1'='1'--
' OR '1'='1'/*
admin'--
' UNION SELECT NULL--
1' AND '1'='2
' AND SLEEP(5)--

If any of these cause unexpected behavior, you have a SQL injection vulnerability.

Framework-Specific Protection

Laravel (PHP):

// ✅ SAFE - Eloquent ORM
$user = User::where('username', $username)
            ->where('password', $password)
            ->first();

// ✅ SAFE - Query builder with bindings
$user = DB::table('users')
          ->where('username', $username)
          ->first();

Django (Python):

# ✅ SAFE - Django ORM
user = User.objects.filter(username=username, password=password).first()

# ✅ SAFE - Raw queries with parameters
from django.db import connection
cursor = connection.cursor()
cursor.execute("SELECT * FROM users WHERE username = %s", [username])

Real-World Data Breach Examples

• TalkTalk (2015): 157,000 customers affected, £400,000 fine
• Heartland Payment Systems (2008): 130 million credit cards stolen
• Sony Pictures (2011): 1 million accounts compromised

All were preventable with proper SQL injection protection.

Security Checklist

• ✅ Use prepared statements or ORM everywhere
• ✅ Never concatenate user input into SQL queries
• ✅ Validate all input (type, format, length)
• ✅ Use principle of least privilege for database users
• ✅ Don't expose database errors to users
• ✅ Implement WAF for additional protection
• ✅ Regular security scans and penetration testing
• ✅ Keep frameworks and libraries updated
• ✅ Log and monitor suspicious queries

Conclusion

SQL injection is 100% preventable. By using prepared statements or ORMs consistently, you can eliminate this vulnerability entirely. There's no excuse for SQL injection in modern applications.