Understanding the Vulnerability

SQL injection happens when a user-provided input is used to directly construct an SQL query. As a basic example, consider the following vulnerable code used to retrieve product details from a fictitious shopping website:

    // Fetches data via product ID
    function getProductId() {
        $itemId = $_GET['itemId'];
        $query  = "SELECT * FROM products WHERE id = '" . $itemId . "';";
        $result = mssql_query($query);
        // [...]
    }

‍

The input is taken directly from the client via the "itemId" parameter in the URL. Without any further processing, the parameter is immediately used to construct the $query string, which is to be executed as an SQL query. When a user submits the product ID as intended, such as 20 (/index.php?itemId=20), the database would execute the following command:

SELECT * FROM products WHERE id = '20';

‍

The arbitrary input value of "20" is being used directly to construct the query. If we observe the SQL command, we can see that our value is inserted between single quotes. If we were to append an additional single quote to our input value of 20, we would break the SQL syntax and trigger a syntax error.

SELECT * FROM products WHERE id = '20'';

‍

Since the application is interpreting our input as valid SQL syntax (provided our command is well-formatted) we can now use begin to append our own custom malicious query using stacked queries. As a simple example, we will use a payload such as 20'; DROP TABLE users ;-- which would simultaneously close the syntax of the original query and execute our command to maliciously destroy the "users" database table. The executed command would now look like this:

SELECT * FROM products WHERE id = '20'; DROP TABLE users;--';

‍

‍Subverting Application Logic

SQL injection can also be exploited to subvert the logic of an application. For example, consider the following database query used in an authentication function:

SELECT id FROM users WHERE username = 'James' AND password = 'Letm3in!';

‍

When valid credentials are submitted, the query returns ‘True’ and the user is authenticated successfully. Since the submitted password parameter is being used directly in the query, we can manipulate the logic by forcing the query to return True, despite the absence of valid credentials. Submitting a password value of ' OR 1=1;-- would achieve this, and bypass the authentication mechanism. This works because 1=1 is a condition which will always evaluate to True. The command becomes:

SELECT id FROM users WHERE username = 'James' AND password = '' OR 1=1;--';

‍

‍Second-Order SQL Injection

A "second-order" SQL injection vulnerability occurs when user-provided input has been stored in the database and is then later used unsafely in a new SQL query. The initial "Injection" happens in a different function than the one which executes it, which is why it's significantly harder to identify from a closed-box testing perspective.

For example, a user registers an account on an application with the name of James'; DROP TABLE products;-- which is subsequently stored somewhere in the database. Although nothing happens immediately, the following function is later executed; fetching the stored $user variable from the database to construct the SQL query unsafely.

    // Fetches user bio
    function getAccountBio() {
        $query  = "SELECT bio FROM users WHERE username = '" . $user . "';";
        $result = mssql_query($query);
        // [...]
    }

‍

This results in the malicious input being executed and destroying the database table. The executed command now looks like this:

SELECT bio FROM users WHERE username = 'James'; DROP TABLE products;--';

‍

What does SQL Injection mean for My Business?

If an attacker exploits SQL injection, the consequences can vary depending on the data they've obtained. As we've explored, SQL injection attacks don't always require mass-exfiltration of data to cause some damage. In some cases, it can be used for bypassing authentication, subverting application logic and even OS command execution. In the worst-case scenario, it would lead to an adversary breaching the network perimeter and moving laterally to internal systems.

From a business perspective, if your applications haven't been thoroughly tested for SQL injection vulnerabilities, your customer data may be at serious risk of compromise - with very little effort required to perform such an attack.

Real World Examples

SQL injection has plagued several large businesses over the years, some of which resulted in massive data breaches and irreversible reputational damage. In fact, a dedicated SQL injection "Hall-of-Shame" exists, which is frequently updated with the latest company to fall victim to an SQL injection bug. Some noteworthy examples are:

