Mr. Krunal Tailor
06MCA60 [FYMCA]

Introduction

SQL injection is a technique for exploiting web applications that use client-supplied data in SQL queries, but without first stripping potentially harmful characters. Despite being remarkably simple to protect against, there is an astonishing number of production systems connected to the Internet that are vulnerable to this type of attack. The objective of this paper is to focus the professional security community on the techniques that can be used to take advantage of a web application that is vulnerable to SQL injection, and to make clear the correct mechanisms that should be put in place to protect against SQL injection and input validation problems in general.

If the application is creating SQL strings naively on the fly and then running them, 
it's straightforward to create some real surprises

SQL Injection occurs when an attacker is able to insert a series of SQL statements into a 'query' by manipulating data input into an application.

Testing procedure

A typical SQL statement looks like this:

select id, forename, surname from authors

This statement will retrieve the 'id', 'forename' and 'surname' columns from the 'authors' table, returning all rows in the table.

The 'result set' could be restricted to a specific 'author' like this:

select id, forename, surname from authors where forename = 'john' and surname = 'smith'

An important point to note here is that the string literals 'john' and 'smith' are delimited with single quotes. Presuming that the 'forename' and 'surname' fields are being gathered from user-supplied input, an attacker might be able to 'inject' some SQL into this query, by inputting values into the application like this:

Forename: jo'hn

Surname: smith

The 'query string' becomes this:

select id, forename, surname from authors where forename = 'jo'hn' and surname = 'smith'

When the database attempts to run this query, it is likely to return an error:

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'hn'.

The reason for this is that the insertion of the 'single quote' character 'breaks out' of the single-quote delimited data. The database then tried to execute 'hn' and failed. If the attacker specified input like this:

Forename: jo'; drop table authors--

Surname:

…the authors table would be deleted, for reasons that we will go into later.

It would seem that some method of either removing single quotes from the input, or 'escaping' them in some way would handle this problem. This is true, but there are several difficulties with this method as a solution. First, not all user-supplied data is in the form of strings. If our user input could select an author by 'id' (presumably a number)

For example, our query might look like this:

select id, forename, surname from authors where id=1234

In this situation an attacker can simply append SQL statements on the end of the numeric input. In other SQL dialects, various delimiters are used; in the Microsoft Jet DBMS engine, for example, dates can be delimited with the '#' character. Second, 'escaping' single quotes is not necessarily the simple cure it might initially seem, for reasons we will go into later.

Attacks

This section describes the following SQL injection techniques:

·         Authorization bypass

·         Using the SELECT command

·         Using the INSERT command

Authorization Bypass

The simplest SQL injection technique is bypassing logon forms. Consider the following web application code:

SQLQuery = "SELECT Username FROM Users WHERE Username = ‘" & strUsername & "‘ AND Password = ‘" & strPassword & "‘" strAuthCheck = GetQueryResult(SQLQuery) If strAuthCheck = "" Then boolAuthenticated = False Else boolAuthenticated = True End If

Here’s what happens when a user submits a username and password. The query will go through the Users table to see if there is a row where the username and password in the row match those supplied by the user. If such a row is found, the username is stored in the variable strAuthCheck, which indicates that the user should be authenticated. If there is no row that the user-supplied data matches, strAuthCheck will be empty and the user will not be authenticated.

Using the SELECT Command

If strUsername and strPassword can contain any characters that you want, you can modify the actual SQL query structure so that a valid name will be returned by the query even if you do not know a valid username or a password. How? Let’s say a user fills out the logon form like this:

Login: ‘ OR ‘‘=‘ Password: ‘ OR ‘‘=‘

This will give SQLQuery the following value:

SELECT Username FROM Users WHERE Username = ‘‘ OR ‘‘=‘‘ AND Password = ‘‘ OR ‘‘=‘‘

Instead of comparing the user-supplied data with that present in the Users table, the query compares a quotation mark (nothing) to another quotation mark (nothing). This, of course, will always return true. (Please note that nothing is different from null.) Since all of the qualifying conditions in the WHERE clause are now met, the application will select the username from the first row in the table that is searched. It will pass this username to strAuthCheck, which will ensure our validation. It is also possible to use another row’s data, using single result cycling techniques.

Using the INSERT Command

The INSERT command is used to add information to the database. Common uses of INSERT in web applications include user registrations, bulletin boards, adding items to shopping carts, etc. Checking for vulnerabilities with INSERT statements is the same as doing it with WHERE. You may not want to try to use INSERT if avoiding detection is an important issue. INSERT injection often floods rows in the database with single quotes and SQL keywords from the reverse-engineering process. Depending on how watchful the administrator is and what is being done with the information in that database, it may be noticed.

Here’s how INSERT injection differs from SELECT injection. Suppose a site allows user registration of some kind, providing a form where you enter your name, address, phone number, etc. After submitting the form, you navigate to a page where it displays this information and gives you an option to edit it. This is what you want. To take advantage of an INSERT vulnerability, you must be able to view the information that you’ve submitted. It doesn’t matter where it is. Maybe when you log on, it greets you with the value it has stored for your name in the database. Maybe the application sends you e-mail with the Name value in it. However you do it, find a way to view at least some of the information you’ve entered.

An INSERT query looks like this:

SQLString = "INSERT INTO TableName VALUES (‘" & strValueOne & "‘, ‘" & strValueTwo & "‘, ‘" & strValueThree & "‘)"

You fill out the form like this:

Name: ‘ + (SELECT TOP 1 FieldName FROM TableName) + ‘ Email: blah@blah.com Phone: 333-333-3333

Making the SQL statement look like this:

INSERT INTO TableName VALUES (‘‘ + (SELECT TOP 1 FieldName FROM TableName) + ‘‘, ‘blah@blah.com’, ‘333-333-3333’)

When you go to the preferences page and view your user’s information, you’ll see the first value in FieldName where the user’s name would normally be. Unless you use TOP 1 in your subselect, you’ll get back an error message saying that the subselect returned too many records. You can go through all of the rows in the table using NOT IN ( ) the same way it is used in single-record cycling.

Solutions  

·         Data Sanitization  

Secure SQL Coding for your Web Application

Data Sanitization

All client-supplied data needs to be cleansed of any characters or strings that could possibly be used maliciously. This should be done for all applications, not just those that use SQL queries. Stripping quotes or putting backslashes in front of them is nowhere near enough. The best way to filter your data is with a default-deny regular expression. Make it so that you include only the type of characters that you want. For instance, the following regular expression will return only letters and numbers:

     s/[^0-9a-zA-Z]//\

Make your filter narrow and specific. Whenever possible, use only numbers. After that, numbers and letters only. If you need to include symbols or punctuation of any kind, make absolutely sure to convert them to HTML substitutes, such as &quote; or >. For instance, if the user is submitting an e-mail address, allow only the “at” sign, underscore, period, and hyphen in addition to numbers and letters, and allow them only after those characters have been converted to their HTML substitutes.

Secure SQL Coding for your Web Application

There are also a few rules specific to SQL injection. First, prefix and append a quote to all user input, even if the data is numeric. Next, limit the rights of the database user. Don’t give that user access to all of the system-stored procedures if that user needs access to only a handful of user-defined ones.