The kill chain: one vulnerable endpoint → schema map → paginated dump of the highest-value table. This is the lesson that turns a finding into a breach - and the lesson every defender needs to understand to size a fix.
- L6UNION-based extraction
- L7Error-based extraction
- L8Blind injection overview
- Paginate a UNION SELECT with
LIMITandOFFSETto dump a full table. - Size the table with
COUNT(*)before extraction. - Use
CONCATto combine multiple columns into a single result row. - Recognise why batching and parallelisation matter for time and detection.
- Pagination
- Reading a table in chunks using LIMIT and OFFSET. The only practical way to dump a table that is larger than a single query response.
- Batching
- Grouping rows into a single response with GROUP_CONCAT (MySQL), STRING_AGG (PostgreSQL), or STRING_AGG-equivalents. Trades response size for request count.
- Order-stable
- A query that returns rows in a deterministic order (ORDER BY id). Without ORDER BY the database may return different rows on every request, and OFFSET pagination skips or duplicates rows.
- Throughput ceiling
- The maximum rows-per-second the application or network can support. Exceeding it triggers rate-limiting, timeouts, or WAF alerts.
The full kill chain, step by step
Once the schema is mapped and the high-value tables are identified, extraction is a pagination loop. The classic primitives:
-- Step 1: size the table
UNION SELECT 1, COUNT(*) FROM users
-- Step 2: paginate with LIMIT / OFFSET
UNION SELECT id, username FROM users ORDER BY id LIMIT 100 OFFSET 0
UNION SELECT id, username FROM users ORDER BY id LIMIT 100 OFFSET 100
UNION SELECT id, username FROM users ORDER BY id LIMIT 100 OFFSET 200
-- ...until the result is empty
-- Step 3: batch rows with CONCAT to reduce request count
UNION SELECT 1, GROUP_CONCAT(username SEPARATOR ',') FROM users
-- PostgreSQL equivalent
UNION SELECT 1, STRING_AGG(username, ',') FROM usersThe ORDER BYon a unique column is non-negotiable. Without it, the database is free to return rows in any order - and pagination skips or duplicates rows depending on the engine's plan.
Paginate a 87-row table
The sandbox fakes a users table with 87 rows. Step through with Next page, or hit Auto-paginate to see what a 200-millisecond-per-page scanner does to the request count. The progress bar tracks the extraction.
SELECT id, username, email, role FROM users ORDER BY id LIMIT 5 OFFSET 0-- | id | username | role | |
|---|---|---|---|
| 1 | user_001 | user1@example.com | admin |
| 2 | user_002 | user2@example.com | member |
| 3 | user_003 | user3@example.com | member |
| 4 | user_004 | user4@example.com | member |
| 5 | user_005 | user5@example.com | member |
What the data looks like in production
The biggest dumps in published SQLi breaches share three patterns. First, the high-value table is almost always users, customers, or accounts - not the smaller auxiliary tables. Second, the dump target is rarely the entire table; it is the email and password_hash columns. Third, the dump finishes in minutes, not hours, because the attacker batched rows with GROUP_CONCAT /STRING_AGG instead of paginating one row at a time.
The same fix; better defence-in-depth
Parameterisation closes the injection. The defence-in-depth measures that survive a successful injection are the same as the enumeration lesson: least-privilege database user, column-level encryption for PII, and PII redaction at the query level (so even a successful SELECT email FROM users returns alice@… with the local part masked).
// SAFE - column whitelist, bound LIMIT, and a server-side mask
const SAFE_COLUMNS = { id: 'id', username: 'username', email: 'email_masked' };
if (!(column in SAFE_COLUMNS)) throw new Error('invalid column');
const query = `SELECT ${SAFE_COLUMNS[column]} FROM users WHERE id = $1`;
await db.query(query, [id]);What to remember
- Extraction is a pagination loop:
LIMIT n OFFSET kuntil the result is empty. ORDER BYon a unique column is non-negotiable - without it, OFFSET skips or duplicates rows.- Batching with
GROUP_CONCATorSTRING_AGGtrades response size for request count. - Defence-in-depth: least-privilege DB user, column-level encryption, and PII masking in the query layer.
Knowledge check
0/3 answered · 0 correct1.Why does the LIMIT/OFFSET pagination loop need an ORDER BY on a unique column?
2.Why does an attacker prefer GROUP_CONCAT (or STRING_AGG) over LIMIT/OFFSET for fast extraction?
3.The application database user has been granted only SELECT on specific tables. The attacker has SQLi. What is still exfiltratable?