-查某个指定字符出现在哪些表哪些字段: declare @searchstr nvarchar(500) set @searchstr ='3b3.org' --这里是你要查的字符内容 declare @t varchar(255),@c varchar(255) create table # (name varchar(256),cols varchar(4000)) declare table_cursor cursor for select a.name,b.name from sysobjects a,syscolumns b ,systypes c where a.id=b.id and a.xtype='u' and b.xtype=c.xtype and c.name in ('char','nchar','varchar','nvarchar','text','next') open table_cursor fetch next from table_cursor into @t,@c while(@@fetch_status=0) begin exec(' set nocount on if exists(select top 1 1 from [' + @t + '] where cast([' + @c + '] as varchar(8000)) like ''%'+@searchstr+'%'') begin if not exists(select 1 from # where name='''+@t+''') insert into # select '''+@t+''','''+@c+''' else update # set cols=cols+'','+@c+''' where name='''+@t+''' --select '+@c+' from [' + @t + '] where [' + @c + '] like ''%'+@searchstr+'%'' end ') fetch next from table_cursor into @t,@c end close table_cursor deallocate table_cursor; select name as '表名',cols as '列名' from # drop table #
这段sql可以查到你的哪些表哪些字段包含某个指定字符.
这种sql注入攻击我怀疑应该是一种工具或称为病毒，但始终没有见过，如果哪位朋友有，希望给我一份，谢谢.
继续：
针对如上的这种sql注入攻击，如果由于某些原因不能更改程序，那么提供如下解决办法：
-创建测试库
create database dbtest
go
use dbtest
go
--创建测试表tb_1
create table tb_1(
id int identity(1,1) primary key,
col1 char(200),
col2 varchar(300),
col3 text,
col4 ntext,
col5 varchar(max)
)
go
--创建测试表tb_2
create table tb_2(
id int identity(1,1) primary key,
col1 char(200),
col2 varchar(300)
)
go
insert into tb_1 select 'a','b','c','d','e'
go
--创建测试视图
create view v_tb_1
as
select *from tb_1
go
--创建测试存储过程
create proc p_tb_1
as
select top 1 * from tb_1 order by col2
go
--创建测试表值函数
create function fn_tb_1(@id int)
returns table
return (select * from tb_1 where id=@id)
go
--创建测试标量函数
create function fn_tb_1_1(@id int)
returns varchar(300)
as
begin
declare @col2 varchar(300)
select @col2 = col2 from tb_1 where id=@id
return @col2
end
go
--创建login帐号及为其添加安全权限,重要
use dbtest
go
set nocount on
declare @dbname varchar(300),@username varchar(300),@pwd varchar(300)
select @dbname=db_name(),
@username='webuser_ dbtest',--这里是你的需要创建的新帐号
@pwd='ExO[!g-k9EQ]#y'--这里是你的密码，要求包含数字、字母、特殊符号,长度14位.
declare @sql varchar(max)
declare @t table(name varchar(256)) --这个表存储你要禁止访问的系统表/视图
insert into @t select 'sysobjects' union all select 'sys.objects '
union all select 'syscolumns' union all select 'sys.columns'
union all select 'sys.tables' union all select 'sys.all_columns'
union all select 'sys.all_objects' union all select 'sys.indexes'
union all select 'sys.index_columns'
set @sql = 'use master;create login ['+ @username +'] with password = '''+@pwd+''',CHECK_EXPIRATION=OFF, CHECK_POLICY=ON'+char(13)
set @sql = @sql + 'use ['+@dbname+']; create user ['+@username +'] for login ['+@username+'] with DEFAULT_SCHEMA =dbo'+char(13)
select @sql = @sql + 'grant select,insert,update,delete on ['+name+'] to ['+@username+']' +char(13) from sys.tables where type='u' and is_ms_shipped !=1
select @sql = @sql + 'grant exec on ['+name+'] to ['+@username+']' +char(13) from sys.procedures where type='P' and schema_id=1 and is_ms_shipped !=1
select @sql = @sql + 'grant exec on ['+name+'] to ['+@username+']' +char(13) from sys.objects where type in ('FN') and is_ms_shipped !=1
select @sql = @sql + 'grant select on ['+name+'] to ['+@username+']' +char(13) from sys.objects where type in ('TF') and is_ms_shipped !=1
select @sql = @sql + 'grant select on ['+name+'] to ['+@username+']' +char(13) from sys.views where type='V' and is_ms_shipped !=1
select @sql = @sql + 'deny select on '+name+' to ['+@username+']' +char(13) from @t
print(@sql)
exec(@sql)
/* --这里生成的创建新用户及设置权限的t-sql.
use master;create login [webuser_dbtest] with password = 'ExO[!g-k9EQ]#y',CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
use [dbtest]; create user [webuser_dbtest] for login [webuser_dbtest] with DEFAULT_SCHEMA =dbo
grant select,insert,update,delete on [tb_2] to [webuser_dbtest]
grant select,insert,update,delete on [tb_1] to [webuser_dbtest]
grant exec on [p_tb_1] to [webuser_dbtest]
grant exec on [fn_tb_1_1] to [webuser_dbtest]
grant select on [v_tb_1] to [webuser_dbtest]
deny select on sysobjects to [webuser_dbtest]
deny select on sys.objects to [webuser_dbtest]
deny select on syscolumns to [webuser_dbtest]
deny select on sys.columns to [webuser_dbtest]
deny select on sys.tables to [webuser_dbtest]
deny select on sys.all_columns to [webuser_dbtest]
deny select on sys.all_objects to [webuser_dbtest]
deny select on sys.indexes to [webuser_dbtest]
deny select on sys.index_columns to [webuser_dbtest]
*/
execute as login='webuser_dbtest' --切换用户，以验证权限是否正确
select USER_NAME()
/*
webuser_dbtest
*/
select top 1 *from tb_1
/*
1 a b c d e
*/
select top 1 * from v_tb_1
/*
1 a b c d e
*/
exec p_tb_1
/*
1 a b c d e
*/
select top 1 * from sys.objects --这里可以看到该系统表也不允许被访问
/*
Msg 229, Level 14, State 5, Line 1
拒绝了对对象 'objects' (数据库 'mssqlsystemresource'，架构 'sys')的 SELECT 权限。
*/
revert --切换回dbo用户
select USER_NAME()
/*
dbo
*/
select top 1 name,object_id from sys.objects --这里可以看到该系统表允许dbo访问
/*
sysrscols 3
*/
只能说明这些人并不明白注入攻击原理.