Safest way to pass collection of values to Stored Procedure designating columns to order by and predicate to filter by
<p>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. </p>

<p>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:</p>

<pre><code>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
</code></pre>

<p>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 <code>SortableEntity</code> which works fine. </p>

<pre><code>var parameters = new DynamicParameters();

// Check if we have anything to sort by
IEnumerable&lt;SortDefinition&gt; sortingDefinitions = builder.GetSortDefinitions();
if (sortingDefinitions.Count() &gt; 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));
}
</code></pre>

<p>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 <code>order by</code> statement in my Stored Procedure. Since we're not using Sql parameter objects for the values being inserted into the <code>DataTable</code>, how do we protect against Sql injection? I know using <code>DynamicParameters</code> 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?</p>

<p>The biggest challenge though is the <code>WHERE</code> 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 <code>WHERE</code> clause in a safe manor, that won't open us up to Sql Injection? </p>