SQL Server with Mr. Denny

Oct 1 2008   11:00AM GMT

T/SQL Code to remove SQL Injection Values from your tables

Denny Cherry Denny Cherry Profile: Denny Cherry

With SQL Injection Attacks being all the rage these days, I’ve been asked a couple of times for T/SQL code to clean up the database.

So I threw this code together to clean up the data. This code will clean all the character and uni-code columns in all the user defined tables in the system. You’ll need to be dbo or sysadmin to run this without error. If you have TEXT or NTEXT columns it will through an error for those columns. Cleaning TEXT and NTEXT columns is a little more complex as you can’t use the REPLACE function on a TEXT or NTEXT datatype.


DECLARE @sql NVARCHAR(4000)
DECLARE @InsertedValue NVARCHAR(1000)
SET @InsertedValue = 'The Script tags which were inserted'
DECLARE cur CURSOR FOR
  	select 'update [' + sysusers.name + '].[' + sysobjects.name + ']
  		set [' + syscolumns.name + '] = replace([' + syscolumns.name + '], ''' + @InsertedValue + ''', '''')'
  	from syscolumns
  	join sysobjects on syscolumns.id = sysobjects.id
  		and sysobjects.xtype = 'U'
  	join sysusers on sysobjects.uid = sysusers.uid
  	where syscolumns.xtype in (35, 98, 99, 167, 175, 231, 239, 241, 231)
  OPEN cur
  FETCH NEXT FROM cur INTO @sql
  WHILE @@FETCH_STATUS = 0
  BEGIN
  	exec (@sql)
  	FETCH NEXT FROM cur INTO @sql
  END
  CLOSE cur
  DEALLOCATE cur

Hopefully you find this useful. If you need code for TEXT or NTEXT columns just post a comment and I’ll throw something together.

This code will work on SQL 2000 and up (it’ll probably work on SQL 7 as well, but I don’t have a SQL 7 machine to test against).

Denny

9  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Weyes
    Hi. It's a great help for me. And I need code for TEXT or NTEXT colums
    0 pointsBadges:
    report
  • Jeffmace
    I REALLY need help with the ntext fields and sql injection removal of a javascript line entered into THOUSANDS of records. Please try to help as fast as you can. Thank you!!!!!!!!!!!
    0 pointsBadges:
    report
  • gshutch
    Two questions: - can the script above be modified to just skip the text / Ntext fields and process the other types? - Is there a script that will also fix text / ntext Thanks!
    15 pointsBadges:
    report
  • Denny Cherry
    Gshutch,The script above does skip the text and ntext fields.  The data type IDs are used to force it to skip those.  I don't currently have a script to fix text and ntext fields.  That's on my list of things to write, but sadly it keeps getting pushed down by other things.Denny
    66,115 pointsBadges:
    report
  • testsharif
    Hi, Nice article it helped me a lot, i need code for text and ntext col
    0 pointsBadges:
    report
  • MinYeKo
    You saved my days. Please provide me code for TEXT or NTEXT colums. Thanks
    0 pointsBadges:
    report
  • regemail
    Hi- how exactly does this script work?  How does it know what records contain SQL injection in order to replace/delete them?

    Thank you!
    20 pointsBadges:
    report
  • Denny Cherry

    regemail,

    The script generates T-SQL code which updates every value in every column looking for whatever value you pass into the @InsertedValue parameter and simply removes that part of the value.  You'll need to look at your data to see what the bad value is.

    66,115 pointsBadges:
    report
  • regemail
    Brilliant- works wonders- thx for your super-fast reply!
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: