Search all Tables for a String
Applicability:
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: ChillyDBA
Date: 2 Dec 2011
Description
The last thing I ever want to do to a database is to have to search through all the text in it.
However, I've needed to do this 3 times now, and each time the reason has been a SQL Injection attack.
Despite the intent (malicious or mischievous), these injection attacks generally excel at injecting a text string (normally a URL) wherever they find a place.
Unless you are particularly lucky and have this occur to a non-production database (in which case one might question why your DEV environments are open to the world), the time to discovery usually means restoring to a previous copy of a production database is likely not to be an option due to the volume of changes that would need to be discarded.
This leaves repair as the only option, which means a long search through the database.
The nature of the search doesn't really lend itself to efficiency - looking at every row in every table is always going to hurt - so the code that I wrote to perform this task is not the prettiest or most efficient. However, it does get the job done, and records all occurrences of the string in an audit table.
I haven't included any code to remove the string, as this is very much dependant on the SQL Server, the database concerned and operational constraints.
I also created a reduced functionality stored procedure which only operates on a single table and does not record any results.
Code
Stored Procedure to search all tables and table in which to record results:
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
Stored Procedure to search a single table (does not record results):
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