Script - Find Text in Database

Find and replace text in database

The following procedure is a very useful stored procedure that is my first port of call when I am tasked to investigate sites that have fallen victim to SQL injection attacks. The script has 4 different methods which are outlined in the comments. However I would first use method 1 which will output a list of any tables and columns within the database and the number of rows that contain the offending hack string. From that data you can then decide whether you need to run the other methods that either output all UPDATE statements needed to remove the hack or run them straight off. The other method outputs every single affected row within the database which is useful as you can determine whether a clean will work or not by the placement of the injected code.


Script Details

I have created 2 versions of the proc one for SQL 2005 and one for 2000/7. The only real differences are that I can use NVARCHAR(max) in the 2005 version and the system views are slightly different.
You could choose to update the 2005 version to use the CLR and a regular expression UDF to speed up the text searches.

Download SQL 2005 Version

Download SQL 2000 Version


Searching and replacing multiple strings

I have also created another script that allows you to search for and if necessary replace multiple strings in one go. Some sites are charging $300 for code like this and you may have even seen the adverts on this site :). So if I have saved you or your company some money then please feel free to make a donation!.

Download code to search and replace for multiple strings

I have added a branch within this proc that checks the version of SQL server and calls the appropriate proc. However you are probably only going to want to use the procedure that your server supports so comment out or remove the following code:

IF patindex('%SQL Server 2005%',@@Version)>0
 SELECT @SQL_VERSION = 2005
ELSE IF patindex('%SQL Server  2000%',@@Version)>0
 SELECT @SQL_VERSION = 2000

And also further down within the loop remove the call to the proc that your not using.

IF @SQL_VERSION = 2005
 BEGIN

       EXEC dbo.usp_sql_find_text_in_database
         @Mode,
         @Value,
         @ReplaceString
 END
ELSE
 BEGIN

       EXEC dbo.usp_sql_find_text_in_database_2000
         @Mode,
         @Value,
         @ReplaceString
 END

Example Usage
So you need to hunt down and remove the following injected hack strings:

<script src="http://www.usaadp.com/ngg.js"></script>
<script src="http://www.bnsdrv.com/ngg.js"></script>
<script src="http://www.cdport.eu/ngg.js"></script>

Just call the usp_sql_find_multiple_text_in_database proc in the following way

EXEC dbo.usp_sql_find_multiple_text_in_database
@MODE = 4,
@FindString = '<script src="http://www.usaadp.com/ngg.js"></script>||<script src="http://www.bnsdrv.com/ngg.js"></script>||<script src="http://www.cdport.eu/ngg.js"></script>',
@SplitOn = '||',
@ReplaceString = ''

Which will hunt for each string in turn in all textual columns (char,nchar,nvarchar,varchar,ntext,text) and replace it with an empty string.

If you don't want to carry the UPDATE out straight away you could use a different option by changing the @MODE flag:

1 = Output an overview list of each table and column containing the string and the no of rows found for each. This is a good way of checking how much data has been corrupted.

2 = Output all the rows containing the string. This may be quite a lot if your whole database has been comprimised.

3 = Output the update statements needed to remove the string.

4 = Find and replace all occurrances of the string.


So there you go a way to clean up your infected SQL databases and save yourself $299 at the same time. As I am saving you some money buying a clean up product and possibly lots of money due to lost business revenue then please consider making a donation so that I can continue publishing scripts like this for free.