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
