sql存储过程
存储过程本身就采用参数传递的方式
在执行时不会将传入的参数值做为sql的可执行代码 只会简单的做为参数的值
所以不用过滤
这和.NET中给sql语句传参数过滤是一个道理
存储过程执行一次后会被缓存起来 执行效率会高很多
CREATE PROCEDURE dbo.QueryCustomers
(
@CompanyName nvarchar(30),
@CustomerID nvarchar(12),
@ContactTitle nvarchar(30)
)
AS
DECLARE @STR nvarchar(255)
DECLARE @WK nvarchar(255)
SET @STR = 'SELECT * FROM Customers'
SET @WK = ''
IF NOT @CompanyName IS NULL
SET @WK = @WK + ' CompanyName LIKE ''%'+@CompanyName+'%'' AND '
IF NOT @CustomerID IS NULL
SET @WK = @WK + ' CustomerID LIKE ''%'+@CustomerID+'%'' AND '
IF NOT @ContactTitle IS NULL
SET @WK = @WK + ' ContactTitle LIKE ''%'+@ContactTitle+'%'' AND '
IF LEN(@STR) > 0
BEGIN
SET @STR = @STR+' WHERE '+SUBSTRING(@WK,0,LEN(@WK)-3)
exec sp_executesql @STR
End
ELSE
exec sp_executesql @STR
那你说存储过程这样写的话会不会被注入？
还是会被注入。解决主案如下：
CREATE PROCEDURE dbo.SafeQueryCustomers
(
@CompanyName nvarchar(30),
@CustomerID nvarchar(12),
@ContactTitle nvarchar(30)
)
AS
DECLARE @STR nvarchar(255)
DECLARE @WK nvarchar(255)
SET @STR = 'SELECT * FROM Customers'
SET @WK = ''
IF NOT @CompanyName IS NULL
BEGIN
SET @WK = @WK + ' CompanyName LIKE @pCompanyName AND '
SET @CompanyName = '%' +@CompanyName + '%'
END
IF NOT @CustomerID IS NULL
BEGIN
SET @WK = @WK + ' CustomerID LIKE @pCustomerID AND '
SET @CustomerID = '%' +@CustomerID + '%'
END
IF NOT @ContactTitle IS NULL
BEGIN
SET @WK = @WK + ' ContactTitle LIKE @pContactTitle AND '
SET @ContactTitle = '%' +@ContactTitle + '%'
END
IF LEN(@STR) > 0
BEGIN
SET @STR = @STR+' WHERE '+SUBSTRING(@WK,0,LEN(@WK)-3)
exec sp_executesql @STR,
N'@pCompanyName nvarchar(30),@pCustomerID nvarchar(12),@pContactTitle nvarchar(30)',
@pCompanyName=@CompanyName,@pCustomerID=@CustomerID,@pContactTitle=@ContactTitle
End
ELSE
exec sp_executesql @STR
