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.
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.
And here's the code from the article
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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('+
PRINT @SQL
EXECUTE(@sql)
FETCH NEXT FROM cur INTO @tablename ,@columnname
END
CLOSE cur
DEALLOCATE cur
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('+
PRINT @SQL
EXECUTE(@sql)
FETCH NEXT FROM cur INTO @tablename ,@columnname
END
CLOSE cur
DEALLOCATE cur
http://vyaskn.tripod.com/sql_server_search_and_replace.htm
Hope that helps :)