SQL (Structured Query Language) is a coding language that allows users to interact with databases, for example on websites. Modern web applications use databases to manage data and display dynamic content to users, which is why they are quite essential for websites.
But cyber criminals have found a way to attack the web applications and compromise the databases. Such an attack is called an SQL injection.
What is SQL injection?
SQL injection, also known as SQLI, is a common attack vector that can use prepared statements in the form of malicious SQL code for database manipulation to gain access to information that should have been hidden. This information may include sensitive company data, username, password, user lists or private customer information.
There are several possible consequences of SQL injection and it can affect businesses significantly. A successful attack targeting businesses can result in unauthorised viewing of user lists, deletion of data and, in some cases, the cybercriminal behind the attack gaining administrative rights to a database, which can be very damaging to a business.
In addition, an SQL injection attack can lead to companies losing customer confidence if personal information such as phone numbers, addresses and credit card details are stolen and published or sold on the dark web.
SQL stands for Structured Query Language and is a specific type of code. Although this vector can be used to attack any SQL database, attacks are often targeted at websites.
An SQL injection is thus a kind of cyber attack where a cyber criminal, by injecting hostile code (SQL code) into a database, can manipulate it and gain access to potentially valuable information stored in that database.
SQL injection attacks are a widespread and dangerous form of cyber attack, as they can potentially be used against any web application or website that uses an SQL-based database. And there are quite a few that do.
Types of SQL injection attacks
There are many types of SQL injection attacks, but SQL injection typically falls into three categories:
In-band SQL injection (Classic)
Inferential SQL injection (Blind)
Out-of-band SQL injection
You can divide SQL injections based on the methods they use to access data and the damage they can potentially cause.
In-band SQL injection
This is the simplest form of SQL injection. The hacker uses the same communication channel to launch their attack and to collect their results. The simplicity and effectiveness of in-band SQL injection makes it one of the most common types of SQL attacks.
There are two sub-variations of this method:
Error-based SQL injection - the hacker performs actions that cause the database to generate error messages. The hacker can potentially use the data from these error messages to gather information about the structure of the database.
Union-based SQL injection - the attack exploits the UNION SQL operator, which concatenates multiple selected statements generated by the database to obtain a single HTTP response. This response may contain data that can be exploited by the attacker.
Inferential (blind) SQL injection
The hacker sends queries to the database and observes its response and behavior to learn more about its structure. This method is called blind SQL injection because the data is not transferred from the website's database to the hacker, and therefore the hacker cannot see information about the attack.
Blind SQL injections rely on database response and behaviour patterns, so they typically take a long time to execute, but can be very damaging. A blind SQL injection can be divided into two types.
Boolean - An attacker sends an SQL query to the database asking the application to return a result. The result will vary depending on whether the query is true or false. Based on the result, the information in the HTTP response will either change or not change. The hacker can then find out whether the message generated a true or false result.
Time-based - The hacker sends an SQL query to the database, which causes the database to wait before it can respond. The hacker can tell from the time it takes the database to respond whether a query is true or false. Based on the result, an HTTP response will be generated immediately or after some time. The hacker can thus figure out whether the query used returned a true or false answer, without relying on data from the database.
Out of band SQL injection
The hacker can only perform this type of attack when certain functions are enabled on the database server used by a web application. This type of attack is primarily used as an alternative to in-band and inferential SQL techniques.
Out-of-band SQL injection is performed when hackers do not have the possibility to use the same channel to launch the attack and gather information, or when a server is too slow or unstable for these actions to be performed. This type of attack exploits the database server's ability to create DNS or HTTP requests to transfer data to the hacker.
Example of SQL injection attack
A cybercriminal wishing to perform an SQL injection manipulates a standard SQL query to exploit unvalidated input vulnerabilities in a database. There are many ways in which this attack can be carried out. Several ways are mentioned here to provide an overview of how SQL injections work.
SQL is a standardized language used to access and manipulate databases to build customized data views for each user. SQL queries are used to perform commands such as retrieving data, performing updates, and removing data records. Various SQL elements implement these tasks, such as queries that use the SELECT statement to retrieve data, based on parameters supplied by the user.
An example of an SQL query for an online shopping website ("e-store") might be:
SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = ItemNumber
From this, the web application creates a string query that is sent to the database as a single SQL statement:
sql_query= "SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = " & Request.QueryString("ItemID")
A user supplied input "http://www.estore.com/items/items.asp?itemid=999" can then generate the following SQL query:
SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = 999
As can be seen from the syntax, this query returns the name and description of item number 999 on the shopping website.
The above input, which retrieves information for a specific product, can be changed to be "http://www.estore.com/items/items.asp?itemid=999 or 1=1".
As a result, the corresponding SQL query looks like this:
SELECT ItemName, ItemDescription FROM Items WHERE ItemNumber = 999 OR 1=1
And since the statement 1 = 1 is always true, the query returns all product names and descriptions in the database, including those to which the user may not be entitled to access.
Hackers can also exploit certain characters that are incorrectly filtered to modify an SQL query, including using a semicolon to separate two fields.
For example, the input "http://www.estore.com/items/iteams.asp?itemid=999; DROP TABLE" can generate the following SQL query:
SELECT ItemName, ItemDescription FROM Items WHERE ItemNumber = 999; DROP TABLE USERS
This query could risk deleting the entire user database.
Another way SQL queries can be manipulated is with a UNION SELECT statement. This combines two unrelated SELECT queries to retrieve data from different database tables.
The input "http://www.estore.com/items/items.asp?itemid=999 UNION SELECT user-name, password FROM USERS" creates the following SQL query:
SELECT ItemName, ItemDescription FROM Items WHERE ItemID = '999' UNION SELECT Username, Password FROM Users;
Using the UNION SELECT statement, this query combines the request for Item 999's name and description with another that retrieves the entered username and password for each user in the database.
Preventing and securing against SQL injection
There are several effective ways to prevent SQLI attacks from manipulating databases and ways to minimize the damage if an attack does occur.
The first way is input validation, where code is written to identify illegitimate user input.
While input validation should always be considered best practice, it is not a bulletproof solution. In many cases, it is not possible to determine all legitimate and non-legitimate inputs, at least not without creating many false positives that disrupt the user experience and an application's functionality.
Therefore, in addition to using input validation, a web application firewall (WAF) can be used to filter out SQL injections and other cyber threats. For a WAF to identify and filter out SQL injection attacks, it relies on a large and constantly updated list of specially crafted signatures that enable it to detect and remove malicious SQL queries. This list usually contains signatures to address specific attack vectors and is regularly updated to protect new vulnerabilities.
Modern web application firewalls are also often integrated with other security solutions. A WAF can receive additional information from them and provide even higher security.
A web application firewall that encounters a suspicious but not directly malicious input can cross-verify it with IP data before itone decides whether to block the input or not. It only blocks input if there is something suspicious about the IP.
A cloud-based WAF can use signature recognition, IP data and other security measures to identify, validate and block SQL injections without filtering out very many legitimate inputs. Of course, there is a risk that a WAF blocks legitimate input.
About the author
Sofie Meyer is a copywriter and phishing aficionado here at Moxso. She has a master´s degree in Danish and a great interest in cybercrime, which resulted in a master thesis project on phishing.