Strengthening Your Web App Security: Preventing SQL Injections

The database plays a vital role in a web application as it stores and organizes its data. It serves as a central repository for storing user information, content, and other application data. The database enables efficient data retrieval, manipulation, and management, allowing the web application to deliver dynamic and personalized content to users. However, poorly implemented communication between a database and a web application can lead to sensitive data breaches, users' mistrust, legal consequences, and loss of profits. In this article, we'll explore backend misconfigurations that lead to such disasters and learn how to ensure the application's safety.

What Is SQL Injection?

SQL Injection – or SQLi – is a vulnerability that allows an attacker to tamper with queries that web applications send to the database. Injection occurs when an application misinterprets users' input and treats it as SQL code rather than a string. As a result, malicious users can change the intended query flow, subvert the application's logic, and gain unauthorized access to its resources.

Most of the time, SQLi occurs when developers need to use parameterized queries that depend on users' input. If developers forget to properly sanitize the user's input before inserting it into the template, they introduce an SQL injection vulnerability. A classic SQL injection example is crafting dynamic queries with plain string interpolation or concatenation, as shown in the picture below. An attacker is able to inject arbitrary SQL statements by replacing numbers in pagination parameters with SQL code.

What Is ORM Injection?

Nowadays, developers rarely use raw SQL statements and, instead, use special frameworks called ORM. ORM stands for Object Relational Mapping. It's a technology that serves as an adapter between two different paradigms: Relational (storing data in tables) and Object-Oriented (storing data in objects). One of the things that ORMs do is generate SQL code under the hood. All the developers have to do is tell the ORM how to do it.

Obviously, automatic generation means the automatic escaping of user-supplied data. The ORM ensures that every dynamic parameter is treated like a plain string unless developers specifically disable the sanitizing. Nevertheless, injections of malicious code are still possible. ORM Injection is a vulnerability that allows an attacker to force the ORM to generate SQL that would be beneficial for them.

Consider the following example. Here we have a function that is supposed to accept Object filters with multiple parameters, such as:

{"email":, ""name": "user"} 

However, the developer forgot to check if the filter is indeed an object and if it contains only safe filtering parameters. Such a mistake allows attackers to inject a malicious filter that can be used to recover users' passwords, such as:

"password LIKE '%a%'"

Is SQL Injection Really Dangerous?

Usually, SQL Injection can be considered a severe vulnerability. In most cases, a single SQL Injection on any part of your website can eventually be expanded to running any query on the database, extracting and manipulating its data. Since databases often hold the most sensitive information in the system, allowing attackers such access is devastating.

Here's a short list of how SQL Injections can be exploited:

• Remote Code Execution (usually through special features)
• Reading and Writing files on the host.
• Subverting web application's logic
• Extracting sensitive data
• Manipulating data
• Denial of Service

What Kinds of SQL Injection Are Possible?

Usually, three types of SQL injections are singled out: In-Band, Out-of-Band, and Blind. In turn, In-Band attacks can be Union-Based or Error-Based, and Blind SQLi can be Boolean-Based or Time-Based.

If an attacker is lucky, they can include the results of a subverted SQL query in the backend's response. This is known as In-Band SQLi. There are two subtypes of In-Band SQLi:

1. Union-Based SQLi: An attacker is able to specify the location (column) of the query output that they can read

2. Error-Based SQLi: This type of SQLi is possible when an application discloses SQL / programming language errors. In that case, the attacker can analyze error messages/stack traces and deduce whether an attack is successful.

With Blind SQLi, an attacker cannot see the results of a subverted SQL query. However, they have some sort of feedback that can help determine whether an injection is present. There are two subtypes of Blind SQLi:

1. Boolean-Based SQLi: An attacker can use SQL conditional statements to modify a server's response somehow. They can then compare this new response with the original one and figure out if the injection worked.

2. Time-Based SQLi: An attacker can combine the database's SLEEP function with conditional statements and delay the backend's responses. They can then compare the original response time with the new one and determine if the injection was successful.

In some cases, the attacker might not get any feedback at all from the database. In that case, they can force the database to redirect output to another location and try to read it from there. This is known as Out-of-Band SQL Injection. For instance, they can force the database to send a DNS query that contains sensitive information to a DNS server they control. Alternatively, they might force the database to write some data into a publicly accessible file. This kind of injection affects many databases.

Common Mistakes When Mitigating SQLi

You shouldn't try to come up with your own sanitizer for SQL input parameters. Doing so requires deep knowledge of database specifications and experience in working with them. Most likely, you'll end up drowning in regular expressions that no one else understands, and nobody is going to support the sanitizer as your database evolves.

Remember: attackers always try to obfuscate their SQLi payloads to smuggle them behind WAFs and IPSs. There's a plethora of frameworks for exploiting SQLi that provide attackers with libraries of scripts that twist payloads. It is also easy to write a custom obfuscator and use it in conjunction with existing ones.

Let's consider some common mistakes developers make when trying to sanitize users' input.

It is a common misconception that you can avoid SQLi by removing/replacing spaces in the SQL query parameter. For example, how much harm can be done if an attacker can only use one word? But many databases transform comments to spaces, so "SELECT email FROM user" is equal to "SELECT/**/email/**/FROM/**/user," which is one word.  

It's also common to think that removing quotes makes the parameter safe, but sometimes an attacker can specify another special character to define a string. For instance, PostgreSQL allows defining multi-line strings enclosed in double-dollar signs. So 'email' is equal to $$email$$  

The last but not least common mistake is to make a non-recursive remover for database keywords. It's really easy to bypass, too, as an attacker can insert a keyword in the middle of that same keyword. So, after sanitizing, the injection remains. For instance:

“SELSELECTECT” -> “SELECT” 

Proper Ways To Prevent SQL Injections

The first step towards SQLi prevention is to use prepared statements. It's also not acceptable to allow users to define prepared statements. Here's an example of preventing SQL Injections with TypeORM: we have hard-coded templates and use the features of this framework to safely insert variables:

But just using an ORM is not enough. As we saw earlier, business logic flaws can allow bypassing protection. The second step towards SQLi remediation is explicit validation of user input. If you expect a number, manually cast the value to a number. If you expect a URL, manually cast the input string to the URL. Such an approach makes SQLi exploitation far less likely. As a bonus, you'll have more consistent data and fewer bugs. There are many libraries that can be used for declarative validation, such as express-validator or class-validator.

Another important thing to remember is to ALWAYS create a dedicated database user per application. Carefully read the documentation on default user privileges and disable everything besides the features that are vital for the application to function. You should NEVER use a DBA account for anything other than database administration. A DBA account is granted all possible privileges by default. This significantly magnifies the severity of SQL Injection.

Last but not least, use a Web-Application Firewall or an Intrusion Prevention system. They are able to spot and disrupt SQL Injection attacks and even have a collection of traffic rules that detect known exploits relying on SQLi. Of course, using a WAF or IPS is not a silver bullet, as they can be bypassed. However, the presence of such tools significantly raises the knowledge threshold required for conducting the attack. As a bonus, WAF / IPS interfere with SQLi exploitation automation and provide better logging than traditional tools.