What are SQL Injection Vulnerability (SQLi), How to Identify Them, and How to Prevent

Author: Travis Phillips

Recently, the FBI and CISA released a Secure by Design alert calling for the elimination of SQL injection vulnerabilities. This classification of vulnerabilities has been known about since the early 2000’s and effective defenses were released a few years after. Despite over 20 years of knowing about this vulnerability and its countermeasures, the FBI/CISA are still seeing these vulnerabilities persist in the wild and their exploitation being leveraged by cybercrime gangs. The advisory highlighted the impact of the SQL injection vulnerability in the MOVEit software, which is being exploited by the CL0P ransomware gang in the wild. It also referenced MITRE’s Unforgivable Vulnerabilities list, which mentions SQL injection vulnerabilities.

While the CISA/FBI advisory included a document about the topic, it was more of a call to action. After reviewing it, we decided to create this article to dive into this topic a little more in-depth and answer some common questions that you might have after reading the alert.

About SQL

Databases are commonly used in applications since it provides a means to store, organize, and manage information dynamically. SQL stands for Structured Query Language. It was designed to provide a language for interacting with databases in a syntax that resembles plain English. There are different types of database servers out there, and there can be slight differences in the SQL language, but in general, these are often minor.

Below is an example of a SELECT statement designed to query information about a user with the email address john.doe@example.com to give an example of the language.

SELECT
    username,
    first_name,
    last_name,
    email
FROM
    users
WHERE
    email = 'john.doe@example.com';

If a developer wants to have their application interact with a database, then in most cases they will be using SQL to do so. Within the application, the developer will create a SQL query string to query, insert or update information. However, these queries will seldom be static queries and need to incorporate user-supplied data in them. For example, the query that was listed above was using a WHERE clause searching for users that had the email address john.doe@example.com. In practice, we would want that email address to be the one provided to the user so the developer needs to dynamically update the query to meet that need.

About SQL Injections

A SQL Injection vulnerability occurs when the user-supplied data can modify the SQL query string that the application is sending to the database. Continuing with the example above, if the email address supplied by the user was instead test' OR 'a'='a, then the resulting query would look like the following:

SELECT
    username,
    first_name,
    last_name,
    email
FROM
    users
WHERE
    email = 'test' OR 'a'='a';

This would change the entire meaning of the WHERE clause. The original WHERE clause was intended to search for a match on the email address. However, the injection adds an additional condition check. While the email address provided is bad and won’t match anything, the 'a'='a' is always true, and the use of the OR keyword means the record should match if one or both of the conditions are true. This would result in the query returning all records from the user table.

SQL Injection attacks can have a wide range of consequences when exploited. The most rudimentary outcome is being able to access data in the database without authorization. However, the exploitation of SQL injections can also lead to behavior changes in applications, allowing attackers to circumvent authentication and authorization checks built into the application. In extreme cases where the server is misconfigured, an attacker can obtain remote code execution and gain shell access to the underlying SQL server.

What Causes SQL Injections

