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

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:

是否可以创建一个参数化的SQL语句,该语句将采用任意数量的参数?我试图允许用户根据多个关键字过滤列表,每个关键字用分号分隔。因此,输入将类似于“Oakland; City; Planning”,WHERE子句将出现与下面相同的内容:

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

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?

使用连接创建这样的列表真的很容易,但由于SQL注入漏洞,我不想采用这种方法。我有什么选择?我是否创建了一堆参数并希望用户永远不会尝试使用我定义的更多参数?或者有没有办法安全地创建参数化SQL?

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.

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

9 个解决方案

#1

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, '* 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)

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

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

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.

这假设您将传递一个分隔的搜索关键字字符串列表(通过@SearchString传入)作为VARCHAR(MAX),实际上 - 您不会对关键字搜索设置限制。

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.

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

From there, any keyword that isn't a part of the project name removes that project from the list...

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

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

因此,搜索“奥克兰”给出了4个结果,但“奥克兰;城市;规划”只给出了1个结果。

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

您也可以更改分隔符,因此它可以使用空格而不是分号。或者无论你的船漂浮......

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.

此外,由于连接和什么不是动态SQL,它不会像你担心的那样冒着SQL注入的风险。

#2

You might also want to consider Full Text Search and using CONTAINS or CONTAINSTABLE for a more "natural" search capability.

您可能还需要考虑全文搜索并使用CONTAINS或CONTAINSTABLE来获得更“自然”的搜索功能。

May be overkill for 1K rows, but it is written and is not easily subverted by injection.

对于1K行可能有点过分,但是写入并且不容易被注入破坏。

#3

The trick would usually to simply pass the list as a string separated by comas (csv style), parse that string in a loop and dynamically build the query.

诀窍通常是简单地将列表作为由逗号(csv样式)分隔的字符串传递,在循环中解析该字符串并动态构建查询。

Above post is also right that maybe the best approach is not T-SQL but the business/application layer.

上面的帖子也是正确的,也许最好的方法不是T-SQL而是业务/应用层。

#4

What about using an XML data type to contain the parameters? It can be unbounded and assembled at run time...

使用XML数据类型来包含参数怎么样?它可以在运行时无限制和组装......

I pass in an unknown number of PKs for a table update then pump them into a temp table. It is easy to then update where PK in PKTempTable.

我传入一个未知数量的PK进行表更新,然后将它们泵入临时表。然后很容易更新PKTempTable中PK的位置。

Here is the code to parse the XML data type...

这是解析XML数据类型的代码......

    INSERT INTO #ERXMLRead (ExpenseReportID)
    SELECT ParamValues.ID.value('.','VARCHAR(20)')
    FROM @ExpenseReportIDs.nodes('/Root/ExpenseReportID') as ParamValues(ID)

#5

using a tool like NHibernate will allow you to dynamically construct your queries safely without the need for stored procedures.

使用像NHibernate这样的工具将允许您安全地动态构建查询,而无需存储过程。

Frans Bouma has an excellent article about stored procs vs dynamic sql and what some of the benefits of using an SQL generator are over using hand generated statements

Frans Bouma有一篇关于存储过程与动态sql的优秀文章,使用SQL生成器的一些好处是使用手工生成的语句

#6

If you use stored procs you can include a default value for the parameters, then you can elect to pass them or not pass them in client code, but you still have have to declare them individually in the stored procedure... Also only if you're using a stored proc, you can pass a single parameter as a delimited string of values, and parse out the individual values inside the sproc (There are some "standard" T-SQL functions available that will split out the records into a dynamic table variable for you)

如果您使用存储过程,您可以包含参数的默认值,那么您可以选择传递它们或不在客户端代码中传递它们,但您仍然必须在存储过程中单独声明它们...也仅限于您使用存储过程,您可以将单个参数作为分隔的值字符串传递,并解析出sproc中的各个值(有一些“标准”T-SQL函数可以将记录拆分为动态表变量给你)

#7

If your using SQL server 2008 check out this artical passing a table valued parameter

如果您使用SQL Server 2008检查此artical传递表值参数

#8

Whatever way you go, watch out for SQL Server's parameter limit: ~2000 parameters.

无论你走哪条路,都要注意SQL Server的参数限制:~2000参数。

#9

Similar to some of the other answers, you can parse out a delimited string or an XML document. See this excellent link which demonstrates both methods with SQL Server.

与其他一些答案类似,您可以解析分隔的字符串或XML文档。请参阅此优秀链接,该链接演示了SQL Server的这两种方法。

#1