SQL injection (SQLi)

What is SQL injection?

SQL injection (SQLi) is a vulnerability that lets a malicious hacker introduce (inject) undesired SQL code into SQL queries executed by the software.

Severity:		very severe
Prevalence:		discovered often
Scope:		may appear in software that uses SQL
Technical impact:		access to the database or system information
Worst-case consequences:		full system compromise
Quick fix:		use prepared statements also known as parameterized queries

How does SQL injection work?

If an application uses an external database, it needs to create queries to that database and retrieve results. Most applications use relational databases that support SQL (Structured Query Language). This is a text language designed to be simple and easy to understand by humans. Popular databases supporting SQL include Oracle, Microsoft SQL Server (MSSQL), MySQL, PostgreSQL, and more.

Queries to databases are rarely static – the information that the application needs to get from the database or store in it usually depends on data supplied by the user. User input is typically in the form of simple text, just like SQL syntax itself, so developers often create queries by directly concatenating data supplied by the user with SQL statements. For example, SELECT first_name,last_name FROM users WHERE user_id = 'id_supplied_by_the_user' returns the first and last name of the user based on the ID supplied by the user.

If there is no input validation, a malicious hacker may use web page input forms or direct HTTP requests to provide a payload that contains SQL statements. If the application simply concatenates such user data with static commands, the attacker is often able to completely change the syntax and the way the original query works. They may be able to use special characters such as single quotes or semicolons to add commands and/or ignore static commands. The resulting malicious code may even allow the attacker to perform commands such as DROP (deleting a database table or even the entire database). This is called an SQL injection.

SQL injections can happen in any software that communicates with SQL databases. They are most prevalent in web application security because web applications very often use back-end SQL servers. However, they may occur in other types of applications and systems, too. 

SQL injections are considered one of the oldest known vulnerabilities – they were first documented in 1998. They are classified as CWE-89: Improper neutralization of special elements used in an SQL command and are included in OWASP Top 10 A3:2021 category (Injection).

A simple example of SQL injection

Let’s see what an attacker can do with the following simple authentication example:

SELECT * FROM users WHERE user_id = 'id_supplied_by_the_user' AND password = 'password_supplied_by_the_user'

This simple SELECT statement returns all relevant user data if there is a matching ID and password record in the database. This means that if the user provides a valid ID and password, the query might return the first and last name of a user (depending on the schema of the users table). If the user provides an invalid ID and/or password, the query returns an empty data set. A developer might use this simple query to check if the user can log in.

A malicious hacker might provide the following id_supplied_by_the_user value:

admin'--

As a result, the query string sent to the database will become:

SELECT * FROM users WHERE user_id = 'admin'--' AND password = ''

The single quote completes the assignment of user_id and the double dash (--) causes the rest of the SQL statement (i.e. the password check) to be treated as a comment. Therefore, the application executes the following query:

SELECT * FROM users WHERE user_id = 'admin'

If executed, this query represents a successful SQL injection. It returns all user data for admin, potentially allowing the malicious hacker to attain unauthorized access to the application as the administrator.

Types of SQL injection vulnerabilities

There are 5 primary SQL injection techniques. Each of them is described in detail in a separate section of this guide:

• In-band SQLi (error-based and union select-based)
• Inferential/blind SQLi (boolean-based and time-based)
• Out-of-band SQLi

Potential consequences of an SQL injection attack

SQL injection is one of the highest severity vulnerabilities for two reasons. First of all, databases accessed by web applications often contain highly sensitive information of top value to attackers. Therefore, attackers are very interested in getting their hands on this data. 

Secondly, exploiting SQL injections in combination with other common vulnerabilities can have dramatic consequences. It is even possible to obtain operating system access through an SQL injection, opening the way to a complete system takeover.

Typical consequences of SQL injections include:

• Access to sensitive data stored in the database, such as passwords and/or credit card numbers
• Access to information about the database and operating system to aid further attacks

If the attacker is able to use privilege escalation (privilege elevation) to obtain operating system access and then root access, they may follow up with any of the following common types of attacks:

