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

Question

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

这些表中可用的记录不会作为“顶级机密”归档，但我仍然希望通过使用参数化的字段名和别名来防止任何SQL注入，以避免人们添加不必要的数据。

声明@requ脂dFieldName NVARCHAR(max) = 'FirstName'； 宣布@别名NVARCHAR(max) =‘名字’； 从MyTable中选择@requ脂dFieldName作为@别名

在WHERE子句和涉及要与字段匹配/赋值/设置的值的其他子句中，有许多使用参数的示例.但是，我在server中找不到任何涉及参数化字段名/别名的例子(关于JDBC和MySQL有一些这样的问题，但Server中没有)

是否有一种将字段名参数化的方法，或者我是否应该考虑构建一个中间接口，以保存用户可以请求的每个可用字段的列表？(我知道第二种选择是经常使用的，但我们有很多桌子，它们的结构会有规律的变化。)

Answer 1

使用varchar参数获取列列表和表名。

然后根据sys.columns表检查这些参数的值。

如果所有列都匹配，则可以安全地使用这些值创建动态SQL并执行它。如果还需要用户传递构建where子句的条件，则对其中的列执行相同的测试。

Update现在我已经创建了一个示例代码，我认为这可能不是最好的解决方案，除非您能够找到一种为每个表动态创建存储过程的方法。其原因是where子句，以及每个表具有不同数据类型的不同数量的列，使得where子句参数列表的创建非常依赖于表。(实际上，select参数的创建也取决于表)。

因此，我不确定这是否是一个实用的解决方案，但对我来说，这是一个有趣的to挑战，尽管如此，我已经创建了代码示例，也许有人会找到一种方法来使用这样的过程，所以我将分享它：

首先，示例表的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#代码，并在那里构建动态查询。它可能是一个更短更快的代码。