sql - 存储过程中动态形成的 SQL 是否否定了存储过程的真正目的？

Question

我们现在有 10-12 年的旧项目。它使用的是 SQL2000，我们现在已迁移到 SQL2008。

在执行此任务时，我发现存储过程接受参数，然后将查询构造为字符串，然后使用 EXEC 执行命令。

CREATE PROCEDURE MyProc
  (@TableName varchar(255),
   @FirstName varchar(50),
   @LastName varchar(50))
AS

    -- Create a variable @SQLStatement
    DECLARE @SQLStatement varchar(255)

    -- Enter the dynamic SQL statement into the
    -- variable @SQLStatement
    SELECT @SQLStatement = "SELECT * FROM " +
                   @TableName + "WHERE FirstName = '"
                   + @FirstName + "' AND LastName = '"
                   + @LastName + "'"

    -- Execute the SQL statement
    EXEC(@SQLStatement)

这是一个不好的方法吗？这是否会消除存储过程的好处(预编译查询的好处)？

Answer 1

不，我认为存储过程的主要好处不再是它是“预编译”的(从 2005 年或更早开始，也许从来没有，除了非常大量的调用)。

有一个计划缓存，也可用于临时语句。

这个特定的示例重新引入了一个注入(inject)漏洞，该漏洞将通过以下方式自动实现:

CREATE PROCEDURE MyProc
  (@FirstName varchar(50),
   @LastName varchar(50))
AS
BEGIN
    SELECT * FROM TABLENAME
    WHERE FirstName = @FirstName
        AND LastName = @LastName
END

所有这些都是为了在表名称上进行参数化。

存储过程的好处包括:

• 安全管理(能够独立于 SELECT/INSERT/UPDATE 控制 EXEC 权限)

• 访问协调(确保所有操作都以特定方式完成)

• 组织(能够以连贯的方式组织数据库接口(interface))

• 注入(inject)预防(存储过程总是参数化的，这确保调用者无法创建容易受到注入(inject)攻击的数据库案例 - 请注意，SP 本身需要正确编写，但客户端程序员将无法如果他们只能访问 SP 而不能访问表，则引入注入(inject))

• 系统 list (能够按名称分析和优化某些过程，能够拥有由存储过程组成的全面且记录良好的界面层)

动态 SQL 在 SP 中占有一席之地，它可以否定一些东西 - 例如安全性(它启动一个新链，因此需要在此处授予 SELECT 权限)和注入(inject)。执行计划缓存不是我优先考虑的一个。