Guarding against SQL injection: Techniques to enhance code security

Structured Query Language (SQL) is a high-level language that interacts with relational databases like MySQL or Microsoft’s SQL Server. It was designed to simplify the process of reading, writing, updating, and deleting data stored in these databases.

A simple query to retrieve all of the users from a user table for example would be something like the following:

SELECT user_id, username, last_name, first_name, email_address from users where username = "user1234";

All programming languages provide ways to create SQL queries, submit them to the database, and then handle the response. 

When writing an interactive application, you would take the input from a user and use that to carry out an operation on the database using SQL that is constructed using that input. 

For example, a user who is searching for a new computer may filter the search by asking to show only “Gaming PCs”. In a PHP application, the code to do this would look like:

$query = "SELECT * from products where type = '$GET['computer_type']'";


$result = $pdo->query( $query );

The great thing about SQL is that it is an intuitive, easy-to-learn, and applied language. 

Constructing queries is also very simple, you just create a string combining the SQL syntax with the parameters you received from the user. 

Unfortunately, it is in this simplicity that the problem of SQL injection lies.

So what is SQL injection?

So, you can create an SQL query easily by combining user input with other text using SQL syntax. 

However, if you don’t check the user input, an attacker can potentially manipulate the final SQL query by crafting the input to alter what the SQL query does. 

So, instead of just returning the details of a specific user “user1234” as in the example above, the attacker can modify the query to return all of the users in the user table.

We will cover this in more detail, with examples, but it is important to realize how big of a problem this type of vulnerability can be. Not only can attackers read data that they shouldn’t have access to, but they can also modify data and even run operating system commands on some systems.

SQL injection is still a big problem 

Bleeping Computer has a long list of all recent breaches and discovered vulnerabilities that involve SQL injection. A vulnerability in a WordPress plugin called LayerSlider exposed over a million sites to SQL injection (CVE-2024-2879).

In a campaign orchestrated by a group calling themselves ResumeLooters, the personal data of over 2 million job seekers was stolen from SQL injection and cross-site scripting (XSS) attacks on 65 different job listing and retail sites.

The problem is still so bad that it has prompted the US agency CISA (Cybersecurity and Infrastructure Security Agency) and the FBI to issue an appeal in early 2024 for developers to review and fix SQL injection vulnerabilities in their code.

Having stressed the importance of SQL injection vulnerabilities, let us look at how these vulnerabilities can be exploited in practice.

Common code vulnerabilities that lead to SQLi

SQL injections happen when a developer creates queries by combining user input, and the code is not checking directly with other text to create an SQL statement.  

In the following PHP code example, the query is constructed using unsanitized inputs:

$query = "SELECT id from users where username = '$_GET['username']'";


$result = $pdo->query( $query );

 If the inputted username was “alice” then the query would be:

SELECT id from users where username = 'alice'

 If the attacker now passes in 

anyone' or '1'='1

The resulting query would be 

SELECT id from users where username = 'anyone' or '1'='1'

Which would always return a result. In the case where the username ‘anyone’ does not exist, it would return all rows in the table.

💡Recommended read: Secure coding practices: A busy dev's guide to bulletproof app security.

Common vulnerabilities and how they are exploited

Injection vulnerabilities are still common as they made the top 3 in the OWASP Top 10 vulnerabilities in 2021. There were a large number of critical SQL injection CVEs still being published in 2024. 

Many of these are PHP applications or plugins but other languages appear in these CVEs. 

Language frameworks like Django for example, use an object-relational mapping that hides the actual SQL used on the database. For greater flexibility, Django allows direct manipulation of the SQL to execute queries, and this is where the vulnerabilities can appear.

The most important thing to remember is that the underlying root cause in SQL injection is failing to validate inputs and creating dynamic SQL statements by combining that input with other text. 

For example, CVE-2024-0610. The Piraeus Bank WooCommerce Payment Gateway plugin for WordPress (listed as having over 4000 active installs), was found to be vulnerable to time-based blind SQL injection attacks. 

The PHP code illustrates the problem:

$ttquery = 'SELECT trans_ticket FROM `' . $wpdb->prefix . 'piraeusbank_transactions` WHERE `merch_ref` = ' . $order_id . '   ;';

