我们正在尝试更新我们经典的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注入的保护的最佳方法是什么？

解决方案

我们可能不希望听到此消息，但最好还是先对代码动态地构造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会挑剔可能的输入的许多可能排列。

我们在这里有3个选择。

• 使用转换列表表并将其联接的函数。因此，我们将拥有类似的东西。

SELECT * 
FROM calendar c
   JOIN dbo.fnListToTable(@Keywords) k 
       ON c.keyword = k.keyword

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

CREATE PROC spTest
@Keyword1 varchar(100),
@Keyword2 varchar(100),
....

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

• 除非我们需要它，否则我们可以简单地删除[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结果提取匹配的数据库行。

我们可以尝试以下方法：

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