In-band SQL injection

What is in-band SQL injection?

In-band SQL injection is a type of SQL injection where the attacker receives the result as a direct response using the same communication channel. For example, if the attacker performs the attack manually using a web browser, the result of the attack will be displayed in the same web browser. In-band SQL injection is also called classic SQL injection.

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.

What is error-based SQL injection?

Error-based SQL injection is a subtype of in-band SQL injection where the result returned to the attacker is a database error string.

Consequences of error-based SQL injection

Returning an error string to an attacker may seem harmless. However, depending on the structure of the application and the type of the database, the attacker may use the received error string to:

• Get information about the type and version of the database to use different attack techniques for a specific database type/version.
• Get information about the structure of the database to try more specific SQL injections once the structure is known.
• Get data out of the database. While the process is much longer and more complex than directly displaying the result of a query, an attacker may be able to manipulate the errors to exfiltrate data from the database.

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.

What is union-based SQL injection?

Union-based SQL injection is a subtype of in-band SQL injection where the attacker uses the UNION SQL clause to receive a result that combines legitimate information with sensitive data.

Consequences of union-based SQL injection

Union-based SQL injection is the most dangerous type of SQL injection because it lets the attacker directly obtain almost any information from the database.

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 to prevent in-band SQL injection vulnerabilities?

The only fully effective way to prevent all types of SQLi vulnerabilities in web applications, including in-band SQLi, is to use parameterized queries (also known as prepared statements) to access SQL databases. If your programming language does not support parameterized queries but your database engine supports stored procedures, you may use stored procedures with prepared statements instead.

Relying purely on other prevention methods, such as whitelists, blacklists, or input filtering/escaping, is not recommended. Malicious hackers may find a way around such sanitization.

Additionally, you should configure your environment not to display database errors – this helps mitigate even undiscovered error-based SQL injection vulnerabilities. However, it has no effect on other types of SQL injections and therefore you should not treat it as a prevention method.