sql - 在 T-SQL 中使用任意数量的参数

Question

是否可以创建一个带任意数量参数的参数化 SQL 语句？我试图让用户根据多个关键字过滤列表，每个关键字用分号分隔。因此，输入将类似于“Oakland;City;Planning”，而 WHERE 子句将与以下内容等效:

WHERE ProjectName LIKE '%Oakland%' AND ProjectName Like '%City%' AND ProjectName Like '%Planning%'

用连接创建这样一个列表真的很容易，但由于 SQL 注入(inject)漏洞，我不想采用这种方法。我有哪些选择？我是否创建了一堆参数并希望用户永远不要尝试使用我定义的更多参数？或者有没有办法安全地动态创建参数化 SQL？

性能不是什么大问题，因为该表现在只有大约 900 行，而且增长不会很快，每年可能会增加 50 到 100 行。

Answer 1

一个基本的概念验证...实际代码会更少，但由于我不知道您的表/字段名称，这是完整的代码，所以任何人都可以验证它是否有效，调整它等。

--Search Parameters

DECLARE @SearchString VARCHAR(MAX)
SET @SearchString='Oakland;City;Planning' --Using your example search
DECLARE @Delim CHAR(1)
SET @Delim=';' --Using your deliminator from the example

--I didn't know your table name, so I'm making it... along with a few extra rows...

DECLARE @Projects TABLE (ProjectID INT, ProjectName VARCHAR(200))
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 1, 'Oakland City Planning'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 2, 'Oakland City Construction'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 3, 'Skunk Works'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 4, 'Oakland Town Hall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 5, 'Oakland Mall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 6, 'StackOverflow Answer Planning'

--*** MAIN PROGRAM CODE STARTS HERE ***

DECLARE @Keywords TABLE (Keyword VARCHAR(MAX))

DECLARE @index int 
SET @index = -1 

--Each keyword gets inserted into the table
--Single keywords are handled, but I did not add code to remove duplicates
--since that affects performance only, not the result.

WHILE (LEN(@SearchString) > 0) 
  BEGIN  
    SET @index = CHARINDEX(@Delim , @SearchString)  
    IF (@index = 0) AND (LEN(@SearchString) > 0)  
      BEGIN   
        INSERT INTO @Keywords VALUES (@SearchString)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @Keywords VALUES (LEFT(@SearchString, @index - 1))   
        SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index))  
      END  
    ELSE 
      SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index)) 
END


--This way, only a project with all of our keywords will be shown...

SELECT * 
FROM @Projects
WHERE ProjectID NOT IN (SELECT ProjectID FROM @Projects Projects INNER JOIN @Keywords Keywords ON CHARINDEX(Keywords.Keyword,Projects.ProjectName)=0)

我决定将几个不同的答案混合在一起:-P

这假设您将作为 VARCHAR(MAX) 传入搜索关键字的分隔字符串列表(通过 @SearchString 传入)，这 -- 实际上 -- 你会'关键字搜索不会受到限制。

每个关键字都从列表中分解并添加到关键字表中。您可能希望添加代码以删除重复的关键字，但在我的示例中不会有任何影响。只是效果稍差一些，因为理想情况下，我们只需要对每个关键字进行一次评估。

从那里，任何不属于项目名称的关键字都会从列表中删除该项目...

所以搜索“Oakland”会得到 4 个结果，但“Oakland;City;Planning”只会得到 1 个结果。

您还可以更改分隔符，因此可以使用空格代替分号。或者任何漂浮在你的船上的东西......

此外，由于有连接和什么不是动态 SQL，它不会像您担心的那样冒 SQL 注入(inject)的风险。