• RockYou - in December 2009, RockYou experienced a data breach resulting in the exposure of over 32 million user accounts. The breach was a direct result of an unpatched 10-year-old SQL injection vulnerability. To add insult to injury, RockYou stored their user passwords in cleartext, meaning the attackers had access to over 30 Million cleartext passwords.
• TalkTalk Cyber Attack - in October 2015, network provider TalkTalk experienced a massive SQL injection attack on one of their unmaintained websites. The hack resulted in over 150,000 customers having their details and banking information exposed, with an estimated cost of £30 million worth of damages.

How is SQL Injection Exploited by Attackers?

Various methods exist for identifying and exploiting SQL injection - all of which are becoming more advanced and available than ever before. Typically, SQL injection attacks begin with an enumeration/identification phase, before moving on with the technical attack.

Identification

Depending on how well designed the web application is, identifying an SQL injection vulnerability is usually straightforward. Attackers use “fuzzing” (a fancy word for repeatedly guessing) techniques and observe how the application responds and behaves with each attack attempt. As we’ve seen, error messages can often be disclosed by submitting simple characters to break the SQL syntax – such messages are often a clear indication that SQL injection is possible.

Of course, there are other less-verbose ways of identifying potential SQL injection bugs:

• Conditional HTTP response codes (e.g. 500 Internal Server Error) when submitting payloads.
• Error-based payloads are used to determine successful execution via verbose error messages.
• Time-based payloads, such as "SLEEP" commands, are used to observe a potential time delay.

‍In-Band SQL Injection

The most common SQL injection category is referred to as "In-Band" injection. These are often the easiest to exploit, as "In-Band" simply means the attacker can perform the attack and see the direct results immediately, just like the previously shown error message screenshot. Error-based and Union-Based are the primary techniques in this category.

• Error-based SQL injection is reliant on sensitive and verbose error messages, which the attacker can leverage to help them craft a payload or view backend queries.
• Union-based SQL injection is reliant when the UNION operator is used to concatenate several SELECT statements together. In this scenario, the attacker simply instructs the database to append the exfiltrated data to the webpage for easy retrieval.

‍Blind SQL Injection

Blind SQL injection attacks are inferential and rely on either conditional responses or time-based payloads. These become necessary when the attacker has no visual way of determining the result of their payload. The attacker is required to craft specific attacks (often using brute-forcing techniques) to slowly extract data using Boolean methods.

• Time-based SQL injection is reliant on the attacker using SLEEP operators to determine if the result of a query returns True or False. The attacker uses this to determine what the contents of the database are and can slowly begin to extract the data deterministically.
• Boolean-based SQL injection is any other method where an attacker can observe a discrepancy between True and False queries. This may include response length, response codes or HTTP response contents.

‍Out-of-Band SQL Injection

Out-of-Band (OOB) SQL injection relies on external communications from the vulnerable server to an attacker-controlled endpoint. This is used as an alternative communication channel if no other methods are available. Various techniques exist for data exfiltration, such as using advanced XXE payloads to retrieve data via HTTP. This method is the least common and is reliant on permitted outbound communications from the target server.

Automated Tooling

For years, free and open-source tools have existed to automate the process of identifying and exploiting SQL injection vulnerabilities. The most notable is sqlmap. Although sqlmap was developed for penetration testers and ethical purposes, the tool has long been used by cybercriminals - even in some of the previously mentioned large data breaches. The tool uses advanced fuzzing techniques to automatically identify SQL injection vulnerabilities and makes mass data exfiltration trivial - with very little effort required by the attacker. Once the attack is complete, the exfiltrated data is automatically saved as a CSV for the attacker to use.

How is SQL Injection Prevented?

There are several ways to prevent SQL injection vulnerabilities in your application, most of which are easy to implement. Some say the best advice is to first make developers aware of SQL injection and ensure they can properly remediate against it. However, it's always recommended to have an application thoroughly tested by a team of dedicated security consultants.

Prevention is Better than Cure

The phrase "prevention is better than cure" is commonplace in the security industry. Identifying the existence of SQL injection before an application is deployed is extremely important. This ensures that, when vulnerabilities are identified, they are addressed and fixed immediately. From a business perspective, this could mean security testing being a fundamental part of the development lifecycle, known as DevSecOps.

