SQL Injection Types.

SQL Injection Types:

There are various types of SQL Injection. We will consider a few of them below.

1.) Union-based SQL Injection:

With the help of the union operators, we can combine the results of two or more SELECT statements to a single output. The return of this single output is a part of the HTTP response. We can use it to extract data from other tables as well.

Example:

‘ AND ‘a’=’b’ UNION SELECT 101, ‘abc’, ‘xyz’ FROM members WHERE ‘a’=’a

The single quote at the start specifies the end of the original query. The first query must set to FALSE, hence we specify the condition as ‘a’=’b’ after AND function. Both queries that are before and after UNION must have the same structure otherwise it will generate an error.

It will generate the SQL query after getting the input from the user as:

SELECT id, name, description FROM products WHERE category = ” AND ‘a’=’b’ UNION SELECT 101, ‘abc’, ‘xyz’ FROM members WHERE ‘a’=’a’

So it will return Usernames and Passwords from the table Member instead of results from Products table. As we altered the first WHERE clause, it will not return any data.

2.) Boolean-based SQL Injection:

The type of example we have so far discussed is the case in Boolean-based SQL Injection. In this, we use a Boolean expression that will return the value TRUE for executing the SQL Injection attack.

Example:

Input: 1 OR 1=1

It will generate the SQL query as:

Select a subject, teacher from Grade11 where index=1 or 1=1

As condition 1=1 will always be going to TRUE, SQL Injection attack can be executed and the attacker can extract a considerable amount of data from the database.

3.) Error-based SQL Injection:

This type of attack is based on sending a malicious query to the database which will return an Error message. Depending on the type of Error message he is getting in return, an attacker can analyze the structure of the database and data stored in it.

Example:

SELECT COUNT(*), CONCAT((SELECT @@version),0x3a,FLOOR(RAND(0)*2)) x FROM information_schema.tables GROUP BY x

When you pass this query you will receive the following error message:

Duplicate entry ‘5.1.73-0ubuntu0.10.04.1:1’ for key ‘group_key’

This error message is generated because GROUP BY requires unique group keys but COUNT(*) always returns the same value thus causing an error of Duplicate values back to the user.

4.) Time-based SQL Injection:

Most attackers use this attack to slow down the database server. It is of great importance when you aren’t able to enter into the database; you can simply slow down the server which will further bring down the application that is running on the database.

SLEEP function is the function which we use in the query that pauses the execution of it for the mentioned time.

Example:

Input: 1 – SLEEP(20)

A query that will be generated with the SLEEP Function is:

Select * from the subject where index=1-SLEEP(20)

Here SLEEP function will pause the query execution by 20 seconds.

SQL Injection Prevention Techniques:

A major reason why the database is vulnerable to SQL Injection attack is due to the developers creating dynamic database queries that take “Input from Users“.

To prevent SQL Injection attack, developers have to focus on two main aspects:

1.) Avoid writing Dynamic Queries.

2.) Processing the user-supplied input containing malicious SQL query such that it won’t be able to affect the logic of the executable query.

OWASP has listed some easy techniques to prevent SQL Injection Vulnerability that counters above mentioned two aspects. All the programming languages can use these techniques irrespective of the type of database we use.

OWASP defined Primary Defense Techniques:

1.) Option 1: Use of Prepared Statements (with Parameterized Queries).

2.) Option 2: Use of Stored Procedures

3.) Option 3: Whitelist Input Validation

4.) Option 4: Escaping All User Supplied Input

There are also other additional Defense Techniques which are:

• Enforcing the Least Privilege
• Performing Whitelist Input Validation as a Secondary Defense

How to prevent SQL Injection in java:

Consider the following java code which is vulnerable to SQL injection attack. As the input “EmployeeName” is appended to the query without any validation allows the attacker to inject the malicious SQL code to it.

String query = “SELECT salary FROM employee WHERE employee_ID = “ + request.getParameter(“EmployeeName”); try { Statement statement = connection.createStatement( … ); ResultSet results = statement.executeQuery( query ); } …

Prepared Statement to remove SQL injection vulnerability:

Using a Prepared Statement with parameterized queries is the easiest way by which SQL injection vulnerability can be removed. They are easy to write and to understand than dynamic queries.

The parameterized queries force the developer to define all the SQL code before passing each parameter to the query. It permits the database to distinguish between code and user input data.

If the attacker passes SQL command as an input as follows

’ OR ‘1’=1’

It will treat it as a complete string and will look for the matching field in the database, thus removing the SQL Injection vulnerability.

Following is the code that uses Prepared Statement that implements parameterized query.

// This should REALLY be validated too String empname = request.getParameter(“EmployeeName”); // Perform input validation to detect attacks String query = “SELECT salary FROM employee WHERE employee_ID = ? “; PreparedStatement pstmt = connection.prepareStatement( query ); pstmt.setString( 1, empname); ResultSet results = pstmt.executeQuery( );

How to prevent SQL Injection in PHP:

Similar to Java, in PHP too, to remove the SQL injection vulnerability, we can use Prepared Statement with parameterized queries. In this, the database server parse the SQL statements separately.

We can achieve this in two ways:

1.) Using PDO (for any supported Database Driver):

$stmt = $pdo->prepare(‘SELECT * FROM students WHERE id = :id’); $stmt->execute(array(‘id’ => $id)); foreach ($stmt as $row) { // do something with $row }

2.) For MySQL:

$stmt = $dbConnection->prepare(‘SELECT * FROM students WHERE id = ?’); $stmt->bind_param(‘s’, $id); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // do something with $row }

SQL Injection Prevention tools:

The following are some of the tools that we use to 3.) Microsoft Source Code Analyzer for SQL Injection:

Similarly, Microsoft has also developed a Source code analyzer tool that helps to find SQL Injection vulnerabilities in ASP code.

By running this tool on the ASP Source web developer can identify the cause of the attack and address them for the Intrusion Detection System. You can download it from the Microsoft Download Center. You can use it free of cost. SQL Injection attacks.

1.) URL Scan:

It is a security tool that restricts the types of HTTP requests processed by Microsoft Internet Information Services (IIS). It blocks specific HTTP requests;

In other words, it restricts HTTP requests from reaching the server, thus reducing the SQL Injection attack to a greater extent. Moreover, in addition to SQL injection, it is also effective in reducing other types of Internet attacks.

Therefore when it denies any request, it logs the action and corresponding reason for the denial of the request which is helpful in the future for the Signature-based Intrusion Detection System.

As it denies any request, it will display a “404 Object not Found” error to the user. This helps to prevent displaying any information about the server to the user as seen in the case of Error-based SQL Injection

2.) HP Scrawlr:

We use this tool to analyze the SQL injection vulnerabilities in the web application. It is easy to use tool that efficiently scans through all the web pages.

However, It can be used even if you are not at a pro-level with an understanding of SQL. You just have to install the application which is only 1.65 MB in size. This app does the rest of the functioning.

3.) Microsoft Source Code Analyzer for SQL Injection:

Similarly, Microsoft has also developed a Source code analyzer tool that helps to find SQL Injection vulnerabilities in ASP code.

By running this tool on the ASP Source web developer can identify the cause of the attack and address them for the Intrusion Detection System. You can download it from the Microsoft Download Center. You can use it free of cost.