W.O.P.R   W.O.P.R STORM monitor - Example of how to remove SQL injection of <script> tags   Help

Home/storm.monitor/SQL.injection/how.to.remove.it.asp

SQL Injection - how to remove the injection of <script> tags

Now that we know how SQL injection works, and how we find it, we we are ready to clean up. If you haven't read how to find it, take a look at

GOTO: Find the injection

So let us build a SQL statement, that looks for <script> injections in the database and removes it. First we reuse the outer structure, to iterate trough the tables and fields. The outer structure is:
DECLARE @T VARCHAR(255),@C VARCHAR(255),@S VARCHAR(4000)
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 
   PRINT 'Table= ['+@T+'], coloumn= ['+@C+']' /* <<===Prints tablename an coloumn name */
   FETCH NEXT FROM Table_Cursor INTO @T,@C
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor 
Just like when we found it, this loop iterates over the tables/coloumns. If we use it on the pubs database you should see the following in Query Analyzers result pane.
Table= [titleauthor], coloumn= [au_id]
Table= [titleauthor], coloumn= [title_id]
Table= [stores], coloumn= [stor_name]
Table= [stores], coloumn= [stor_address]
Table= [stores], coloumn= [city]
Table= [sales], coloumn= [ord_num]
Table= [sales], coloumn= [payterms]
Table= [sales], coloumn= [title_id]
Table= [roysched], coloumn= [title_id]
.... etc
But now we want to remove the <script> injection. To remove it, we build an SQL update statement, that finds the first occurence of <script inside the string, and update the string to the first part uptil the posistion of the <script. For each combination we generate an SQL string:
'UPDATE ['+@T+'] SET ['+@C+'] = SUBSTRING(['+@C+'],1,CHARINDEX(''<script'',['+@C+'])-1)  WHERE ['+@C+'] LIKE ''%<script%'''
This SQL builds an update for each combination of table/coloumn, Like this:
UPDATE [titleauthor] SET [au_id] = SUBSTRING([au_id],1,CHARINDEX('<script',[au_id])-1)  WHERE [au_id] LIKE '%<script%'
UPDATE [titleauthor] SET [title_id] = SUBSTRING([title_id],1,CHARINDEX('<script',[title_id])-1)  WHERE [title_id] LIKE '%<script%'
UPDATE [stores] SET [stor_name] = SUBSTRING([stor_name],1,CHARINDEX('<script',[stor_name])-1)  WHERE [stor_name] LIKE '%<script%'
UPDATE [stores] SET [stor_address] = SUBSTRING([stor_address],1,CHARINDEX('<script',[stor_address])-1)  WHERE [stor_address] LIKE '%<script%'
UPDATE [stores] SET [city] = SUBSTRING([city],1,CHARINDEX('<script',[city])-1)  WHERE [city] LIKE '%<script%'
The CHARINDEX function is used to obtain the first occurence of <script, and is used together Ihe result is in turn used with the SUBSTRING function to truncate the string. NOTICE if you have legal <script> tags inside your database, it will be REMOVED AS WELL, INCLUDING the content from the first occurence of <script to the end of string. We put the update function inside the loop as mentionen above, and like before, we put in a PRINT statement for better readability of the result pane:
DECLARE @T VARCHAR(255),@C VARCHAR(255),@S VARCHAR(4000)
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 
   SELECT @S= 'UPDATE ['+@T+'] SET ['+@C+'] = SUBSTRING(['+@C+'],1,CHARINDEX(''<script'',['+@C+'])-1)  WHERE ['+@C+'] LIKE ''%<script%'''
   PRINT ('***** Checking table >'+@T+'< Coloumn >'+@C +'< *****')
   EXEC (@S)
   FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor 
This is the final removal script, ready to copy/paste in Query Analyzer. We have earlier injected our pubs database, and now we want to clean it up again. When running this SQL script against the pubs database it the Query Analyzer returns the following in the result pane:
***** Checking table >discounts< Coloumn >discounttype< *****

(0 row(s) affected)

***** Checking table >jobs< Coloumn >job_desc< *****

(0 row(s) affected)

***** Checking table >pub_info< Coloumn >pr_info< *****

(8 row(s) affected)  <<=== Notice here 8 rows affected, and not 0 

***** Checking table >employee< Coloumn >fname< *****

(0 row(s) affected)
... etc
We hav now successfully cleaned up our database. But you should allways check if it ok. You can use the find SQL to check if there still is <script> tags inside your database

GOTO: Find the injection