WeChall - Order By Query

Posted on Edited on In Views: Disqus:

Challenge

SQL injection in an ORDER BY clause. Recover Admin's 32-character uppercase MD5 hash from the users table and submit it as the solution.

Analysis

Live source fetch (index.php?highlight=christmas) confirms the vulnerable view code:

1
2
3
4
5
6
static $whitelist = array(1, 3, 4, 5);
if (!in_array($orderby, $whitelist)) {
    return htmlDisplayError('Error 1010101: Not in whitelist.');
}
$orderby = $db->escape($orderby);
$query = "SELECT * FROM users ORDER BY $orderby $dir LIMIT 10";

The bug is the non-strict in_array(). PHP loosely compares strings to numbers, so a value like 3,(SELECT ...)-- passes the whitelist check (converts to integer 3), then reaches SQL as raw ORDER BY expression.

Exposed table schema:

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS users(
    username VARCHAR(32) CHARACTER SET ascii COLLATE ascii_general_ci,
    password CHAR(32) CHARACTER SET ascii COLLATE ascii_bin,
    apples INT(10) UNSIGNED DEFAULT 0,
    bananas INT(10) UNSIGNED DEFAULT 0,
    cherries INT(10) UNSIGNED DEFAULT 0,
    PRIMARY KEY(username)
);

Solution

Attempt 1: Scalar subquery (fails)

The initial approach used a scalar subquery in ORDER BY to compare one character at a time:

1
2
3,(SELECT IF(SUBSTRING(`password`,N,1)=CHAR(X),0,1)
   FROM users WHERE username=0x41646d696e)--

This does NOT work because MySQL evaluates scalar subqueries in ORDER BY as constants — the same value for all rows. Both SELECT 0 and SELECT 1 produce identical sort orders, so Admin's position never changes. Live diagnostic confirmed: both left Admin at position 12.

Attempt 2: Per-row conditional (works)

The key insight: column references and expressions in ORDER BY ARE evaluated per-row. The password column is directly accessible without a subquery:

1
3,IF(username=0x41646d696e AND ASCII(SUBSTRING(password,N,1))=X,0,1)--

How it works: - username=0x41646d696e (hex for "Admin") identifies Admin's row - ASCII(SUBSTRING(password,N,1))=X checks the N-th character - AND combines: only Admin's row with matching character gets IF=0 - All other rows get IF=1 - With ORDER BY 3, IF(...), Admin moves from position 13 → 10 when the condition is true

The -- comments out DESC LIMIT 10, returning all rows sorted by the injected expression.

Extraction

Binary search on hex character ASCII values (0-9: 48-57, A-F: 65-70) using >= comparisons, with equality verification per position. About 5 requests per character × 32 positions ≈ 160 requests total.

Result: 二分爆破得到 32 位大写 hex 串（见文末 spoiler）。

Why scalar subqueries fail in ORDER BY

In MySQL, ORDER BY evaluates expressions per row, but scalar subqueries are evaluated once and treated as constants. This is a common pitfall: (SELECT ...) in ORDER BY looks like it should work per-row, but the optimizer collapses it. Direct column references and non-subquery expressions (IF(condition, value1, value2)) are the correct path.

Useful observations

  • $db->escape() does NOT strip parentheses — function calls and subqueries work fine
  • in_array() non-strict check: 3,anything passes because PHP converts "3,anything" to integer 3
  • Hex encoding (0x41646d696e) bypasses any single-quote escaping in $db->escape()
  • Backticks around `password` are needed in subquery contexts (to avoid collision with MySQL's PASSWORD() function) but not in direct expressions like SUBSTRING(password,N,1)
  • Admin's default position is 9 (sorted by apples DESC) or 13 (sorted by apples ASC)
  • WeChall rate limits aggressively after ~80 rapid requests — use 1.0-1.2s delays
3C3CBEB0C8ADC66F2922C65E7784BE14