October 04, 2011

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

4 comments:

  1. Greetings Inder,

    Thank you for the post, i enjoyed the post thoroughly. Software testing seems to be more interesting than i had envisioned. Thanks for sharing your experience and teaching beginners like me.

    regards,

    ReplyDelete
  2. Thanks for the great post. Also, can you help us with our project? Our collage project requires that we test TCP protocol implementation, once with correct implementation and once after introducing faults. Please suggest how we go about this as we don’t have a clue about setting up the test environment and various other software that need to be hooked up to the protocol code, and how to manually carry out the tests and observe results. If its not too much to ask, please also tell how to make a faulty version of protocol, some fault injection tools, may be? Thanks in advance.
    P.S: We are ready to work on any kind of operating system.

    ReplyDelete
  3. It was really helpful for me in terms of understanding the SQL Injection.

    ReplyDelete
  4. This was a great example as really before this I did not understand the injection process full but this really explains it in full - excellent work

    ReplyDelete

Note: Only a member of this blog may post a comment.