Search all Tables for a String

DROP TABLE dbo.InjectionCheckResults

GO

CREATE TABLE dbo.InjectionCheckResults

(

DatabaseName SYSNAME,

TableName VARCHAR(250),

TableSchema VARCHAR(250),

ColumnName VARCHAR(250),

ColumnText VARCHAR(MAX)

)

--initialize transaction

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

--initial declarations

DECLARE

@RowID INT,

@MaxRowID INT,

@SQL_Command NVARCHAR(MAX),

@SearchValue VARCHAR(100)

DECLARE @SQL_CommandStatements TABLE

(

RowID INT,

SQL_Command VARCHAR(MAX)

)

CREATE TABLE #TablesAndColumns

(

TableName SYSNAME,

TableSchema SYSNAME,

ColumnName SYSNAME

)

-- specify the search string

-- chances are that it will look something like the default value supplied here

SET @SearchValue = '></title><script src="http://'

SET @RowID = 1

-- retrieve all tables and columns with searchable text-based columns

-- ie those coluns that could have been affected by the injection attack

INSERT #TablesAndColumns

(

TableName,

TableSchema,

ColumnName

)

SELECT DISTINCT

so.name AS table_name,

schema_name(so.schema_id) AS table_schema,

sc.name AS column_name

FROM sys.objects so

INNER JOIN sys.columns sc

ON so.OBJECT_ID = sc.OBJECT_ID

INNER JOIN sys.types st

ON sc.system_type_id = st.system_type_id

AND sc.user_type_id = st.user_type_id

INNER JOIN sys.types st2

ON st.system_type_id = st2.system_type_id

AND so.TYPE = 'u'

AND st2.name NOT IN ('ntext','image','text','timestamp','binary','uniqueidentifier','xml')

AND st.name NOT IN ('ntext','image','text','timestamp','binary','uniqueidentifier','xml')

--create search command statements

INSERT INTO @SQL_CommandStatements

SELECT

ROW_NUMBER() OVER (ORDER BY TableName, ColumnName) AS RowID,

'insert into dbo.InjectionCheckResults select distinct '''

+ DB_NAME()

+ ''', '''

+ TableName

+ ''', '''

+ TableSchema

+ ''', '''

+ ColumnName

+ ''', ['

+ ColumnName

+ '] from ['

+ TableSchema

+ '].['

+ TableName

+ '](nolock) where convert(varchar,['

+ ColumnName

+ ']) like ''%'

+ @SearchValue

+ '%''' FROM #TablesAndColumns

--initialize the loop and process ommand statements

SELECT @MaxRowID = MAX(RowID)

FROM @SQL_CommandStatements

WHILE @RowID <= @MaxRowID

BEGIN

SELECT @SQL_Command = SQL_Command

FROM @SQL_CommandStatements

WHERE RowID = @RowID

--EXEC sp_executeSQL @SQL_Command

SET @RowID = @RowID + 1

END

-- output a list of the search command strings

SELECT SQL_Command FROM @SQL_CommandStatements ORDER BY RowID

--SELECT * FROM dbo.InjectionCheckResults

DROP TABLE #TablesAndColumns

DROP PROCEDURE p_FindStringInTable

GO

CREATE PROCEDURE p_FindStringInTable @TableName VARCHAR(100),

@SearchString VARCHAR(100)

AS

/****************************************************

Purpose: To locate all occurences of the specified string in the

specified table (text-based columns only)

Output is purely a count of the number of rows that have text-based

columns containing the specified string

No indication of which column or row is produced.

Author: ChillyDBA

History: 16 Apr 08 - Initial Issue

****************************************************/

SET NOCOUNT ON

DECLARE @Columns TABLE

(

ColID INT IDENTITY(1,1),

ColText VARCHAR(200)

)

DECLARE @Counter INT,

@MaxCounter INT,

@Cmd VARCHAR(8000),

@ShortTableName VARCHAR(100)

SELECT @SearchString = '%' + @SearchString + '%'

-- remove schema and [] from the supplied table name ready to match.

-- NOTE: I have since realised that the OBJECT_ID(<TableName>) accepts a 2-part bracketed table name

-- so that the WHERE clause could be ID = OBJECT_ID(@ShortTableName) thus removing the need for the following 3 statements,

-- but this SP works and isn't a high-concurrency OLTP SP, so I left it as it was.

SELECT @ShortTableName = SUBSTRING(@TableName, CHARINDEX('.', @TableName) + 1, 100)

SELECT @ShortTableName = REPLACE (@ShortTableName, ']','')

SELECT @ShortTableName = REPLACE (@ShortTableName, '[','')

INSERT @Columns (ColText)

SELECT sc.Name FROM syscolumns sc (NOLOCK)

INNER JOIN SysTypes st (NOLOCK) ON sc.xusertype = st.xusertype

WHERE OBJECT_NAME(ID) = @ShortTableName

AND st.Name IN

(

'text',

'ntext',

'varchar',

'char',

'nvarchar',

'nchar',

'xml',

'sysname'

)

ORDER BY ColID

SELECT @MaxCounter = @@ROWCOUNT

IF @MaxCounter > 0

BEGIN

SELECT @Cmd = 'SELECT COUNT(*) AS Occurrences, ''' + @TableName + ''' AS Tablename FROM ' + @TableName + '(NOLOCK)' + CHAR(13)

SELECT @Counter = MIN(ColID)

FROM @Columns

WHILE @Counter IS NOT NULL

BEGIN

SELECT @Cmd = @Cmd

+ CASE WHEN @Counter = 1 THEN 'WHERE [' ELSE 'OR [' END

+ ColText

+ '] LIKE '''

+ @SearchString

+ ''''

+ CHAR(13)

FROM @Columns

WHERE ColID = @Counter

SELECT @Counter = MIN(ColID)

FROM @Columns

WHERE ColID > @Counter

END

--select @cmd

EXEC (@CMD)

END

SET NOCOUNT OFF