如何将表名列表传递到SQL Server中的存储过程

Question

我正试图通过将tableName作为参数传递给存储过程来获取特定表名中的数据。

CREATE PROCEDURE schemaName.spDynamicTableName
@tableName NVARCHAR(100)
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM ' + @tableName
EXECUTE sp_executesql @sql
END;
--> EXEC schemaName.spDynamicTableName  'Employee';

现在，我如何将表名列表传递给过程，以便过程迭代表名列表并从所有表中获取数据？

Answer 1

好的，让我们从当前设置中的问题开始。首先，听起来你有一个设计缺陷。很可能是使用表的名称来推断应该在列中的信息。例如，可能每个客户端都有不同的表。在这种情况下，客户端的名称应该是单数表中的一列。这使查询数据变得更加容易，并允许良好地用于关键约束。

接下来是你的程序。这是一个巨大的安全漏洞。您的动态对象的值是未被清除或验证，这意味着有人(恶意(有近100个字符来破坏您的实例并将SQL注入其中。有很多文章解释了如何安全地注入(包括我自己(，我将在这里介绍几个过程。

请注意，根据我最初的段落，您可能真的有设计缺陷，所以这个才是真正的解决方案。不过，我们无法在这里的答案中解决这个问题，因为我们没有您正在处理的数据的详细信息。

固定注射

安全注射

注入动态对象名称的基本原理是使其安全。您可以使用QUOTENAME；它既分隔标识对象名称，又转义任何需要的字符。例如，QUOTENAME(N'MyTable')将返回具有值[MyTable]的nvarchar，而QUOTENAME(N'My Alias"; SELECT * FROM sys.tables','"')将返回值"My Alias""; SELECT U FROM sys.tables"的nvarchar。

正在验证值

您可以通过检查对象是否确实存在来轻松验证值。我更喜欢对sys对象执行此操作，所以类似这样的操作会起作用：

SELECT @SchemaName = s.[name],
@TableName = t.[name]
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE s.[name] = @Schema --This is a parameter
AND t.[name] = @Table; --This is a parameter

因此，如果FROM不返回值，则SELECT中的2个变量将不会分配值，也不会运行SQL(作为{String} + NULL = NULL(。

解决方案

表类型参数

因此，为了允许多个表，我们需要一个表类型参数。我会在列中创建一个同时具有模式和表名称的表，但我们可以默认模式名称。

CREATE TYPE dbo.Objects AS table (SchemaName sysname DEFAULT N'dbo',
TableName sysname); --sysname is a sysnonym for nvarchar(128) NOT NULL

您可以将DECLARE和INSERT放入TYPE中，如下所示：

DECLARE @Objects dbo.Objects;
INSERT INTO @Objects (TableName)
VALUES(N'test');

创建动态语句

假设您使用的是受支持的SQL Server版本，您将可以访问STRING_AGG；这将从过程中删除任何类型的循环，这对性能非常有利。如果您只使用扩展支持的版本，则使用"；旧的"；CCD_ 16方法。

这意味着您可以获取这些值，并按照以下行创建一个动态语句：

SET @SQL = (SELECT STRING_AGG(N'SELECT * FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';',' ')
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN @Objects O ON s.name = O.SchemaName
AND t.name = O.TableName);

存储程序

把所有这些放在一起，这将给你一个看起来像这样的程序：

CREATE PROC schemaName.spDynamicTableName @Objects dbo.Objects AS
BEGIN
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = (SELECT STRING_AGG(N'SELECT N' + QUOTENAME(t.[name],'''') + N',* FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';',@CRLF) --I also inject the table's name as a column
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN @Objects O ON s.name = O.SchemaName
AND t.name = O.TableName);
EXEC sys.sp_executesql @SQL;
END;

然后你会按照以下方式执行：

DECLARE @Objects dbo.Objects;
INSERT INTO @Objects (SchemaName,TableName)
VALUES(N'dbo',N'MyTable'),
(N'dbo',N'AnotherTable');
EXEC schemaName.spDynamicTableName @Objects;

Answer 2

这个接受逗号分隔的表列表，并通过简单的QUOTENAME转义来防止SQL注入(但不确定这是否足够(：

IF OBJECT_ID('dbo.spDynamicTableName') IS NOT NULL DROP PROC dbo.spDynamicTableName
GO
/*
EXEC dbo.spDynamicTableName 'Students,Robert--
DROP TABLE Students'
*/
CREATE PROC dbo.spDynamicTableName
@tableName NVARCHAR(100)
AS
BEGIN
DECLARE @sql nvarchar(max)

SELECT @sql = STRING_AGG('SELECT * FROM ' + QUOTENAME(value), ';')
FROM STRING_SPLIT(@tableName, ',')
--PRINT @sql
EXEC dbo.sp_executesql @sql
END;
GO

Answer 3

有两种方法可以做到这一点：使用一个包含所需名称的字符串，并用一个特殊字符分隔，如：

Table1, Table2, Table3

并将其拆分到存储过程中(检查此项(

第二种方法：如下拼写错误：

CREATE TYPE [dbo].[StringList] AS TABLE
(
[TableName] [NVARCHAR(50)] NULL
)

为存储过程添加一个参数作为StringList:

CREATE PROCEDURE schemaName.spDynamicTableName
@TableNames [dbo].[StringList] READONLY,    
AS
BEGIN

END;

然后使用以下代码测量其长度，并重复循环：：

DECLARE @Counter INT
DECLARE @TableCount INT
SELECT @TableCount = Count(*), @Counter = 0 FROM @TableNames
WHILE @Counter < @TableCount
BEGIN
SELECT @TableName = Name 
FROM @TableNames
ORDER BY Name
OFFSET @Counter ROWS FETCH NEXT 1 ROWS ONLY
SET @sql = 'SELECT * FROM ' + @TableName
EXECUTE sp_executesql @sql
SET @Counter = @Counter + 1
END