我应该如何将表名传递到存储过程中？

Question

我刚刚遇到了一件奇怪的事情...我们网站上的一些代码采用了一个巨大的 SQL 语句，通过基于某些用户值进行一些搜索和替换来修改代码，然后将其传递到 SQL Server 作为一个问题。

我认为这作为对存储过程的参数化查询会更清晰，以用户值作为参数，但是当我更仔细地观察时，我明白他们为什么可能这样做......他们从中选择的表是不同程度地取决于这些用户值。

例如，在一种情况下，如果值是（“FOO”，“BAR”），则查询最终将类似于“SELECT * FROM FOO_BAR”

是否有一种简单明了的方法可以做到这一点？ 我所做的一切似乎都不优雅。

编辑：当然，我可以在存储过程中动态生成sql，并执行它（bleh），但那时我想知道我是否获得了任何东西。

编辑2：以某种智能方式重构表名称，比如说将它们全部放在一个具有不同名称的表中作为新列将是解决所有这些问题的好方法，有几个人指出了这一点直接或暗示。 遗憾的是，在这种情况下这不是一个选择。

Answer 1

我反对在存储过程中动态生成 SQL； 这会给你带来麻烦并可能导致注入漏洞。

相反，我会分析可能受查询影响的所有表，并创建某种枚举来确定查询使用哪个表。

Answer 2

听起来 ORM 解决方案会更好。

当我在存储过程中看到动态 sql 时，我感到畏缩。

Answer 3

您可以考虑的一件事是创建一个 case 语句，其中包含所需的相同 SQL 命令，每个有效表一次，然后将表名作为字符串传递到此过程中，并让 case 选择要运行的命令。

顺便说一句，作为一名安全人员，上面的建议告诉您从系统表中进行选择以确保您拥有有效的表，对我来说这似乎是浪费的操作。 如果有人可以通过 QUOTENAME() 进行注入，那么注入将在系统表和基础表上起作用。 唯一有助于确保它是有效的表名，我认为上面的建议是更好的方法，因为您根本没有使用 QUOTENAME() 。

Answer 4

根据这些表中的列集是否相同或不同，从长远来看，我会以两种方式处理它：

1）如果它们相同，为什么不创建一个用作选择器的新列，其值源自用户提供的参数？ （这是性能优化吗？）

2）如果它们不同，那么对它们的处理很可能也不同。 因此，对我来说，将选择/处理代码分成单独的块，然后单独调用它们似乎是最模块化的方法。 您将重复“select * from”部分，
但在这种情况下，表集希望是有限的。

允许调用代码提供表名的两个任意部分来进行选择感觉非常危险。

Answer 5

我不知道为什么您将数据分布在多个表中，但听起来您正在打破其中一项基本原则。 数据应该在表中，而不是表名。

如果表的布局大致相同，请考虑是否最好将数据放在单个表中。 这将解决您的动态查询问题，并且将使数据库布局更加灵活。

Answer 6

您可以选择过程，而不是根据用户输入值查询表。
也就是说
1. 创建一个过程 FOO_BAR_prc 并在其中放入查询 'select * from foo_bar' ，这样查询将由数据库预编译。
2. 然后根据用户输入，从应用程序代码中执行正确的过程。

由于您有大约 50 张桌子，这可能不是一个可行的解决方案，因为它需要您做大量工作。

Answer 7

事实上，我想知道如何传递表名来在存储过程中创建表。 通过阅读一些答案并尝试进行一些修改，我终于能够创建一个名称作为参数传递的表。 这是存储过程，供其他人检查其中是否有错误。

使用[数据库名称]
去
/***** 对象：StoredProcedure [dbo].[sp_CreateDynamicTable] 脚本日期：06/20/2015 16:56:25 ******/
设置 ANSI_NULLS 为开
去
将 QUOTED_IDENTIFIER 设置为 ON
去
创建过程 [dbo].[sp_CreateDynamicTable]
@tName varchar(255)
作为
开始
设置不计数；
声明 @SQL nvarchar(max)

SET @SQL = N'CREATE TABLE [DBO].['+ @tName + '] (DocID nvarchar(10) null);'

    EXECUTE sp_executesql @SQL

END

Answer 8

