Thursday 4 September 2008

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.






11 comments:

Anonymous said...

Great script! I was going to have to persuade my boss to fork out $299 for one of those Injection cleaner scripts but then I found this. Looks like it does the same thing for free. Thanks a lot you saved me a lot of grief.

Anonymous said...

A site our company runs got hacked this morning with one of the URLS you mention in the previous article. Thanks to this script I managed to remove all 2421 occurrances of the injected SCRIPT tag from our database in minutes. Keep up the good work.

ultimateuss said...

Hi Mate,

Excellent and informative forum. Thanks.

A couple of things that would be appreciated if you have a chance to answer.

1. In each of the 2 scripts you have posted the last line...

##############
IF @DEBUG = 1
PRINT 'LOOP WHILST ' + CAST(@RowNo as varchar)+
##############

is one long line, over 1300 characters long when I copy and paste from your page. I am assuming this is wrong or does it not matter when using in the script editor.

Perhaps a link with the test files correctly formatted would be possible. My browser must have an issue with the text box on the forum page.

2. These scripts appear great for finding a singular occurrence of an offending url reference. The bigger issue for me is that we have been hit multiple times, I have multiple entries within the same fields.

ie

"Legitimate data"(bad Script01)(bad Script02)(bad Script03)etc

Would it be possible for you to modify include in the scripts an option to either read from a text file or separate procedure the multiple search strings/domains.

I would list the domain references I have found and wish to search for in other databases/tables.

The harder I look the more domain references I have found... I have a number of databases with lots of tables.

Most of the mssql db I manage are 2000 era including procedures and page code. Not viable to repair as such...

I am currently hardening the servers to plug holes and apply defensive barriers.

When possible a major rewrite or upgrade will have to be completed.

8 years without successful hacking until this..., so I am grateful for small mercies.

Thanks for your time and I appreciate any thoughts.

Have a nice day.

KENT

ultimateuss said...

Hi Mate,

Me again.

Does the script Search/Replace in the TEXT fields as well.

Thanks again.

Rob Reid said...

Hi Ultimate
I will sort that formatting issue out and post a link to the scripts in a file so that you can view it correctly when I get to work today.
As for the replace it will search and replace all text fields (nvarchar,nchar,ntext,text,char,varchar) within a database.
I will also modify the script so that you can supply multiple search terms. By the way are you using the SQL 2000 or 2005 script?

Rob Reid said...

Hi Ultimate

I have updated the article to contain links to text file versions of both procs.

I have also created a wrapper proc that allows you to search/replace as many different strings as you need to in one go.

See the examples in the article and also in the downloadable source code.

Hope it helps!

ultimateuss said...

Hi Mate,

Thanks for the scripts and help so far. A couple of points that may be worth looking and would really make a difference if you have time.

Note < changed to (

Point 1
The multiple search critera

ie (bad script01)||(bad script02)||(bad script03)

works well, but would be far more effective if it can be read from a list.. ie
(bad script01)
(bad script02)
(bad script03)
etc

and maybe delimited by a CRLF, so a cut/paste of say your list of "Latest SQL Injection URLS" (with complete (script...) tags) could be used so instead of trying to find individual domain injection URL's first to search for etc.

if it were possible, as in one db alone, I have many separate occurrences of the domain variations.

Your great script could then iterate through the list and report results/changes as now.

What is left can be further cleaned up manually...

Point 2

is that due to data field length restrictions on some of the effected fields, the injected strings have different levels of truncation.

This causes the strings to be incomplete, Hence looking for the complete string is not then possible.

Perhaps an automated way via iteration that can start with the complete Injection string.

(script src=http://www.destbnp.com/ngg.js__/script)

then loop until the full string minus 1 character then minus 2 characters then 3 character etc

until a nominated minimum string length ie "(script src=http://www.des" is reached.

The minimum string length will stop the premature removal of the beginning of a string which is common to all the search strings ie http"//www.

To finish I have struck a small glitch in the OPTION 2 of the Search/Replace which returns the following...

IN usp_sql_find_multiple_text_in_database
MODE = 2
FindString = (script src=http://www.bkpadd.mobi/ngg.js)||(script src=http://www.bkpadd.mobi/ngg.js||(script src=http://www.bkpadd.mobi/ngg.j
ReplaceString =
RUN FIND / REPLACE PROC FOR SQL VERSION = 2000
CALL usp_sql_find_text_in_database VALUE = (script src=http://www.bkpadd.mobi/ngg.js)
Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type text to data type nvarchar, table 'tempdb.dbo.#TEMP_______________________________________________________________________________________________________________000200001634', column 'Value'. Use the CONVERT function to run this query.
Msg 260, Level 16, State 1, Line 1

The line that begins with

Disallowed implicit conversion...

is repeated many times. Not sure how to overcome that one??

Thanks again for your help.

Kind Regards

KENT

AxelGunn said...

I have two servers, one v.8.0 and one v.10.
I ran the 2005 script on the version 8 and got the following error:

Msg 170, Level 15, State 1, Procedure usp_sql_find_text_in_database, Line 99
Line 99: Incorrect syntax near 'MAX'.

hope you can help?

Rob Reid said...

Hi Alex It would seem you are trying to run the wrong script on the wrong server. NVARCHAR(max) is not supported in SQL 2000 so you need to run the 2k script for that server and if you have a server running 2005 then you can use the other script.

Rob Reid said...

Hi ultimateuss
The scripts are purely an example of database code that can be utilised to clean a DB. You can use any wrapper code you like to pass the required parameters to this "engine". For example If you wanted to input a list then I suggest writing a script, application or webpage that would accept a file loop through the data line by line and then call the find/replace stored proc for each line returning the results which can then be outputted to screen or to a log file. In a similar way for point 2 you just need to write the code that supplies it with the parameters you want to search for. This could be another stored procedure that breaks an initial search string down char by char or a front end application. If I had the time to write a nice GUI interface application to numptify the process up I would but then I would probably be charging £££ like the companies you can see advertising on all these google adverts :)

As for point 3 I have managed to run the 2000 script on a DB that uses text/ntext columns so I am not sure what is going on there.

If you can provide details of the table that its erroring on so I can see the structure of the table then I might be able to work out what the problem is.

Thanks

Fraser said...

Just wanted to add my thanks too!