• Ransomware or other malware: The attacker may install a ransomware agent on the machine. The malware will then use other methods to spread to other assets owned by the victim, potentially crippling entire enterprise networks.
• Cryptocurrency mining: Attackers often install cryptocurrency miners on compromised machines. Mining consumes your computing resources to provide cybercriminals with funding for more malicious activities.
• Access to other systems: Even if the current system is of low importance, it may make it easier for the attacker to target high-priority systems.

Examples of known SQL injection vulnerabilities

• 2021: GabLeaks – the far-right social media platform Gab was attacked using an SQL injection that allowed the attackers to obtain 70 gigabytes of data.
• 2020: Sophos XG Firewall – one of the world’s most renowned security companies found an SQL injection in their product because malicious hackers had been using it to attack their clients.
• 2019: Bulgarian VAT service – as a result of an SQL injection attack, records containing the tax data for 5 million citizens over a span of 10 years, including full names, income information, personal identification numbers (EGN), employment status, social benefits, and medical insurance, became accessible via underground forums.
• 2014: 420,000 websites attacked by a Russian gang – using botnets and common SQL injections, the cybercriminals were able to gather 1.2 billion ID credentials from approximately 420 thousand public websites and web applications.

How to detect SQL injection vulnerabilities?

The best way to detect SQLi vulnerabilities varies depending on whether they are already known or unknown.

• If you only use commercial or open-source software and do not develop software of your own, it may be enough to identify the exact version of the system or application you are using. If the identified version is susceptible to SQLi, you can assume that your software is vulnerable. You can identify the version manually or use a suitable security tool, such as a software composition analysis (SCA) solution for web applications or a network scanner for networked systems and applications.
• If you develop your own software or want the ability to potentially find previously unknown SQLi vulnerabilities (zero-days) in known applications, you must be able to successfully exploit the SQLi vulnerability to be certain that it exists. This requires either performing manual penetration testing with the help of security researchers or using a vulnerability scanner tool that can use automation to exploit web vulnerabilities. Examples of such tools are Invicti and Acunetix by Invicti. We recommend using this method even for known vulnerabilities.

How to prevent SQL injection vulnerabilities in web applications?

The only fully effective way to prevent SQLi vulnerabilities in web applications is to use parameterized queries (also known as prepared statements) to access SQL databases. Parameterized queries are available in almost every common programming language. They let you avoid string concatenation and instead pass parameters safely to SQL queries. If your programming language does not support parameterized queries but your database engine supports stored procedures, you may use stored procedures with prepared statements instead.

Relying purely on other prevention methods such as whitelists, blacklists, or input filtering/escaping, is not recommended. Malicious hackers may find a way around such sanitization. With the widespread availability of parameterized queries in programming languages and application frameworks, there is no excuse for using custom methods. Such methods may be a fallback only when parameterized queries and stored procedures are not available.

Specific tips on how to use parameterized queries to protect against specific SQLi types are contained in sections dedicated to SQLi types (blind SQL injection, union SQL injection, etc.).

How to mitigate SQL injection attacks?

Possible methods to mitigate SQLi attacks will vary depending on the type of vulnerable software:

• In the case of custom software, such as web applications, the only way to permanently mitigate the SQLi is to use parameterized queries, or stored procedures if parameterized queries are not available. You should only use other measures if switching to parameterized queries would require a major change in software design, for example, if you would need to migrate a legacy PHP application from the original MySQL API to PDO or MySQLi.
• In the case of known SQLis in third-party software, check the latest security advisories for a fix and update to a non-vulnerable version (usually the latest version).
• In the case of zero-day SQLis in third-party software, you can only rely on temporary WAF (web application firewall) rules for mitigation. However, this only makes the SQLi harder to exploit and does not eliminate the root cause of the problem.
• Consider measures that will help to mitigate the damage if an existing SQLi vulnerability is successfully exploited:
  • Developers: Use the lowest possible privileges for database access. Every time you access the database in your application, make sure that you are using a database account that has the minimum necessary privileges for that operation.
  • Database administrators: Turn off all options that cause database error messages to be returned to the user, for example, in HTTP responses. Turn such options on temporarily only when debugging your code.
  • System administrators: Make sure that the underlying operating systems of the application and the database server are secured and up to date. This will help to prevent privilege escalation attacks in case an SQL injection vulnerability exists in the application.