SQL injection attacks

Contents

• Introduction
• How SQL injection works
• How to find out which SQL server is on work
• A few examples for SQL injection
• Preventing SQL injection
  • Parameterized query
  • Stored procedure
  • Regular expression to discard string
  • Quoteblock function

Introduction

There was an era when children grew up reading comic strips but now comic strips have got some action and has evolved as the animated cartoon films.

Web sites initially started just to show some static information to the user on plain HTML pages with some graphics and colorful text. Soon the advent of technologies like CGI made it possible to have dynamic features attached to them. Sites were able to query an online database and fullfill requests. With the growing pace of the IT, soon websites got a powerful shot gun called online transaction. Now sites not only display dynamic data but are also capable of taking the orders from the customers and process them online.

This whole drama of accessing the database had the database accessing API at its heart. The input given by the end user (visitor) was processed by the back end SQL engine to perform CRUD operations on the database (CRUD - Create, Read, Update, and Delete).

How SQL injection works

SQL injection is no more exception to the golden rule ‘ALL INPUT IS EVIL’. SQL injection is nothing but, using the CRUD operation against the database in a way that it no more fulfills the desired results but give the attacker an opportunity to run his own SQL command against the database that too using the front end of your web site.

Well, mostly text box input is the best friend of the attacker. However any input going neatly to the database is not safe.

Let us take an example. The normal query like:

Select * from customers

is safe for SQL injection. As we can see there is no user input involved in it. An interactive query like:

select * from customers where customerID = 'ALFKI'

is an alarm for SQL injection if ALFKI is a user input. By user input I mean data got through the front end of your application. Let us assume some text box is there (it could be combo box also) where the end user can input customer name and in turn your application will show the result of a query after executing the query against a database.

In case it is a combo box, the attacker can first save your page in HTML format and then craft the combo box value field to make an attack. However, the best he can do is offline analysis of your page and then making an application which will send the GET or POST request directly to your application using the same name for the input controls to make his query string/ request, thus bypassing your front end script validations. It’s very easy to make this kind of application. An average programmer with one year of experience in web application development can make it.

So let us see one example of evil user input. If instead of typing the customer name in the text box, the user types the following string. Then the SQL Server running behind the scene will lock the server. This however depends upon how misconfigured the server is.

a'  exec master.dbo.xp_cmdshell 'rundll32.exe user32.dll,LockWorkStation'

So if you want to make a site unavailable and the server is misconfigured then you can run the 'iisreset/Stop' command. Here is the complete input:

a'  exec master.dbo.xp_cmdshell 'iisreset/Stop'

However this is just a hint. Actually the attacker will instead want to gain information stored inside your tables on the server to get access to the valuable data stored inside it. And real data is money. Think about this, if there is a badly designed application and an attacker is able to get information regarding credit card number or personal email address of your customer along with their personal information like home address, he might commit a physical crime there or sell the email to spammers.

Knock knock knock... "madam I am from abc.com and here is the order you placed on the site" on so and so date.. thus gaining physical access to the house of your customer.

How to find out which SQL server is on work

However, before breaking in to the site for access, an attacker needs to know which SQL engine is at the back end, and getting this out of a poorly designed system is a mere child's play. And before even trying, there is another way called guessing.

If your site has web page extensions like .asp, .aspx etc. then there is a high chance of SQL Server or MS Access. If the pages end as .jsp it could be an Oracle system at your end. If the pages are ending as .php, I guess it will be MySQL. However if my guess is wrong I can always go ahead and start doing the real work. Let us see how you can find out which SQL engine is at the back end. To identify the back end SQL server the minimum qualification required is beginner level experience with SQL. The general tool used to find out the SQL engine at the back end are the string concatenating characters and the comment characters.

• For SQL Server: ALFKI and AL + FKI are same.
• However, for Oracle, ALFKI and ALF || KI are the same.

So give the input as AL+FKI and if you get an error it means definitely the back end is not SQL Server. So now you can move on to the string concatenation syntax of other databases till you find out that you are getting the proper result.

Attackers generally work like a commando instead of an immature person with a gun in hand. They always put the correct tools ready near to them. What I mean to say is that an attacker will have text files ready with him, which will have the canned queries ready and the attack is a matter of quick copy and paste.