In this case, the variable $order_id is just filtered using the PHP function filter_var

$order_id = filter_var($_REQUEST['MerchantReference'], FILTER_SANITIZE_STRING);

The function filter_var with the option FILTER_SANITIZE_STRING only removes HTML tags and HTML encodes quote marks. This will not remove SQL statements, so the attacker's input can still pass through to be executed.

What made this CVE particularly bad was that it allowed unauthenticated attackers to exploit it. This means that if you could find the right URL that accesses this function, you didn’t need to be logged in as a valid user to exploit it!

Types of SQL injection attack

Having looked at the basics of how an attacker can modify the input to a SQL statement to carry out a SQL injection attack, there is another problem that the attacker needs to deal with: how to get and process the response from that modified SQL. 

From an attacker’s perspective, it would be convenient if the response from the query was simply displayed on a web page for them to read. That only happens in some situations and in others, the attacker has to resort to other techniques to access responses from the SQL query to extract useful information. 

The names given to these different types of SQL injection attacks are technical but are useful to know because tools like sqlmap refer to them (as we will see later).

In-band SQL injection

This is best explained by an earlier example, of manipulating an input on a product search page. In this example, imagine a search page for computers that the users can buy online. The search function will result in text and images being returned and displayed for the user. 

An attacker using SQL injection can modify the way the query is executed, displaying the results on the same page as the normal search results.

This is called “in-band” because the results of the SQLi attack come back as the direct response to the request. 

Error-based SQL injection

The attacker may only be interested in whether their query results in an error code or other feedback that tells them if the query worked. This type of SQLi is called “Error-based SQLi”. 

The attacker is not interested in fetching data but is more interested in executing queries that give useful information like whether the input is vulnerable to SQL injection in the first place, or information on the type of database being used.

Union-based SQL injection

When the attacker wants actual data returned from the SQLi, they can use a technique called union-based SQL injection. 

This uses a SQL operator called the UNION operator. You use UNION to take data from two queries and return them in the same response.  

The following query, for example, would return two columns of data with the rows being the product_id and product_name from the products table, followed by the user_id and username from the users table:

SELECT product_id, product_name FROM products UNION SELECT user_id, username d FROM users;

When running this attack on a page that displays the product ID and product name for products, the attacker will also be able to see the user ID and username for all of the users. 

Inferential SQLi

When exploring whether a particular input is vulnerable to SQL injection attacks, you can use techniques that don’t necessarily produce any direct output but instead, change the behavior of the application’s response in some way.

For example, let us say that you know that there is a particular product with an ID of 42 that is returned from a query, and the result is displayed on a page. The query would look like this:

SELECT product_id, product_name FROM products where product_id = 42;

If instead of the product ID 42, you can inject the following:

SELECT product_id, product_name FROM products where product_id = 42 AND 1=1;

SELECT product_id, product_name FROM products where product_id = 42 AND 1=0;

The ‘AND’ part of the query will cause the query to succeed when true, i.e. 1=1, and fail when it is false i.e. 1=0.

In the second case, the product would not be displayed because both conditions are false. 

This type of attack is called a boolean-based attack (sometimes also called True/False attack or content-based attack).

This makes this type of attack useful because we can use it to do brute-force queries that will reveal information by constructing a string letter-by-letter, for example. 

In the following query, we can start with the letter ‘a’ to enumerate a table name that is held in the sysobjects table of the database:

SELECT product_id, product_name FROM products where product_id = 42 AND
(SELECT TOP 1 substring(name, 1, 1)
  FROM sysobjects
  WHERE id=(SELECT TOP 1 id
    FROM (SELECT TOP 1 id
      FROM sysobjects
      ORDER BY id)
    AS subq
    ORDER BY id DESC)) = 'a'

Time-based Blind SQLi

In a different version of the boolean attack, we can change the logical expression to incorporate a timed delay using the SLEEP or WAITFOR DELAY commands. 

Similarly to the boolean-based attack, data can be brute forced because we know that the query will pause for a known period if the ‘AND’ does not prove true.

