What's the best way of cleaning up after a SQL

问题:

I've been tasked with the the maintenance of a nonprofit website that recently fell victim to a SQL injection attack. Someone exploited a form on the site to add text to every available text-like field in the database (varchar, nvarchar, etc.) which, when rendered as HTML, includes and executes a JavaScript file.

A Google search of the URL indicates that it's from email spammers based out of Romania or China, but that's not what's important right now.

I went through and manually removed the information from the the text fields that render on most visible and popular pages on the site but I'm curious as to what would be the best programmatic way of removing the text from the other text fields on the site.

Obviously there's more that needs to be done (hardening the site against SQL injections, using something like markdown instead of storing HTML, etc.) and I am working on those but for the time being what I really need is a good way to go in and programmatically remove the injected text. I know what the exact text is, it's the same every time, and it's always appended to the end of any text field. I can't afford to strip out all HTML in the database at this time and I don't know when this happened exactly so I can't just roll back to a backup. Also, the site is on shared hosting and I cannot connect to the database directly with SQL Server tools. I can execute queries against it though, so if there's any way of constructing a SQL update statement to the effect of "hey find all the text fields in all of the tables in the entire database and do this to clean them" that would be the best.

回答1:

Restore the data from a recent backup.

回答2:

I was victim and you can use it to clean up

UPDATE Table 
SET TextField = SUBSTRING(TextField, 1, CHARINDEX('</title', TextField) - 1)
WHERE (ID IN (SELECT ID FROM Table WHERE (CHARINDEX('</title', Textfield, 1) > 0)))

回答3:

Assuming you've fallen victim to the same attack as everyone else, then SQLMenace' code is close. However, that attack uses a number of different script urls, so you'll have to customize it to make sure it matches the url you're seeing in your database.

I wrote about it as well, and my solution code included a more-generic cleanup.

One important point is that the very first thing you need to do is take down the site. Right now you're actively serving malware to your users, and that could put you in a legal fix later. Put up a placeholder page so your users aren't left in the dark, but don't keep serving up malware. Then you can fix the site to make sure it's no longer vulnerable to injection. The simplest way to do that for this particular attack is to just disable sysobjects/syscolumns permissions for your web user, but you'll want to make a more through cleanup as well or it's only a matter of time until you're cracked again. Then you can use the code provided to clean up the site and put it back live.

回答4:

This will reverse that, also it would be wise to take sysobject permissions away from the username your site runs with, and to sanitize input of course

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('if exists (select 1 from ['+@T+'] where ['+@C+']  like ''%"></title><script src="http://1.verynx.cn/w.js"></script><!--'') begin print ''update ['+@T+'] set ['+@C+']=replace(['+@C+'],''''"></title><script src="http://1.verynx.cn/w.js"></script><!--'''','''''''') where ['+@C+']  like ''''%"></title><script src="http://1.verynx.cn/w.js"></script><!--'''''' end')
FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

I wrote about this a while back here: Microsoft Has Released Tools To Address SQL Injection Attacks