How hard would you try to make your SQL queries secure?

Question

I am in a situation where I am given a comma-separated VarChar as input to a stored procedure. I want to do something like this:

SELECT * FROM tblMyTable 
INNER JOIN /*Bunch of inner joins here*/ 
WHERE ItemID IN ($MyList);

However, you can't use a VarChar with the IN statement. There are two ways to get around this problem:

1. (The Wrong Way) Create the SQL query in a String, like so:

   SET $SQL = ' SELECT * FROM tblMyTable INNER JOIN /*Bunch of inner joins here*/ WHERE ItemID IN (' + $MyList + ');

   EXEC($SQL);

2. (The Right Way) Create a temporary table that contains the values of $MyList, then join that table in the initial query.

My question is:

Option 2 has a relatively large performance hit with creating a temporary table, which is less than ideal.

While Option 1 is open to an SQL injection attack, since my SPROC is being called from an authenticated source, does it really matter? Only trusted sources will execute this SPROC, so if they choose to bugger up the database, that is their prerogative.

So, how far would you go to make your code secure?

Answer 1

I would personally prefer option 2 in that just because a source is authenticated, does not mean you should be letting your guard down. You would leave yourself open to potential rights escalations where an authenticated low lvl user, is able to still execute commands against the database you had not intended.

The phrase you use of 'trusted sources' - it might be better if you assume an X-Files aproach and to trust no-one.

Answer 2

Option 3 is to confirm each item in the list is in fact an integer before concatenating the string to your SQL statement.

Do this by parsing the input string (e.g., split into an array), loop through and convert each value to an int, and then recreate the list yourself before concatenating back to the SQL statement. This will give you reasonable assurance that SQL injection cannot occur.

It is safer to concatenate strings that have been created by your application, because you can do things like check for int, but it also means your code is written in a way that a subsequent developer may modify slightly, thus opening back up the risk of SQL injection, because they do not realize that is what your code is protecting against. Make sure you comment well what you are doing if you go this route.

Answer 3

A third option: pass the values to the stored procedure in an array. Then you can either assemble the comma-separated string in your code and use the dynamic SQL option, or (if your flavour of RDBMS permits it) use the array directly in the SELECT statement.

Answer 4

If someone buggers up the database you might still be getting a call.

A good option that is similar to option two is to use a function to create a table in memory from the CSV list. It is reasonably fast and offers the protections of option two. Then that table can be joined to the Inner Join, e.g.

CREATE FUNCTION [dbo].[simple_strlist_to_tbl] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (str varchar(4000) NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (str)
         VALUES (substring(@list, @pos + 1, @valuelen))
      SELECT @pos = @nextpos
   END
  RETURN
END

Then in the join:

tblMyTable INNER JOIN
simple_strlist_to_tbl(@MyList) list ON tblMyTable.itemId = list.str

Answer 5

What database are you using? in SQL Server you can create a split function that can split a long string and return a table sub-second. you use the table function call like a regular table in a query (no temp table necessary)

You need to create a split function, or if you have one just use it. This is how a split function can be used:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can use the CSV string like this, not temp table necessary:

SELECT * FROM tblMyTable 
INNER JOIN /*Bunch of inner joins here*/ 
WHERE ItemID IN (select ListValue from dbo.FN_ListToTable(',',$MyList));

Answer 6

Why don't You write an CLR split function, that will do all the job nice and easy? You can write user Defined table functions which will return a table doing string splitting with .Net infructure. Hell in SQL 2008 you can even give them hints if they return the strings sorted in any way... like ascending or something which can help the optimizer? Or maybe You can't do CLR integration then You have to stick to the tsql but I personally would go for the CLR soluton