Using the SQL engine defined functions are also good examples of narrowing down the range, e.g., you can use the date functions to find out which server is at the back end: Sysdate for Oracle, Getdate for SQL Server.

Using the character used to end a SQL statement is also a good candidate for finding out the SQL engine at the backend.

We can not stop this kind of attacks because even showing an error page back to an attacker is like letting him know that the entered string was wrong. However the golden thumb of rule is in the error page don’t show the full detailed error message back, instead just say some error has occurred, and you can thus be saved from further damage the attacker is going to cause. Because, SQL injection attacks generally use the returned error messages to go a bit deep into the attacking process.

A few examples for SQL injection

Let us see what the few types of attacks which are popular are:

The common attacks are:

1. Finding out the tables in a database.
2. Finding out the column names in a database.
3. Using the ORDER BY clause to find out the number of columns used in the database.

Being an admirer of Microsoft, I will use MS SQL Sever. This doesn’t mean it is week. But I feel at home while using MS SQL Server. First let us see a simple query to find out which tables are there in the database:.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

This query returns the user defined tables in a database and an attacker can use this to find out the tables present in your system. To find out the columns present in your table, the next query which is used as sequel to this query is:

SELECT  COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='customers'

You will be wandering how an attacker is going to directly execute these queries in your system. The answer is he can easily find out the number of columns returned in your result set and then do a Union with the returned result.

Simple commands like:

UNION select table_name , null ,null ,null

are helpful in this case. Let us take the old query once again:

select * from customers where customerID = 'ALFKI'

The vulnerable query will be:

select * from customers where customerID = 'ALFKI' 
union 
SELECT table_name,null,null,null,null,null,null,null,null, 
           null,null FROM INFORMATION_SCHEMA.TABLES --'

Exploit string used [starting from the single quote sign below]:

' union 
SELECT table_name,null,null,null,null,null,null,null, 
     null,null,null FROM INFORMATION_SCHEMA.TABLES --

However you will be now astonished how an attacker can find out the number of nulls to be attached. So the answer is very simple: by looking at the output when a normal query produces result on the HTML page or by using the ORDER BY clause:

select * from customers where customerID = 'ALFKI' Order by 1 -- ‘  

select * from customers where customerID = 'ALFKI' Order by 15 -- ‘

will give an error to the attacker. However:

select * from customers where customerID = 'ALFKI' Order by 11 -- ‘

will not give an error so the attacker now knows that there are only 11 columns in the result returned.

I will stop my discussion here itself, rest is on your imagination.

Some advanced forms of SQL injection (MS SQL Only)

Using the technique of SQL injection, a more advanced attack can be done. This can be done if we inject the SQL string containing the call to sp_addlinkedserverlogin. However this again depends upon the privileges to the account under which this SQL injection has run. Similarly, the openrowset, Openquery functions can provide an attacker a chance to guess your password using brute force techniques. For more details, see this.

Preventing SQL injection

The various techniques used to prevent SQL injections are:

1. Parameterized query
2. Stored procedure
3. Regular expression to discard input string
4. Quoteblock function
5. Don’t show detailed error messages to the user.
6. Have a less privileged user/role of your application in database.

SQL injections can be prevented. However there are myths about them (like if I am using stored procedure I am safe). One rule still follows: ‘Don't show detailed error messages to the user’. The basic mantra to prevent SQL injections is to let the string input as a string input to the SQL Server. This can be done by checking the string terminator character before giving the input to the server. Some techniques like using parameterized query and using stored procedures will do it free for you. However I would still suggest you to use the Quoteblock function inside them (MS SQL Server). The other prevention will be only carefully deciding the grant of the execute command to the service account which you are using to make the communication possible between the SQL server and the web server. One handy tip is not to use dynamic SQL in your application. Dynamic SQL is like (MS SQL).

Sp_executesql(‘string from the user’)

Or

exec (‘string from the user’)

I would like to add one more point here: don’t rely only on regular expressions and other string handling functions to discard the input, as SQL injection may come in encoded form instead of just plain human readable language.

Another way is getting a snort rule to work for you. However the discussion of snort rules and their implementation is out of the scope of this article. Snort rules help you in the detection of SQL injection intrusions happening in your network. Snort generally sends you an alert or logs the intrusion attempts. See this page for more details on snort rules.

In the next part, I will cover the details of Script injection, how attackers use it and how to prevent them.