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

Question

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

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

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

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

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

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表，该表的结构是通过执行存储过程获得的。所以我不能在页面中放入任何静态查询。

Answer 1

这有点暗算，但表类型如何？然后，您可以在此基础上构建WHERE子句。正如我所说，在黑暗中捅一刀，然而，这至少可以让你走上正确的道路：

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

Answer 2

考虑使用可信来源(硬编码或元数据表(的列名和参数名在应用程序代码中构建和执行参数化查询。在这里使用proc没有什么价值。