問題描述

是否可以創建一個帶任意數量參數的參數化 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模板網！