Introduction to SQL Injections
SQL injection is an attack technique that exploits a security vulnerability occurring in the database layer of an application. Hackers use injections to obtain unauthorized access to the underlying data, structure, and DBMS. By an SQL injection attacker can embed a malicious code in a poorly-designed application and then passed to the back end database. The malicious data then produces database query results or actions that should never have been executed.
By using an SQL Injection vulnerability, given the right circumstances, an attacker can use it to bypass a web application’s authentication and authorization mechanisms and retrieve the contents of an entire database. SQL Injection can also be used to add, modify and delete records in a database, affecting data integrity. To such an extent, SQL Injection can provide an attacker with unauthorized access to sensitive data.
In the following article I’m going to discuss a simple example regarding how to perform an SQL injection and then I’ll explain about differnt types of SQL injections and finally I’ll explain about preventive measures that can be taken to stop an SQL injection attack.
How An SQL Injection Occurs ?
An SQL injection vulnerability allows an attacker to inject malicious input into a SQL statement. To fully understand the issue we first have to understand how server-side scripting languages handle SQL queries. For example, a functionality in the web application generates a string with the following SQL statement:
$query = " SELECT * FROM users WHERE username = 'Charithra' AND
password = '1234' ";
The above database query is a simple query which will return all the fields in the users table where the given “WHERE” condition meets. In here the query must only return the all the fields of the table where the user has a name Charithra and a password of ‘1234’
Example 1 : By inputing malicious code into user input fields
Now let’s consider a SQL query usually uses in a login page.
In here the user can change the values of the username and the password that will be used by the query.
$name = $_POST['Name'];
$id = $_POST['Id'];
$query = " SELECT * FROM users WHERE name = '$name' AND
id = '$id' ";
The above code section describes a scenario where two user-entered variables are passed to the database. Using the POST methods the parameters entered by the user will be sent to the backend code. Then it will be used in the query to retrieve the authentication and authorization details of a given user. The main scenario that needs to be identified in here is that user has entered information that will process in the backend. So that means if there are no filters to check the input, an attacker can easily enter a malicious code which will be eventually processed in the backend. Let’s understand the following code first
$query = " SELECT * FROM users WHERE name = ' admin'; -- ' AND
password = ' anything ' ";
The bold text in the above query is the user input text. “ ; “ is used to instruct the SQL parser that the current statement has ended. This is not necessary in most cases. The “ --” instructs the SQL parser that the rest of the line is a comment and should not be executed. The following figure will make the scenario more clear.
- When analyzing the first query we can see that I have given a faulty id with the admin name. So it has returned with an empty set because there is no “admin” who has an id of 1.
- But when we come to the second query we can see that even though I have given the faulty id, the query has passed. This is because of the
admin'; --input. It has end the query by the delimiter and has instructed to treat the rest of the code as a comment.
To give an empty set, the above queries must pass the both conditions that has connected using an “AND” clause. But in the second query the name input has effectively removed the password check and returned a dataset for an existing user, like admin in this case. Therefore the attacker can now login in with an administrator account without having to specify a password.
The above is a simple scenario to give an idea what can be performed using an SQL Injection attack. There are several types of SQL injection attacks. Some of them are as follows
Types Of SQL Injection Attacks
SQL Injection can be classified into three major categories
- In-band SQL Injection
- Inferential SQL Injection
- Out-of-band SQL Injection
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
- 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
- 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
- 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.
- 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 undersatnding how different types of SQL Injections work. Let’s understand what are the security measures that can be taken to control those attacks
Mitigating SQL Injections
SQL Injection attacks are very common, and this is due to two factors:
- The significant availability of SQL Injection vulnerabilities
- The target is more highlighited :- Because the sensitive data in a system are stored in a databse. So attackers will be attracted towards the target.
Some of the main defenses against SQL atacks are as follows
Use of Prepared Statements
The problem with SQL injection is, that a 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 typical work-flow of using a prepared statement is as follows:
- Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled “?” below):
INSERT INTO PRODUCT (name, price) VALUES (?, ?) - The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.
- Execute: At a later time, the application supplies values for the parameters, and the DBMS executes the statement.
The root of the SQL injection problem is mixing of the code and the data. In the above figure we can see that the query and the data are sent to the SQL server separately.
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.
Summary
Above article is intended to be an introductory article to SQL Injections and it’s basic concepts. Main target of above article is to give a theoretical background how SQL Injections works. I have described what is a mysql injection and how they attack the systems. Then an understanding about the types of SQL Injections. Finally about the preventive methods. I hope this article will provide a brief understanding on how SQL injections works.
