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?
Je suppose que la seule façon de rendre vos entrées de paramètres 'safe' est de vérifier les valeurs avant d’attribuer les paramètres proc stockés. Vous devez rechercher «SELECT», «DELETE» et «UPDATE». Mais, je pense que puisque vous travaillez avec des noms de colonnes au lieu de commandes SQL dynamiques complètes, vous devriez être d'accord. Lisez ce qui suit: tsql - comment empêcher l'injection SQL
Mais je ne suis pas expert en la matière. Vous devriez faire vos propres recherches.
Pour vous donner une idée sur la façon de gérer le filtrage dynamique dans une procédure stockée, j'utilise simplement une fonction SQL qui divise une chaîne avec des valeurs séparées par des virgules et la transforme en une table. Je rejoins cette fonction avec la table contenant la colonne à filtrer. Par exemple, je dois filtrer mon ensemble de données avec plusieurs valeurs à l'aide de la colonne DIVISION d'une table. Ma procédure stockée prendra un paramètre optionnel VARCHAR de longueur 3000:
@strDIVISION VARCHAR(3000) = NULL
Ensuite, lorsque vous recevez une valeur NULL pour ce paramètre, donnez-lui une valeur de chaîne vide:
SELECT @strDIVISION = ISNULL(@strDIVISION,'')
Au lieu de filtrer dans la clause WHERE, vous pouvez joindre la fonction de division de chaîne en tant que telle:
...
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)
L'instruction CASE permet de déterminer quand toutes les valeurs doivent être autorisées ou d'utiliser uniquement les valeurs provenant de l'entrée du paramètre.
Enfin, il s'agit de la fonction SQL qui divise les valeurs de chaîne en une 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