sql server 存储过程注入

[zhangfengyi]

以下是登录存储过程，之前是参数化，后来改成存储过程，漏洞检测工具检测到有sql注入，这个要怎样整 改

ALTER PROCEDURE [dbo].[Login_Login]
 @username varchar(30),
 @password varchar(50),
 @merchantno varchar(30),
 @usertype varchar(1)
AS
 
BEGIN
	declare @sql Nvarchar(4000)
	if @usertype ='1'--1商户
		begin
		   set @sql='select state,username,password,certificatinfo_name,certificateinfo,ModifyTime from userinfo where username='''+@username+''' and password='''+@password+''' and merchantno='''+@merchantno+''' and usertype=1'
		end
	else             --2管理员
		begin
		   set @sql='select state,username,password,certificatinfo_name,certificateinfo,ModifyTime from userinfo where username='''+@username+''' and password='''+@password+''' and usertype=2'
		end
		--print @sql
	exec (@sql)
END

[独木成林_]

從前台加密傳入進來對比

[吉普赛的歌]

必须参数化:

ALTER PROCEDURE [dbo].[Login_Login]
 @username varchar(30),
 @password varchar(50),
 @merchantno varchar(30),
 @usertype varchar(1)
AS
BEGIN
	declare @sql Nvarchar(MAX)
	if @usertype ='1'--1商户
		begin
		   set @sql='select state,username,password,certificatinfo_name,certificateinfo,ModifyTime from userinfo where username=@username and password=@password and merchantno=@merchantno and usertype=1'
		end
	else             --2管理员
		begin
		   set @sql='select state,username,password,certificatinfo_name,certificateinfo,ModifyTime from userinfo where username=@username and password=@password and usertype=2'
		end
		--print @sql
	EXEC sp_executesql @sql
		,N'@username varchar(30),
		 @password varchar(50),
		 @merchantno varchar(30),
		 @usertype varchar(1)'
		,@username
		,@password
		,@merchantno
		,@usertype
END

[Hello World,]

不用拼接

ALTER PROCEDURE [dbo].[Login_Login] @username VARCHAR(30),
                                    @password VARCHAR(50),
                                    @merchantno VARCHAR(30),
                                    @usertype VARCHAR(1)
AS
    BEGIN
        SELECT  state,
                username,
                password,
                certificatinfo_name,
                certificateinfo,
                ModifyTime
        FROM    userinfo
        WHERE   username = @username AND password = @password AND   (merchantno = @merchantno OR usertype = '2');
    END;

[zhangfengyi]

引用 3 楼 Hello World, 的回复:

不用拼接

ALTER PROCEDURE [dbo].[Login_Login] @username VARCHAR(30),
                                    @password VARCHAR(50),
                                    @merchantno VARCHAR(30),
                                    @usertype VARCHAR(1)
AS
    BEGIN
        SELECT  state,
                username,
                password,
                certificatinfo_name,
                certificateinfo,
                ModifyTime
        FROM    userinfo
        WHERE   username = @username AND password = @password AND   (merchantno = @merchantno OR usertype = '2');
    END;

我想返回查询结果怎么办

[zhangfengyi]

之前是拼接sql语句，通过exec (@sql)返回查询 结果的

[Hello World,]

引用 4 楼 zhangfengyi 的回复:

[quote=引用 3 楼 Hello World, 的回复:]
不用拼接

ALTER PROCEDURE [dbo].[Login_Login] @username VARCHAR(30),
                                    @password VARCHAR(50),
                                    @merchantno VARCHAR(30),
                                    @usertype VARCHAR(1)
AS
    BEGIN
        SELECT  state,
                username,
                password,
                certificatinfo_name,
                certificateinfo,
                ModifyTime
        FROM    userinfo
        WHERE   username = @username AND password = @password AND   (merchantno = @merchantno OR usertype = '2');
    END;

我想返回查询结果怎么办[/quote]
执行了就有结果了

[zhangfengyi]

引用 6 楼 Hello World, 的回复:

[quote=引用 4 楼 zhangfengyi 的回复:]
[quote=引用 3 楼 Hello World, 的回复:]
不用拼接

ALTER PROCEDURE [dbo].[Login_Login] @username VARCHAR(30),
                                    @password VARCHAR(50),
                                    @merchantno VARCHAR(30),
                                    @usertype VARCHAR(1)
AS
    BEGIN
        SELECT  state,
                username,
                password,
                certificatinfo_name,
                certificateinfo,
                ModifyTime
        FROM    userinfo
        WHERE   username = @username AND password = @password AND   (merchantno = @merchantno OR usertype = '2');
    END;

我想返回查询结果怎么办[/quote]
执行了就有结果了[/quote]
可以了，所有的存储过程都要改下，