参数化SQL与动态WHERE列

Question

我试图编写简单的筛选功能，允许用户输入要筛选的列和值。困难的部分是动态选择要筛选的列。

我在网上找到了几个解决方案，但不确定要实施哪个。我更倾向于强调性能而非可维护性。如有任何意见，烦请指教。

假设我有一个名为“t”的表，它有5个VARCHAR列：“c1”，“c2”，“c3”，“c4”和“c5”。

解决方案1-简单方法

我可以使用动态SQL。类似以下内容：

DECLARE @sql VARCHAR(MAX) = 'SELECT * FROM t WHERE ' + @columnName + ' = ''' + @columnValue + ''';'
EXEC (@sql);

这大概意味着：

SELECT *
FROM t
WHERE c1 = 'asdf'
;

我不想使用这种解决方案，原因如下。我主要是将其作为一个简单的参考点，然后再深入探究。

1. 它不能防止SQL注入。
2. 即使我对columnValue进行参数化，每个列都会有5个不同的执行计划缓存，因为你无法对@columnName进行参数化。

解决方案2 - OR语句

可以使用一系列只有两个参数的OR语句。所以假设：

@columnName = 'c1'
@columnValue = 'asdf'

然后SQL将变为：

SELECT *
FROM t
WHERE (@columnName = 'c1' AND c1 = @columnValue)
  OR (@columnName = 'c2' AND c2 = @columnValue)
  OR (@columnName = 'c3' AND c3 = @columnValue)
  OR (@columnName = 'c4' AND c4 = @columnValue)
  OR (@columnName = 'c5' AND c5 = @columnValue)
  OR (@columnName IS NULL AND 0 = 0)
;

在可能的情况下，我通常尽量避免使用OR。我记得在某个地方读到它存在性能问题，但我不是DBA，无法证实。你有什么想法吗？

解决方案3 - COALESCE

这种解决方案依赖于每列都有一个参数。因此，参数应该类似于：

@c1 = 'asdf';
@c2 = NULL;
@c3 = NULL;
@c4 = NULL;
@c5 = NULL;

SQL的意思是：

SELECT *
FROM t
WHERE c1 = COALESCE(@c1, c1)
  AND c2 = COALESCE(@c2, c2)
  AND c3 = COALESCE(@c3, c3)
  AND c4 = COALESCE(@c4, c4)
  AND c5 = COALESCE(@c5, c5)
;

有人对实现哪种方法有意见吗？我倾向于使用COALESCE，但是我没有具体的数据或经验。也许有更好的方法吗？

- user1886415

---

您可以使用 sys.columns 视图来验证列名。 - HABO

你尝试过查询并调查执行计划了吗？ - Möoz

Answer 1

最安全的方法：

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM dbo.t WHERE ' 
 + QUOTENAME(@columnName) + ' = @ColumnValue;';

EXEC sp_executesql @sql, N'@ColumnValue VARCHAR(255)', @ColumnValue;

为了进一步防止SQL注入，您可以首先检查：

IF @columnName NOT IN (N'c1',N'c2',N'c3',N'c4',N'c5')
BEGIN
  RAISERROR('Nice try! %s is not valid.', 11, 1, @columnName);
  RETURN;
END

就像 @HABO 建议的那样，使用 sys.columns 目录视图：

IF NOT EXISTS 
(
   SELECT 1 FROM sys.columns WHERE name = @ColumnName
     AND [object_id] = OBJECT_ID('dbo.t')
)
BEGIN
  RAISERROR('Nice try! %s is not valid.', 11, 1, @columnName);
  RETURN;
END

特别是当与“针对临时工作负载进行优化”组合使用时，可能有5个不同的执行计划是可以接受的 - 因为它们毕竟是5个不同的查询，根据不同列上的索引、数据在这些列中的分布等因素，可能会有不同的优化方式。

除非您每次都要支付编译成本，否则您的OR和COALESCE版本将被限制使用相同的计划，无论提供哪个列，因此对于某些情况可能效果很好，但对于其他情况可能效果不佳。而且每个人都得到的计划都不是基于最佳实践，而是基于发送的参数顺序。

此外，如果您担心性能问题，也许不要使用SELECT * - 尤其是如果您不需要所有列。即使您确实需要，也永远不知道什么时候有人将一个blob或geometry或XML或其他昂贵的列添加到表中，并且您的代码检索了它，尽管它并不关心它。

- Aaron Bertrand