SQL Injection attacks have been a known threat for many years, and so have the ways to prevent them. Despite this, research suggests that these attacks are still being used to exploit application security, and new vulnerabilities related to this threat are being discovered. 

According to the OWASP Top Ten, injection attacks are ranked third among the most dangerous threats [1]. Moreover, the number of known vulnerabilities associated with this threat is also rising [2] . 

When developing an application, paying close attention to security issues is critical. This is especially important because these attacks can pose a severe threat with disastrous consequences. They can allow unauthorized access to the application, enable the extraction of all the data from the database, or even give attackers complete control of the system. 

Therefore, it is essential to understand how these attacks are carried out, how to detect vulnerabilities, and how to protect applications from these threats. 

Source: https://xkcd.com/327

What is SQL Injection? 

SQL Injection is a method of inserting a fully valid SQL code into the application logic by inputting data to force the desired response from the database. The database engine may execute the injected code if the input data is not sanitised correctly. 

We mean input data parameters passed via GET or POST methods, such as from a web form. However, HTTP Cookie or HTTP User-Agent headers can also be used for this purpose, depending on the application and vulnerability. 

Consider an app with a URL like this: 

http://192.168.10.10/products?cat_id=1 

Let this URL returns a list of products for the specified category using the following SQL query:

SELECT * FROM products WHERE category_id=cat_id AND active=1

So, for the above URL, the query executed would be: 

SELECT * FROM products WHERE category_id=1 AND active=1 

The above query returns a list of products for a category whose id is 1 and that meet the condition ‘active = 1’, which limits the products only to those that are marked as active. 

However, if we modify the above URL as follows: 

http://192.168.10.10/products?cat_id=1– 

This will result in the following SQL query being executed (in case the input data is not sanitised properly):

SELECT * FROM products WHERE category_id = 1– AND active=1 

Since ‘–‘ indicates a comment in SQL, the entire query after the comment will not be executed. Therefore, such a query will return all products for category 1, regardless of the ‘active‘ value. 

Let’s modify our URL again: 

http://192.168.10.10/products?cat_id=1+OR+1=1– 

After passing the parameters to the SQL query, we get: 

SELECT * FROM products WHERE category_id=1 OR 1=1– AND active=1 

Since the condition ‘category_id = 1 OR 1=1‘ always returns ‘true‘, the entire query will return all products, regardless of category and ‘active‘ value. 

These are the simplest examples of using SQL Injection, but they show how it’s possible to retrieve data from an insecure application. 

In fact, many different techniques are used to gain unauthorised access to applications or data.

Unauthorized access to the application

SQL Injection is a type of cyber attack that can be used to gain unauthorized access to applications. This type of attack is frequently discussed in cybersecurity literature. 

Let’s assume that two parameters, ‘email‘ and ‘password‘, are used for authentication, which will be entered by the user in the login form and sent using the POST method. 

Let’s also assume, for simplicity, that the following SQL query is used to authenticate users: 

sql = “SELECT * FROM users WHERE email='” + email + “‘ AND password='” + password + “‘” 

Of course, this is a very simplified scenario, and at the same time particularly dangerous, because it does not assume password encryption, but for the purposes of the example it is sufficient. What’s more, as we will see in a moment, the very method of storing and comparing the password in this case loses its importance, because the SQL Injection attack assumes that the password is not known. 

If the following parameter values are transmitted: 

email = john.doe@example.com 

‘password’ = ‘admin’ 

In this case, the query executed by the database engine will take the following form: 

SELECT * FROM users WHERE email=’john.doe@example.com’ AND password=’admin’

If the condition in the ‘WHERE‘ section is met, the user is authenticated. 

However, suppose we don’t know that user’s password. So, let’s modify the value of the ‘email‘ parameter as follows:

john.doe@example.com’– 

In this case, the query sent to the database will take the following form: 

SELECT * FROM users WHERE email=’john.doe@example.com’–‘ AND password=” 

Note that entering ‘–‘ will treat the rest of the query as a comment and the password will not be checked at all. 

A SQL injection attack can also be carried out when even the user’s email address is unknown. Let’s consider the case where we send the following value as the ’email’ parameter: 

‘ OR 1=1– 

Then the query sent to the database will look like this: 

SELECT * FROM users WHERE email=” OR 1=1–‘ AND password =” 

