Exploiting hard filtered SQL Injections 3

This is a follow-up post of the first edition of Exploiting hard filtered SQL Injections and at the same time a writeup for Campus Party CTF web4. In this post we will have a closer look at group_concat() again.

Last month I was invited to Madrid to participate at the Campus Party CTF organized by SecurityByDefault. Of course I was mainly interested in the web application challenges, but there was also reverse engineering, cryptography and network challenges. For each of the categories there was 4 difficulty levels. The hardest webapp challenge was a blind SQLi with some filtering. Techniques described in my last blogposts did not helped me so I had to look for new techniques and I promised to do a little writeup on this.
The challenge was a news site with a obvious SQLi in the news id GET parameter. For different id’s specified by the user one could see different news articles while a SQL error resulted in no article being displayed. The filter was like the “basic keyword filter” I already introduced here with additional filtering for SQL comments:

if(preg_match('/\s/', $id))
	exit('attack'); // no whitespaces
if(preg_match('/[\'"]/', $id))
	exit('attack'); // no quotes
if(preg_match('/[\/\\\\]/', $id))
	exit('attack'); // no slashes
if(preg_match('/(and|null|where|limit)/i', $id))
	exit('attack'); // no sqli keywords
if(preg_match('/(--|#|\/\*)/', $id))
	exit('attack'); // no sqli comments

The first attempt was to create a working UNION SELECT with %a0 as a whitespace alternative which is not covered by the whitespace regex but works on MySQL as a whitespace.


However no UNION SELECT worked, I had no FILE PRIV and guessing the table and column names was too difficult in the short time because they were in spanish and with different upper and lower case letters. So I decided to go the old way with parenthesis and a CASE WHEN:


The news article with id=1 is shown when the first letter of all concated table names is ‘a’, otherwise news article with id=2 is shown.

As stated in my last post the output of group_concat() is limited to 1024 characters by default. This is sufficient to retrieve all table names because all default table names concated have a small length and there is enough space left for custom tables.
However the length of all standard columns is a couple of thousands characters long and therefore reading all column names with group_concat() is not easily possible because it will only return the first 1024 characters of concated standard columns of the database mysql and information_schema *.
Usually, the goal is to SELECT column names only from a specific table to make the result length smaller than 1024 characters. In case WHERE and LIMIT is filtered I presented a “WHERE alternative” in the first part:


Here I co-SELECTed the column table_name to use it in the HAVING clause (otherwise the error Unknown column ‘table_name’ in ‘having clause’ would occur). In a subSELECT you cannot select from more than one column and this is where I struggled during the challenge. The easiest way would have been to use GROUP BY with %a0 as delimiter:


But what I tried to do is to find a way around the limiting 1024 character of group_concat(). Lets assume the keywords “group” and “having” are filtered also 😉 First I checked the total amount of all columns:


Compared to newer MySQL versions the amount of 187 was relatively small (my local MySQL 5.1.36 has 507 columns by default, it was MySQL 5.0).
Now the idea was to only concatenate the first few characters of each column_name to fit all beginnings of all column_names into 1024 characters. Then it would be possible to read the first characters of the last columns (this is where the columns of user-created tables appear). After this the next block of characters can be extracted for each column_name and so on until the whole name is reconstructed.
So the next step was to calculate the maximum amount of characters I could read from each column_name without exceeding the maximum length of 1024:

5 characters * 187 column_names = 935 characters

Well thats not correct yet, because we have to add the commas group_concat() adds between each column. That is additional 186 characters which exceeds the maximum length of 1024. So we take only 4 characters per column_name:

4 characters * 187 column_name + 186 commas = 934 characters

The injection looked like this:


To avoid finding the right offset where the user tables starts I began to extract column name by column name from the end, until I identified columns of the default mysql database (a local mysql setup helps a lot).

I think the following graphic helps to get a better idea of what I did.
The first SELECT shows a usual group_concat() on all column names (red blocks with different length) that misses the columns from user-created tables that appear at the end of the block list.
The second query concatenates only the first 4 characters (blue) of every name to make the resultset fit into the 1024 character limit. In the same way the next block of 4 characters can be SELECTed (third query).

Each string of concatenated substrings can be read char by char to reconstruct the column names (last query).

It gets a bit tricky when the offsets change while reading the second or third block of 4 characters and you need to keep attention to not mix up the substrings while putting them back together for every column name. A little PHP script automated the process and saved some time. Although this approach was way to complicated to solve this challenge, I learned a lot 😉
In the end I ranked 2nd in the competition. I would like to thank again SecurityByDefault for the fun and challenging contest, especially Miguel for the SQLi challenges and give kudos to knx (1st), aw3a (3rd) and LarsH (the only one solving the tough reversing challenges).

