by Erlend Oftendal and Naane Baars

SQL injection was introduced in an article by Rain Forrest Puppy (Jeff Forristal) in Phrack 25 years ago. Even though it is a well-known bug with a well-known remedy, it still frequently occurs even in today's products.

If we look at the OWASP Top 10, injection risk started in the 6th position in the initial 2003 version, and then moved across the top three spots in the later versions.

SQL injection is also number 3 on the SANS CWE Top 25 Most Dangerous Software Errors, and is still frequently on the reports from penetration tests and bug bounty programs, although there is a declining trend. If we look at CVE details of 2023 we get a list of 2159 of 29065 vulnerabilities found.

CISA, together with the FBI, recently released a Design Alert called Eliminating SQL Injection Vulnerabilities in Software, asking for all call to action to remediate this vulnerability once and for all.

That begs the question: why, after all these years, does SQL injection still crop up? It should be a thing of the past.

What Is an SQL Injection?

SQL injection typically occurs where attacker-controlled data is concatenated with strings to build SQL queries. The classic example is logins where the application attempts to look up a username and password in the database:

query = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password "'"
result = db.execute(query)

This type of query is not very common in modern applications for various reasons. Passwords are not stored verbatim, but hashed with a fitting hashing algorithm as per the OWASP Password Storage Cheat Sheet. Additionally, this specific type of bug is often quickly found in penetration tests and scans. As a result, developers might falsely assume that they have avoided the risk of SQL injection.

In practice, SQL injection can take many forms and attacker-controlled data can come from many different locations in the code. We typically think of attacker-controlled data as form inputs, parameters from the URL, or data from a JSON body of an HTTP request, but input can come from all parts of an incoming request. This includes header values and encoded values in tokens such as JWT, embedded in image EXIF data, or even in encrypted data, among many other forms. There are even examples of SQL injection in door entry systems where the input is coming from proprietary wire protocols.

The most common approach to avoiding SQL injection is the use of parameterized queries (or prepared statements), where the data inputs are replaced with placeholders and separated from the query itself:

select * from users where username = :username AND password = :password

With this an attacker is no longer able to change the meaning of the query. However, there are cases where using parameterized queries alone is not enough. Suppose we have an application where the user is allowed to change the sort order of the data. The query could look something like:

select * from servers order by hostname

In the code that is generating this statement, the order_by column may be added by the code like this.

def fetchServers(order_by: str):
  db.execute("select * from servers order by " + orderBy)
  ...

One thing to note is that you cannot use a parameterized query for the order by clause. The order by clause will normally be a column name, however if we look at the SQL grammar definition it can be a complete expression. This would also be a valid statement:

select * from servers
order by case when (
    select ip from servers
    where substr(ip,1,1) = '9'
) IS NOT NULL
then hostname
else id end

The attacker can look at the ordering of the results and tell you whether the query sent to the database is matching something. Again we end up with a SQL injection. This manual process can be easily accelerated with SQLMap, for example. To fix this you need to validate the column passed to our function above, preferably against a strict allow-list.

In short, many SQL injection risks can be avoided with a combination of parameterized queries and validation. For all possible mitigations have a look at the OWASP SQL Injection Cheat Sheet.

Remain Vigilant

One reason SQL injections remain a threat is a persistent lack of awareness and due diligence. Many developers use platforms like StackOverflow and tools like GitHub Copilot to quickly find answers, but end up with code vulnerable to SQL injection. A simple copy-and-paste or autocomplete, and you introduce that vulnerability inside your own code. Even if someone in the answers section on StackOverflow points out the SQL injection, it often isn't the top answer or comment.

Code scanners (SAST) and application scanners (DAST) can certainly find many of the SQL injection issues, but may miss some due to lack of framework support or because they don’t scan all the possible injection points. Maybe AI will one day be able to flawlessly detect SQL injections, and we will get rid of this threat forever. In the meantime, we must remain vigilant.

Misplaced Trust

Another source of SQL injection can be the libraries used to communicate with the database. Many developers assume that the libraries have done things right, but that trust is too often misplaced. There are cases where even if you as a developer have done everything right in your code, the application is still vulnerable to SQL injection.

One such example is the recent CVE-2024-1597 in the Java postgresql driver (although this had some really specific preconditions). Another example is CVE-2019-14900 which was a flaw in Hibernate ORM.

WordPress plugins have also been a running source of SQL injection. Some examples from 2023 alone include CVE-2023-23488, CVE-2023-23489, CVE-2023-23490, CVE-2023-26325, CVE-2023-28659 and CVE-2023-28660 released last year. This year, CVE-2024-1071 was published.

To detect these types of vulnerabilities, we should first and foremost know our dependencies and versions, and which of them have vulnerabilities. The OWASP Top 10 2021 identifies this need as A06:2021-Vulnerable and Outdated Components. OWASP has several tools for this, including Dependency Check and Dependency Track. These tools will warn about the use of components with vulnerabilities.

If the SQL injection vulnerabilities are not known, there are chances they can be detected by scanning the code, scanning the applications, or running a manual penetration test. Although these are not guaranteed to find the bugs, there is an increased chance of finding the vulnerabilities before attackers do.

Learn More About SQL Injection

It is essential that developers learn to spot, prevent, and fix SQL injections. OWASP has several resources to enable you to do just that, and have some fun in the process!

WebGoat and Juice Shop are two "deliberately insecure" applications containing hundreds of security vulnerabilities for you to find and exploit, including SQL injections. Both projects provide extensive educational material to guide you.

The SQL Injection Prevention Cheat Sheet is an indispensible reference for defending against SQL injection in your own project. You can also find some more interesting examples on the OWASP community page for SQL injection.

Remember: only you can prevent SQL injection!

OWASP is a non-profit foundation that envisions a world with no more insecure software. Our mission is to be the global open community that powers secure software through education, tools, and collaboration. We maintain hundreds of open source projects, run industry-leading educational and training conferences, and meet through over 250 chapters worldwide.