Although several automated solutions are available to identify low-hanging injection flaws, they cannot be solely relied upon for large-scale applications. It's common for second-order SQL injection to slip under the radar of these solutions and may provide a false sense of security (with both false negatives and false positives often reported).

CMS applications such as WordPress or Drupal often use third-party plugins, few of which incentivise developers to patch security issues when found. It’s common for plugins to be “up-to-date” despite having an unpatched critical security flaw, such as SQL injection. All third-party dependencies and plugins should be regularly monitored for publicly disclosed vulnerabilities.

‍Prepared Statements and Parameterised Queries

Prepared statements practically eliminate the risk of SQL injection. Put simply, the code defines the exact SQL query they want to execute, and any user-provided input is exclusively used in the appropriate place as parameters - meaning that even if they attempted to inject SQL syntax - their input is treated as text and never as executable code because query and the input values are sent to the database separately. Consider the revised getAccountBio function:

    // Fetches user bio
    function getAccountBio() {
        $name = $_GET['name'];
    
        // Prepared statement to avoid sqli
        $query = "SELECT bio FROM users WHERE username=?";
        $result = $conn->prepare($query); 
        $result->bind_param("s", $name);
        $result->execute();
        // [...]
    }

‍

In this case, regardless of what characters the user provides via the name GET parameter, the input is exclusively treated as a string literal. The database already knows what query it expects to execute, and therefore cannot be tampered with. This way, even if an attacker did attempt to submit SQL syntax, the database would query for a user with the name of "James'; DROP TABLE products;--" which, of course, would not return any results.

By using prepared statements and parameterised queries, our function is fundamentally safe from SQL injection attacks - whilst also avoiding the risk of tampering with user data. Prepared statements handle the heavy work for us; there's no need to "sanitize" anything.

‍"Sanitise" your Hands, not your Database Inputs

The advice to "sanitise your inputs" is technically vague and prone to error. “Sanitising input” might be misinterpreted as “filtering” data like removing/escaping special characters. These will not prevent SQL injection attacks.

Using the correct terminology is important, which is why the term “sanitise” cannot be used as a blanket remediation term. It’s common for developers to design insecure “sanitisation” functions which are easily bypassed by a technically competent attacker. The only way to prevent SQL injection attacks is by using prepared statements and parameterised queries.

The age-old “Exploits of a Mom” xkcd comic would therefore be considered poor advice, if taken literally. The mother should be advising the school to use prepared statements, rather than filtering input. (However, the idea of input-sanitation is easy enough for most people to understand, so we’ll excuse it!)

‍Secondary Defences

Additional steps can be taken to reduce the risk of SQL injection attacks on an application and potentially reduce the damage if they are exploited in the wild. Some of these include:

• Implementing a Web Application Firewall (WAF) can help defend your application against common SQL injection payloads. Generally, this makes it harder for an attacker to identify and exploit them – especially if the attacker is using automated tools. However, bypasses and 0-days will always be identified in these solutions. This should only be considered as a secondary layer of defence.
• Enforcing the Principle of Least Privilege (PoLP) means ensuring the database user has exclusive access to the required areas, and nothing else. If an SQL injection vulnerability were to be exploited, this would act as an additional barrier for the attacker to circumvent. It’s recommended for all software components and database accounts to begin with the ‘read-only’ privilege before moving on to other privileges.

How can Penetration Testing Help my Business?

Undoubtedly, the best way to prevent SQL injection is to have your application thoroughly tested by security experts. Penetration testing should always be considered during both the development and post-deployment stages of an application.

Applications with an increasingly large codebase can often become neglected. In this case, access to source code is especially useful. With backend access, the vulnerability identification process becomes more efficient with the aid of real-time database error logs.

Establishing clear objectives during the scoping phase is a great way to get additional value from your penetration test. As each web application has unique features, it'll also face unique risks. Setting clear, practical exploitation scenarios allows the testers to demonstrate real-world impact with actionable advice.

If you’re worried about your exposed web applications, Secure Impact’s team of experts can help. Our testers can identify critical vulnerabilities like SQL injection in your codebase before the attackers do. If you have any questions about SQL injection or would like to discuss the security of your web application, please contact our offensive team.