这个存储过程可以安全地避免 SQL 注入吗？

Question

此存储过程使用 sp_executesql 执行带参数的 sql。
sql注入安全吗？

create procedure ExecutePeopleFilter 
    (@lastNameFilter varchar(20), 
    @companyNameFilter varchar(20), 
    @ageFilter int, 
    @dateFilter datetime)
as
begin
    declare @sql varchar(4000)
    declare @params varchar(1000)
    declare @whereClause varchar(1000)

    set @whereClause = ''

    if ISNULL(@lastNameFilter,'') <> ''
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '
        if (LEN(@lastNameFilter) < 20) set @lastNameFilter += '%'
        set @whereClause += 'LastName like @lastName '
    end

    if ISNULL(@companyNameFilter,'') <> ''
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '
        if (LEN(@companyNameFilter) < 20) set @companyNameFilter += '%'
        set @whereClause += 'CompanyName like @companyName '
    end

    if @ageFilter is not null
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '     
        set @whereClause += 'Age = @age '
    end

    if @dateFilter is not null
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '
        set @whereClause += 'StartDate = @startDate '
    end


    set @sql = 'select FirstName, LastName, CompanyName, Age, StartDate 
        from People'
    if (LEN(@whereClause) <> 0) set @sql += ' where ' + @whereClause

    set @params = '@lastName varchar(20), 
        @companyName varchar(20), 
        @age int, 
        @startDate datetime'

    execute sp_executesql @sql, @params, 
        @lastName = @lastNameFilter, 
        @companyName = @companyNameFilter, 
        @age = @ageFilter, 
        @startDate = @dateFilter
end

Answer 1

除了众所周知的硬编码值之外，您永远不会将任何内容连接到要发送到数据库引擎的 SQL 语句中，因此它对于所有当前已知的 SQL 注入方法都是安全的（并且应该能够抵御未来的攻击）。但是，它确实存在其他问题（例如未声明@startDate）。

Answer 2

是的，您对字符串 concat 和 sp_executesql 的使用是正确的，并且 SQL 注入不会成为问题。

不，仅仅因为 LINQ 是新热门并不意味着它是正确的解决方案。

也就是说，您的 varchar(20) 参数很容易溢出，您可能需要将它们稍微增加一点，至少达到实际字段的大小。

Answer 3

您不需要动态 SQL 来创建可选的 WHERE 子句...只需使用：

WHERE ((@x IS NULL) OR (@x = ...)) AND ...

也应该更快，并且没有字符串溢出、注入等风险。

像这样：

CREATE PROCEDURE ExecutePeopleFilter
  (
   @lastNameFilter varchar(20),
   @companyNameFilter varchar(20),
   @ageFilter int,
   @dateFilter datetime
  )
AS 
  BEGIN
    SELECT FirstName, LastName, CompanyName, Age, StartDate
      FROM People
      WHERE (
             (ISNULL(@lastNameFilter, '') = '')
             OR (LastName LIKE @lastNameFilter+'%')
            )
        AND (
             (ISNULL(@companyNameFilter, '') = '')
             OR (LastName LIKE @companyNameFilter+'%')
            )
        AND (
             (@ageFilter IS NULL)
             OR (Age = @ageFilter)
            )
        AND (
             (@dateFilter IS NULL)
             OR (StartDate = @dateFilter)
            ) ;
  END

Answer 4

即使是……“呃”。

为什么不使用临时表，用与非可选参数匹配的元素结果的 ID 填充它，然后根据已指定的参数从临时表中消除其余内容？完成后，只需加入您要查找的结果集即可。

CREATE TABLE #People
   (personid int)
INSERT INTO #People SELECT personid FROM people
IF NOT @lastNameParam IS NULL
   DELETE FROM #People WHERE personid NOT IN (SELECT personid FROM people WHERE lastname LIKE @lastNameParam + '%')
-- And so on...

Answer 5

我认为是这样。至少，它看起来还不错。但这可能不是我会采取的方式。

安全性的关键之一是永远永远永远永远编写自己的安全代码。您总是希望尽可能多地依赖平台提供的和内置的机制。您通过使用 sp_executesql 来做到这一点，但我希望您还可以做更多的事情。

---

根据要求，我可能会这样做：

create procedure ExecutePeopleFilter 
    (@lastNameFilter varchar(20) = NULL, 
    @companyNameFilter varchar(20) = NULL, 
    @ageFilter int = NULL, 
    @dateFilter datetime = NULL)
as
begin

    if (LEN(@lastNameFilter) < 20) set @lastNameFilter += '%'
    if (LEN(@companyNameFilter) < 20) set @companyNameFilter += '%'

    SELECT FirstName, LastName, CompanyName, Age, StartDate 
    FROM People
    WHERE  LastName    LIKE COALESCE(@lastNameFilter, LastName) 
       AND CompanyName LIKE COALESCE(@companyNameFilter, CompanyName)
       AND Age          =   COALESCE(@ageFilter, Age)
       AND StartDate    =   COALESCE(@dateFilter, StartDate)

end

不需要动态 sql，并且 WHERE 子句中没有“OR”。

Answer 6

为什么要在存储过程中这样做？更好的解决方案可能是在客户端攻击此问题，在调用存储过程之前转义字符串并检查长度。 MS Enterprise DAAB (.NET) 等库提供了便捷的方法，通过在将参数添加到命令对象时指定参数的数据类型和长度来实现此目的。

Answer 7

差不多了。

防止 SQL 注入的关键是通过“批准”机制正确处理参数并避免字符串连接。

您的代码不会使用参数构建字符串：它们通过 sp_executesql 进行分离和清理。

你是否会这样做是另一回事......正如其他答案所示