Solved

Complex SQL Statement

Posted on 2008-10-09
8
172 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
  • 4
  • 4
8 Comments
 
LVL 59

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 59

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
 
LVL 59

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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 59

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now