Geek asks and answers

Using an arbitrary number of parameters in T-SQL

Is it possible to create a parameterized SQL statement that will accept an arbitrary number of parameters? I am trying to allow users to filter a list based on several keywords, each of which is separated by a semicolon. Thus, the input will be something like "Auckland, City, Planning" and the WHERE clause will come out something like the following:

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

It is very simple to create such a list with concatenation, but I do not want to use this approach due to SQL injection vulnerabilities. What are my options? Create a set of parameters and hope that users never try to use more parameters that I defined? Or is there a way to safely create parameterized SQL on the fly?

Performance is not very important, because now the table is about 900 rows and will not grow very quickly, maybe from 50 to 100 rows per year.

+3
source share
9 answers

Basic proof of concept ... Actual code will be less, but since I don’t know your table and field names, this is the complete code, so anyone can check its operation, configure 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

, ( @SearchString) VARCHAR (MAX), - - ' t .

. , , , , . , .

, , ...

, "Oakland" 4 , "Oakland, City; Planning" 1 .

, . - ...

, - , Dynamic SQL, SQL Injection, .

+6

CONTAINS CONTAINSTABLE "" .

1K, .

+2

, comas (csv style), .

, , , T-SQL, -.

+1

XML ? ...

PK , temp. , PK PKTempTable.

XML...

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

, NHibernate, .

Frans Bouma procs vs dynamic sql, SQL

0

procs, , , ... proc, sproc ( T-SQL, )

0

SQL Server 2008, ,

0

, SQL Server: ~ 2000 .

0
source

As with some other answers, you can parse a string or an XML document. See this great link that demonstrates both methods with SQL Server.

0
source

More articles:


All Articles