July 22, 2009 at 12:26 pm
As I noted in another thread, my DB got hit with SQL injection through our company website. Obviously the long-term solution is to put injection-prevention code in the site. But for now, I need to find those places in the database where some of the malicious code got placed. As it turns out, they appended our existing data with HTML script tags with a reference to a javascript.
I found several fields where this occurred and cleaned it out, but I would like to search the entire database for this particular string. I can't do a SELECT * FROM * WHERE * = "whatever" So how do you do a DB-wide search for a given string?
July 22, 2009 at 12:32 pm
Here is an article with some examples that should help.
http://www.mssqltips.com/tip.asp?tip=1525
You will specifically want to look at the last code sample.
July 22, 2009 at 12:34 pm
Without writing a bunch of code myself I can present a framework of an idea..
You could use the information schema views to get the table names and column names for each table in the database. You would filter out all the numeric fields for search, since the code can't be stored ther. You could then use whats left to build a dynamic SQL statement that searches each field in each table for some snippet of the offending code and outputs some indicator. I would probably not use the whole string, but the beginning of it or a unique string in it. The reason I recommend that is that because of your data types it may have gotten truncated and I think you are trying to find all traces of it.
Depending on the size of your database this can be quite intensive, but the script should be able to do most of the detection work..
CEWII
July 22, 2009 at 12:39 pm
Ken Simmons (7/22/2009)
Here is an article with some examples that should help.http://www.mssqltips.com/tip.asp?tip=1525
You will specifically want to look at the last code sample.
Aha.. Same idea, but with code!
CEWII
July 22, 2009 at 1:03 pm
thanks, guys!
July 22, 2009 at 2:48 pm
I think i will go with http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
July 22, 2009 at 3:41 pm
Here is the code to search an entire database for keywords. This should do the trick.
I know... stupid variable names, I was bored one morning. š
Michelle
SET NOCOUNT ON
DECLARE @Search varchar(100)
SET @Search = 'enter search criteria here'
--Iām searching for cable in the above line
SELECT Sharpie = TABLE_NAME
INTO Marker
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
SELECT Sharpie, COLUMN_NAME AS Highlighter
INTO Tomo
FROM Marker JOIN INFORMATION_SCHEMA.COLUMNS
ON TABLE_NAME = Sharpie
AND DATA_TYPE LIKE '%char'
CREATE TABLE Yellow (Red varchar(1000))
DECLARE @Coffee varchar(1000)
SET @Coffee =
'INSERT INTO Yellow SELECT TOP 1 Red=''Found ' +
REPLACE(@Search,'''','''''') + ' in SHARPIE.HIGHLIGHTER'' FROM SHARPIE WHERE ' +
'HIGHLIGHTER LIKE ' + QUOTENAME('%'+@Search+'%','''')
--'HIGHLIGHTER = ' + QUOTENAME(@Search,'''')
/*if you want to find the string as a substring of a column value, change the above line to
'HIGHLIGHTER LIKE ' + QUOTENAME('%'+@Search+'%','''') */
DECLARE @Work varchar(1000)
DECLARE @Sharpie sysname
DECLARE @Highlighter sysname
DECLARE Project CURSOR FOR
SELECT Highlighter, Sharpie FROM Tomo
WHERE Sharpie 'Marker'
OPEN Project
DECLARE @Region int
FETCH NEXT FROM Project INTO @Highlighter,@Sharpie
WHILE @@FETCH_STATUS = 0 BEGIN
SET @Work =
REPLACE(REPLACE(@Coffee,'HIGHLIGHTER',quotename(@Highlighter)),
'SHARPIE',quotename(@Sharpie))
exec (@Work)
SELECT @Region = COUNT(Red) FROM Yellow
FETCH NEXT FROM Project INTO @Highlighter,@Sharpie
END
IF @Region IS NULL
PRINT '['+@Search+'] not found'
ELSE
SELECT Red FROM Yellow
DEALLOCATE Project
go
DROP TABLE Marker
DROP TABLE Tomo
DROP TABLE Yellow
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply