Méthode la plus sûre pour transmettre la collection de valeurs à la procédure stockée en désignant les colonnes à ordonner et en prédisant le filtrage par

Nous construisons un projet MVC qui doit utiliser MVG DataGrid. Dans ce cadre, nous souhaitons autoriser le filtrage et le classement des colonnes DataGrid. Nous voulons que cela soit traité du côté Sql, avec la pagination. La gestion de la pagination est très simple et nous avons déjà ce travail avec nos procédures stockées.

Le défi auquel nous sommes confrontés maintenant est de savoir comment obtenir les colonnes triées par l'utilisateur dans la procédure stockée afin que nous puissions trier les enregistrements lors de la pagination. J'ai joué avec l'utilisation d'un type de table pour envoyer une «collection» de colonnes en utilisant quelque chose comme ceci:

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

Nous utilisons Dapper comme ORM, et nous sommes obligés d'utiliser uniquement les procédures stockées par stratégie. Dans mon référentiel, j'utilise le DataTable suivant pour essayer d'insérer les enregistrements dans le SortableEntity qui fonctionne correctement.

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));
}

Avec cela, je suis en mesure d'obtenir mes valeurs triées dans la procédure stockée, mais je suis concerné par l'injection SQL. Une des façons de contourner cela consiste à rechercher dans la table sys-columns si les colonnes données sont des colonnes valides avant de les utiliser. Je ne sais pas comment procéder, en prenant les colonnes valides et en les appliquant à une instruction order by dans ma procédure stockée. Comme nous n'utilisons pas d'objets de paramètres SQL pour les valeurs insérées dans le DataTable , comment protégeons-nous contre l'injection SQL? Je sais que l'utilisation de DynamicParameters nous protégera pour les valeurs entrant dans les paramètres de procédure stockée, mais comment cela fonctionne-t-il lorsque la valeur est une table contenant des valeurs?

Le plus grand défi est la clause WHERE . Nous souhaitons transmettre un filtre de la grille de données à la procédure stockée afin que les utilisateurs puissent filtrer les ensembles de résultats. L'idée étant que la procédure stockée filtrerait, commanderait et créerait une page pour nous. Je sais que je peux gérer cela facilement dans Dapper en utilisant Sql intégré ou dynamique; Tenter de gérer cela par le biais d'une procédure stockée s'est avéré excessif. De quoi aurais-je besoin pour que ma procédure stockée reçoive un prédicat de l'application, applicable à une série de colonnes, qu'elle applique en tant que clause WHERE dans un manoir sécurisé, qui ne nous ouvre pas Sql Injection?