SQL injection

Definition

SQL injection is a basic attack used to either gain unauthorized access to a database or to retrieve information directly from the database.

SQL injection is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to affect the execution of predefined SQL commands.

SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It generally allows an attacker to view data that they are not normally able to retrieve. This might include data belonging to other users, or any other data that the application itself is able to access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application’s content or behavior.

In some situations, an attacker can escalate an SQL injection attack to compromise the underlying server or other back-end infrastructure, or perform a denial-of-service attack.

Types

There are four types of SQL Injection attacks.

1. SQL manipulation using incorrectly constructed SQL statements

1. Most common type of SQL injection.
2. The attacker attempts to modify the existing SQL statement by adding elements to the WHERE clause or extending the SQL statement with set operators like UNION, INTERSECT, or MINUS. There are other possible variations, but these are the most significant examples.
3. SQL injection occurs when specially crafted user input is processed by the receiving program in a way that allows the input to exit a data context and enter a command context. This allows the attacker to alter the structure of the SQL statement which is executed.

Example:

Checking user authentication

SELECT * FROM users
WHERE username = 'bob' and PASSWORD = 'mypassword'

In this example, bob and mypassword are data.

Attacker attempt

SELECT * FROM users
WHERE username = 'bob' and PASSWORD = 'mypassword' or 'a' = 'a'

Another example:

select * from person where name = 'susan' and age = 2

Attacker attempt

Now imagine that instead of entering ‘susan’ the attacker entered ’ or 1=1; –.

select * from person where name='' or 1=1; -- and age = 2

Based on operator precedence, the WHERE clause is true for every row and the attacker has gained access to the application.

The set operator UNION is frequently used to manipulate a SQL statement into returning rows from another table.

Return a list of available products

SELECT product_name FROM all_products
WHERE product_name like '%Chairs%'

Attacker attempt

SELECT product_name FROM all_products
WHERE product_name like '%Chairs'
UNION
SELECT username FROM dba_users
WHERE username like '%'

The list returned to the web form will include all the selected products, but also all the database users in the application.

2. Code injection

Code injection attacks attempt to add additional SQL statements or commands to the existing SQL statement.

Some programming languages or APIs may allow for multiple SQL statements to be executed.

In databases that don’t allow them, this will not work. e.g. This will not work in Oracle DB.

SELECT * FROM users
WHERE username = 'bob' and PASSWORD = 'mypassword'; DELETE FROM users
WHERE username = 'admin';

Execute an application stored procedure to encrypt and save user’s password

BEGIN ENCRYPT_PASSWORD('bob', 'mypassword'); END;

Attacker attempt

BEGIN ENCRYPT_PASSWORD('bob', 'mypassword'); DELETE FROM users
WHERE upper(username) = upper('admin'); END;

3. Function call injection

Function call injection is the insertion of Oracle database functions or custom functions into a vulnerable SQL statement. These function calls can be used to make operating system calls or manipulate data in the database.

Application developers will sometimes use database functions instead of native code (e.g., Java) to perform common tasks. There is no direct equivalent of the TRANSLATE database function in Java, so the programmer decided to use a SQL statement.

SELECT TRANSLATE('user input',
 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
 '0123456789')
FROM dual;

This SQL statement is not vulnerable to other types of injection attacks, but is easily manipulated through a function injection attack.

Attacker attempt

SELECT TRANSLATE('' || UTL_HTTP.REQUEST('http://192.168.1.1/') || '',
 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
 '0123456789')
FROM dual;

The changed SQL statement will request a page from a web server. The attacker could manipulate the string and URL to include other functions in order to retrieve useful information from the database server and send it to the web server in the URL.

4. Buffer overflow attacks

Buffer overflows have been identified in the standard functions of several databases. A few standard Oracle database functions are susceptible to buffer overflows, which can be exploited through a SQL injection attack in an un-patched database. Known buffer overflows exist in the standard database functions tz_offset, to_timestamp_tz, and bfilename.

Most application and web servers do not gracefully handle the loss of a database connection due to a buffer overflow. Usually, the web process will hang until the connection to the client is terminated, thus making this a very effective denial of service attack.

A buffer overflow attack using tz_offset, to_timestamp_tz, and bfilename is executed using the function injection methods described previously.

Technical implementations

Blind SQL injection

Many instances of SQL injection are blind vulnerabilities. This means that the application does not return the results of the SQL query or the details of any database errors within its responses. Blind vulnerabilities can still be exploited to access unauthorized data, but the techniques involved are generally more complicated and difficult to perform.

Depending on the nature of the vulnerability and the database involved, the following techniques can be used to exploit blind SQL injection vulnerabilities:

1. You can change the logic of the query to trigger a detectable difference in the application’s response depending on the truth of a single condition. This might involve injecting a new condition into some Boolean logic, or conditionally triggering an error such as a divide-by-zero.
2. You can conditionally trigger a time delay in the processing of the query, allowing you to infer the truth of the condition based on the time that the application takes to respond.
3. You can trigger an out-of-band network interaction, using OAST techniques. This technique is extremely powerful and works in situations where the other techniques do not. Often, you can directly exfiltrate data via the out-of-band channel, for example by placing the data into a DNS lookup for a domain that you control.

