SQL Injection Explained: What It Is, How It Works, and How to Prevent It

by

SQL injection (SQLi) has been the number one web application vulnerability for over two decades. Despite being well-understood and entirely preventable, it continues to power some of the largest data breaches in history.

What Is SQL Injection?

SQL injection occurs when user input is incorporated into a database query without proper sanitization, allowing an attacker to modify the query logic and extract or alter data they should not have access to.

How It Works: Vulnerable Login Example

# VULNERABLE login query (Python pseudo-code):
query = "SELECT * FROM users WHERE username=" + username + " AND password=" + password

# Attacker enters in username field: OR 1=1 --
# Resulting query:
SELECT * FROM users WHERE username= OR 1=1 --  AND password=anything
# 1=1 is always TRUE -- logs attacker in as admin!

More Dangerous Payloads

# Extract all usernames and passwords:
UNION SELECT 1,username,password,4 FROM users --

# Read server files:
UNION SELECT 1,LOAD_FILE("/etc/passwd"),3,4 --

Real-World SQL Injection Breaches

  • Heartland Payment Systems (2008) – 130 million credit cards stolen via SQLi
  • Sony Pictures (2011) – 1 million user records via basic SQLi
  • TalkTalk (2015) – 157,000 records stolen by a 15-year-old using SQLi

Types of SQL Injection

In-Band (Classic): Attacker sees results directly in the application response. Most common type.

Blind SQLi: No visible output, but the attacker infers data from the application behavior (page loads differently, or delays indicate true/false).

Time-based Blind: Uses database sleep functions to extract data one bit at a time.

Testing for SQL Injection

# Manual testing - try in input fields and URL parameters:
'  (single quote - triggers errors in vulnerable apps)
' OR '1'='1
'--

# Automated with sqlmap (only on systems you own or have permission!):
pip install sqlmap
python sqlmap.py -u "http://testsite.com/item?id=1" --dbs

Prevention: Parameterized Queries

Parameterized queries are the definitive fix. The query structure is compiled separately from user data — malicious input cannot change the query logic.

# VULNERABLE (concatenation):
query = "SELECT * FROM users WHERE username=" + username

# SAFE - Python + SQLite:
cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

# SAFE - PHP + PDO:
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :user");
$stmt->execute(["user" => $username]);

# SAFE - Node.js + mysql2:
const [rows] = await db.execute("SELECT * FROM users WHERE username = ?", [username]);

Additional Defenses

  • Input validation – Whitelist allowed characters as a secondary layer
  • Principle of least privilege – Database accounts should only have permissions they need
  • Web Application Firewall (WAF) – ModSecurity or Cloudflare WAF to detect common SQLi patterns
  • Error handling – Never expose raw database errors to users

Wrap Up

SQL injection is 100% preventable with parameterized queries. Use prepared statements for every database interaction, validate input as a secondary defense, and deploy a WAF for defense-in-depth. If your codebase uses string concatenation in queries, refactoring to parameterized queries should be your top security priority.