Frage Parametriere eine SQL IN-Klausel

Sie können parametrieren jeder Wert, so etwas wie:

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

Was dir geben wird:

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"

Nein, das ist nicht offen für SQL-Injektion. Der einzige injizierte Text in CommandText basiert nicht auf Benutzereingaben. Es basiert ausschließlich auf dem fest codierten Präfix "@tag" und dem Index eines Arrays. Der Index wird immer eine Ganzzahl sein, wird nicht vom Benutzer generiert und ist sicher.

Die vom Benutzer eingegebenen Werte werden immer noch in Parameter gefüllt, so dass dort keine Sicherheitslücke besteht.

Bearbeiten:

Bedenken Sie bei der Einfügung von Problemen, dass das Erstellen des Befehlstexts, um eine variable Anzahl von Parametern (wie oben) zu berücksichtigen, die Fähigkeit des SQL-Servers beeinträchtigt, zwischengespeicherte Abfragen zu nutzen. Das Endergebnis ist, dass Sie fast sicher den Wert der Verwendung von Parametern an erster Stelle verlieren (im Gegensatz zum bloßen Einfügen der Prädikat-Strings in das SQL selbst).

Nicht, dass zwischengespeicherte Abfragepläne nicht wertvoll sind, aber IMO ist diese Abfrage nicht annähernd kompliziert genug, um viel Nutzen daraus zu ziehen. Während die Kompilierungskosten die Ausführungskosten erreichen (oder sogar übersteigen), sprechen Sie immer noch Millisekunden.

Wenn Sie genug RAM haben, würde ich erwarten, dass SQL Server wahrscheinlich auch einen Plan für die häufigen Anzahl von Parametern zwischenspeichern würde. Ich nehme an, Sie könnten immer fünf Parameter hinzufügen, und die unspezifizierten Tags wären NULL - der Abfrageplan sollte gleich sein, aber es scheint mir ziemlich hässlich zu sein und ich bin mir nicht sicher, ob es die Mikrooptimierung wert ist auf Stack Overflow - es kann sich durchaus lohnen).

Außerdem wird SQL Server 7 und höher Abfragen automatisch parametrisieren, so dass die Verwendung von Parametern nicht wirklich von einem Leistungsstandpunkt aus erforderlich ist - es ist jedoch kritisch aus Sicherheitsgründen - vor allem mit solchen vom Benutzer eingegebenen Daten.

Für SQL Server 2008 können Sie a Tabellenwert-Parameter. Es ist ein bisschen Arbeit, aber es ist wohl sauberer als meine andere Methode.

Zuerst müssen Sie einen Typ erstellen

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

Dann sieht Ihr ADO.NET-Code wie folgt aus:

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

Die ursprüngliche Frage war "Wie parametriere ich eine Abfrage ..."

Lassen Sie mich hier sagen, dass dies so ist keine Antwort zur ursprünglichen Frage. Es gibt bereits einige Demonstrationen in anderen guten Antworten.

Mit dem gesagt, geh voran und markiere diese Antwort, lehne sie ab, markiere sie als keine Antwort ... tu was immer du für richtig hältst.

Finden Sie die Antwort von Mark Brackett für die bevorzugte Antwort, die ich (und 231 andere) uplooted. Der Ansatz, der in seiner Antwort gegeben wird, erlaubt 1) eine effektive Verwendung von Bind-Variablen und 2) für Prädikate, die sargierbar sind.

Ausgewählte Antwort

Was ich hier ansprechen möchte, ist der Ansatz, den Joel Spolsky in seiner Antwort gegeben hat, die Antwort "ausgewählt" als die richtige Antwort.

Joel Spolsky ist clever. Und es funktioniert vernünftig, es wird ein vorhersagbares Verhalten und eine vorhersagbare Leistung zeigen, bei "normalen" Werten und bei den normativen Randfällen, wie NULL und der leeren Zeichenkette. Und es kann für eine bestimmte Anwendung ausreichend sein.

Aber um diesen Ansatz zu verallgemeinern, betrachten wir auch die eher obskuren Eckfälle, wie zum Beispiel den Name Spalte enthält ein Platzhalterzeichen (wie vom Prädikat LIKE erkannt). Das am häufigsten verwendete Platzhalterzeichen ist % (ein Prozentzeichen.). Lassen Sie uns das hier jetzt behandeln und später zu anderen Fällen übergehen.