In the example provided above, the injection was successfully able to modify the query string. This is what leads to SQL injection vulnerabilities. The most common cause of SQL injection (SQLi) is because the developer wrote the SQL query strings in the code and simply concatenated the user-supplied data into the query string. While this is simple to do, and under normal circumstances doesn’t cause an issue, attackers do not operate “under normal circumstances”. Attackers are aware of SQLi vulnerabilities and have a range of test cases that they can use to attempt to find SQLi vulnerabilities within applications. For example, sending a single quote (') alone would have caused the WHERE clause to become:

SELECT
    username,
    first_name,
    last_name,
    email
FROM
    users
WHERE
    email = ''';

This query would be invalid and cause an error when the SQL database attempts to run it. How an application handles that error can often tell the attacker that the application is vulnerable to exploitation.

So What Can We Do About SQL Injections?

This is a pretty critical flaw, and we are not going to abandon the utility a database server brings to the table. As a result, a technique known as query parameterization, parameter substitution, or prepared statements was introduced.

The idea here is that instead of string concatenation, the developer will instead write their query and in the places where they need to include dynamic values, such as user input, they will instead put a placeholder in the string. This string is known as a prepared statement and can remain a constant in the application. When the application has user input, it will send the prepared statement with the placeholder to the SQL server, with the user-supplied information along with it as a separate argument. The SQL server will receive both the prepared statement and the user-supplied data, and run the query using the user-supplied data as just data, and not as a part of the query string.

In Python using SQLite3, we can safely use the user-supplied email address without string concatenation using a prepared statement.

QUERY = 'SELECT username, first_name, last_name FROM users WHERE email=?'

cursor.execute(QUERY, (user_email,))

print(cursor.fetchone())

The question mark in the QUERY string is the placeholder for the SQLite3 API. The cursor object is our connection to the database which provides us with the execute() method. This method allows us to provide the parameterized query string as the first argument, and either a Python dict or sequence as the second parameter. This would prevent us from concatenating the user-supplied data and our query string, which is what needs to be avoided to prevent SQL injections.

Please note that each language and SQL database has its own implementation of how this works, so you’ll need to look it up for your language. For example, PHP has PDO (prepared data objects) where .NET has SqlCommand.Prepare(). While different, the concept is the same.

What Not to Do About SQL Injections

SQL injections are tricky to protect against. This is one of those issues where text-based strings are being treated as code, similar to how cross-site scripting (XSS) flaws work. Working in the field of security, we wanted to call out some common, but misguided, approaches we see that still allow SQLi vulnerabilities to persist in applications.

While prepared statements are a great starting point, we have seen them implemented incorrectly where the developer still performs some string concatenation on the parameterized query string, which still allows the SQL injection vulnerability to persist.

Another flawed security measure we have observed in the wild is attempting to avoid prepared statements in favor of allow-listing or deny-listing sanity checks against the user-supplied data. Please, please, please do not do this! Just like with XSS flaws, these sanity checks often fail. It doesn’t matter how well you think your deny or allow list is crafted, attackers often find a way to circumvent them. This approach is similar to writing your own custom cryptography instead of using a tried and true cryptography algorithm, which is also on MITRE’s list of Unforgivable Vulnerabilities.

How to Detect SQL Injections

The FBI/CISA Secure by Design alert had outlined three principles for eliminating SQL injection vulnerabilities, which are outlined below:

Take Ownership of Customer Security Outcomes
Embrace Radical Transparency and Accountability
Build Organizational Structure and Leadership to Achieve These Goals

These are good, but mostly organizational in nature. Change needs to occur at an organization level, and without those principals in place, security is more difficult since buy-ins are unlikely. However, we would like to provide some additional information assuming those conditions are met. Before you can fix a problem, you need to identify it. On this front, developers and organizations have a few tools at their disposal.

One option is to implement automated static code analysis into your CD/CI pipeline. Many languages, especially legacy languages, have prepared statements, but also have the older and more direct execution APIs available. For simplicity's sake, developers can often still use these and introduce SQLi vulnerabilities. The use of static code scanners that run when code changes are being checked in can help catch some of the low-hanging fruit where these practices are being attempted. This can help detect SQLi vulnerabilities and reject them before they are placed into the codebase.

Another code-level tool that organizations can use in the fight against SQLi vulnerabilities is periodic code review. Even if you were to implement code scanners today, it might not detect SQLi vulnerabilities that were put in place before the scanner was implemented. Some legacy applications might also have development workflows that do not use CD/CI pipelines and the organization does not have time or resources to take on the migration. Having the code reviewed for security by hand can help detect SQLi vulnerabilities, among other flaws.

The next line of defense is dynamic testing. Static code scanners cannot catch every mistake that can lead to SQLi vulnerabilities, while human code review tends to be costly and time consuming. Dynamic testing is testing the code for these flaws in a running environment. Dynamic testing for SQLi vulnerabilities is a common practice performed in penetration testing.

Finally, an organization should invest in security training for developers. SQLi vulnerabilities are a type of flaw that is due to poor security practices in code. This often occurs due to a lack of understanding of security with developers. Providing the resources for security training can help your development team be more security-minded as they develop your products.

Conclusion

This was a long post, and if you made this far, we hope this helps you to better navigate SQLi prevention. If you still have questions regarding the nature or prevention of SQL injections, do not hesitate to reach out to us. We are happy to help answer questions on this topic and provide resources, such as penetration testing and security training that can help your organization do its part in the war on SQLi vulnerabilities!