Solved

Complex SQL Statement

Posted on 2008-10-09
8
177 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

631 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