WeChall - Order By Query

Posted 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: 3C3CBEB0C8ADC66F2922C65E7784BE14

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