How to search for malicious strings in your database


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

Advertisements

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s