如何在动态SQL查询中设置表名？

Question

我想在一个动态SQL查询中设置表名。对于参数，我尝试了以下方式并取得了成功：

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

现在我想使用参数动态地获取TABLE NAME，但我尝试失败了。请指导我。

- Neo

---

你不能将表名作为参数化。在“SET @SQLQuery”步骤中，你只能手动执行它。 - MatBailie

@MatBailie 实际上你可以将表名作为字符串传递，并使用字符串名称的 exec（它的行为与过程完全相同） - HellBaby

@hellbaby - 而且你仍然无法将表名作为参数提供给sp_executesql，这只能通过将其替换到字符串中来实现。 这些是非常不同的。 参数化允许类型检查、保护免受SQL注入攻击、执行计划重用等。 将字符串替换为其他字符串并没有做到这一点，因此不是参数化。 - MatBailie

@MatBailie，实际上你不需要使用'sp_executesql'命令，在mssql 2008+中只需以这种方式调用即可：exec @ myqueryconcatenatedvariablewithtablename，然后你就可以得到答案了；关于注入 - 也许你是对的（有技能的人可以破解它）。所以最终取决于他需要在哪里使用它... - HellBaby

@HellBaby - 这正是相同的机制 - 你只是将表名替换为字符串，然后执行生成的字符串。在你的示例中，表名从未作为参数出现。 - MatBailie

Answer 1

为了防止SQL注入，我通常尽可能使用函数。在这种情况下，您可以这样做：

...
SET @TableName = '<[db].><[schema].>tblEmployees'
SET @TableID   = OBJECT_ID(TableName) --won't resolve if malformed/injected.
...
SET @SQLQuery = 'SELECT * FROM ' + QUOTENAME(OBJECT_NAME(@TableID)) + ' WHERE EmployeeID = @EmpID' 

- galaxis

---

提示：在组装对象名称到动态 SQL 语句时，最佳实践是使用 QuoteName() 来避免出现奇怪的名称，例如带有空格的 New Table 或保留字 From。 - HABO

Answer 2

表名不能作为参数提供，因此您需要手动构建 SQL 语句，类似于以下方式：

SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID' 

然而，请确保您的应用程序不允许用户直接输入@TableName的值，因为这会使您的查询容易受到SQL注入攻击。有一种可能的解决方案，请参见此答案。

- Dan

---

谢谢，但我真的不想要这个。无法将其作为表名传递给sp_executesql :( - Neo

@Neo，你应该能够使用sp_executesql，因为在设置SQL字符串时，你应该已经插入了正确的表名。这是手动构建SQL字符串的主要原因之一。 - Govind Rai

另一个需要考虑的问题是在变量周围应用QuoteName。这将使用对象包装正确地包装表名，默认为方括号 [...]。 - GoldBishop

Answer 3

试一下这个：

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @TableName AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
SET @TableName = 'tblEmployees'
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

- Saharsh Shah

---

绝对是我正在寻找的完整答案。 - Ji_in_coding

太棒了！完美地解决了我的问题。 - Dan Bechard

容易受到SQL注入攻击。只需传递一个不良的@TableName参数，如“sys.databaeses; Alter Server Role sysadmin Add Member [BadGuy]; --”，你的系统就会崩溃。 - Cody Konior

Answer 4

这是动态获取架构并将其添加到数据库中的不同表以便动态获取其他信息的最佳方法。

选择 @sql = 'insert #tables SELECT ''[''+SCHEMA_NAME(schema_id)+''.''+name+'']'' AS SchemaTable FROM sys.tables'

exec (@sql)

当然，#tables是存储过程中的动态表。最初的回答。

- CA Martin

Answer 5

在@user1172173之前的回答中已经提到了SQL注入漏洞，具体内容如下：

CREATE PROCEDURE [dbo].[spQ_SomeColumnByCustomerId](
@CustomerId int,
@SchemaName varchar(20),
@TableName nvarchar(200)) AS
SET Nocount ON
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @Table_ObjectId int;
DECLARE @Schema_ObjectId int;
DECLARE @Schema_Table_SecuredFromSqlInjection NVARCHAR(125)

SET @Table_ObjectId = OBJECT_ID(@TableName)
SET @Schema_ObjectId = SCHEMA_ID(@SchemaName)
SET @Schema_Table_SecuredFromSqlInjection = SCHEMA_NAME(@Schema_ObjectId) + '.' + OBJECT_NAME(@Table_ObjectId)

SET @SQLQuery = N'SELECT TOP 1 ' + @Schema_Table_SecuredFromSqlInjection + '.SomeColumn 
FROM dbo.Customer 
INNER JOIN ' + @Schema_Table_SecuredFromSqlInjection + ' 
ON dbo.Customer.Customerid = ' + @Schema_Table_SecuredFromSqlInjection + '.CustomerId 
WHERE dbo.Customer.CustomerID = @CustomerIdParam 
ORDER BY ' + @Schema_Table_SecuredFromSqlInjection + '.SomeColumn DESC' 
SET @ParameterDefinition =  N'@CustomerIdParam INT'

EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @CustomerIdParam = @CustomerId; RETURN

- outofcoolnames