We help IT Professionals succeed at work.

Search and Replace in all Tables within a Database

I need a script SQL or ASP that will do a Search and Replace in all of the tables in my Database. The Database belongs to a MCMS installation that has been infected by an SQL Injection. I have been using:

UPDATE    MyTABLE
SET              MyROW= REPLACE(MyROW, '<script src=http://ww.dodgyScript.us/u.js></script>', '')
WHERE     (MyROW LIKE '%<script src=http://ww.dodgyScript.us/u.js></script>%')

The above works fine but needs to by typed in once per column.

You're help is much appreciated.
Comment
Watch Question

Commented:
I don't have access to MS SQL server right now but I got the following article.

http://vyaskn.tripod.com/sql_server_search_and_replace.htm

Hope that helps :)

Commented:
And here's the code from the article
Commented:
Sorry, here's the code :)
CREATE PROC SearchAndReplace
(
	@SearchStr nvarchar(100),
	@ReplaceStr nvarchar(100)
)
AS
BEGIN

	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string and replace it with another string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 2nd November 2002 13:50 GMT

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
	SET @RCTR = 0

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				SET @SQL=	'UPDATE ' + @TableName + 
						' SET ' + @ColumnName 
						+ ' =  REPLACE(' + @ColumnName + ', ' 
						+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') + 
						') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				EXEC (@SQL)
				SET @RCTR = @RCTR + @@ROWCOUNT
			END
		END	
	END

	SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

Open in new window

Run the below CODE

declare @tablename varchar(50),@columnname varchar(50)
declare @sql varchar(1000)
declare cur cursor for select a.name,b.name from sys.tables a inner join sys.columns b on a.object_id=b.object_id
open cur
FETCH NEXT FROM cur INTO @tablename,@columnname
WHILE @@FETCH_STATUS=0
BEGIN
      select * from sys.columns
      set @sql='update '+@tablename+' set '+@columnname+'=replace('+@columnname+',''<script src=http://ww.dodgyScript.us/u.js></script>'','''')'
      PRINT @SQL
      EXECUTE(@sql)
      FETCH NEXT FROM cur INTO @tablename      ,@columnname
END
CLOSE cur
DEALLOCATE cur