In the past, CSS had a number of support calls regarding SQL Injection. Nowadays, people are much more aware of how to secure their front-end and back-end systems so that they do not fall prey to malicious attackers and become victims of issues like SQL Injection. Now let’s take a scenario where you have identified a malicious string in your database table. You are not sure how many more table(s)/column(s) can have this kind of malicious string. Searching this would be a daunting task. You can use the script below to run a search on all tables columns that have character data and look for the malicious string pattern that you provide the @string variable. This search would be a very performance intensive search as this would take a long time to execute as it is combing through all character columns in the entire database. So, here’s the warning that you have heard multiple times 🙂 : DO NOT RUN THIS DURING PRODUCTION HOURS. However, if you have a case of SQL Injection, then production should definitely be halted till you have plugged the hole in the security.
set nocount on DECLARE @T varchar(255), @C varchar(255),@string varchar(255); SET @string = '<script' -- Malicious value to be searched for 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 --ntext b.xtype = 35 OR -- text b.xtype = 231 OR -- nvarchar b.xtype = 175 OR -- char b.xtype = 167) -- varchar declare @sql varchar(8000) OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @T, @C WHILE (@@FETCH_STATUS = 0) BEGIN select 'Table Name:'+@T EXEC('select * from [' + @T + '] where [' + @C + '] ' + 'like ''%'+@string+'%'' OR ' + @C + ' like ''%EXEC %''' ) FETCH NEXT FROM Table_Cursor INTO @T, @C; END CLOSE Table_Cursor; DEALLOCATE Table_Cursor; set nocount off