动态 SQL - 搜索查询 - 可变数量的关键字

Question

我们正在尝试更新我们的经典 asp 搜索引擎以保护它免受 SQL 注入。 我们有一个 VB 6 函数，它通过根据各种搜索参数将查询连接在一起来动态构建查询。 我们已将其转换为存储过程，对除关键字之外的所有参数使用动态 SQL。

关键字的问题在于用户提供的单词数量可变，我们希望为每个关键字搜索几列。 既然我们无法为每个关键字创建单独的参数，那么如何构建安全的查询呢？

示例：

@CustomerId AS INT
@Keywords AS NVARCHAR(MAX)

@sql = 'SELECT event_name FROM calendar WHERE customer_id = @CustomerId '

--(loop through each keyword passed in and concatenate)

@sql = @sql + 'AND (event_name LIKE ''%' + @Keywords + '%'' OR event_details LIKE ''%' + @Keywords + '%'')'

EXEC sp_executesql @sql N'@CustomerId INT, @CustomerId = @CustomerId

处理此问题并保持 SQL 注入保护的最佳方法是什么？

Answer 1

您可能不喜欢听到这个，但在对数据库发出查询之前，最好返回到在代码中动态构建 SQL 查询。 如果您在 SQL 字符串中使用参数占位符，您将获得针对 SQL 注入攻击的保护。

示例：

string sql  = "SELECT Name, Title FROM Staff WHERE UserName=@UserId";
using (SqlCommand cmd = new SqlCommand(sql))
{
  cmd.Parameters.Add("@UserId", SqlType.VarChar).Value = "smithj";

您可以根据需要查询的列集构建 SQL 字符串，然后在字符串完成后添加参数值。 这样做有点痛苦，但我认为这比使用非常复杂的 TSQL 要容易得多，因为 TSQL 会取消许多可能输入的可能排列。

Answer 2

这里你有 3 个选择。

1. 使用转换列表的函数并加入其中。 所以你会得到这样的东西。

   <前><代码>选择 *
   FROM 日历 c
   JOIN dbo.fnListToTable(@Keywords) k
   ON c.keyword = k.keyword

2. 有一组固定的参数，并且最多只允许搜索N个关键字

   CREATE PROC spTest 
     @关键字1 varchar(100), 
     @Keyword2 varchar(100), 
     ....  
     

3. 在 TSQL 中编写转义字符串函数并转义您的关键字。

Answer 3

• 除非您需要它，否则您可以简单地删除 [a-zA-Z ] 中不存在的任何字符 - 其中大多数内容不会出现在搜索中，并且您不应该能够以这种方式注入，也不应该这样做你必须担心关键字或类似的东西。 但是，如果您允许引号，则需要更加小心。

• 与 sambo99 的 #1 类似，您可以将关键字插入到临时表或表变量中并连接到它（甚至使用通配符），而没有注入的危险：

这并不是真正的动态：

SELECT DISTINCT event_name
FROM calendar
INNER JOIN #keywords
    ON event_name LIKE '%' + #keywords.keyword + '%'
    OR event_description LIKE '%' + #keywords.keyword + '%'

• 您实际上可以生成具有大量参数的 SP，而不是手动编码（根据您在搜索编码中的偏好，将默认值设置为 '' 或 NULL）。 如果您发现需要更多参数，那么增加它生成的参数数量会很简单。

• 您可以将搜索移动到数据库外部的全文索引（例如 Lucene），然后使用 Lucene 结果拉取匹配的数据库行。

Answer 4

你可以试试这个：

SELECT * FROM [tablename] WHERE LIKE % +keyword%