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.
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.
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