删除SQL注入

三种方法:第一种针对单个表 第二种针对单个表字段太多的情况(print输出有字数限制) 第三种针对于单个库(最强模式可直接将库中所有表的所有字符串类型字段全部更新)

第一种:直接导出sql语句
declare
@curTable varchar(500), @FilterStr varchar(500);--过滤字符串 字符串及之后的数据将被替换为空 set @FilterStr=</title><style>.alx2{; set @curTable=users_log_table; DECLARE @SQLList varchar(8000), @tableName VARCHAR(1000), @name VARCHAR(1000), @type VARCHAR(1000); set @SQLList=‘‘; BEGIN -- 定义游标. DECLARE c_test_main CURSOR FAST_FORWARD FOR -- select [dbid],[name] from sysdatabases where dbid>4;--查询数据库名称 select b.name as tableName,a.name as columnName,c.name as columnType from syscolumns a inner join sysobjects b on a.id=b.id inner join systypes c on a.xtype=c.xtype WHERE a.id=OBJECT_ID(N[+@curTable+]) And c.name not like sysname ORDER BY colid -- 打开游标. OPEN c_test_main; WHILE 1=1 BEGIN -- 填充数据. FETCH NEXT FROM c_test_main INTO @tableName,@name,@type; -- 假如未检索到数据,退出循环. IF @@fetch_status!= 0 BREAK; if @type=varchar or @type=nvarchar begin --select @tableName,@name,@type; set @SQLList=@SQLList+;update [+@tableName+] set [+@name+]=replace([+@name+],isnull(substring([+@name+], case charindex(‘‘‘+@FilterStr+‘‘‘,[+@name+]) when 0 then len([+@name+])+1 else charindex(‘‘‘+@FilterStr+‘‘‘,[+@name+]) end ,len([+@name+])),‘‘‘‘),‘‘‘‘); +(char(10)+char(13)); end else if @type=ntext or @type=text begin set @SQLList=@SQLList+;update [+@tableName+] set [+@name+]=cast(replace(cast([+@name+] as varchar(8000)), isnull(substring(cast([+@name+] as varchar(8000)),case charindex(‘‘‘+@FilterStr+‘‘‘,cast([+@name+] as varchar(8000))) when 0 then len(cast([+@name+] as varchar(8000)))+1 else charindex(‘‘‘+@FilterStr+‘‘‘,cast([+@name+] as varchar(8000))) end,len(cast([+@name+] as varchar(8000)))),‘‘‘‘),‘‘‘‘) as ntext ); +(char(10)+char(13)); end END; -- 关闭游标 CLOSE c_test_main; -- 释放游标. DEALLOCATE c_test_main; END print @SQLList;




 

第二种:单条直接导出(防止字段过多 print无法直接输出)
declare @curTable varchar(500),
        @FilterStr varchar(500);--过滤字符串 字符串及之后的数据将被替换为空
set  @FilterStr=</title><style>.ahcw{position:ab;
set  @curTable=Sheet;        
DECLARE 
    @SQLList varchar(8000),
    @tableName VARCHAR(1000),
    @name VARCHAR(1000),
    @type VARCHAR(1000);
set @SQLList=‘‘;
BEGIN
  -- 定义游标.
  DECLARE c_test_main CURSOR FAST_FORWARD FOR
    -- select [dbid],[name] from sysdatabases where dbid>4;--查询数据库名称
    select b.name as tableName,a.name as columnName,c.name as columnType from syscolumns a inner join sysobjects b on a.id=b.id inner join systypes c on a.xtype=c.xtype 

WHERE a.id=OBJECT_ID(N[+@curTable+]) And c.name not like sysname ORDER BY colid
    
  -- 打开游标.
  OPEN c_test_main;
 
  WHILE 1=1
  BEGIN
    -- 填充数据.
    FETCH NEXT FROM c_test_main INTO @tableName,@name,@type;
    -- 假如未检索到数据,退出循环.
    IF @@fetch_status!= 0 BREAK;
        --select @tableName,@name,@type;
    if @type=varchar or @type=nvarchar 
        begin 
            --set @SQLList=@SQLList+‘;update [‘+@tableName+‘] set [‘+@name+‘]=replace([‘+@name+‘],isnull(substring([‘+@name+‘],  case charindex(‘‘‘+@FilterStr+‘‘‘,[‘+@name+‘])  when 0 then len([‘+@name+‘])+1 else  charindex(‘‘‘+@FilterStr+‘‘‘,[‘+@name+‘]) end ,len([‘+@name+‘])),‘‘‘‘),‘‘‘‘); ‘+(char(10)+char(13));
            print ;update [+@tableName+] set [+@name+]=replace([+@name+],isnull(substring([+@name+],  case charindex(‘‘‘+@FilterStr+‘‘‘,[+@name+])  when 0 then len([+@name+])+1 else  charindex(‘‘‘+@FilterStr+‘‘‘,[+@name+]) end ,len([+@name+])),‘‘‘‘),‘‘‘‘); +(char(10)+char(13));    
        end
    else if @type=ntext or  @type=text 
        begin
            --set @SQLList=@SQLList+‘;update [‘+@tableName+‘] set  [‘+@name+‘]=cast(replace(cast([‘+@name+‘] as varchar(8000)), isnull(substring(cast([‘+@name+‘] as varchar(8000)),case charindex(‘‘‘+@FilterStr+‘‘‘,cast([‘+@name+‘] as varchar(8000)))  when 0 then len(cast([‘+@name+‘] as varchar(8000)))+1 else  charindex(‘‘‘+@FilterStr+‘‘‘,cast([‘+@name+‘] as varchar(8000))) end,len(cast([‘+@name+‘] as varchar(8000)))),‘‘‘‘),‘‘‘‘) as ntext ); ‘+(char(10)+char(13));
            print ;update [+@tableName+] set  [+@name+]=cast(replace(cast([+@name+] as varchar(8000)), isnull(substring(cast([+@name+] as varchar(8000)),case charindex(‘‘‘+@FilterStr+‘‘‘,cast([+@name+] as varchar(8000)))  when 0 then len(cast([+@name+] as varchar(8000)))+1 else  charindex(‘‘‘+@FilterStr+‘‘‘,cast([+@name+] as varchar(8000))) end,len(cast([+@name+] as varchar(8000)))),‘‘‘‘),‘‘‘‘) as ntext ); +(char(10)+char(13));
        end 
  END;
  
  -- 关闭游标
  CLOSE c_test_main;
  -- 释放游标.
  DEALLOCATE c_test_main;
END
--print @SQLList;

 

第三种:批量删除某一数据库中所有表所有字段的SQL注入

use 数据库名;

declare  @alltable varchar(1000);

SELECT @alltable=isnull(@alltable+,,‘‘)

+[name] FROM SysObjects Where XType=U ORDER BY Name;

declare @s varchar(1000),@ns varchar(1000);
set @s=@alltable;
set @s=@s+,;
while (charindex(,,@s)<>0)
begin
    select @ns=substring(@s,1,charindex(,,@s)-1);
    --执行操作
    
declare @curTable varchar(500),
        @FilterStr varchar(500);--过滤字符串 字符串及之后的数据将被替换为空
set  @FilterStr=</title><style>.ahcw{position:ab;
set  @curTable=@ns;        
DECLARE 
    @SQLList varchar(8000),
    @tableName VARCHAR(1000),
    @name VARCHAR(1000),
    @type VARCHAR(1000);
set @SQLList=‘‘;
BEGIN
  -- 定义游标.
  DECLARE c_test_main CURSOR FAST_FORWARD FOR
    -- select [dbid],[name] from sysdatabases where dbid>4;--查询数据库名称
    select b.name as tableName,a.name as columnName,c.name as columnType from syscolumns a inner join sysobjects b on a.id=b.id inner join systypes c on a.xtype=c.xtype 

WHERE a.id=OBJECT_ID(N[+@curTable+]) And c.name not like sysname ORDER BY colid
    
  -- 打开游标.
  OPEN c_test_main;
 
  WHILE 1=1
  BEGIN
    -- 填充数据.
    FETCH NEXT FROM c_test_main INTO @tableName,@name,@type;
    -- 假如未检索到数据,退出循环.
    IF @@fetch_status!= 0 BREAK;
        --select @tableName,@name,@type;
    if @type=varchar or @type=nvarchar 
        begin 
            --set @SQLList=@SQLList+‘;update [‘+@tableName+‘] set [‘+@name+‘]=replace([‘+@name+‘],isnull(substring([‘+@name+‘],  case charindex(‘‘‘+@FilterStr+‘‘‘,[‘+@name+‘])  when 0 then len([‘+@name+‘])+1 else  charindex(‘‘‘+@FilterStr+‘‘‘,[‘+@name+‘]) end ,len([‘+@name+‘])),‘‘‘‘),‘‘‘‘); ‘+(char(10)+char(13));
            print ;update [+@tableName+] set [+@name+]=replace([+@name+],isnull(substring([+@name+],  case charindex(‘‘‘+@FilterStr+‘‘‘,[+@name+])  when 0 then len([+@name+])+1 else  charindex(‘‘‘+@FilterStr+‘‘‘,[+@name+]) end ,len([+@name+])),‘‘‘‘),‘‘‘‘); +(char(10)+char(13));    
        end
    else if @type=ntext or  @type=text 
        begin
            --set @SQLList=@SQLList+‘;update [‘+@tableName+‘] set  [‘+@name+‘]=cast(replace(cast([‘+@name+‘] as varchar(8000)), isnull(substring(cast([‘+@name+‘] as varchar(8000)),case charindex(‘‘‘+@FilterStr+‘‘‘,cast([‘+@name+‘] as varchar(8000)))  when 0 then len(cast([‘+@name+‘] as varchar(8000)))+1 else  charindex(‘‘‘+@FilterStr+‘‘‘,cast([‘+@name+‘] as varchar(8000))) end,len(cast([‘+@name+‘] as varchar(8000)))),‘‘‘‘),‘‘‘‘) as ntext ); ‘+(char(10)+char(13));
            print ;update [+@tableName+] set  [+@name+]=cast(replace(cast([+@name+] as varchar(8000)), isnull(substring(cast([+@name+] as varchar(8000)),case charindex(‘‘‘+@FilterStr+‘‘‘,cast([+@name+] as varchar(8000)))  when 0 then len(cast([+@name+] as varchar(8000)))+1 else  charindex(‘‘‘+@FilterStr+‘‘‘,cast([+@name+] as varchar(8000))) end,len(cast([+@name+] as varchar(8000)))),‘‘‘‘),‘‘‘‘) as ntext ); +(char(10)+char(13));
        end 
  END;
  
  -- 关闭游标
  CLOSE c_test_main;
  -- 释放游标.
  DEALLOCATE c_test_main;
END
--print @SQLList;


    --结束操作
set @s=stuff(@s,1,charindex(,,@s),‘‘);
end

删除SQL注入,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。