为SELECT子句创建参数化字段名

本文关键字：字段 参数 创建 SELECT 子句 | 更新日期: 2023-09-27 17:53:27

我目前正在研究一个数据API，我想根据请求的字段名生成一个SQL查询。这些字段名将是公开可用的，用户还可以使用别名来重命名API返回的数据。

这些表中可用的记录不会被归档为"top secret"，但我仍然希望通过使用参数化字段名和别名来防止任何SQL注入，以避免人们添加不需要的数据。

DECLARE @ requestdfieldname NVARCHAR(max) = 'FirstName';

声明@alias NVARCHAR(max) = '名';

SELECT @ requestdfieldname AS @alias FROM MyTable

在WHERE子句和其他子句中使用参数的例子很多，这些子句涉及到要与字段匹配/分配/设置的值…然而，我找不到任何例子涉及一个参数化字段名/别名在SQL服务器(有一些关于JDBC和MySQL的问题，但没有SQL服务器)

是否有一种方法来参数化字段名，或者我是否应该考虑构建一个中间接口来保存用户可以请求的每个可用字段的列表?(我知道第二个选项使用得很多，但我们有很多表，它们的结构会定期改变)。

为SELECT子句创建参数化字段名

使用varchar参数获取列列表和表名。
然后根据sys.columns表检查这些参数的值。
如果所有列都匹配，那么您可以安全地使用这些值来创建动态SQL并执行它。如果您还需要用户通过构建where子句的条件，则对其中的列执行相同的测试。

现在我已经创建了一个示例代码，我认为这可能不是您的最佳解决方案，除非您可以找到一种为每个表动态创建存储过程的方法。这样做的原因是where子句，以及每个表具有不同数据类型的不同数量的列这一事实，使得where子句参数列表的创建非常依赖于表。(实际上，选择参数数量的创建也取决于表)。
因此，我不确定这是否是一个实用的解决方案，但对我来说，这是一个有趣的t-sql挑战尽管如此，我已经创建了代码示例，也许有人会找到使用这样一个过程的方法，所以我将分享它:

首先，示例表的DDL:

CREATE TABLE [dbo].[TblDays](
    [Day_Id] [int] IDENTITY(1,1) NOT NULL,
    [Day_Date] [date] NOT NULL,
    [Day_Name] [nchar](10) NOT NULL,
 CONSTRAINT [PK_TblDays] PRIMARY KEY CLUSTERED 
(
    [Day_Id] ASC
)

和一个安全的动态SQL存储过程的示例:

CREATE PROCEDURE DynamicalySelectFromTblDays
(
    @ErrorMessage varchar(100) output,
    @SelectCol1 sysname,
    @SelectCol2 sysname = null,
    @SelectCol3 sysname = null,
    @WhereCol1 sysname = null,
    @WhereCol2 sysname = null,
    @WhereCol3 sysname = null,
    @WhereValue1 int = null,
    @WhereValue2 date = null,
    @WherValue3 nchar(10) = null
)
AS
DECLARE @ExceptedColumnCount int,
        @ActualColumnsCount int,
        @TableName sysname = 'TblDays',
        @SQL varchar(max)
-- get the number of columns expcected to get back from sys.columns
SELECT @ExceptedColumnCount = COUNT(*) 
FROM (VALUES (@SelectCol1), (@SelectCol2), (@SelectCol3)) as x(a)
WHERE a is not null 
-- get the number of columns from sys.columns
SELECT @ActualColumnsCount = COUNT(*)
FROM sys.columns c
INNER JOIN sys.tables t ON(c.object_id = t.object_id)
WHERE t.name = @TableName
AND c.name IN(@SelectCol1, @SelectCol2, @SelectCol3)
-- only if we get all of the non null columns back from the sys.columns table
IF @ExceptedColumnCount = @ActualColumnsCount AND (@ExceptedColumnCount = 0 OR @ActualColumnsCount > 0)
BEGIN
    -- same test for where clause columns
    SELECT @ExceptedColumnCount = COUNT(*) 
    FROM (VALUES (@WhereCol1), (@WhereCol2), (@WhereCol3)) as x(a)
    WHERE a is not null 
    SELECT @ActualColumnsCount = COUNT(*)
    FROM sys.columns c
    INNER JOIN sys.tables t ON(c.object_id = t.object_id)
    WHERE t.name = @TableName
    AND c.name IN(@WhereCol1, @WhereCol2, @WhereCol3)
    IF @ExceptedColumnCount = @ActualColumnsCount AND (@ExceptedColumnCount = 0 OR @ActualColumnsCount > 0)
    BEGIN
        -- dynamically build the sql statement:
        SET @SQL = 'SELECT '+ COALESCE(@SelectCol1 +', ', '') + COALESCE(@SelectCol2 +', ', '')+ COALESCE(@SelectCol3, '') +
                   ' FROM '+ @TableName 
        IF COALESCE(@WhereCol1, @WhereCol2, @WhereCol3) IS NOT NULL
        BEGIN
            SET @SQL = @SQL + ' WHERE '
            IF @WhereCol1 IS NOT NULL 
                SET @SQL = @SQL + @WhereCol1 +' = '+ CAST(@WhereValue1 as varchar(10))
            IF @WhereCol2 IS NOT NULL 
                SET @SQL = @SQL + ' AND ' + @WhereCol2 +' = '''+ CONVERT(char(10), @WhereValue2, 120) +''''
            IF @WhereCol3 IS NOT NULL 
                SET @SQL = @SQL + ' AND ' + @WhereCol3 +' = '''+ @WherValue3 +''''
        END
        PRINT @SQL
        -- EXEC(@SQL) -- Commented out since it's better to print first and look at the results, and only then execute it.
    END
    ELSE
    BEGIN
        -- perhaps using raise error instead would suit your needs better
        SELECT @ErrorMessage = 'where columns does not match table columns'
    END
END
ELSE
BEGIN
    -- perhaps using raise error instead would suit your needs better
    SELECT @ErrorMessage = 'select columns does not match table columns'
END
GO

当然，还有一个示例执行代码:

DECLARE @ErrorMessage varchar(100),
        @SelectCol1 sysname = 'Day_Id',
        @SelectCol2 sysname = 'Day_Date',
        @SelectCol3 sysname = 'Day_Name',
        @WhereCol1 sysname = 'Day_Id',
        @WhereCol2 sysname = 'Day_Date',
        @WhereCol3 sysname = null,
        @WhereValue1 int = 1,
        @WhereValue2 date = convert(date, '14/04/2015', 103),
        @WherValue3 nchar(10) = null
EXEC DynamicalySelectFromTblDays    @ErrorMessage output, 
                                    @SelectCol1, 
                                    @SelectCol2, 
                                    @SelectCol3,
                                    @WhereCol1,
                                    @WhereCol2,
                                    @WhereCol3,
                                    @WhereValue1,
                                    @WhereValue2,
                                    @WherValue3

PRINT @ErrorMessage

所有这些混乱的结果是:

SELECT Day_Id, Day_Date, Day_Name FROM TblDays WHERE Day_Id = 1 AND Day_Date = '2015-04-14'

你可以获取每个表的列列表到你的c#代码中，并在那里构建动态查询。这可能是一个更短更快的代码。

相关文章：