Use this script to identify the tables and fields that may have been injected with problem data.
The active search string is a test to show that it works.
Terms of Agreement:
By using this article, you agree to the following terms...
You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this article from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
-- Find All Injected Fields In A Database
DECLARE @Tbl VARCHAR(255), @aID INT, @Field VARCHAR(400)
DECLARE @SelectClause VARCHAR(2000), @WhereClause VARCHAR(2000)
DECLARE @strComma VARCHAR(3), @strOr VARCHAR(4)
DECLARE @ScriptString VARCHAR(50)
SET NOCOUNT ON
-- The Script Fragment To Search For (Some Examples Of Valid Hits)
SET @ScriptString = 'script'
SET @ScriptString = 'script src=http'
SET @ScriptString = 'http'
DECLARE curTables CURSOR FAST_FORWARD READ_ONLY FOR
-- Get All Of The Tables In The Database
SELECT a.name, a.ID
FROM sysobjects a
WHERE a.xtype='u'
OPEN curTables
FETCH NEXT FROM curTables INTO @Tbl, @aID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SelectClause = ''
SET @WhereClause = ''
SET @strComma = ''
SET @strOr = ''
DECLARE curVars CURSOR FAST_FORWARD READ_ONLY FOR
-- Get All Of The Injectable Character Fields In Each Table
SELECT b.name
FROM syscolumns b
WHERE @aID=b.id
AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)
OPEN curVars
FETCH NEXT FROM curVars INTO @Field
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SelectClause = @SelectClause + @strComma + RTRIM(@Field)
SET @WhereClause = @WhereClause + @strOr + RTRIM(@Field) + ' LIKE ''%' + @ScriptString + '%'''
SET @strComma = ', '
SET @strOr = ' OR '
FETCH NEXT FROM curVars INTO @Field
END
CLOSE curVars
DEALLOCATE curVars
IF LTRIM(RTRIM(@SelectClause)) <> ''
BEGIN
-- Execute The Test
PRINT 'SELECT ' + @SelectClause + ' FROM ' + @Tbl + ' WHERE (' + @WhereClause + ');'
-- Show The Data
-- EXEC('SELECT ' + @SelectClause + ' FROM ' + @Tbl + ' WHERE (' + @WhereClause + ');')
-- Show The Count Of Hits
EXEC('SELECT COUNT(*) ' + @Tbl + ' FROM ' + @Tbl + ' WHERE (' + @WhereClause + ');')
END
FETCH NEXT FROM curTables INTO @Tbl, @aID
END
CLOSE curTables
DEALLOCATE curTables
Works Beautifully Because Of The b.xtype values. And Yes, It won't work unless you use a string specific to the injection that you suspect. (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular article, please
click here instead.)
7/27/2008 9:57:41 PM: 
