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

问题描述

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

Is it possible to create a parameterized SQL statement that will taken an arbitrary number of parameters? I'm trying to allow users to filter a list based on multiple keywords, each separated by a semicolon. So the input would be something like "Oakland;City;Planning" and the WHERE clause would come out something equivalent to the below:

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

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

It's really easy to create such a list with concatenation, but I don't want to take that approach because of the SQL injection vulnerabilities. What are my options? Do I create a bunch of parameters and hope that users never try to use more parameters that I've defined? Or is there a way to create parameterized SQL on the fly safely?

性能不是什么大问题，因为该表目前只有大约 900 行，而且不会快速增长，每年可能增长 50 到 100 行.

Performance isn't much of an issue because the table is only about 900 rows right now, and won't be growing very quickly, maybe 50 to 100 rows per year.

推荐答案

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

A basic proof-of-concept... Actual code would be less, but since I don't know your table/field names, this is the full code, so anyone can verify it works, tweak it, etc.

--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

I decided to mix a few different answers together into one :-P

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

This assumes you'll pass in a delimited string list of search keywords (passed in via @SearchString) as a VARCHAR(MAX), which -- realistically -- you won't run into a limit on for keyword searches.

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

Each keyword is broken down from the list and added into a keyword table. You'd probably want to add code to remove out duplicate keywords, but it won't hurt in my example. Just slightly less effective, since we only need to evaluate once per keyword, ideally.

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

因此，搜索Oakland"会得到 4 个结果，但Oakland;City;Planning"只会得到 1 个结果.

So searching for "Oakland" gives 4 results but "Oakland;City;Planning" gives only 1 result.

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

You can also change the delimiter, so instead of a semi-colon, it can use a space. Or whatever floats your boat...

此外，由于加入了连接和什么而不是动态 SQL，它不会像您担心的那样冒 SQL 注入的风险.

Also, because of the joins and what not instead of Dynamic SQL, it doesn't run the risk of SQL Injection like you were worried about.

这篇关于在 T-SQL 中使用任意数量的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！