Fixing the problem of SQL injections

Prepared statements with parameterized queries

Irrespective of the language, using dynamic queries with unsanitized variables will lead to the potential for an SQL injection attack. There are several solutions to this problem but the principle one is to use prepared statements with parameterized queries. In Java, this looks something like:

 

String custname = request.getParameter("customerName");

String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, custname);
ResultSet results = pstmt.executeQuery( );

In PHP, the fix for the CVE mentioned above was to use the prepare function:

$ttquery = $wpdb->prepare(
    'select %s from %s where merch_ref = %s',
    [
        'trans_ticket',
        $wpdb->prefix . 'piraeusbank_transactions',
        $order_id
    ]
 );
$tt = $wpdb->get_results($ttquery);

Parameterized stored procedures

Stored procedures also have protection against SQLi through the way parameters are handled. An example in C# is:

Try
Using con As New  SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection")   
              .ConnectionString)
Using cmd = New SqlCommand("SaveUser", con)
  cmd.CommandType = CommandType.StoredProcedure
  cmd.Parameters.AddWithValue("@Surname", txtSurname.Text)
  cmd.Parameters.AddWithValue("@firstName", txtFirstName.Text)
If con.State = ConnectionState.Open Then
  con.Close()
End If

con.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
  MsgBox("Error")
End Try

SQL injection in the wild

In a recently reported attack, a group called “ResumeLooters” was uncovered after stealing the personal data of over 2 million job applicants from 65 compromised job listings and retail sites. 

To achieve this, ResumeLooters exploited SQLi vulnerabilities on these sites, combined with uploading documents that had cross-site scripting (XSS) attacks. 

The attackers used a variety of tools to assist with discovering and exploiting the SQLi vulnerable sites, including:

• SQLmap.

• Acunetix.

• Beef Framework.

• X-Ray.

• Metasploit.

• Asset Reconnaissance Lighthouse (ARL).

The personal data of compromised users was then sold on several markets on the dark web. 

In another case, attackers exploited SQL injection vulnerabilities (CVE-2023-46748) in F5 BIG-IP network devices to access configuration information and execute arbitrary system commands. F5 alerted their users that numerous devices had been found to have been compromised. 

Usually, to discover that an attack has happened, traces can be identified by investigators or forensic teams. These traces may be things like binaries, scripts, or other files. Because they point to an attack, these pieces of evidence are called “Indicators of Compromise (IOC)”. 

In this particular attack, the problem was that attackers were covering their tracks really well, so not finding any of these IOCs didn’t mean that a device was safe and had not been attacked.

Tools and techniques for detecting SQL injection vulnerabilities

Before discussing tools, it is worth emphasizing that the best way to identify SQL injection vulnerabilities is to use a secure software development process that includes coding standards and reviews. 

When conducting code reviews, a red flag should be raised anytime code is found that uses SQL statements created by taking unchecked user input and concatenating them with other text. 

An example of this is:

$query = "SELECT id from users where username = '$_GET['username']'";

The code should be searched for every occurrence of an SQL query and the input validation should be verified.

As an additional precaution, and to identify other potential vulnerabilities, the code testing and deployment process can use Dynamic Application Security Testing (DAST) and/or vulnerability scanning tools. 

In addition to identifying SQL injection, these tools can identify Cross-Site Scripting (XSS),Cross-Site Request Forgery (CSRF), and other vulnerabilities.

Learn to spot and defend against SQL injection

Our Hack The Box (HTB) Academy offers dedicated content to help developers secure their code, and security professionals identify and defend against code-related attacks. SQL topics covered on HTB Academy include: 

• SQL Injection Fundamentals. 

• Blind SQL Injection.

• Advanced SQL Injection

• SQLMap Essentials.

• Introduction to NoSQL Injection. 

With real-world environments and content, HTB machines enable learners to practice and familiarize themselves with SQL injection. These can be incorporated into CTFs and specific tailored labs. A list of relevant machines can be found using a search on the Academy x HTB labs tool.

Ultimately, the best way to avoid SQL injection vulnerabilities in code is to follow secure software development practices. So if you're a developer looking to improve app security, check out the secure coding practices guide.