SQL 我应该如何将表名传递给存储过程？

How should I pass a table name into a stored proc?

提问by Beska

I just ran into a strange thing...there is some code on our site that is taking a giant SQL statement, modifying it in code by doing some search and replace based on some user values, and then passing it on to SQL Server as a query.

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

I was thinking that this would be cleaner as a parameterized query to a stored proc, with the user values as the parameters, but when I looked more closely I see why they might be doing it...the table that they are selecting from is variably dependant on those user values.

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

For instance, in one case if the values were ("FOO", "BAR") the query would end up being something like "SELECT * FROM FOO_BAR"

例如，在一种情况下，如果值是 ("FOO", "BAR")，则查询最终将类似于 "SELECT * FROM FOO_BAR"

Is there an easy and clear way to do this? Everything I'm trying seems inelegant.

有没有简单明了的方法来做到这一点？我正在尝试的一切似乎都不优雅。

EDIT:I could, of course, dynamically generate the sql in the stored proc, and exec that (bleh), but at that point I'm wondering if I've gained anything.

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

EDIT2:Refactoring the table names in some intelligent way, say having them all in one table with the different names as a new column would be a nice way to solve all of this, which several people have pointed out directly, or alluded to. Sadly, it is not an option in this case.

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

回答by RBarryYoung

First of all, you should NEVERdo SQL command compositions on a client app like this, that'swhat SQL Injection is. (Its OK for an admin tool that has no privs of its own, but not for a shared use application).

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

Secondly, yes, a parametrized call to a Stored procedure is both cleaner and safer.

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

However, as you will need to use Dynamic SQL to do this, you still do not want to include the passed string in the text of the executed query. Instead, you want to used the passed string to look up the names of the actualtables that the user should be allowed to query in the way.

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

Here's a simple naive example:

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

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

---

---

Some have fairly asked why this is safer. Hopefully, little Bobby Tables can make this clearer: 0

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

---

---

Answers to more questions:

更多问题的答案：

1. QUOTENAME alone is not guaranteed to be safe. MS encourages us to use it, but they have not given a guarantee that it cannot be out-foxed by hackers. FYI, real Security is all about the guarantees. The table lookup with QUOTENAME, is another story, it's unbreakable.

2. QUOTENAME is not strictly necessary for this example, the Lookup translation on INFORMATION_SCHEMA alone is normally sufficient. QUOTENAME is in here because it is good form in security to include a complete and correct solution. QUOTENAME in here is actually protecting against a distinct, but similar potential problem know as latent injection.

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

2. QUOTENAME 在这个例子中不是绝对必要的，仅在 INFORMATION_SCHEMA 上的 Lookup 翻译通常就足够了。QUOTENAME 在这里是因为包含完整和正确的解决方案是安全的好形式。这里的 QUOTENAME 实际上是在防止一个不同但类似的潜在问题，称为潜在注入。

---

---

I should note that you can do the same thing with dynamic Column Names and the INFORMATION_SCHEMA.COLUMNStable.

我应该注意到你可以对动态列名和INFORMATION_SCHEMA.COLUMNS表做同样的事情。

You can also bypass the need for stored procedures by using a parameterized SQL query instead (see here: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.8). But I think that stored procedures provide a more manageable and less error-prone security facility for cases like this.

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

回答by AlexS

(Un)fortunately there's no way of doing this - you can't use table name passed as a parameter to stored code other than for dynamic sql generation. When it comes to deciding where to generate sql code, I prefer application code rather that stored code. Application code is usually faster and easier to maintain.

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

In case you don't like the solution you're working with, I'd suggest a deeper redesign (i.e. change the schema/application logic so you no longer have to pass table name as a parameter anywhere).

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

回答by ScottE

Sounds like you'd be better off with an ORM solution.

听起来您最好使用 ORM 解决方案。

I cringe when I see dynamic sql in a stored procedure.

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

回答by Randolpho

I would argue against dynamically generating the SQL in the stored proc; that'll get you into trouble and could cause injection vulnerability.

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

Instead, I would analyze all of the tables that could be affected by the query and create some sort of enumeration that would determine which table to use for the query.

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

回答by pilavdzice

One thing you can consider is to make a case statement that contains the same SQL command you want, once for each valid table, then pass as a string the table name into this procedure and have the case choose which command to run.

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

By the way as a security person the suggestion above telling you to select from the system tables in order to make sure you have a valid table seems like a wasted operation to me. If someone can inject passed the QUOTENAME() then then injection would work on the system table just as well as on the underlying table. The only thing this helps with it to ensure it is a valid table name, and I think the suggestion above is a better approach to that since you are not using QUOTENAME() at all.

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

回答by Sushil Pugalia

In fact, I wanted to know how to pass table name to create a table in stored procedure. By reading some of the answers and attempting some modification at my end, I finally able to create a table with name passed as parameter. Here is the stored procedure for others to check any error in it.

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

USE [Database Name] GO /****** Object: StoredProcedure [dbo].[sp_CreateDynamicTable] Script Date: 06/20/2015 16:56:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_CreateDynamicTable] @tName varchar(255) AS BEGIN SET NOCOUNT ON; DECLARE @SQL nvarchar(max)

USE [数据库名称] GO /****** 对象：StoredProcedure [dbo].[sp_CreateDynamicTable] 脚本日期：06/20/2015 16:56:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_CreateDynamicTable] @tName varchar(255) AS BEGIN SET NOCOUNT ON; 声明 @SQL nvarchar(max)

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

    EXECUTE sp_executesql @SQL

END

结尾

回答by omostan

@RBarry Young You don't need to add the brackets to @ActualTableName in the query string because it is already included in the result from the query in the INFORMATION_SCHEMA.TABLES. Otherwise, there will be error(s) when executed.

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

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

CREATE PROC spCountAnyTableRows( @PassedTableName as NVarchar(255) ) AS -- 计算任何非系统表中的行数，安全开始声明 @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

结尾

回答by Andrew Y

Depending on whether the set of columns in those tables is the same or different, I'd approach it in two ways in the longer term:

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

1) if they the same, why not create a new column that would be used as a selector, whose value is derived from the user-supplied parameters ? (is it a performance optimization?)

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

2) if they are different, chances are that handling of them is also different. As such, it seems like splitting the select/handle code into separate blocks and then calling them separately would be a most modular approach to me. You will repeat the "select * from" part, but in this scenario the set of tables is hopefully finite.

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

Allowing the calling code to supply two arbitrary parts of the table name to do a select from feels very dangerous.

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

回答by Guffa

I don't know the reason why you have the data spread over several tables, but it sounds like you are breaking one of the fundamentals. The data should be in the tables, not as table names.

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

If the tables have more or less the same layout, consider if it would be best to put the data in a single table instead. That would solve your problem with the dynamic query, and it would make the database layout more flexible.

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

回答by Rajat

Instead of Querying the tables based on user input values, you can pick the procedure instead. that is to say
1. Create a procedure FOO_BAR_prc and inside that you put the query 'select * from foo_bar' , that way the query will be precompiled by the database.
2. Then based on the user input now execute the correct procedure from your application code.

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

Since you have around 50 tables, this might not be a feasible solution though as it would require lot of work on your part.

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