Database Security

 

SQL Injection Attacks and Database Security

Introduction

    Most websites, other than very simple ones, use databases that are used to store data, so they store user names, passwords, news articles, blog posts, pictures, etc. Anything that happens on the website is stored on a database and the web application queries the database and then displays the data to you or the users, on screen. When the user does something, it will either update, delete, or modify the database's data. This interaction, between the web application and the database, happens mainly using a language called SQL.

SQL injection is an attack technique or a vulnerability that occurs in the database of an application where the attacker can exploit the vulnerable code, input that the application takes from users and any parameter influencing the database query and alter the query which further can be used to retrieve sensitive data, changing the original data, deleting the data and even gain full access of the database server.

How can SQL injection vulnerability be exploited?

SQL injection vulnerability allows the attacker to inject malicious input into a SQL statement. To understand this let us take an example,

Login Page (Mutillidae is a free, open-source, deliberately vulnerable web application providing a target for web-security training)

At times we are prompted to enter our ID and Password while logging in to any website. We can perform SQL injection into the text field where we are driven to enter our ID or Password. Let us submit “” into the password field. It results in an error as SQL query by default adds “” around the username and password. The error can be very useful too as it is giving us different types of information about the database.

Error

Let Us try to inject a SQL query inside the password section. The sql query will be ’123456’ and 1=1. This will go inside the passwords section and in the database, it will be executed as SELECT * from accounts where username=’xxx’ and password= ’123456’ and 1=1’. Everything after ’ will be commented out. This query results in a true statement thus, the user gets logged in. This example depicts the most basic type of SQL injection.

Now let's try a false query saying SELECT * from accounts where username=’xxx’ and password=’123456’ and 1=2’.

Login Failed

Now the login failed. So this website is executing our queries and it has SQLi Vulnerability which can be exploited.

Previously we have injected SQL queries using the and operator. The result of and operator is true if both the conditions are true. Instead, if or operator is used then the login can be easily bypassed. The new query inside the password field will be anyRandomPassword’ or 1=1 which will always be true and log in the attacker.

Detecting SQL injection vulnerabilities:

There are automated tools to detect SQL vulnerabilities like Burp Suite’s web vulnerability scanner however the same can be done manually using hit and trail too. To detect the vulnerability we can,

1. Enter the single quote character ‘ and look for errors.

2. Enter SQL syntax that evaluates to true like boolean conditionals such as or 1=1, or 1=2, etc and check the differences in the responses of the application.

3. Introducing the time delay and noting the time taken to respond.

Types of SQL Injection attacks

This kind of injection attacks can be classified as:

• In-band SQLi / Classic SQLi:
  Sub-types: Union-based SQLi, Error-based SQLi
• Inferential SQLi / Blind SQLi
  Sub-types: Boolean-based Blind SQLi, Time-based Blind SQLi
• Out-of-band SQLi

1) In-band SQL Injections

In-band SQL Injection is the most common and easy-to-exploit of SQL Injection attacks. In-band SQL Injection occurs when an attacker is able to use the same communication channel to both launch the attack and gather results. As an example an attacker may use the HTTP communication deploy the attack to a backend and get the results on the same channel like example 1 in this article.

There are two main types of In-band SQL injections

1. Error-based SQL injection :- Error-based SQL Injection is an in-band SQL Injection technique that relies on error messages thrown by the database server to obtain information about the structure of the database
2. Union based SQL injection :- Union-based SQL Injection is an in-band SQL injection technique that leverages the UNION SQL operator to combine the results of two or more SELECT statements into a single result which is then returned as part of the HTTP response.

2) Inferential SQL Injection(Blind SQL Injection)

In an inferential SQLi attack, no data is actually transferred via the web application and the attacker would not be able to see the result of an attack in-band. Instead, an attacker is able to reconstruct the database structure by sending payloads, observing the web application’s response and the resulting behavior of the database server.

There are two types of Blind SQL Injections

1. Boolean-based Blind SQL Injections :- This is a type of attack that asks the database true or false questions and determines the answer based on the applications response. This attack is often used when the web application is configured to show generic error messages, but has not secureed the code that is vulnerable to SQL injection.
2. Time-based Blind SQL Injections :- Time-based SQL Injection is an inferential SQL Injection technique that relies on sending an SQL query to the database which forces the database to wait for a specified amount of time (in seconds) before responding. The response time will indicate to the attacker whether the result of the query is TRUE or FALSE.

Blind SQL injection is nearly identical to normal SQL injection, the only difference being the way the data is retrieved from the database. When the database does not output data to the web page, an attacker is forced to steal data by asking the database a series of true or false questions. This makes exploiting the SQL Injection vulnerability more difficult, but not impossible.

3) Out-of-band SQL Injections

This not very common type of injection, mostly because it depends on features being enabled on the database server being used by the web application. Out-of-band SQL Injection occurs when an attacker is unable to use the same channel to launch the attack and gather results.

As we now have an understanding of how different types of SQL Injections work. Let’s understand what are the security measures that can be taken to control those attacks

Use of Prepared Statements

The problem with SQL injection is, that user input is used as part of the SQL statement. By using prepared statements you can force the user input to be handled as the content of a parameter (and not as a part of the SQL command).

In a prepared statement, the values that will be inserted into a SQL query are sent to the SQL server after the actual query is sent to the server. In other words, the data input by a potential hacker is sent separately from the prepared query statement. This means that there is absolutely no way that the data input by a hacker can be interpreted as SQL, and there’s no way that the hacker could run his own SQL on our application. Any input that comes in is only interpreted as data, and can not be interpreted as part of your own application’s SQL code. The downside of this approach is that it can have an impact on performance, albeit slight, and that each query on the site must be structured in this method for it to be completely effective. If one query is inadvertently bypassed, that could be enough to leave the application vulnerable to SQL Injection. The following code shows a sample SQL statement that is SQL injectable.

• sSql = “SELECT LocationName FROM Locations “;
  sSql = sSql + “ WHERE LocationID = “ + Request[“LocationID”];
  oCmd.CommandText = sSql;
• The following example utilizes parameterized queries, and is safe from SQL Injection attacks.
• sSql = “SELECT * FROM Locations “;
  sSql = sSql + “ WHERE LocationID = @LocationID”;
  oCmd.CommandText = sSql;
  oCmd.Parameters.Add(“@LocationID”, Request[“LocationID”]);
  The application will send the SQL statement to the server without including the user’s input. Instead, a parameter-@LocationID- is used as a placeholder for that input. In this way, user input never becomes part of the command that SQL executes. Any input that an attacker inserts will be effectively negated. An error would still be generated, but it would be a simple data-type conversion error and not something which a hacker could exploit.

Escaping all user supplied input

You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.

Validate input

The vast majority of SQL Injection checks can be prevented by properly validating user input for both type and format. The best method of doing this is via “white listing”. This is defined as only accepting specific account numbers or specific account types for those relevant fields, or only accepting integers or letters of the English alphabet for others. Many developers will try to validate input by “black listing” characters, or “escaping” them. Basically, this entails rejecting known bad data, such as a single quotation mark, by placing an “escape” character in front of it so that the item that follows will be treated as a literal value. This approach is not as effective as white listing because it is impossible to know all forms of bad data ahead of time.

Following are some of the other measures that can be used to avoid SQL injections:

Restrict Application Privileges

Strong SA Password Policy

Consistent Error Messaging Scheme

Stored Procedures

Summary

This blog is intended to be an introductory article to SQL Injections and how to avoid them. The main target of the above article is to give a theoretical background on how SQL Injections work and the preventive methods.