求一个函数..调用此函数时,将 'aaaaa/bbbbb/ccccc/ddddd'和 ' or MYNAME = '两个字符串参数传到这个函数.要求此函数返回的字符串结果为:
(MYNAME = 'aaaaa' or MYNAME = 'bbbbb' or MYNAME = 'ccccc' or MYNAME = 'ddddd')
(MYNAME = 'aaaaa' or MYNAME = 'bbbbb' or MYNAME = 'ccccc' or MYNAME = 'ddddd')
RETURNS nvarchar(50)
AS
BEGIN
RETURN @str1 + @str2
END??
CREATE FUNCTION strs(@str1 varchar(255),@str2 varchar(255))
RETURNS nvarchar(50)
AS
BEGIN
RETURN @str1 + @str2
END
--可以这样来解决if object_id('tb')is not null drop table tb
go
create table tb(userName varchar(11) )
insert tb select
'aaaaa' union select
'bbbbb' union select
'c' union select
'd' union select
'e' union select
'e'
declare @s varchar(100),@s1 varchar(100)
set @s='aaaaa/bbbbb/ccccc/ddddd'
set @s=''''+replace(@s ,'/',''',''')+''''set @s1='select * from tb where username in('+@s+')' exec(@s1)userName
-----------
aaaaa
bbbbb(2 行受影响)
if object_ID('dbo.GetStr') Is not null
drop function dbo.GetStr
go
Create function dbo.GetStr(@str NVARCHAR(100),@tag NVARCHAR(20))
RETURNS nvarchar(100)
as
begin
select @str=''''+replace(@str,'/',''' '+@tag+' ''')+''''
select @str='('+ substring(@tag,charindex(' ',ltrim(@tag)),len(@tag)- charindex(' ',ltrim(@tag))+1) + @str +')'
return @str
end
go
select dbo.GetStr('aaaaa/bbbbb/ccccc/ddddd','or MYNAME =')
/*
----------------------------------------------------------------------------------------------------
( MYNAME ='aaaaa' or MYNAME = 'bbbbb' or MYNAME = 'ccccc' or MYNAME = 'ddddd')
*/
select dbo.GetStr('aaaaa/bbbbb/ccccc/ddddd','and MYNAME =')
/*
----------------------------------------------------------------------------------------------------
( MYNAME ='aaaaa' and MYNAME = 'bbbbb' and MYNAME = 'ccccc' and MYNAME = 'ddddd')
*/
returns varchar(200)
as
begin
declare @s varchar(200)
set @s=replace(@s1,'/',''' '+@s2+'''')
return '('+stuff(@s2,1,3,'')+''''+@s+''''+')'
endselect dbo.k_of('aaaaa/bbbbb/ccccc/ddddd','or MYNAME=')
/*
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(MYNAME='aaaaa' or MYNAME='bbbbb' or MYNAME='ccccc' or MYNAME='ddddd')*/
declare @s1 as varchar(100)
declare @s2 as varchar(100)
set @s1 = 'aaaaa/bbbbb/ccccc/ddddd'
set @s2 = 'MYNAME'set @s = @s2 + ' = ''' + replace(@s1 , '/' , ''' or ' + @s2 + ' = ''') + ''''select @s/*
----------------------------------------------------------------------------
MYNAME = 'aaaaa' or MYNAME = 'bbbbb' or MYNAME = 'ccccc' or MYNAME = 'ddddd'(所影响的行数为 1 行)
*/