The above ‘WHERE‘ condition will always be true and the query will return all records from the ‘users‘ table. In many cases, this will allow you to log in to the account of the first user returned. 

The consequences of a SQL injection attack can be catastrophic, as it allows unauthorized access to protected resources. However, securing an application against such attacks is relatively simple, and it is crucial to use appropriate software development techniques and secure the program already at the level of creating its architecture. 

UNION operator – retrieving data from another table 

So far, we have analyzed examples when, by properly injecting an SQL fragment, we modified the query while operating on the table that was in the original query. However, SQL Injection allows you to retrieve data from other tables as well, which makes this type of vulnerability even more dangerous. 

The “UNION” operator is used to retrieve data from another table. This operator allows you to execute an additional ‘SELECT‘ query, which is added to the original query. 

Let’s go back to our earlier example, where the original query returned a list of active products for a given category. Let’s just assume, for the sake of simplicity, that the query returns only data from two columns, ‘name‘ and ‘description‘ 

SELECT name, description FROM products WHERE category_id=1 AND active=1 

If, instead of category id, we put the following into the above query: 

1 UNION SELECT email, password FROM users — 

We will then receive: 

SELECT name, description FROM products WHERE category_id=1 UNION SELECT email, password FROM users — AND active=1

In this case, along with the names and descriptions of the products, we will also receive the users’ email addresses and passwords. 

Even if passwords are hashed using cryptographic hashes (which is standard), the hashes obtained can also be an attack vector and (depending on the type of hash function) can also lead to obtaining passwords. 

In our example, however, we have adopted some simplifications. Firstly, we assumed that we knew the number of columns in the ‘SELECT‘ query, and secondly, we assumed that we knew the structure of the database, or at least that we knew about the existence of the ‘users‘ table. 

However, this is not much of a problem for a potential attacker. By modifying the query, you can easily check what is the required number of columns in the query. The name of the table can be guessed. What’s more, if the database is not properly secured, it is possible to obtain knowledge about the database engine used also by means of ‘UNION’. On this basis, knowing how table information is stored in a given type of database, you can retrieve the complete database schema (also using ‘UNION’). With this information, you can download the entire database! That is why this type of attack is so dangerous. 

In this article, we focus on the importance of properly securing applications against SQL Injection attacks and how to detect this type of threat in the solutions we create. That is why we do not analyze in depth all the possibilities of using the ‘UNION’ operator, but only point out how important it is to adopt the right coding standards and what to pay attention to during the implementation of projects. 

Stacked queries – taking control 

SQL Injection attacks that use the “Stacked queries” technique allow hackers to gain control of a database. This involves adding another query to an existing one, but not every type of database supports this operation. 

In cases where databases allow such queries, using this feature when the application is vulnerable to SQL Injection attacks can lead to dangerous situations. 

Going back to our example with the list of products, if the following value is sent as the value of the parameter specifying the category id: 

1; DELETE FROM products 

then the query will take the following form: 

SELECT * FROM products WHERE category_id=1; DELETE FROM products

In this case, the ‘SELECT‘ query will be followed by a ‘DELETE‘ query to delete any records from the ‘products‘ table. This vulnerability can also be used to change data – using ‘UPDATE‘ queries. 

Preventing SQL Injection Attacks 

SQL Injection attacks are a serious threat to database-driven applications. Various techniques can be used to launch these attacks, and it’s crucial to identify security vulnerabilities to protect your application correctly. 

One of the most essential steps to protect against SQL Injection attacks is to sanitise the input data. All user input should be treated as potentially harmful. Avoid concatenating strings and user-submitted data to generate SQL queries, as this can introduce vulnerabilities. Instead, use parameterised queries and stored procedures. 

In addition to this, it’s crucial to ensure appropriate security standards at the database level. The principle of least privilege should be implemented, granting only required permissions to the database user. Error messages should be generic and not reveal sensitive information. 

Web Application Firewall (WAF) systems can be used to filter out potentially harmful URL queries. 

Modern solutions that prioritise security can also minimise the risk of SQL Injection attacks. For instance, Laravel framework offers built-in mechanisms for query builder and ORM to protect against SQL Injection attacks. However, correct usage is essential. 

It’s essential to have consistent standards for creating source code and using tools offered by the framework to minimise the occurrence of vulnerabilities. Protecting against SQL Injection attacks is relatively easy to implement, provided the correct mechanisms are in place. 

---