SQL Injection

If you have read my earlier article, Code injection attacks, you would have some idea about SQL injection attack. This post explains SQL injection in detail so that you may understand it well.

What is the SQL injection vulnerability? Vulnerability is a weakness in the application software under test that can be attacked to cause the application (or even the underlying operating system) to behave in an undesirable manner. The SQL injection (SQLi in short) vulnerability lives in the middle-layer or the database layer of the application. It exists when the application executes a dynamic SQL query against the database without validating, escaping or rejecting the unexpected inputs given by the attacker. These inputs become a part of the dynamic SQL query and are executed against the database.

What is the SQL injection attack? It occurs when some text or even another SQL query is inserted into the application's SQL query. Attacks can be successful or unsuccessful depending on the application and the underlying database. A successful SQL injection attack may show confidential data to the attacker, allow the attacker to impersonate another user, increase the attacker's privileges to higher levels, insert/ modify/ delete data in the database tables or even perform administrative operations on the database like shutting down the database instance.

With this background, let us see examples showing SQL injection.

Example 1. The application query is

SELECT * from Salaries WHERE EmployeeName = ? AND EmployeeId = ?

The EmployeeName and EmployeeId are strings that are obtained from the client-side application. The intention of the developer is that an EmployeeName, say John, and an EmployeeId, say A100, is sent by the client to the server. After concatenating these values, the query becomes

SELECT * from Salaries WHERE EmployeeName = 'John' AND EmployeeId = 'A100'

The application then shows the salary details of this particular employee. Now, the attacker passes  "name" as the EmployeeName and "Id' OR 'x'='x" for EmployeeId. After concatenating these values, the query becomes

SELECT * from Salaries WHERE EmployeeName = 'name' AND EmployeeId = 'Id' OR 'x'='x'

The result is that the WHERE clause now contains an OR  condition that is always true. Due to operator precedence, this query returns all rows of the Salaries table.

Example 2. The application query on the login page of the application is

SELECT UserName FROM Users WHERE UserName = ? AND Password = ?

As in the previous example, there are two inputs obtained from the client-side application. Here, the developer expects two strings, say John and Smith99, are sent by the client to the server. In such a case, the query becomes

SELECT UserName FROM Users WHERE UserName = 'John' AND Password = 'Smith99'

If this UserName and Password combination exists in the users table, it returns one or more rows. Instead, the attacker passes "Administrator'--" and "password" values. After concatenating these values, the query becomes

SELECT UserName FROM Users WHERE UserName = 'Administrator'--' AND Password = 'password'

After removing the commented part, the query is effectively

SELECT UserName FROM Users WHERE UserName = 'Administrator'

The result is that if there is a user named Administrator, the attacker logs into the application impersonating this user and has all of the user's privileges on which to base further attacks.

Example 3. The application query on the search page of the application is

SELECT * FROM Products WHERE ProductName = ?

If the attacker provides the value "name'; DELETE FROM Products;--", the query becomes

SELECT * FROM Products WHERE ProductName = 'name'; DELETE FROM Products;

If the database allows batch execution (of multiple queries), the latter query delete all rows from the Products table.

I hope that you understand SQL injection now. Few other points to note are

1. SQL injection is further divided into two categories, SQL manipulation and Code injection. Strictly speaking, examples 1 and 2 involve SQL manipulation. Example 3 involves Code injection, because extra SQL code is inserted there.

2. If the attacker knows in advance about the attack SQL, he can devise complex attacks. This is possible if the application throws error messages showing the SQL query text or if it is an Open Source application or if the attacker somehow obtains access to the application source code.

3. Testing SQL injection can be automated. A variety of  tools are available for this. Examples include SQLiX (SQL Injection Scanner) and sqlmap (Open Source Penetration testing tool).

Image: Ambro / FreeDigitalPhotos.net