Paramétrer un SQL À la clause

Question

Comment dois-je paramétrer une requête contenant un IN clause avec un nombre variable d'arguments, comme celui-ci?

select * from Tags 
where Name in ('ruby','rails','scruffy','rubyonrails')
order by Count desc

Dans cette requête, le nombre d'arguments pourrait être n'importe où de 1 à 5.

Je préfère ne pas utiliser un dédié procédure stockée (ou XML), mais si il y a un moyen élégant spécifique à SQL Server 2008, je suis ouvert à cela.

Answer 1

Vous pouvez paramétrer chaque valeur, donc quelque chose comme:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

Ce qui vous donnera:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0,@tag1,@tag2,@tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

Non, ce n'est pas ouverte à l'injection SQL. La seule injecté texte dans CommandText n'est pas basée sur la saisie de l'utilisateur. C'est uniquement sur la base des codé en dur "@tag" préfixe, et l'indice d'un tableau. L'index doit toujours être un nombre entier, n'est pas généré par l'utilisateur, et est sûr.

L'utilisateur saisi les valeurs sont toujours fourré dans les paramètres, donc il n'y a pas de vulnérabilité.

Edit:

Injection préoccupations de côté, prenez bien soin de noter que la construction de la commande de texte à accueillir un nombre variable de paramètres (comme ci-dessus) empêchent de SQL server est capable de prendre avantage de la mise en cache de requêtes. Le résultat net est que vous avez presque certainement perdre de la valeur à l'aide des paramètres en premier lieu (par opposition à simplement d'insérer le prédicat des chaînes dans le SQL lui-même).

Pas mis en cache les plans de requête ne sont pas utiles, mais l'OMI cette requête n'est pas presque assez compliqué de voir beaucoup d'avantages. Alors que les coûts de compilation peut approcher (et même dépasser) les frais d'exécution, vous êtes encore à parler de quelques millisecondes.

Si vous avez suffisamment de RAM, je m'attends à SQL Server serait probablement cache un plan pour le compte de paramètres. Je suppose que vous pouvez toujours ajouter des cinq paramètres, et de laisser l'une quelconque des balises être NUL - le plan de requête doit être le même, mais il semble assez laid pour moi et je ne suis pas sûr que ça en vaut la micro-optimisation (bien que, sur un Débordement de Pile - il peut très bien être la peine).

En outre, SQL Server 7 et les versions ultérieures auto-paramétrer des requêtes, donc, à l'aide de paramètres n'est pas vraiment nécessaire d'un point de vue des performances, - il est, cependant, critique à partir d'un point de vue sécurité - en particulier avec l'utilisateur saisie des données de cette façon.

Answer 2

Voici un rapide et sale technique que j'ai utilisé:

select * from Tags
where '|ruby|rails|scruffy|rubyonrails|'
like '%|' + Name + '|%'

Alors, voici le code C#:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Deux mises en garde:

• La performance est terrible. like "%...%" des requêtes ne sont pas indexés.
• Assurez-vous que vous n'avez pas de |, vide ou null tags ou cela ne fonctionnera pas

Il y a d'autres façons d'accomplir ce que certaines personnes peuvent considérer plus propre, donc merci de continuer à lire.

Answer 3

Pour SQL Server 2008, vous pouvez utiliser une table d'une valeur de paramètre. C'est un peu de travail, mais il est sans doute plus propre que mon autre méthode.

Tout d'abord, vous devez créer un type de

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

Ensuite, votre ADO.NET code ressemble à ceci:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

Answer 4

La question initiale était "Comment dois-je paramétrer une requête ..."

Permettez-moi de préciser ici, que ce n'est pas une réponse à la question d'origine. Il y a déjà quelques démonstrations de ce que dans les autres bonnes réponses.

Cela dit, aller de l'avant et le drapeau de cette réponse, downvote, le marquer comme non pas une réponse... faire ce que vous pensez est juste.

Voir la réponse de Mark Brackett pour la meilleure réponse que j'ai (et 231 autres) upvoted. L'approche adoptée dans sa réponse permet 1) pour l'utilisation efficace de lier les variables, et 2) pour les prédicats qui sont sargable.

Réponse sélectionnée

Ce que je veux aborder ici est l'approche adoptée dans Joel Spolsky de la réponse, la réponse "sélectionné" que le droit de réponse.