Conditional responses

A book review website uses a query string to determine which book review to display.

So the URL https://books.example.com/review?id=5 would cause the server to run the query SELECT * FROM bookreviews WHERE ID = '5';

he query happens completely on the server; the user does not know the names of the database, table, or fields, nor does the user know the query string. The user only sees that the above URL returns a book review. A hacker can load the URLs

https://books.example.com/review?id=5 OR 1=1 and https://books.example.com/review?id=5 AND 1=2

which may result in queries

SELECT * FROM bookreviews WHERE ID = '5' OR '1'='1';
SELECT * FROM bookreviews WHERE ID = '5' AND '1'='2';

If the original review loads with the “1=1” URL and a blank or error page is returned from the “1=2” URL, and the returned page has not been created to alert the user the input is invalid, or in other words, has been caught by an input test script, the site is likely vulnerable to an SQL injection attack as the query will likely have passed through successfully in both cases. The hacker may proceed with this query string designed to reveal the version number of MySQL running on the server: https://books.example.com/review?id=5 AND substring(@@version, 1, INSTR(@@version, ‘.’) - 1)=4, which would show the book review on a server running MySQL 4 and a blank or error page otherwise. The hacker can continue to use code within query strings to achieve their goal directly, or to glean more information from the server in hopes of discovering another avenue of attack.

Second order SQL injection

Second order SQL injection occurs when submitted values contain malicious commands that are stored rather than executed immediately. In some cases, the application may correctly encode an SQL statement and store it as valid SQL. Then, another part of that application without controls to protect against SQL injection might execute that stored SQL statement. This attack requires more knowledge of how submitted values are later used. Automated web application security scanners would not easily detect this type of SQL injection and may need to be manually instructed where to check for evidence that it is being attempted.

Mitigation

Fortunately, SQL injection attacks are easy to defend against with simple coding practices. However, every parameter passed to every dynamic SQL statement must be validated or bind variables must be used.

An SQL injection is a well known attack and easily prevented by simple measures.

Object relational mappers

Developers can use ORM frameworks such as Hibernate, iBatis to create database queries in a safe and developer-friendly way. Since database queries are no longer constructed as strings, there is no danger of an injection vulnerability.

Bind variables

The PreparedStatement interface is used to execute dynamic SQL statements. The standard JDBC PreparedStatement interface may be used.

A PreparedStatement that is vulnerable to SQL injection may look something like this –

String name = request.getParameter("name");
PreparedStatement pstmt =
 conn.prepareStatement("insert into EMP (ENAME) values ('" + name + "')");
pstmt.execute();
pstmt.close();

To prevent SQL injection, a bind variable must be used –

PreparedStatement pstmt =
 conn.prepareStatement ("insert into EMP (ENAME) values (?)");
String name = request.getParameter("name");
pstmt.setString (1, name);
pstmt.execute();
pstmt.close();

The most powerful protection against SQL injection attacks is the use of bind variables. Using bind variables will also improve application performance. Application coding standards should require the use of bind variables in all SQL statements. No SQL statement should be created by concatenating together strings and passed parameters.

Bind variables should be used for every SQL statement regardless of when or where the SQL statement is executed. This should be your coding standard.

The use of bind variables is simple, but does require at least one more line of code per variable. Since a typical SQL statement is using 10-20 values, the additional coding effort may be substantial. But that should not stop you from using them.

Input validation

Every passed string parameter should be validated. Many web applications use hidden fields and other techniques, which also must be validated. If a bind variable is not being used, special database characters must be removed or escaped.

The use of bind variables and escaping of single quotes should not be done for the same string. A bind variable will store the exact input string in the database and escaping any single quotes will result in double quotes being stored in the database.

Function security

Standard and custom database functions can be exploited in SQL injection attacks. Many of these functions can be used effectively in an attack. Oracle is delivered with hundreds of standard functions and by default all have grants to PUBLIC. The application may have additional functions which perform operations like changing passwords or creating users that could be exploited.

All functions that are not absolutely necessary to the application should be restricted.

Pattern check

Integer, float or boolean, string parameters can be checked if their value is valid representation for the given type. Strings that must follow some strict pattern (date, UUID, alphanumeric only, etc.) can be checked if they match this pattern.

Database permissions

Limiting the permissions on the database login used by the web application to only what is needed may help reduce the effectiveness of any SQL injection attacks that exploit any bugs in the web application.

Web application firewalls

While WAF products such as ModSecurity CRS cannot prevent SQL injection vulnerabilities from creeping into a codebase, they can make discovery and exploitation significantly more challenging to an attacker.

Reading material

https://en.wikipedia.org/wiki/SQL_injection

https://portswigger.net/web-security/sql-injection (very good explanation)

https://owasp.org/www-community/attacks/SQL_Injection