SQL Injection explained

Introduction

The SQL Injection attack exploits a vulnerability of the database access layer of an application. It can happen in any application, not only, as many people thinks, in web applications, although given the huge quantity of user consuming Internet applications as well as the "laziness" of some web developers using GET parameters, this type of attacks has become popular in the Net.

Attack anatomy

An SQL Injection based attack exploits a code structure where we are performing a query or a command in the database providing some user fetched parameters.

The problem arises where those parameters are not validated but just used by the programmer appending them into the relevant sections of the query, for example:

string query = "SELECT Count(*) FROM Users WHERE Name='" +
                        txtNombre.Text + "' AND Password = '" + txtPassword.Text + "'";

that query returns the number of user whose user name and password match the ones provided in the given text fields. The problem with an SQL Injection attack comes, not because the code is incorrect but because it is assuming the input parameters are correct, that is, is not taking into account the full range of possibilities. That way, if we give the following values for those fields:

txtNombre.Text -> "Jorge"
txtPassword.Text -> "' OR '1' = '1"

the final string will be:

SELECT Count(*) FROM Users WHERE Name='Jorge' AND Password='' OR '1' = '1';

That, as we can see, has "injected" a modification over the original SQL query which wasn't foreseen and as a result, our original query is modified so that, no matter the provided user name or password, will return the total count of users in the database, so that if our access mechanism relies on testing if the number of returned rows is greater than zero in order to allow or deny access for the user, with this simple attack we would have tricked the system.

We can easily find much worst cases, for example, continuing with the previous example we can write the following text in the password field:

'; DELETE * FROM Users; --';

which will result in a query like:

SELECT Count(*) FROM Users WHERE Name='Jorge' AND Password='';
DELETE * FROM Users;
-- El resto de la query queda como un comentario;

which in turn will delete every user from our database.

Guessing the database strcucture

Usually, SQL Injection techniques are based on the presence of unhandled error message to guess the structure of the database, for example if the error log of PHP or ASP is active or by means of unhandled execptions of the application.

There are several techniques (which I'm not covering here) used to force errors whose output hint us about the database structure, for example, showing that a given table or field does not exits. Generally speaking, all strategies are based on using common names for a given set of tables. For example, is quite common to use "users" as the name of the table in which all the information about the used is stored.

Avoiding SQL Injection

The first step in order to prevent SQL Injection attacks is taking conscience about the fact that a user will not always provide the input he is suppose to provide.

The second step is performing something which is basic in security: "data validation". For each data provided by the user to the application we must ensure it is of the appropriate type as well as within the domain of the data. For example, Delphi provides a very useful function called QuotedStr which wraps the content of an string variable between quotes so that, for the previous example we would have:

query := 'SELECT Count(*) FROM Users WHERE Name=" +
            QuotedSql(txtNombre.Text) + " AND Password = " + QuotedStr(txtPassword.Text);

that, for txtPassword.Text" -> "'OR '1' = '1'" will return:

'SELECT Count(*) FROM Users WHERE Name='Jorge' AND Password = ''' OR ''1''=''1''';

which is a perfectly valid string in Delphi and which translate into an SQL query that looks for a password euqal to OR '1'='1'

More information

  • Advanced SQL Injection - English Pdf by Chris Anley explaining some of the most relevant techniques in SQL Injection
  • Sql Injection - Spanish pdf by the @rroba magazine. Quite complete with a lot of practical examples.
  • Técnicas de Sql Injection - Spanish Pdf. Another SQL Injection manual which includes also a review of SQL syntax as well as output traces in ASP that can be used for SQL Injection.
10
Average: 10 (2 votes)
Your rating: None