Analysis

Database Activity Monitoring Part 2 - SQL Injection Attacks

Nigel Stanley By: Nigel Stanley, Practice Leader - IT Security, Bloor Research
Published: 26th February 2010
Copyright Bloor Research © 2010
 Logo for Bloor Research

If you think through the web sites you visit on a daily basis the chances are that you will need to login to verify who you are. In most cases your username would be stored in a relational database along with all the other registered users on that web site. Hopefully your password will be encrypted and not stored in plain text.

Unfortunately there are gaping holes in the security of some web sites that are being aggressively exploited by a form of hacker attack called SQL Injection.

Even more worrying is that this attack can be carried out against fully patched databases. It is not a problem with patches, more an issue with the way databases work and how a system has been designed.

This attack can be made against any website page that contains data from a database including search pages, feedback forms and customer comment forms. The types of database being used to run web sites include products such as MySQL, SQL Server and Oracle and all of these can be vulnerable to attack. SQL injection is not only specific to SQL Server!

In a typical database used to manage website logins a table will be used to store user's details. This would probably have at least two columnsone for the username and one for the password. The table would probably be called users or something very similar.

When a user submits their details on the website the database will parse the username and password and convert it into a string of SQL to send to the database, so the database engine receives a line of SQL similar to this:

SELECT * FROM users WHERE username = 'fredsmith' AND password = 'userspassword'

This is a pretty standard SQL statement and would look more or less the same whichever database you were using to store the user's data.

Hackers are interested in SQL injection attacks as they can manipulate the database by sending it some duff data. Instead of giving a valid username they could type something like this:

SELECT * FROM users WHERE username = ' ' ' AND password = ' '

The hacker has supplied a single quote character as the username and a blank as the password. The database, being well behaved, would see this query and decide to create an error message. On seeing this error message the hacker will then start to dig around a bit more, as the error messages returned from the database will start to become increasingly more helpful, and start to point out the structure of the tables in the database. With some careful digging an experienced hacker could quite quickly compromise a vulnerable database.

In the next case we can start to fool the database further by adding in other strings to the username. For example if we type in or email='345 as our username it will be resolved by the query parser as:

SELECT * FROM users
WHERE username = ' ' or email='345  ' AND password = ' '

At this point the database may give an error if there is no such column in the users database as email. On the other hand it may not give an error message suggesting that there is a column called email after all. All we need to do then is type in an email address for a user. As most organisations have a standard email address structure it is very easy to get an employee name and then use that as your user name in the SQL injection attack.

As you can see it is scarily easy to build up a picture of a database structure based on some simple fooling of the user login screen and some intelligent guesses as to the likely structure of a database.

The good news is that it is possible to protect a database from SQL injection attacks.

Common measures include filtering out single quotes, double quotes, slash, backslash, semi colon and extended characters from user input strings and cookie values and parameters from a URL. In addition numeric values can be converted into integers before passing to the database and extended stored procedures can be deleted.

On the other hand a database activity monitoring solution will normally provide you with protection against web applications being manipulated. Once installed in a learning mode the database activity monitoring solution will start to understand what is "normal" activity for a specific application. This process of normalisation is where the intelligence of the system comes into play, especially as it needs to determine what is a normal parameterised query as opposed to one that has been submitted by a hacker - a process more involved than simply listing routine queries. Once up to speed the database activity monitoring application will then create an alert if unusual activity starts on the website, such as an attempted SQL Injection attack, and take appropriate action.

Good system design is vital to prevent SQL Injection attacks, but combining this with a database activity monitoring solution you will be even more assured that the solution will be secure.

The next article will cover other attacks that can be prevented using database activity monitoring.

Reader Comments

Sorry, we are no longer accepting comments on this item. We suggest trying to contact the author directly.

4th March 2010: 'Alam' said:

Great article, thanks for share.

This article introduces what is SQL Injection Attacks, hazard of SQL Injection Attacks and how to detect SQL Injection Attacks.
http://www.ids-sax2.com/articles/DetectSQLInjectionAttacks.htm

Reply to Alam?

8th March 2010: 'The Cat' said:

