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

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