Joel Spolsky approche est intelligent. Et il fonctionne raisonnablement, il va présentent un comportement prévisible et des performances prévisibles, compte tenu de la "normale" valeurs", et avec le normatif des cas limites, comme NULLE, et la chaîne vide. Et il peut être suffisant pour une application particulière.

Mais en termes de généraliser cette approche, mais nous considérons également la plus coin obscur cas, comme lorsque l' Name colonne contient un caractère générique (tel que reconnu par le prédicat LIKE.) Le caractère générique je vois le plus souvent utilisé est l' % (un signe de pourcentage.). Donc, nous allons en traiter ici, maintenant, et plus tard passer à d'autres cas.

Quelques problèmes avec le caractère %

Considérons un Nom de valeur de 'pe%ter'. (Pour les exemples ici, j'utilise un littéral de chaîne de valeur en lieu et place du nom de la colonne.) Une ligne avec une valeur de Nom de `pe%ter " seraient retournés par une requête de la forme:

select ...
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

Mais cette même ligne pourront pas être retournés si l'ordre des mots est inversé:

select ...
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

Le comportement que nous observons est bizarre. Modification de l'ordre des termes de recherche dans la liste des modifications de l'ensemble de résultats.

Il va presque sans dire que nous ne voulons pas pe%ter de match de beurre d'arachide, peu importe combien il l'aime.

Coin obscur cas

(Oui, je suis d'accord que c'est une obscure affaire. Probablement l'un qui n'est pas susceptible d'être testé. On ne s'attendrait pas d'un caractère générique dans la valeur d'une colonne. On peut supposer que l'application empêche une telle valeur d'être stocké. Mais dans mon expérience, j'ai rarement vu une contrainte de base de données spécifiquement des caractères non autorisés ou des motifs qui seraient considérés comme des caractères génériques sur le côté droit de l' LIKE opérateur de comparaison.

Patcher un trou

Une approche à colmater ce trou est d'échapper à l' % caractère générique. (Pour quiconque n'est pas familier avec la clause de sauvegarde sur l'opérateur, voici un lien vers la documentation de SQL Server.

select ...
 where '|peanut|butter|'
  like '%|' + 'pe\%ter' + '|%' escape '\'

Maintenant, nous pouvons comparer les littéral %. Bien sûr, quand on a un nom de colonne, nous allons avoir besoin de dynamique d'échapper le caractère générique. Nous pouvons utiliser l' REPLACE , afin de rechercher des occurrences de l' %personnage et insérer une barre oblique inverse devant chacun, comme ceci:

select ...
 where '|pe%ter|'
  like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

Si cela résout le problème avec le générique%. Presque.

Échapper au échapper

Nous reconnaissons que notre solution a présenté un autre problème. Le caractère d'échappement. Nous voyons que nous allons aussi avoir besoin d'échapper à toutes les occurrences d'un caractère d'échappement lui-même. Cette fois, nous utilisons la ! comme le caractère d'échappement:

select ...
 where '|pe%t!r|'
  like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

Le trait de soulignement trop

Maintenant que nous sommes sur une lancée, nous pouvons ajouter une autre REPLACE gérer le trait de soulignement générique. Et juste pour le fun, cette fois, nous allons utiliser $ comme caractère d'échappement.

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

Je préfère cette approche s'échapper, car il travaille dans Oracle et MySQL ainsi que SQL Server. (J'ai l'habitude d'utiliser le \ barre oblique inverse comme caractère d'échappement, puisque c'est le personnage que nous utilisons dans les expressions régulières. Mais pourquoi être contraint par la convention!

Ces satanés crochets

SQL Server permet également des caractères génériques pour être traités comme des littéraux en les enfermant dans des crochets []. Nous ne sommes donc pas fait de fixation encore, au moins pour SQL Server. Comme les paires de crochets ont une signification spéciale, nous aurons besoin d'échapper à ceux aussi bien. Si nous parvenons à sortir correctement des parenthèses, puis au moins on n'aura pas à s'embêter avec le tiret - et le carat ^ dans les crochets. Et nous pouvons laisser tout %et _ entre parenthèses échappé, puisque nous allons avoir désactivé la signification spéciale des crochets.

Trouver des paires de crochets ne devrait pas être difficile. C'est un peu plus difficile de gérer les occurrences de singleton % "et"_". (Notez qu'il ne suffit pas de juste d'échapper à toutes les occurrences de crochets, car un singleton support est considéré comme un littéral, et n'a pas besoin d'être échappé. La logique est un peu plus floue que je peux gérer sans courir le plus de cas de test.)

Inline expression devient malpropre

Que inline expression dans le SQL est de plus en plus longues et de plus laid. On peut probablement le faire fonctionner, mais ciel, à l'aide de la pauvre âme qui vient par derrière et a de le déchiffrer. En tant que fan je suis pour les expressions, je suis enclin à ne pas utiliser une ici, principalement parce que je ne veux pas avoir à laisser un commentaire expliquant la raison de la pagaille, et s'excuser pour cela.

Une fonction où ?

Bon, alors, si nous ne gérons pas que comme une ligne d'expression dans le SQL, le plus proche de la solution que nous avons est une fonction définie par l'utilisateur. Et nous savons que de ne pas accélérer les choses tout (à moins que nous pouvons définir un index sur elle, comme nous avons pu avec Oracle.) Si nous avons pour créer une fonction, nous pourrions mieux faire que dans le code qui appelle l'instruction SQL.

Et qu'une fonction peut avoir quelques différences dans le comportement, dépendant du SGBD et la version. (Un shout out à tous les développeurs Java tellement envie d'être en mesure d'utiliser tout moteur de base de données de façon interchangeable.)

La connaissance du domaine

Nous pouvons avoir des connaissances du domaine pour la colonne, (c'est l'ensemble des valeurs admissibles appliquée pour la colonne. Nous savons a priori que les valeurs stockées dans la colonne ne comprendront jamais un signe de pourcentage, un trait de soulignement, ou support de paires. Dans ce cas, nous avons simplement inclure un commentaire rapide que ces cas sont couverts.

Les valeurs stockées dans la colonne peut autoriser % ou _ personnages, mais une contrainte peut exiger de ces valeurs pour être échappé, peut-être l'aide d'un caractère définis, tels que les valeurs sont des valeurs de comparaison "safe". Encore une fois, un rapide commentaire sur le jeu autorisé de valeurs, et en particulier le caractère qui est utilisé comme un caractère d'échappement, et d'aller avec Joel Spolsky de l'approche.

Mais, en l'absence de la connaissance spécialisée et d'une garantie, il est important pour nous d'envisager la gestion de coin obscur de cas, et d'examiner si le comportement est raisonnable et "par le cahier des charges".

---

D'autres questions récapitulé

Je crois que d'autres ont déjà suffisamment souligné quelques-uns des autres communément considérés comme des domaines de préoccupation:

• SQL injection (prendre ce qui semble être fourni par l'utilisateur de l'information, et notamment que, dans le texte SQL plutôt que de leur fournir par le biais de lier les variables. À l'aide de variables de liaison n'est pas obligatoire, c'est juste une approche pratique pour contrecarrer avec injection SQL. Il y a d'autres façons de traiter avec elle:

• l'optimiseur de plan à l'aide d'analyse d'index plutôt que de l'indice vise, au besoin éventuel d'une expression ou de la fonction pour échapper les caractères génériques (indice possible sur l'expression ou de la fonction)

• l'utilisation de valeurs littérales en place de lier les variables impacts de l'évolutivité

---

Conclusion

J'aime Joel Spolsky de l'approche. Il est intelligent. Et il fonctionne.

Mais dès que j'ai vu, j'ai tout de suite vu un problème potentiel avec elle, et ce n'est pas ma nature de le laisser glisser. Je ne veux pas être critique sur les efforts des autres. Je sais que beaucoup de développeurs prennent leur travail très personnellement, parce qu'ils investissent tellement dans et ils tiennent tellement à son sujet. Donc, s'il vous plaît comprendre, ce n'est pas une attaque personnelle. Ce que je suis à identifier ici est le type de problème que l'on rencontre dans la production plutôt que de tester.

Oui, je suis allé au-delà de la question d'origine. Mais où d'autre de laisser cette remarque à propos de ce que je considère être un problème important avec les "sélectionnés" répondre à une question?

Mon espoir est que quelqu'un va trouver ce post pour être de quelque utilité.

---

Excuses

Encore une fois, je ne m'excuse pour mon incapacité à respecter les règles et les conventions de Débordement de Pile, de poster ici ce n'est clairement pas une réponse à la discussion de la question.

Answer 5

Vous pouvez passer le paramètre sous la forme d'une chaîne de caractères

Si vous avez la chaîne

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails'

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|'))
order by Count desc

Alors tout ce que vous avez à faire est de passer la chaîne 1 paramètre.

Ici, c'est la scission de la fonction que j'utilise.

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END