Safest way to pass collection of values to Stored Procedure designating columns to order by and predicate to filter by

Question

We are building an MVC project that needs to make use of of the MVC DataGrid. As part of that, we are wanting to allow for filtering and ordering of the DataGrid columns. We want this to be handled on the Sql side, with paging. Handling the paging is really straightforward and we've already got that working with our Stored Procedures.

The challenge we are facing now is how to get what columns the user has sorted by, into the stored procedure so we can sort the records during paging. I played with using a Table Type to send in a 'collection' of columns using something like this:

CREATE TYPE [dbo].[SortableEntity] AS TABLE(
    [TableName] [varchar](50) NULL,
    [ColumnName] [varchar](50) NULL,
    [Descending] [bit] NULL
)

CREATE PROCEDURE [dbo].[DoSomethingWithEmployees]
  @SortOrder AS dbo.SortableEntity READONLY
AS
BEGIN
    SELECT [ColumnName] FROM @SortOrder
END

We're using Dapper as our ORM, and we're constrained to using only Stored Procedures by policy. In my Repository, I use the following DataTable to try and insert the records into the SortableEntity which works fine.

var parameters = new DynamicParameters();

// Check if we have anything to sort by
IEnumerable<SortDefinition> sortingDefinitions = builder.GetSortDefinitions();
if (sortingDefinitions.Count() > 0)
{
    var dt = new DataTable();
    dt.Columns.Add(nameof(SortableEntity.TableName));
    dt.Columns.Add(nameof(SortableEntity.ColumnName));
    dt.Columns.Add(nameof(SortableEntity.IsDescending));
    Type tableType = typeof(SortableEntity);
    foreach(SortDefinition sortDefinition in sortingDefinitions)
    {
        var dataRow = dt.NewRow();
        dataRow.SetField(0, sortDefinition.TableName);
        dataRow.SetField(0, sortDefinition.Column);
        dataRow.SetField(2, sortDefinition.IsDescending);

        dt.Rows.Add(dataRow);
    }

    parameters.Add("SortOrder", dt.AsTableValuedParameter(tableType.Name));
}

With this I'm able to get my sorted values into the stored procedure, but I'm concerned with Sql Injection. One way I can see getting around it is to lookup in the sys-columns table to see if the columns given are valid columns before using them. I'm not sure how to go about doing that, and taking the valid columns and applying them to an order by statement in my Stored Procedure. Since we're not using Sql parameter objects for the values being inserted into the DataTable, how do we protect against Sql injection? I know using DynamicParameters will protect us for the values going into the Stored Procedure parameters, but how does that work when the value is a table containing values?

The biggest challenge though is the WHERE clause. We want to pass in a filter from the data grid into the stored procedure, so users can filter out results sets. The idea being that the stored procedure would filter, order and page for us. I know I can handle this easily in Dapper using embedded or dynamic Sql; attempting to handle this via a Stored Procedure has proven to be over-my-head. What would I need to do to have my Stored Procedure receive a predicate from the app, applicable to a series of columns, that it applies as a WHERE clause in a safe manor, that won't open us up to Sql Injection?

score 1 · Answer 1 · edited May 23 '17 at 11:59

I guess the only way to make your parameter inputs 'safe' is to check the values before assigning to your stored proc parameters. You'd have to look for 'SELECT', 'DELETE', and 'UPDATE'. But, I think since you are working with column names instead of entire dynamic SQL commands, you should be ok. Read the following: tsql - how to prevent SQL injection

But, I'm no expert on this. You should do your own research.

To give you an idea on how to handle dynamic filtering in a stored procedure, I just use a SQL function that splits up a string with comma separated values and turns it into a table. I JOIN this function with the table that contains the column that needs to be filtered. For example, I need to filter my dataset with multiple values using the DIVISION column from some table. My stored procedure will take in a optional VARCHAR parameter of length 3000:

@strDIVISION VARCHAR(3000) = NULL

Next, when receiving a NULL value for this parameter, give it an empty string value:

SELECT @strDIVISION = ISNULL(@strDIVISION,'')

Instead of filtering in the WHERE clause, you can JOIN the string split function as such:

...
FROM tblTransDTL td 
   INNER JOIN tblTransHDR th ON th.JOB_ID = td.JOB_ID 
   INNER JOIN dbo.udf_STRSPLIT(@strDIVISION) d1 ON 
      (d1.Value = th.DIVISION OR 1=CASE @DIVISION WHEN '' THEN 1 ELSE 0 END)

The CASE statement helps to determine when all values should be allowed or use only the values from the parameter input.

Lastly, this is the SQL function that splits the string values into a table:

CREATE FUNCTION udf_STRSPLIT 
(   
   @Delim_Values VARCHAR(8000)
)
RETURNS  @Result TABLE(Value VARCHAR(2000))
AS
begin
WITH StrCTE(start, stop) AS
(
  SELECT  1, CHARINDEX(',' , @Delim_Values )
  UNION ALL
  SELECT  stop + 1, CHARINDEX(',' ,@Delim_Values  , stop + 1)
  FROM StrCTE
  WHERE stop > 0
)

insert into @Result
SELECT   SUBSTRING(@Delim_Values , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
FROM StrCTE

return
end
GO

Thanks, we're still working through this. Your solution is an interesting one. We'll give it a shot and see how it goes — Johnathon Sullinger, Mar 30 '16 at 13:53

Safest way to pass collection of values to Stored Procedure designating columns to order by and predicate to filter by

1 Answers1