Cool Tips‎ > ‎Data Searching‎ > ‎

Data Searching - 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



ċ
Search a table for a specified text string - generic usage.sql
(2k)
Andy Hughes,
Jun 18, 2012, 7:47 AM
ċ
Search all tables for a specified text string - recover from injection attack.sql
(3k)
Andy Hughes,
Jun 15, 2012, 12:07 PM
Comments