sql server——在存储过程中动态形成的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年或更早以来，可能从来没有，除了非常大的容量调用)。

有一个计划缓存，也可用于特设语句。

这个特殊的例子重新引入了一个注入漏洞，该漏洞将自动使用:

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权限)

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

• 组织(能够以一致的方式组织到数据库的接口)

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

• 系统清单(能够按名称配置和优化某些过程，能够拥有由存储过程组成的全面且文档完备的接口层)

动态SQL在SP中有它的位置，它可以否定一些东西——比如安全性(它启动一个新的链，所以这里需要授予SELECT权限)和注入。我不会把执行计划缓存放在列表的前面。

Answer 2

用sp_execute(@SQL)代替EXEC(@SQL)。

sp_execute(@SQL)促进查询计划重用。当使用sp_executesql时，用户或应用程序显式地标识参数。在这篇技术文章中阅读有关计划缓存问题的信息，请参阅#3。

Answer 3

这取决于您认为存储过程的"目的"是什么。我认为存储过程的存在是为了向数据库公开一个安全的API，并通过将SQL从代码库中隔离出来，将应用程序与数据库实现隔离。

这样做使数据库成为一个独立的组件，可以独立于引用它的应用程序代码进行修改和版本控制(只要API签名不改变)。

如果你所有的数据库访问都是通过存储过程进行的，只要存储过程参数和结果集没有改变，dba就可以自由地调整查询和重构数据库，只要他们不破坏代码对数据模型的假设:人们可以[理论上]完全改变底层模式而不需要更改代码。

Answer 4

这取决于执行计划的复杂程度。对于这个特殊的例子，我认为存储过程没有问题，因为执行计划非常简单。