By the way the regex filters presented in the last posts are not only for fun and challenges: I have seen widely used community software using (bypassable) filters like these.

* Note that the exact concated length and amount of columns and tables depends on your MySQL version. Generally the higher your version is, the more column names are available and the longer is the concated string. You can use the following queries to check it out yourself:

select sum(length(table_name)) from information_schema.tables where table_schema = 'information_schema' or table_schema='mysql'
select sum(length(column_name)) from information_schema.columns where table_schema = 'information_schema' or table_schema='mysql'

Part 1, Part2, SQLi filter evasion cheatsheet


17 Responses to Exploiting hard filtered SQL Injections 3

  1. toby57 says:

    How To ByPass The Filter Like
    preg_match(‘/(select)/i’, $id)&&die();
    when the SELECT keyword filtered,How to get data from other table?

  2. mgesteiro says:

    very detailed explanation as usual.

    nice post!

  3. hc0de says:

    Hi, yours “Exploiting hard filtered sql injections” serie is great!

  4. Hünkar says:

    Türkçesini de paylaşırsan veya bi yere upload edersen cok güzel olur 🙂

  5. _antivirus_ says:

    very good..
    it’s a great article on sql injection

  6. Adi says:

    Very informative series! Thanks!

    I’ve a question, though: What to do, if word needed for identifiers get filtered out?
    if (preg_match(‘/users|username/’, $id)) exit(‘attack’);

    How can a query like the following be successful?

  7. Reiners says:

    interesting question. in mysql you can use case-insensitive table names, so bypassing your regex can simply be done with:

    SELECT username FROM usErS

    If you also check for that there is no other way that comes to my mind atm because there is no eval and you cant encode the table name somehow. fortunately most times a filter is more SQL specific, e.g. also checking for the keyword FROM to avoid blocking legal requests that contain the word “users”:

    if (preg_match(‘/FROM (users|username)/i’, $id)) exit(‘attack’);

    then you could use bypasses like:


  8. f0n says:

    very nice post Reiners, good moments :).
    I also like your last project RIPS, I’ve been testing it 🙂

  9. anjin says:

    First of all i love your blog postings on mysql injection and syntax.
    I’ve learned alot here and would like to say thanks!

    Secondly, do you think it is possible to exploit (blind) mysql injection with commas being filtered?

    And if that is not the case, wouldn’t it be enough for a decent filter to just block commas and server error output to block all injections?

    • Reiners says:

      as I have seen lightos already answered you at sla.ckers. for the record:

      1 AND (SELECT SUBSTRING(table_name FROM 1 FOR 1) FROM information_schema.tables WHERE version=10 LIMIT 1)>CHAR(0)

      Btw I would never recommend to filter anything specific as protection, even if there is no known bypass.

  10. clay says:

    well nice work but i have a question suppose that we have a blind sql injection and a mod_security that filter the word name so when we will try for exemple this query substring((select column_name from information_schema.columns),1,1) limit 1,1 well we will face a error so is there any solution “especially” when we dont know any table name

  11. clay says:

    again nice work but i think that i found the best solution to be safe from sql injection it’s simple but it does the job suppose that in your database you have a table “users” that containe two columns “username” and “password” simply you can filter the columns name or the table name and i think that it will be much more easy and fast to do the job.


  12. Reiners says:

    @clay: first of all I think you missed the message of this blogpost. You should never implement filters to prevent SQLi. However there are some IDS and WAFs and this blogpost shows how to bypass those filters and why they don’t make sense.
    To protect against SQLi you should always use typecasting or escaping. You can find more at the end of this blogpost:


    Further filtering for “users” and “username” will lead to lots of false positives depending on your webapplication.

  13. Xacker says:

    I have “FROM” keyword filtered.

    Alternative letter case isn’t possible to bypass the filter, I think the value gets lowercased before checked.

    Also surrounding FROM with /*!..*/ or parenthesis or anything wasn’t helpful.

    Any tip on that? Thanks

  14. aman says:

    What if ‘ and — ars filtered then is there any way so i could escape the previous ‘ to run a code like
    Username:’ or 1=1; drop table [tablename];–
    I tried using char(39) instead of ‘, the filter is bypassed but it says in correct id/passwd it means its not converting char(39) into ‘
    Plz help i m facing this prblm since a long time but no soln…

  15. Akshay says:

    First of all I would like to say that your blog is the most informative one that I found on the net. Keep up the good work man!!
    I have a query? Could you help me with that?
    If i encounter a mysql_fetch_row error and nothing else, then can it be exploited?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: