Types of SQL Injection

Web applications play a very important role in the day to life right from fulfilling our daily needs to our work web applications make our every task easier. With the increasing use of web applications and the data they maintain, they are the frequent targets of attackers to steal our data and perform malicious activities. One such attack is the SQL Injection attack which is carried on applications using a database to store the information. Before starting on describing the attack let us have a look at what is a database

Types of SQL Injection:

There are two types of SQL injection

1. Error Based SQL injection
2. Blind Based SQL injection

Error Based SQL Injection:

The Error based technique, when an attacker tries to insert malicious query in input fields and get some error which is regarding SQL syntax or database.

For example, SQL syntax error should be like this:

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 ‘‘VALUE’’.

The error message gives information about the database used, where the syntax error occurred in the query. Error based technique is the easiest way to find SQL Injection.

How to detect Error based SQL injection?

In the input field parameter add a single quote (‘), double quote (“) as well as can try some SQL keyword like ‘AND’, ‘OR’ for the test.

For example, a single quote is inserted in the title parameter

http://demo.testfire.net/index.php?title=1’

after adding a single quote get some error like

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 ‘‘VALUE’’.

 Let’s see a practical way to find and exploit SQL injection through Error based technique

Case1: We have an application that contains a login page.

Case2: Captured the request which sends the username and password to the application

Case3: Added a single quote ( ‘) to the username field and the application throws an error. The error tells us the user input break the query.

Case4: Save the captured request in txt file and add a custom marker to the username parameter to tell sqlmap to insert the payloads

Case5: Add the text file to sqlmap

Case 6: SQLMap tried different payloads

 Case7: Here we get information about which database is used.

Case8: we can see the database name.

Case9: We can see a table name that is present in the database.

Case10: How much column is present in the table is also we can see.

Union-based Query:

After getting an error we try to exploit the SQL by using SQL query with the help of the “UNION” operator.

The UNION operator is used for combining 2 tables or performing 2 select queries at the same time. In union operators, they remove duplicate row or column which we try to execute at the same time.

For example:-

Select a, b from table 1 UNION select c, d from table 2.

Here we use the union operators for merging data from both tables.

Let’s see a practical way to exploit the UNION operator through Error based technique.

Case1: We check how much column is present in the database.

Case2: After that, we use the UNION operator.

Case3: Try to see the database name and version.

Blind Based SQL injection

By attacking through normal SQLI application gives a normal error message saying that syntax of SQL query is incorrect. Blind SQLI is a type of SQLI technique that works on injecting SQLI query to the database blindly and identify the output based on the change in the behavior of response.

Blind SQLI is not similar to ERROR based in which the user inserts some SQL queries against the database where the user gets a specified error message. The attacker tries to get information by asking the database true or false query. So based on the prediction we need to define the output.

It has 2 types:

1. Boolean based SQLI
2. Time-based SQLI

1. Boolean based SQLI:-

Boolean based SQLI is one in which the attacker is sending an SQL query to the database based on true and false condition and according to that response is getting changed. Helpful to find database name character by character.

Let us take an example to exploit Boolean SQLI using the DVWA application. This is vulnerable to SQLI.

1. The following screenshot is for the DVWA application. It has a module called SQLI blind.

In this, there is a number of users present in the database. as soon as the user enters user id=2 and submits it will go to the database and check whether the following user is available of not. Like SELECT first_name,last_name FROM users WHERE user_id=2

2. If he is present in the database it will show such a message as

And if the user enters some wrong user-id it will show a message as User ID s missing from the database.

AS discussed in Boolean we can’t get them out from the database directly we have to keep on inserting payloads and asking database true and false queries & will check the output according to change in the behavior of response.

1. We have captured the application request using a proxy tool Burp Suite for testing.
2. Observe in this figure we insert a payload id=2’or 1=0#. Meaning that ‘ is to break the syntax of SQL query, or 1=0 is a false condition, id=2 is true #is to comment out the part, or operator works of any input is true it will show output for the true condition so combine this query will look like SELECT first_name,last_name FROM users WHERE user_id=2’or 1=0#.

This says that the user is present in the database

3. Now we insert a payload id=2’ or 1=1#. Meaning that ‘ is to break the syntax of SQL query, or 1=1 is True condition, id=2 is True, #is to comment out the part ,OR operator works of any input is True it will show output for True condition so combine this query will look like SELECT first_name,last_name FROM users WHERE user_id=2’or 1=1# meaning that user ID present in the database.

4. Similarly, you can use AND operators to perform SQL It will show different kinds of output.

5. SO as to exploit back-end database name we have used Substring function. Works as arbitrary guessing characters of the database. This function returns the specified number of characters from a particular position of a given string.

Example;

1. We tried randomly guessing the database name character by charact 2’condition is true, the database is a given string, () is to call the database function, (1,1) row, column structure to find name letter by letter.
2. By inserting ‘a’%23 as the very first letter to guess the name but did not get the output. Similar to ‘b’,’c’.
3. As we insert ‘d’%23 gives Id exist in the database. So We can say that the first letter of the database is ‘D’

4. Brute forcing the characters gives the following output. First Character=’d’, Second Character=’v’, Third Character=’w’, Fourth Character=’a’. You got the database name “DVWA.”

2. Time-based SQLI:

Time based SQI in which attackers insert SQL query causing database pause for a specified amount of time and then returning the results(just delaying the output). This is helpful when the attacker does not have any kind of answer (error/output) from the application because the input validation has been sanitized.

Let us take an example to exploit Time based SQLI using DVWA application.

1. Following is the query to exploit Time based SQLI. Its basic function is to Sleep for supplied seconds. Here we keep it for 5 seconds response is shown in the figure. As the output is delayed for 5ms.

2. Sleep the response for 10 seconds output is to delayed for 10ms

Similarly, you can use different commands to wait for the delay, pg_sleep.

SQLI Mitigation

SQLI is a very dangerous attack that steals your data, modifies it, and causing the attacker to view unauthorized user lists, delete entire tables.

So, it is necessary to prevent this from happening. There are several techniques that a developer can implement in code this might help to reduce taking advantage of SQLI ad perform the harmful tasks.

1. Stored procedure: In this SQL code for a stored procedure is defined and stored in the database and then called from the application when the user inserts input. However, this is not always safe.
2. Whitelist Input Validation: Avoid external malicious input and only allow accepted input. Whitelist validation checks an external input against a set of known (approved input). This is very much useful because the application knows what to allow and reject.
3. Least Privilege: Limit the privilege assigned to every user that is accessing the application. Do not assign database administrator or admin type access rights to your application accounts.

Best Mitigation

4. Parameterized Queries (Prepared statements)

Parameterized queries force the developer to first define all the SQL code, and then pass each parameter to the query later to the application, Unlike stored procedure.

This makes sure that the attacker may not change the content of the query even if he is trying to insert a query against the database.

Take an example where attacker enters the user_ID 2’OR 1=1 the parameterized query will look for a user_ID which literally matched the entire string 2’OR 1=1.

Language specific recommendations for Prepared Statement:

1. Java EE– use Prepared Statement() with bind variables
2. .NET – use parameterized querielike SqlCommand() or OleDbCommand() with bind variables
3. PHP – use PDO with strongly typed parameterized queries (using bindParam())
4. Hibernate – use createQuery()with bind variables (called named parameters in Hibernate)
5. SQLite – use sqlite3_prepare()to create a statement object

Assume following code is vulnerable

String query = “SELECT first_name,last_name FROM users WHERE user_id = ”   + request.getParameter(“user”);

try {

Statement statement = connection.createStatement( … );

ResultSet results = statement.executeQuery( query );}

…

…

// This should REALLY be validated too by the parameterized query.

String user= request.getParameter(“user”); // Perform input validation to detect attacks

String query = “SELECT first_name,last_name FROM users WHERE user_id = ? “;

PreparedStatement pstmt = connection.prepareStatement( query );

pstmt.setString( 1, user);

ResultSet results = pstmt.executeQuery( );

We can also automate this process by using a tool called SQLMAP.