数据库被插入代码的清理

[wangkiyo]

想用SQL实现自动搜索数据库所有表的所有 varchar/nvarchar/text/ntext 等文本字段,并查找出带有<script 或者是http内容的字段,然后根据实际的内容,如:<script src='http://xxx.xxx.xxx'></script>,对字段进行替换清空,估计是要分2步走,之前有找到一个保存下来,不过这次想用的时候找不到了.
麻烦大家帮忙一下.

[qgqch2008]

参考，查询出来的每条sql语句，要使用游标逐行执行就行

SELECT  'select ' + c.COLUMN_NAME + ' from ' + c.TABLE_NAME
        + ' where charindex(''<script'',''' + c.COLUMN_NAME
        + ''')>0 or charindex(''http'',''' + c.COLUMN_NAME + ''')>0'
FROM    INFORMATION_SCHEMA.TABLES t
        LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE   c.DATA_TYPE IN ( 'varchar', 'nvarchar', 'text', 'ntext' )
        AND t.TABLE_TYPE = 'BASE TABLE'

[怪众生太美丽]

--不知对你可有用：
--代码来自CSDN
use tempdb
go
create FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END
GO

declare @news table 
(id int,content varchar(80),nohtm varchar(150))
insert into @news
select 1,'<a href="www.baidu.com">图片新闻</a>',null union all
select 2,'<p>dizhi</p><a href="www.baidu.com">图片新闻</a>',null union all
select 3,'<a href="www.baidu.com">图片新闻1</a>',null union all
select 4,'<a href="www.baidu.com">图片新闻2</a>',null union all
select 5,'<span><a href="www.baidu.com">图片新闻</a><span><br/>',null union all
select 6,'<div><a href="www.baidu.com">图片新闻</a></div>',null union all
select 7,'图片新闻<222新闻<>',null

select * from @news 
/*
1    <a href="www.baidu.com">图片新闻</a>    NULL
2    <p>dizhi</p><a href="www.baidu.com">图片新闻</a>    NULL
3    <a href="www.baidu.com">图片新闻1</a>    NULL
4    <a href="www.baidu.com">图片新闻2</a>    NULL
5    <span><a href="www.baidu.com">图片新闻</a><span><br/>    NULL
6    <div><a href="www.baidu.com">图片新闻</a></div>    NULL
7    图片新闻<222新闻<>    NULL
*/
update @news set nohtm = dbo.[udf_StripHTML](content)--dbo.clearhtml(content)
select id,nohtm from @news
/*
1    图片新闻
2    dizhi图片新闻
3    图片新闻1
4    图片新闻2
5    图片新闻
6    图片新闻
7    图片新闻
*/ 

[dawugui]

数据库被注入攻击 所有文本型字下段数据都被加了 <script_src=http://ucmal.com/0.js> </script>
怎么删掉？

--sql 2000解决方法
DECLARE @fieldtype sysname
SET @fieldtype='varchar'

--删除处理
DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT N'update '+QUOTENAME(o.name)
    +N' set  '+ QUOTENAME(c.name) + N' = replace(' + QUOTENAME(c.name) + ',''<script_src=http://ucmal.com/0.js> </script>'','''')'
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id 
    AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
    AND c.xusertype=t.xusertype
    AND t.name=@fieldtype
EXEC sp_MSforeach_Worker @command1=N'?'

--sql 2005 解决方法1
declare  @t  varchar(255),@c  varchar(255)  
declare  table_cursor  cursor  for  
select  a.name,b.name  from  sysobjects  a,syscolumns  b  
where  a.iD=b.iD  AnD  a.xtype='u'  
AnD  (b.xtype=99  or  b.xtype=35  or  b.xtype=231  or  b.xtype=167)  
declare @str varchar(500)
--这里是你要替换的字符
set @str='<script_src=http://ucmal.com/0.js> </script>'
open  table_cursor  fetch  next  from  table_cursor  
into  @t,@c  while(@@fetch_status=0)  
begin      
    exec('update  [' + @t + ']  set  [' + @c + ']=replace(cast([' + @c + '] as varchar(8000)),'''+@str+''','''')')      
    fetch  next  from  table_cursor  into  @t,@c  
end  
close  table_cursor  deallocate  table_cursor; 

--sql 2005 解决方法2
declare @sql varchar(max)
set @sql='
declare @sql varchar(max)
set @sql=''update  ? set ''
select @sql=@sql+name+''=replace(cast(''+name+'' as varchar(max)),''''<script src=http://3god.ne%54/c.js> </script>'''',''''''''),''
from syscolumns where id=object_id(''?'')
and xtype in (35,99,167,175,231,239)
set @sql=left(@sql,len(@sql)-1)+'' from ?''
exec(@sql)
'