Blind SQL Injection without an “in”
Alternative ways to retrieve table names in MySQL — without information_schema.
As for the sake of exercising, I looked up a few web challenges on TetCTF and noticed an interesting one — ”Secure System”. When solving the challenge, I explored many SQL Injection techniques that you will probably not find in any tutorials. Enjoy reading!
The challenge was to craft a Blind SQL Injection payload without using:
- UNION … SELECT
- “in” and “or” keywords
Although the filter was way more complex, these were the hardest obstacles to overcome.
Alternatives to information_schema table
I researched the Internet looking for alternative ways to retrieve table names from MySQL database but didn’t find anything interesting. All techniques either rely on information_schema or mysql.innodb_table_stats and both of them would be filtered out because of the “in” keyword. So we looked for alternatives and discovered the table
Not only it contains a
table_name column but also the name of the indexed column, in the example above
id. But this is not the only way, there is also another table
sys.schema_table_statistics that displays more tables:
With that and a blind SQLi technique, I managed to retrieve the secret table name: “Th1z_Fack1n_Fl4444g_Tabl3”.
Looking up others’ solution
I was stuck for a while trying to retrieve the secret column name but was unsuccessful here. What I discovered though, was the table
sys.x$statement_analysis that allowed me to look up solutions of other players :)
I was curious if anyone has found the column name but the script was taking way too much time, so I only managed to retrieve something like on the example above. This can get very handy when solving other SQL challenges in the future! (update: The intended solution to the challenge was to read a record when the flag was being inserted into the table and from there discover the column name. When I was solving the challenge though, that record was gone due to the table auto-cleaning so it’s the reason it didn’t work for me.)
Retrieving the secret without the column name
If a table contains only one column it’s easy to retrieve the information from the table without knowing the column name. Just a simple
SUBSTR((SELECT * FROM table),1,1)='x' will do the job. If a table contains more than one column that query will throw an error. There is a nice trick that allows comparing queries with the same number of columns!
With that, using less than (<) instead of equal (=) operator you can retrieve the secret from a known table character by character. There is one problem though — the comparison in MySQL is by default case-insensitive.
Forcing case-sensitive comparison
Although I got the secret in lower case letters, I needed a case-sensitive one. What I discovered is that casting a string to binary format forces a byte-to-byte comparison which is exactly what I needed. The problem was that this was also filtered out because of the “in” keyword in the “binary” word.
I noticed that when concatenating a string with a binary one
CONCAT("aa", BINARY("BB")) the result will also be binary thus I needed to find a way to have a binary string as an argument of the concat function.
After some trial and error, I found it. JSON object in MySQL are binary objects, therefore,
CAST(0 AS JSON) returns a binary string and accordingly the query
SELECT CONCAT(“A”, CAST(0 AS JSON)) returns a binary string as well.
With that improvement, I managed to retrieve the full flag which actually had only one upper case character, so I spent a few hours figuring out the bypass while I could just guess the flag :facepalm:
The actual challenge and the solution
That’s exactly what I love in CTFs, one simple challenge can lead to pretty awesome research. I am not sure whether my solution is even intended or not but surely it was a great ride! :) (update: it was completely unintended)
The challenge was extremely simple to understand. The whole source code of the challenge is the snippet below:
We ideally would want to use something like
ORD(SUBSTR((SELECT smth),x,1))=77 to retrieve the values in Blind SQL technique, but
ORD is filtered out because of the “or” keyword. It can be easily bypassed with
CONV(HEX(SUBSTR((SELECT ...),x,1)),16,10)=77 that does basically the same job and is also case-sensitive.
Following the discoveries and already retrieved table name, I was fetching the flag with that short payload:
My complete solution:
Update: The intended solution
The intended solution was to bypass blacklisting
UNION .*? SELECT with an overlong statement such as
UNION /*aaa...aaa*/ SELECT 1
Why does it work? Because PHP.
The error code PCRE_ERROR_MATCHLIMIT is returned by the JIT code if searching a very large pattern tree goes on for too long, as it is in the same circumstance when JIT is not used, but the details of exactly what is counted are not the same. The PCRE_ERROR_RECURSIONLIMIT error code is never returned by JIT execution.
With that, you didn’t need a blind SQL Injection at all, and the intended solution was to just retrieve the column name from the
sys.x$statement_analysis table that was inserted on starting the server. However, the author didn’t realize that the record could be auto-removed from there and when I was attempting the challenge it was long gone. I am glad that they did that mistake because the research would be cut in half otherwise :)
There is also another unintended solution posted by MrR3boot, that exploits the UNION…SELECT bypass without knowing the column name. Recommend reading it too!