使用动态列保护动态 SQL Where 子句-解网

问：

我正在处理一个项目，我需要创建一个动态网页，该网页由一个表组成，该表的列结构和数据取决于存储过程选择结果。

下面是要使用的存储过程的示例：

CREATE PROCEDURE dbo.usp_CustomerView
    @Address VARCHAR(1000)
AS
    SELECT *
    FROM Customer
    WHERE CustomerAddress LIKE '%' + @Address + '%'

当用户筛选表中的地址列时，此过程正在起作用。

问题是，这个过程是静态的。如果我想过滤其他字段，例如或，我需要创建另一个参数并像这样重新制作过程。CustomerNameCustomerPhone

CREATE PROCEDURE dbo.usp_CustomerView
    @Address VARCHAR(1000),
    @Name VARCHAR(1000),
    @Phone VARCHAR(1000)
AS
    SELECT *
    FROM Customer
    WHERE CustomerAddress LIKE '%' + @Address + '%'
      AND CustomerName LIKE '%' + @Name + '%'
      AND CustomerPhone LIKE '%' + @Phone + '%'

我正在尝试创建一个动态过程来执行基于字符串过滤器的动态 SQL 查询，如下所示。

CREATE PROCEDURE dbo.usp_CustomerView
    @Filter VARCHAR(MAX)
AS
    DECLARE @sql VARCHAR(MAX)
    SET @sql = 'SELECT * FROM Customer WHERE ' + @Filter
    EXEC @sql

然后像这样从我的网站动态生成一个字符串过滤器。

Dim filterString As String = ""
For Each filter As DataFilter In e.Filter   'Here Filter give Array of filtered user input
    If Not String.IsNullOrEmpty(filterString) Then filterString &= " AND "
    filterString &= filter.Property & " = '" & filter.Value & "'"
Next
Command.CommandText = "usp_CustomerView @Filter"
Command.Parameters.AddWithValue("@Filter", filterString)
'Read Return Value from Command

这是有效的，但我担心安全性，因为它可以很容易地注入。有没有人尝试过创建一个具有良好安全性的动态 where 子句？我认为这是一件很常见的事情。但我找不到任何线索。

有什么建议吗？

更多解释：

它的实现实际上更复杂。我想制作一个动态网页，其中我只在数据库中设置了一个存储过程的字符串名称，该页面将自动生成一个html表，其结构是通过执行存储过程获得的。所以我不能在页面中放置任何静态查询。

.NET SQL-Server SQL注入

USE Sandbox;
GO

CREATE TYPE dbo.WhereClause AS TABLE (ColumnName sysname NOT NULL,
                                      ColumnValue sql_variant NOT NULL);
GO

CREATE PROC dbo.SampleProc @WhereClause WhereClause READONLY AS
BEGIN

    DECLARE @SQL nvarchar(MAX);

    SET @SQL = N'SELECT *' + NCHAR(10) + 
               N'FROM Customer'

    SET @SQL = @SQL + 
               ISNULL(NCHAR(10) +N'WHERE' +
                      STUFF((SELECT NCHAR(10) + N'  AND ' + QUOTENAME(WC.ColumnName) + N' LIKE ''%'' + ' + QUOTENAME(CONVERT(varchar(100),ColumnValue),'''') + ' + ''%'''
                             FROM @WhereClause WC
                             FOR XML PATH(N'')),1,6,N''),N'') + N';';
    PRINT @SQL; --Your best debugging friend (provided you don't go over an nvarchar(4000))
    --EXEC sp_executesql @SQL; --Uncomment this to actually run the Dynamic SQL

END

GO

DECLARE @Where WhereClause;
INSERT INTO @Where (ColumnName,
                    ColumnValue)
SELECT N'CustomerAddress', '123 test street';

INSERT INTO @Where (ColumnName,
                    ColumnValue)
SELECT N'CustomerName', 'Joe Bloggs';

INSERT INTO @Where (ColumnName,
                    ColumnValue)
SELECT N'CustomerPhone', '01234 567890';

INSERT INTO @Where (ColumnName,
                    ColumnValue)
SELECT N'CustomerDOB', CONVERT(date,'19810507');

EXEC dbo.SampleProc @Where;
/*
Returns:
SELECT *
FROM Customer
WHERE [CustomerAddress] LIKE '%' + '123 test street' + '%'
  AND [CustomerName] LIKE '%' + 'Joe Bloggs' + '%'
  AND [CustomerPhone] LIKE '%' + '01234 567890' + '%'
  AND [CustomerDOB] LIKE '%' + 'May  7 1981' + '%';

Note that the date isn't ideal. This *could* be a problem.
*/

 DELETE 
 FROM @Where;

 EXEC dbo.SampleProc @Where;
 /*
Returns:
SELECT *
FROM Customer;
*/
GO

DROP PROC dbo.SampleProc;
DROP TYPE dbo.WhereClause;
GO

使用动态列保护动态 SQL Where 子句

Securing Dynamic SQL Where Clause with Dynamic Column

评论

评论

评论