Using an arbitrary number of parameters in 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:

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?

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.

回答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, '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)

I decided to mix a few different answers together into one :-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.

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.

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.

回答2:

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

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

回答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.

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

回答4:

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

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.

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

    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.

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

回答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)

回答7:

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

回答8:

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

回答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.