You claim to be a security expert? Your description of SQL injection filtering is "worst practise"! Microsoft has been very clear for at least three years that you should never filter to prevent undesirable characters. Instead, you should always filter to only allow desirable characters. The reason for this is simple: with the large number of foreign characters in modern character sets, there are many ways to encode a character and new ways will be discovered over time. Some of those methods result in conversion from a foreign symbol to a local symbol after the SQL injection check has been done.

Reply to The Cat?

9th March 2010: 'Nigel Stanley' said:

Thanks for your comments. Certainly I have seen both techniques adopted successfully. As for calling myself a security expert, that is something I studiously avoid as the more I learn about IT security the more I realise I don't know...

Reply to Nigel Stanley?

10th March 2010: 'Hans' said:

Hi Nigel,

Your modesty is positive.
I agree with 'The Cat' that checking for allowed characters would be more safe, but see no reason in 'beating in you up' for that.
For a username you might only allow letters and digits, and thus implicitly disallow all other characters.
In the example above when using SQL Server it would even be better to call a stored procedure with username and password as parameters that returns in an OUTPUT paramter if the combination is valid. In that case you do not need dynamic SQL and thus have no risk of SQL injection.

Reply to Hans?

10th March 2010: 'mtcoder' said:

I agree with using functions / stored procedures to do double checking of input. I have a few functions, that strips or returns error strings. For example a password can / should be allowed to have special characters. I actually have a table of excluded combinations. My functions run through and recursive check for possible string combinations from the table, via a simple loop. Granted there is a performance hit but the combination allows me to at any time check any string at any time. My checkagainst table has several columns, User name, password, url parameters, etc. The function takes string name, and columnname values. But.... the bigger thing is all dba's should sit down with their developers and talk about sql injection. They have great tools like masking and expressions to block most attempts before they even happen. Most of the time though, they have no idea or understanding of how it works, cause they are not DBA's they just want to get/set some data.

Reply to mtcoder?

10th March 2010: 'David Wendelken' said:

This is a query that will find sql server database code that likely contains dynamic sql calls. It won't help you against goober front-end developers who concatenate strings, though!

Run from an account with sysadmin privileges to see all the code modules, otherwise you may only see a partial list.

select
s.name "SchemaName"
,o.type "ModuleType"
,o.type_desc "ModuleTypeDesc"
,o.name "ModuleName"
,asm.definition "ModuleCode"
from sys.all_sql_modules asm
inner join sys.all_objects o
on asm.object_id = o.object_id
inner join sys.schemas s
on o.schema_id = s.schema_id
where s.name 'sys'
/* get rid of line breaks and extra white space before checking */
and replace(replace(replace(replace(
asm.definition,' ',''),char(13),''),char(12),''),char(11),'')
like '%EXEC(%'
order by 1,2,3,4;

Reply to David Wendelken?

11th March 2010: 'Nigel Stanley' said:

These are some really cool responses to the article, thanks.

Out of interest has anyone got experience of statistical or tracker attacks against databases? I'd be interested in how you detected them...

Reply to Nigel Stanley?

17th March 2010: 'Doug' said:

Nigel - don't forget EXECUTE( too - not everyone shortcuts the reserved word.

Reply to Doug?

10th March 2010: 'Al' said:

So far, the discussion has been all about the input from the user/hacker. The other thing to control is the output. All SQL error messages should be sent to a log (for the support desk to refer to), with only a generic error message presented to the user (e.g. "Missing or invalid data field on form" or "Invalid login"). Don't give up any information that can be used to develop a hack!

Reply to Al?

11th March 2010: 'Michael Christie' said:

Here is a sequence to apply in retrofit situations.
1) never output sql error messages to web pages - this changes sql injection attack into a blind sql injection attack and dramatically reduces the possibility of a success.
2) the login used to access the database should be restricted to only the permissions needed to run sql from the web pages
3) use the smallest set of sql possible on web pages - ideally use stored procs only - this combined with #2 significantly reduces sql injection vulnerability
4) filtering of inputs

Reply to Michael Christie?

The messages above were all contributed by IT-Director.com readers. Whilst we take care to remove any posts deemed inappropriate, we can take no responsibility for these comments. If you would like a comment removed please contact our editorial team.