更实用的清马SQL语句
[重要通告]如您遇疑难杂症,本站支持知识付费业务,扫右边二维码加博主微信,可节省您宝贵时间哦!
替换数据库内被挂马的字段内容
update 表名 set 字段名=replace(字段名,'木马字符串','') where id=10
---------------------------
SQL Server 企业管理器
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]函数 replace 的参数 1 的数据类型 ntext 无效。
---------------------------
确定 帮助
---------------------------
出现以上错误的时候可以用以下语句进行替换!
update city set city=Replace(Cast(city as varchar(8000)),'','')
要替换整个库内所有表所有列的马还有更好的办法!
方法如下:
1:sp_msforeachtable 用来loop表中的所有列
2:更新类型为ntext,text类型的列时,先判断DATALENGTH(Column)是否大于8000字节,如果小于8000字节的话,我们可以使用
update Table set Column=Replace(Cast(Column as varchar(8000)),'oldkeyword','newkeyword')来更新。
源码如下:
使用方法:在当前数据库使用查询分析器建立两个存储过程,然后执行下面的命令即可!
存储过程一:UpdateTextColumn
---------------------------------------------------------------------------------------------------
Create proc [dbo].[UpdateTextColumn]
@Table varchar(100),
@Columns varchar(200),--eg:Column1,Column2,
@old varchar(100),
@new varchar(100)
as
set nocount on
declare @sql nvarchar(2000)
declare @Column varchar(50)
declare @cpos int,@npos int
set @cpos=1;
set @npos=1;
set @npos=charindex(',',@Columns,@cpos);
while(@npos>0)
begin
set @Column = substring(@Columns,@cpos,@npos-@cpos);
set @cpos = @npos+1
set @npos=charindex(',',@Columns,@cpos);
set @sql = 'update '+@Table+' set '+@Column+'=replace(cast('+@Column+' as varchar(8000)),@old,@new) where Datalength('+@Column+')<=8000';
EXECUTE sp_executesql @Sql,
N'@old varchar(100),@new varchar(100)',
@old,
@new
declare @ptr binary(16) ,@offset int,@dellen int
set @dellen = len(@old)
set @offset = 1
while @offset>=1
begin
set @offset = 0
set @sql = 'select top 1 @offset = charindex('''+@old+''' , '+@Column+'), @ptr = textptr('+@Column+') from '+@Table+' where Datalength('+@Column+')>8000 and '+@Column+' like ''%'+@old+'%''';
EXEC sp_executesql @Sql,N'@offset int OUTPUT,@ptr binary(16) OUTPUT,@old varchar(100)',
@offset OUTPUT,@ptr OUTPUT,@old;
if @offset > 0
begin
set @offset = @offset-1
set @sql='updatetext '+@Table+'.'+@Column+' @ptr @offset @dellen @new';
EXEC sp_executesql @Sql,N'@offset int ,@ptr binary(16),@dellen int,@new varchar(100)',@offset,@ptr,@dellen,@new;
end
end
end
go
---------------------------------------------------------------------------------------------------
存储过程二:ReplaceKeyWord
---------------------------------------------------------------------------------------------------
Create proc [dbo].[ReplaceKeyWord]
@old nvarchar(100),
@new nvarchar(100)
as
declare @sql nvarchar(1000)
set @sql=N'
declare @s nvarchar(4000),@tbname sysname
select @s=N'''',@tbname=N''?''
select @s=@s+N'',''+quotename(a.name)+N''=replace(''+quotename(a.name)+N'',N'''''+@old+''''',N'''''+@new+''''')''
from syscolumns a,systypes b
where a.id=object_id(@tbname)
and a.xusertype=b.xusertype
and b.name like N''%char''
if @@rowcount>0
begin
set @s=stuff(@s,1,1,N'''')
exec(N''update ''+@tbname+'' set ''+@s)
end '
--print @sql
exec sp_msforeachtable @sql;
set @sql=N'
declare @s nvarchar(4000),@tbname sysname
select @s=N'''',@tbname=N''?''
select @s=@s+quotename(a.name)+N'',''
from syscolumns a,systypes b
where a.id=object_id(@tbname)
and a.xusertype=b.xusertype
and b.name like N''%text''
if @@rowcount>0
begin
exec UpdateTextColumn @tbname,@s,'''+@old+''','''+@new+'''
end
' ;
exec sp_msforeachtable @sql
go
---------------------------------------------------------------------------------------------------
使用方法如下:Exec ReplaceKeyWord 'www.aaa.com','www.bbb.cn'
问题未解决?付费解决问题加Q或微信 2589053300 (即Q号又微信号)右上方扫一扫可加博主微信
所写所说,是心之所感,思之所悟,行之所得;文当无敷衍,落笔求简洁。 以所舍,求所获;有所依,方所成!