Complex SQL Statement

AN EE genius gave me an invaluable SQL statement to find evidence of SQL injection (see attached.)  Now I'm hoping for a similar script that can rid my tables of the injection.
I use this simple replace statement
SET @intruderscript = '<script src=http://xxxxxx.cn></script>'
UPDATE negdiscountcodes SET code=REPLACE(code,@intruderscript,'')
but as you can guess, it is quite tedious to append each infected field.  Can someone combine the two in a script that will scrub my DB?  Help!
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('Select ['+@C+'] as [' +@T+'.'+@C+'] From [' +@T+'] where ['+@C+'] like ''%<script src=%''') 
      FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

Open in new window

webdorkAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Just to make sure we are testing on the same code, please see if you get the error with this:
DECLARE @T VARCHAR(255)
DECLARE @C VARCHAR(255)
DECLARE @intruderscript VARCHAR(255)
-- set injected script to detect
SET @intruderscript = '<script src=http://xxxxxx.cn></script>'
-- declare cursor and correct injected sql
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 
 
-- loop through cursor updating each table/column combination
WHILE(@@FETCH_STATUS=0) 
BEGIN 
      EXEC('UPDATE ['+@T+'] SET ['+@T+'].['+@C+'] = REPLACE(['+@T+'].['+@C+'],'''+@intruderscript+''','''')')
      FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
 
-- cleanup cursor
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
This should do the trick:
DECLARE @T VARCHAR(255),
@C VARCHAR(255),
@intruderscript varchar(255)
SET @intruderscript = '<script src=http://xxxxxx.cn></script>'
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 ['+@T+'].['+@C+'] = REPLACE(['+@T+'].['+@C+'],'+@intruderscript+','''')') 
      FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
You can try changing this:
EXEC('UPDATE ['+@T+'] SET ['+@T+'].['+@C+'] = REPLACE(['+@T+'].['+@C+'],'+@intruderscript+','''')')

To:
PRINT('UPDATE ['+@T+'] SET ['+@T+'].['+@C+'] = REPLACE(['+@T+'].['+@C+'],'+@intruderscript+','''')')

And make sure this comes out way you want first.  May need to put quotes in around @intruderscript  -- ('UPDATE ['+@T+'] SET ['+@T+'].['+@C+'] = REPLACE(['+@T+'].['+@C+'],'''+@intruderscript+''','''')')
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
webdorkAuthor Commented:
I got this error when I ran

/*-----------------------------
DECLARE @T VARCHAR(255),
@C VARCHAR(255),
@intruderscript varchar(255)
-----------------------------*/
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'in'.
0
 
Kevin CrossChief Technology OfficerCommented:
Try replacing those 3 lines with this:

DECLARE @T VARCHAR(255)
DECLARE @C VARCHAR(255)
DECLARE @intruderscript VARCHAR(255)

Open in new window

0
 
webdorkAuthor Commented:
I get this error now

/*-----------------------------
DECLARE @T VARCHAR(255)
DECLARE @C VARCHAR(255)
DECLARE @intruderscript VARCHAR(255)
-----------------------------*/
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '<'.
0
 
webdorkAuthor Commented:
That block took a bit longer to run.  I got some errors (below).  I stripped out some of the rows affected lines to shorten the paste

/*-----------------------------
DECLARE @T VARCHAR(255)
DECLARE @C VARCHAR(255)
DECLARE @intruderscript VARCHAR(255)
-----------------------------*/

Server: Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.
0
 
webdorkAuthor Commented:
Thanks,  that seemed to do the trick.  I'd give ya more points if I could.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.