Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

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

0
webdork
Asked:
webdork
  • 4
  • 4
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Kevin CrossChief 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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now