Échapper des caractères spéciaux dans une instruction SQL LIKE en utilisant les parameters sql

J'ai une table contenant des produits. J'ai besoin de faire une requête pour find tous les résultats correspondants à une valeur d'input user. J'utilise SqlParameter pour l'insertion des inputs.

 SqlCommand findProcutsByPattern = new SqlCommand( "SELECT *" + " FROM [Products]" + " WHERE ProductName LIKE @pattern", connection); findProcutsByPattern.Parameters.AddWithValue("@pattern", '%' + pattern + '%'); 

Le problème survient lorsque la string d'input de l'user contient '_' ou '%' car ils sont interprétés comme des caractères spéciaux. D'un autre côté, en considérant ceci:

Les objects de command utilisent des parameters pour transmettre des valeurs à des instructions SQL ou à des procédures stockées, fournissant une vérification et une validation de type. Contrairement au text de command, l'input de paramètre est traitée comme une valeur littérale, pas comme un code exécutable.

Je ne devrais pas avoir de tels problèmes. Dois-je replace / échapper tous les '_' et '%' dans la string d'input ou existe-t-il une solution plus élégante? Je veux que l'input soit considérée comme littérale.

J'ai quelques loggings dans la table qui incluent des caractères spéciaux dans le nom (N_EW, N \ EW, N% EW, N "EW, N'EW) .Spécifier \ , " , et ' comme l'input fonctionne bien (les considère comme littéraux).

Vous avez deux options:

  • les entourer de [ et ] . Alors:

     where pattern like '[%]' 

    Cherche le caractère de pourcentage. Liste complète des caractères à extraire – '_', '%', '[', ']' avec les rlocations correspondants '[_]', '[%]', '[[]', '[]]' . Exemple de code peut être trouvé dans Échapper au caractère d'échappement ne fonctionne pas – Opérateur SQL LIKE

  • utiliser un caractère d'échappement qui est peu susceptible d'être dans la string, comme un caractère en retrait:

     where pattern like '`%' escape '`' 

    (Voir la syntaxe sur MSDN – LIKE (Transact-SQL) .)

Dans les deux cas, je vous suggère de faire la substitution dans la couche application, mais vous pouvez aussi le faire en SQL si vous voulez vraiment:

 where pattern like replace(@pattern, '%', '[%]') 

Et, donner à l'user final l'access aux caractères generics peut être une bonne chose en termes d'interface user.


Note: il y a quelques caractères spéciaux '-' et '^' dans la requête LIKE, mais ils n'ont pas besoin d'être échappés si vous échappez déjà à '[' et ']' .

De manière générale, l'échappement manuel de valeurs dans SQL est considéré comme une mauvaise pratique car l'utilisation de parameters est la solution préférée (et la plus sécurisée).

Dans votre exemple, cependant, vous utilisez déjà des parameters et vous voulez utiliser l'opérateur LIKE, donc l'échappement manuel des caractères devrait être correct. Ne pas oublier d'échapper les caractères d'échappement, aussi – voir https://stackoverflow.com/a/13861567/232175 pour du code.

Vous pouvez le faire comme ceci: spécifiez un caractère d'échappement explicite dans votre string SQL, puis placez cette échappement devant tous les caractères % et _ dans la string input par l'user:

 SqlCommand findProcutsByPattern = new SqlCommand( @"SELECT * FROM [Products] WHERE ProductName LIKE @pattern", connection) ESCAPE '_'" 

Lorsque vous définissez le paramètre, remplacez toutes les instances de _ et % par __ et _% :

 var escapedPattern = Regex.Replace(pattern, "[%_]", "_$0"); 

Démo

Donc, fondamentalement, j'ai manuellement échappé (remplacé) tous les symboles generics, ce qui semble fonctionner correctement maintenant. Voici le code final.

  SqlCommand findProcutsByPattern = new SqlCommand( "SELECT *" + "FROM [Products]" + "WHERE ProductName LIKE @pattern", connection); ssortingng patternEscaped = pattern.Replace("_", "[_]"); patternEscaped = patternEscaped.Replace("%", "[%]"); patternEscaped = patternEscaped.Replace("[", "[[]"); findProcutsByPattern.Parameters.AddWithValue("@pattern", '%' + patternEscaped + '%'); 

Merci pour votre soutien!

MISE À JOUR: Je vois …

escape_character Caractère placé devant un caractère générique pour indiquer que le caractère générique doit être interprété comme un caractère normal et non comme un caractère générique. escape_character est une expression de caractère qui n'a pas de valeur par défaut et doit évaluer un seul caractère.

Donc vous devez toujours vous échapper.