Solved

Complex SQL Statement

Posted on 2008-10-09
8
176 Views
Last Modified: 2010-05-18
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
Comment
Question by:webdork
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22684092
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22684108
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
 

Author Comment

by:webdork
ID: 22684176
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!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22686012
Try replacing those 3 lines with this:

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

Open in new window

0
 

Author Comment

by:webdork
ID: 22688624
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22688682
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
 

Author Comment

by:webdork
ID: 22688744
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
 

Author Comment

by:webdork
ID: 22688825
Thanks,  that seemed to do the trick.  I'd give ya more points if I could.
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question