我应该如何将表名传递给存储过程? [英] How should I pass a table name into a stored proc?
问题描述
我刚刚遇到一个奇怪的事情......我们网站上的一些代码采用了一个巨大的 SQL 语句，通过根据一些用户值进行一些搜索和替换来修改代码，然后将其传递给SQL Server 作为查询.
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.
我认为这作为对存储过程的参数化查询会更清晰，将用户值作为参数，但是当我更仔细地观察时，我明白为什么他们可能会这样做......他们所在的表选择取决于这些用户价值.
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.
例如，在一种情况下，如果值是 ("FOO", "BAR")，则查询最终会类似于 "SELECT * FROM FOO_BAR"
For instance, in one case if the values were ("FOO", "BAR") the query would end up being something like "SELECT * FROM FOO_BAR"
有没有简单明了的方法来做到这一点?我正在尝试的一切似乎都不优雅.
Is there an easy and clear way to do this? Everything I'm trying seems inelegant.
我当然可以在存储的 proc 中动态生成 sql，然后执行它 (bleh)，但那时我想知道我是否获得了什么.
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.
以某种智能方式重构表名，比如将它们全部放在一个表中，将不同的名称作为一个新列将是解决所有这些问题的好方法，几个人直接指出，或暗示.遗憾的是，在这种情况下它不是一种选择.
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.
推荐答案
首先，你应该永远不要在客户端应用程序上这样编写 SQL 命令，就是什么是 SQL 注入.(对于没有自己的权限的管理工具来说是可以的，但对于共享使用的应用程序来说则不行).
First of all, you should NEVER do SQL command compositions on a client app like this, that's what SQL Injection is. (Its OK for an admin tool that has no privs of its own, but not for a shared use application).
其次，是的，对存储过程的参数化调用既干净又安全.
Secondly, yes, a parametrized call to a Stored procedure is both cleaner and safer.
然而，由于您需要使用动态 SQL 来执行此操作，您仍然不希望在执行的查询的文本中包含传递的字符串.相反，您希望使用传递的字符串来查找实际 表的名称，用户应该允许以这种方式查询.
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 actual tables that the user should be allowed to query in the way.
这是一个简单的例子:
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
Some have fairly asked why this is safer. Hopefully, little Bobby Tables can make this clearer: 0
回答更多问题:
QUOTENAME 本身并不能保证安全.MS 鼓励我们使用它，但他们并没有保证它不会被黑客打败.仅供参考，真正的安全就是保证.使用 QUOTENAME 查找表是另一回事，它牢不可破.
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.
QUOTENAME 对于这个例子来说不是绝对必要的，通常只对 INFORMATION_SCHEMA 进行查找翻译就足够了.QUOTENAME 在这里是因为包含完整和正确的解决方案是安全的好形式.此处的 QUOTENAME 实际上是防止出现一个独特但类似的潜在问题，称为潜在注入.
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.
<小时>
我应该注意到，您可以对动态列名和 INFORMATION_SCHEMA.COLUMNS 表执行相同的操作.
您还可以通过使用参数化 SQL 查询来绕过对存储过程的需求(请参阅此处:https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.8).但我认为存储过程为此类情况提供了更易于管理且不易出错的安全设施.
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.
这篇关于我应该如何将表名传递给存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