Einige Probleme mit% Charakter

Betrachten Sie einen Namen von 'pe%ter'. (Für die Beispiele hier verwende ich einen Literal-String-Wert anstelle des Spaltennamens.) Eine Zeile mit einem Namen-Wert von "pe% ter" würde von einer Abfrage des Formulars zurückgegeben werden:

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

Aber die gleiche Reihe wird es tun nicht zurückgegeben werden, wenn die Reihenfolge der Suchbegriffe umgekehrt ist:

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

Das Verhalten, das wir beobachten, ist irgendwie seltsam. Durch Ändern der Reihenfolge der Suchbegriffe in der Liste wird die Ergebnismenge geändert.

Es ist fast selbstverständlich, dass wir nicht wollen pe%ter zu Erdnussbutter passen, egal wie sehr er es mag.

Obskurer Eckfall

(Ja, ich stimme zu, dass dies ein obskurer Fall ist. Wahrscheinlich einer, der wahrscheinlich nicht getestet wird. Wir würden keine Wildcard in einem Spaltenwert erwarten. Wir können annehmen, dass die Anwendung verhindert, dass ein solcher Wert gespeichert wird Meiner Erfahrung nach habe ich selten eine Datenbankeinschränkung gesehen, die speziell erlaubte Zeichen oder Muster, die auf der rechten Seite von einem als Platzhalter gelten würden, ignoriert LIKE Vergleichsoperator.

Patchen eines Loches

Ein Ansatz, um dieses Loch zu flicken, besteht darin, dem zu entkommen % Platzhalterzeichen. (Für alle, die mit der Escape-Klausel des Betreibers nicht vertraut sind, hier ein Link zum SQL Server-Dokumentation.

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

Jetzt können wir das Literal% anpassen. Wenn wir einen Spaltennamen haben, müssen wir natürlich den Platzhalter dynamisch entschlüsseln. Wir können das benutzen REPLACE Funktion zum Auffinden von Vorkommen des %Zeichen und fügen Sie vor jedem einen umgekehrten Schrägstrich ein, wie folgt:

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

So löst das Problem mit dem% Wildcard. Fast.

Entkomme der Flucht

Wir erkennen, dass unsere Lösung ein anderes Problem verursacht hat. Das Escape-Zeichen. Wir sehen, dass wir auch jedem Vorkommen von Escape-Zeichen selbst entkommen müssen. Diesmal benutzen wir das! als Fluchtzeichen:

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

Der Unterstrich auch

Jetzt, wo wir eine Rolle spielen, können wir eine weitere hinzufügen REPLACE Behandle den Unterstrich-Platzhalter. Und nur zum Spaß verwenden wir dieses Mal $ als Escape-Zeichen.

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

Ich bevorzuge diesen Ansatz, da er sowohl in Oracle und MySQL als auch in SQL Server funktioniert. (Normalerweise verwende ich den \ Backslash als Escape-Zeichen, da dies das Zeichen ist, das wir in regulären Ausdrücken verwenden. Aber warum sollte man durch Konvention eingeschränkt werden?

Diese lästigen Klammern

SQL Server ermöglicht auch, dass Platzhalterzeichen als Literale behandelt werden, indem sie in Klammern eingeschlossen werden []. Wir sind also noch nicht fertig mit der Reparatur, zumindest für SQL Server. Da Klammernpaare eine besondere Bedeutung haben, müssen wir auch diesen Klammern entkommen. Wenn es uns gelingt, den Klammern zu entkommen, müssen wir uns zumindest nicht um den Bindestrich kümmern - und das Karat ^ innerhalb der Klammern. Und wir können alle verlassen %und _ Zeichen in den Klammern sind ausgeblendet, da wir die spezielle Bedeutung der Klammern grundsätzlich deaktiviert haben.

Passende Paare von Klammern zu finden, sollte nicht so schwer sein. Es ist etwas schwieriger als die Singleton% und _. (Beachten Sie, dass es nicht ausreicht, alle Vorkommnisse von Klammern zu ignorieren, da eine Singleton-Klammer als Literal betrachtet wird und nicht maskiert werden muss. Die Logik wird etwas unschärfer, als ich ohne weitere Testfälle verarbeiten kann .)

Inline-Ausdruck wird unordentlich

Dieser Inline-Ausdruck in SQL wird länger und hässlicher. Wir können es wahrscheinlich schaffen, aber der Himmel hilft der armen Seele, die zurückkommt und sie entziffern muss. So sehr ich Fan von Inline-Ausdrücken bin, bin ich geneigt, hier keinen zu benutzen, hauptsächlich, weil ich keinen Kommentar hinterlassen möchte, der den Grund für das Durcheinander erklärt und mich dafür entschuldigt.

Eine Funktion wo?

Okay, wenn wir das nicht als Inline-Ausdruck in SQL behandeln, ist die nächste Alternative, die wir haben, eine benutzerdefinierte Funktion. Und wir wissen, dass das alles nicht beschleunigen wird (es sei denn, wir können einen Index dafür definieren, so wie wir es mit Oracle tun konnten.) Wenn wir eine Funktion erstellen müssen, tun wir das besser im Code, der das SQL aufruft Erklärung.

Und diese Funktion kann abhängig vom DBMS und der Version einige Unterschiede im Verhalten haben. (Ein Aufruf an alle Java-Entwickler, die so daran interessiert sind, jede Datenbank-Engine synonym zu verwenden.)

Fachwissen

Möglicherweise verfügen wir über spezielles Wissen über die Domäne für die Spalte (dh die zulässigen Werte für die Spalte) a priori dass die in der Spalte gespeicherten Werte niemals ein Prozentzeichen, Unterstriche oder Klammerpaare enthalten. In diesem Fall geben wir nur einen kurzen Kommentar, dass diese Fälle abgedeckt sind.

Die Werte, die in der Spalte gespeichert werden, können% oder _ Zeichen enthalten, aber eine Einschränkung kann erfordern, dass diese Werte maskiert werden, möglicherweise unter Verwendung eines definierten Zeichens, so dass die Werte LIKE-Vergleich "sicher" sind. Wieder ein kurzer Kommentar über die erlaubte Menge von Werten, und insbesondere, welches Zeichen als Escape-Zeichen verwendet wird, und gehen Sie mit Joel Spolsky's Ansatz.

In Ermangelung des Fachwissens und einer Garantie ist es jedoch wichtig, dass wir uns zumindest mit diesen obskuren Fällen befassen und überlegen, ob das Verhalten vernünftig und "nach der Spezifikation" ist.

Andere Probleme wurden wiederholt

Ich glaube, andere haben bereits einige der anderen häufig in Betracht gezogenen Problembereiche ausreichend herausgestellt:

• SQL-Injektion (Nehmen Sie Informationen entgegen, die vom Benutzer bereitgestellt werden, und binden Sie diese in den SQL-Text ein, anstatt sie über Bind-Variablen bereitzustellen. Die Verwendung von Bind-Variablen ist nicht erforderlich, es ist nur ein praktischer Ansatz, um SQL-Injection zu verhindern Komm damit klar:

• Optimiererplan, der Indexsuche statt Indexsuche verwendet, mögliche Notwendigkeit für einen Ausdruck oder eine Funktion zum Entweichen von Platzhaltern (möglicher Index für Ausdruck oder Funktion)

• Die Verwendung von Literalwerten anstelle von Bind-Variablen beeinträchtigt die Skalierbarkeit

Fazit

Ich mag Joel Spolsky's Ansatz. Es ist schlau. Und es funktioniert.

Aber sobald ich es sah, sah ich sofort ein potentielles Problem damit, und es liegt nicht in meiner Natur, es rutschen zu lassen. Ich will den Bemühungen anderer nicht kritisch gegenüberstehen. Ich weiß, dass viele Entwickler ihre Arbeit sehr persönlich nehmen, weil sie so viel investieren und sich so sehr darum kümmern. Also bitte verstehen Sie, das ist kein persönlicher Angriff. Was ich hier identifiziere, ist die Art von Problem, die in der Produktion auftaucht, anstatt zu testen.

Ja, ich bin weit von der ursprünglichen Frage entfernt. Aber wo sonst sollte ich diese Anmerkung zu dem, was ich für ein wichtiges Thema halte, mit der "ausgewählten" Antwort auf eine Frage belassen?

Sie können den Parameter als Zeichenfolge übergeben

Sie haben also die Zeichenfolge

DECLARE @tags

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

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

Dann müssen Sie nur die Zeichenfolge als 1 Parameter übergeben.

Hier ist die Split-Funktion, die ich verwende.

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

Ich habe Jeff / Joel heute im Podcast darüber reden hören (Episode 34, 2008-12-16 (MP3, 31 MB), 1 Std. 03 Min. 38 Sek. - 1 Std. 06 Min. 45 Sek.), Und ich dachte, ich erinnere mich, dass Stack Overflow verwendet hat LINQ zu SQLaber vielleicht wurde es weggeworfen. Das Gleiche gilt für LINQ to SQL.

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

Das ist es. Und, ja, LINQ schaut bereits rückwärts genug, aber die Contains Klausel scheint extra rückwärts zu mir. Als ich eine ähnliche Abfrage für ein Projekt bei der Arbeit durchführen musste, versuchte ich natürlich, dies falsch zu machen, indem ich eine Verbindung zwischen dem lokalen Array und der SQL Server-Tabelle herstellte, um herauszufinden, ob der LINQ to SQL-Übersetzer schlau genug wäre Übersetzung irgendwie. Es tat es nicht, aber es gab eine Fehlermeldung, die beschreibend war und mich auf die Verwendung hinwies Enthält.

Wie auch immer, wenn Sie dies in der sehr empfehlen LINQPadund führen Sie diese Abfrage aus, können Sie die tatsächliche SQL anzeigen, die der SQL LINQ-Anbieter generiert hat. Es zeigt Ihnen jeden der Werte, die in ein parametrisiert werden IN Klausel.

Wenn Sie von .NET anrufen, könnten Sie verwenden Dapper Punktnetz:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

Dapper macht das Denken, also müssen Sie nicht. Ähnliches ist mit möglich LINQ zu SQL, Na sicher:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
           where names.Contains(tag.Name)
           orderby tag.Count descending
           select tag;

Dies ist möglicherweise eine halb böse Art, es zu tun, ich habe es einmal verwendet, war ziemlich effektiv.

Abhängig von Ihren Zielen könnte es nützlich sein.

1. Ein ... kreieren temporäre Tabelle mit einer Spalte.
2. INSERT jeder Nachschlagewert in diese Spalte.
3. Anstatt einen zu verwenden INSie können dann einfach Ihren Standard verwenden JOIN Regeln. (Flexibilität ++)

Dies hat ein bisschen mehr Flexibilität in dem, was Sie tun können, aber es ist eher für Situationen geeignet, in denen Sie eine große Tabelle abfragen müssen, mit guter Indizierung, und Sie möchten die parametrisierte Liste mehr als einmal verwenden. Speichert, dass es zweimal ausgeführt werden muss und die gesamte Desinfektion manuell durchgeführt wird.

Ich bin nie dazu gekommen, genau zu profilieren schnell es war, aber in meiner Situation wurde es benötigt.

Wir haben eine Funktion, die eine Tabellenvariable erstellt, der Sie beitreten können:

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),
                                           @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
  Position INT,
  Value    VARCHAR(8000))
AS
  BEGIN
      DECLARE @myPos INT

      SET @myPos = 1

      WHILE Charindex(@delim, @list) > 0
        BEGIN
            INSERT INTO @listTable
                        (Position,Value)
            VALUES     (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))

            SET @myPos = @myPos + 1

            IF Charindex(@delim, @list) = Len(@list)
              INSERT INTO @listTable
                          (Position,Value)
              VALUES     (@myPos,'')

            SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
        END

      IF Len(@list) > 0
        INSERT INTO @listTable
                    (Position,Value)
        VALUES     (@myPos,@list)

      RETURN
  END 

Damit:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

Das ist eklatant, aber wenn Sie garantiert mindestens eins haben, können Sie Folgendes tun:

SELECT ...
       ...
 WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )

Mit IN ('tag1', 'tag2', 'tag1', 'tag1', 'tag1') wird einfach von SQL Server entfernt. Außerdem erhalten Sie direkte Indexsuchen