Handling Arrays

Stored procedures don’t support arrays as inputs, but there’s a common need to develop procedures that accept an unknown number of input parameters. For example, suppose you need to create a procedure in the Northwind database that accepts an unknown number of order IDs as input and returns information from the Orders table about the requested orders. One of the most obvious solutions is to use dynamic SQL. This option is available in SQL Server 2008, 2005, and 2000.

Implementing the Solution
To use the dynamic SQL solution, run the code in Listing 1 to create the stored procedure usp_getorders. The implementation of the procedure is very simple. The procedure accepts a character string parameter called @arr that holds a comma-separated list of order IDs as input. The procedure constructs a character string with a query against the Orders table in a variable called @sql. In the query’s filter the code concatenates the contents of @arr in the parentheses of the IN predicate. Finally, the code uses the sp_executesql procedure to dynamically execute the code residing in the @sql variable.

I restricted the input parameter @arr to 2,000 characters. If you need to support longer strings, SQL Server 2000 lets you go up to 4,000 characters with the NVARCHAR data type and 8,000 characters with VARCHAR (which will require you to use EXEC instead of sp_executesql). In SQL Server 2005, you can use NVARCHAR(MAX) or VARCHAR(MAX) and go up to the maximum supported large object size (2GB).

Run the following code to test the procedure with two different sets of order IDs:

EXEC dbo.usp_getorders N'10248,10250,10249';
EXEC dbo.usp_getorders
  N'10260,10270,10265,10290';

For each invocation, the procedure constructs a query string in which the comma-separated order IDs appear as constants, then executes it. The query returns information from the Orders table about the requested orders. For example, the first invocation of the procedure returns the output in Table 1.

Limitations
Unfortunately, the dynamic SQL solution has serious disadvantages. These drawbacks include security, performance, and limited control over the order of rows in the output.

Security. The procedure uses dynamic SQL without validating the input, which completely exposes the procedure to SQL injection. To see how easy it is to inject code here, run the following command:

EXEC dbo.usp_getorders N'0); RAISERROR( 'This
  could have been much worse than a RAISERROR
  statement.', 16, 1); --';

The dynamic batch that is constructed by the stored procedure is:

SELECT OrderID, OrderDate, CustomerID, EmployeeID
FROM dbo.Orders
WHERE OrderID IN(0); RAISERROR( 'This could have
  been much worse than a RAISERROR statement.',
  16, 1); --);

In this example I injected a RAISERROR statement to the code for demonstration purposes, but a real injection would likely be much worse. This call to the stored procedure produces an empty set as output because I specified only one order ID that doesn’t exist (order ID 0), followed by the output of the injected RAISERROR statement.

You can try to prevent the injection by inspecting the input. In our case the input should have only digits and commas, so input validation is simple. Add the following code at the beginning of the stored procedure to check whether the input contains any character other than a digit or a comma, and if it does, raise an error and return from the procedure without executing the dynamic batch:

IF @sql LIKE N'%[^0-
9,]%'
BEGIN
  RAISERROR('Input
may contain SQL
injection. Procedure
aborted.', 16, 1);
  RETURN;
END

Run Listing 2 to create the revised procedure.

Continue to page 2