SQL ，注入 – ITJOY.NET

程序中执行SQL时，可以使用参数化防注入，如：

select * from tablenmae where id = @a  --@a=123

1	select * from tablenmae where id = @a --@a=123

但是where id形式不可以直接使用参数化：

select * from tablenmae where id in('0,1,2,3')

1	select * from tablenmae where id in('0,1,2,3')

参考：https://www.cnblogs.com/shenyixin/archive/2011/08/26/2154115.html

将逗号分隔的字符串转换为参数table，再使用参数化调用，如：

declare str vchar(100)
set str='1,2,3'
select * from tablename where id in (select toint from StrTointTable(@str,',') )

declare str vchar(100)

set str='1,2,3'

select * from tablename where id in (select toint from StrTointTable(@str,',') )

where in 字符串：

declare str vchar(100)
set str='abc,bbb,ccc'
select * from tablename where name in (select string from StrToTable(@str,',')

declare str vchar(100)

set str='abc,bbb,ccc'

select * from tablename where name in (select string from StrToTable(@str,',')

字符串转换参数(string类型)table函数如下：

IF OBJECT_ID('dbo.StrToTable', 'TF') IS NOT NULL
    DROP FUNCTION dbo.StrToTable;
GO
CREATE Function StrToTable(@str nvarchar(2000),@split nvarchar(100))
Returns @tableName Table
(
   string varchar(50)
)
As
--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将变成一个表
Begin
	set @str = @str+@split
	declare @splen int	--分割符长度
	set @split=ltrim(rtrim(@split))
	set @splen=len(@split)
	Declare @insertStr nvarchar(50) --截取后的第一个字符串
	Declare @newstr nvarchar(2000) --截取第一个字符串后剩余的字符串
	set @insertStr = left(@str,charindex(@split,@str)-1)
	set @newstr = stuff(@str,1,charindex(@split,@str)-1+@splen,'')
	Insert @tableName Values(@insertStr)
	while(len(@newstr)>0 and charindex(@split,@newstr)>0)
	begin
	   set @insertStr = left(@newstr,charindex(@split,@newstr)-1)
	   Insert @tableName Values(@insertStr)
	   set @newstr = stuff(@newstr,1,charindex(@split,@newstr)-1+@splen,'')
	end

	if(len(@newstr)>0)
	begin
	   Insert @tableName Values(@newstr)
	end
	delete from @tableName where isnull(string,'')=''
	Return
End

IF OBJECT_ID('dbo.StrToTable', 'TF') IS NOT NULL

DROP FUNCTION dbo.StrToTable;

CREATE Function StrToTable(@str nvarchar(2000),@split nvarchar(100))

Returns @tableName Table

(

string varchar(50)

)

--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将变成一个表

Begin

set @str = @str+@split

declare @splen int --分割符长度

set @split=ltrim(rtrim(@split))

set @splen=len(@split)

Declare @insertStr nvarchar(50) --截取后的第一个字符串

Declare @newstr nvarchar(2000) --截取第一个字符串后剩余的字符串

set @insertStr = left(@str,charindex(@split,@str)-1)

set @newstr = stuff(@str,1,charindex(@split,@str)-1+@splen,'')

Insert @tableName Values(@insertStr)

while(len(@newstr)>0 and charindex(@split,@newstr)>0)

begin

set @insertStr = left(@newstr,charindex(@split,@newstr)-1)

Insert @tableName Values(@insertStr)

set @newstr = stuff(@newstr,1,charindex(@split,@newstr)-1+@splen,'')

end

if(len(@newstr)>0)

begin

Insert @tableName Values(@newstr)

end

delete from @tableName where isnull(string,'')=''

Return

End

字符串转换参数(int类型)table函数如下：

IF OBJECT_ID('dbo.StrToIntTable', 'TF') IS NOT NULL
    DROP FUNCTION dbo.StrToIntTable;
GO
create Function StrToIntTable(@str nvarchar(2000),@split nvarchar(100))
Returns @tableName Table
(
   string varchar(50),
   toint int
)
As
--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将变成一个表
Begin
	set @str = @str+@split
	declare @splen int	--分割符长度
	set @split=ltrim(rtrim(@split))
	set @splen=len(@split)
	Declare @insertStr nvarchar(50) --截取后的第一个字符串
	Declare @newstr nvarchar(2000) --截取第一个字符串后剩余的字符串
	set @insertStr = left(@str,charindex(@split,@str)-1)
	set @newstr = stuff(@str,1,charindex(@split,@str)-1+@splen,'')
	Insert @tableName Values(@insertStr,null)
	while(len(@newstr)>0 and charindex(@split,@newstr)>0)
	begin
	   set @insertStr = left(@newstr,charindex(@split,@newstr)-1)
	   Insert @tableName Values(@insertStr,null)
	   set @newstr = stuff(@newstr,1,charindex(@split,@newstr)-1+@splen,'')
	end

	if(len(@newstr)>0)
	begin
	   Insert @tableName Values(@newstr,null)
	end

	update @tableName set toint=cast(string as int)
	where PATINDEX('%[^0-9]%', string)=0
	and  PATINDEX('%[0-9]%', string)>0

	delete from @tableName where toint is null
	Return
End

IF OBJECT_ID('dbo.StrToIntTable', 'TF') IS NOT NULL

DROP FUNCTION dbo.StrToIntTable;

create Function StrToIntTable(@str nvarchar(2000),@split nvarchar(100))

Returns @tableName Table

(

string varchar(50),

toint int

)

--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将变成一个表

Begin

set @str = @str+@split

declare @splen int --分割符长度

set @split=ltrim(rtrim(@split))

set @splen=len(@split)

Declare @insertStr nvarchar(50) --截取后的第一个字符串

Declare @newstr nvarchar(2000) --截取第一个字符串后剩余的字符串

set @insertStr = left(@str,charindex(@split,@str)-1)

set @newstr = stuff(@str,1,charindex(@split,@str)-1+@splen,'')

Insert @tableName Values(@insertStr,null)

while(len(@newstr)>0 and charindex(@split,@newstr)>0)

begin

set @insertStr = left(@newstr,charindex(@split,@newstr)-1)

Insert @tableName Values(@insertStr,null)

set @newstr = stuff(@newstr,1,charindex(@split,@newstr)-1+@splen,'')

end

if(len(@newstr)>0)

begin

Insert @tableName Values(@newstr,null)

end

update @tableName set toint=cast(string as int)

where PATINDEX('%[^0-9]%', string)=0

and PATINDEX('%[0-9]%', string)>0

delete from @tableName where toint is null

Return

End

标签：SQL ，注入

SQL Server 防注入参数化之 Where in