@RBarry杨
您不需要将括号添加到查询字符串中的@ActualTableName，因为它已经包含在 INFORMATION_SCHEMA.TABLES 中的查询结果中。 否则执行时会出现错误。

创建过程 spCountAnyTableRows( @PassedTableName as NVarchar(255) ) AS
-- 计算任何非系统表的行数，安全
开始
DECLARE @ActualTableName AS NVarchar(255)

SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @PassedTableName

DECLARE @sql AS NVARCHAR(MAX)
--SELECT @sql = 'SELECT COUNT(*) FROM [' + @ActualTableName + '];'

-- changed to this
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'

EXEC(@SQL)

END

Answer 9

我会不惜一切代价避免动态 SQL。

这不是最优雅的解决方案，但可以完美地完成工作。

PROCEDURE TABLE_AS_PARAMTER (
        p_table_name IN VARCHAR2
    ) AS
    BEGIN
        CASE p_table_name
            WHEN 'TABLE1' THEN
                UPDATE TABLE1
                SET
                    COLUMN1 =1
                WHERE
                    ID =1;
            WHEN 'TABLE2' THEN
                UPDATE TABLE1
                SET
                    COLUMN1 =1
                WHERE
                    ID =2;
        END CASE;

        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK
END TABLE_AS_PARAMTER;

Answer 10

您可以在存储过程中使用准备好的语句来执行查询。

简单示例演示：

DELIMITER //
CREATE PROCEDURE example (IN p_table_name VARCHAR(20), IN p_col VARCHAR(20))
BEGIN
  SET @sql = CONCAT('SELECT ', p_col, '_input, ', p_col, '_output', ' FROM ', p_table_name);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

这里，如果表 input_output 中有“first_input”、“first_output”、“second_input”和“second_output”等列。

如果要从表 input_output 中检索“first_input”、“first_output”的数据：

call example('input_output', 'first');

Answer 11

首先，您应该永远在客户端应用程序上执行这样的 SQL 命令组合，这就是 SQL 注入。 （对于没有自己的权限的管理工具来说这是可以的，但对于共享使用的应用程序来说就不行了）。

其次，是的，对存储过程的参数化调用既干净又安全。

但是，由于您需要使用动态 SQL 来执行此操作，因此您仍然不希望在执行的查询的文本中包含传递的字符串。 相反，您希望使用传递的字符串来查找应允许用户以这种方式查询的实际表的名称。

这是一个简单的天真的例子：

CREATE PROC spCountAnyTableRows( @PassedTableName as NVarchar(255) ) AS
-- Counts the number of rows from any non-system Table, *SAFELY*
BEGIN
    DECLARE @ActualTableName AS NVarchar(255)

    SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName

    DECLARE @sql AS NVARCHAR(MAX)
    SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'

    EXEC(@SQL)
END

---

有些人相当询问为什么这更安全。 希望小鲍比表可以让这一点更清楚：
0

---

更多问题解答：

1. 单独使用 QUOTENAME 并不能保证安全。 MS 鼓励我们使用它，但他们并没有保证它不会被黑客欺骗。 仅供参考，真正的安全在于保证。 使用 QUOTENAME 进行表查找是另一回事，它是牢不可破的。

2. QUOTENAME 对于本示例来说并不是绝对必要的，仅 INFORMATION_SCHEMA 上的查找翻译通常就足够了。 QUOTENAME 放在这里是因为从安全角度考虑，包含完整且正确的解决方案是一种良好的形式。 这里的 QUOTENAME 实际上是在防止一个独特但类似的潜在问题，即潜在注入。

---

我应该指出，您可以使用动态列名称和 INFORMATION_SCHEMA.COLUMNS 表执行相同的操作。

您还可以通过使用参数化 SQL 查询来绕过对存储过程的需求（请参阅此处：https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.8）。 但我认为存储过程为此类情况提供了更易于管理且不易出错的安全设施。

Answer 12

（不）幸运的是，没有办法做到这一点 - 除了动态 SQL 生成之外，您不能使用作为参数传递给存储代码的表名。 当谈到决定在哪里生成 SQL 代码时，我更喜欢应用程序代码而不是存储的代码。 应用程序代码通常更快且更易于维护。

如果您不喜欢正在使用的解决方案，我建议进行更深入的重新设计（即更改架构/应用程序逻辑，以便您不再需要在任何地方将表名称作为参数传递）。