Blind SQL Injection without an “in”

Alternative ways to retrieve table names in MySQL — without information_schema.

terjanq
terjanq
Jan 7 · 5 min read

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
  • information_schema
  • “in” and “or” keywords

Although the filter was way more complex, these were the hardest obstacles to overcome.

Full blacklist with examples

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 sys.x$schema_flattened_keys

sys.x$schema_flattened_keys

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:

sys.schema_table_statistics

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 :)

SELECT query FROM sys.x$statement_analysis WHERE query LIKE ‘%Th1z_Fack1n_Fl4444g_Tabl3%’

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!

SELECT (SELECT 1, ‘aa’) = (SELECT * FROM example)

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

BINARY(‘Aa’)

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.

Force case-sensitive comparison

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:
TetCTF{0wl_d0nkey_means_Liarrrrrrr}

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:

Source code of the challenge

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.

Oracle for Blind SQL Injection 1

Following the discoveries and already retrieved table name, I was fetching the flag with that short payload:

My complete solution:

Complete solution

https://gist.github.com/terjanq/3cf40b6ad5a2bbac72aaf0bd4aa53ab8

Update: The intended solution

The intended solution was to bypass blacklisting UNION .*? SELECT with an overlong statement such as UNION /*aaa...aaa*/ SELECT 1

PHP backtracking bypass

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!

terjanq

Written by

terjanq

Security enthusiast that loves playing CTFs and hunting for bugs in the wild. Also likes to do some chess once in a while. twitter.com/terjanq

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade