[Artikelserie] SQL: Parameter von Befehlen

juetho

[Artikelserie] SQL-Befehle: auch bei Formatproblemen sicher durch Parameters

Diese Artikel bieten im Bereich

Datentechnologien Hilfe, wenn eines oder mehrere der genannten Probleme (und natürlich auch ähnliche Probleme) auftreten, und zwar mit folgenden Abschnitten:

1. Beitrag: Problemstellungen und Lösung
2. Beitrag: Vorteile der Parameter-Lösung in der Praxis
3. Beitrag: Unterschiede der DbProvider
4. Beitrag: Ergänzende Hinweise

Diskussion
Da die Artikelserie geschlossen bleibt, gibt es einen extra Diskussionsthread

Diskussion zur Artikelserie "Parameter von SQL Befehlen"

Allgemeiner Hinweis: Wenn ich für Klassen einen Db-Bezeichner verwende, ist dies immer durch den Klassennamen des betreffenden DbProviders zu ersetzen: Sql-Klassen für MS-SQL Server, SqlCe-Klassen für MS-SQL Compact, MySql-Klassen für MySql usw. Die Db-Klassen selbst sind nur bei providerunabhängiger Programmierung von Bedeutung; das ist aber im Rahmen dieser Artikel nicht relevant.

Zur Forumssuche: Ich kann verstehen, dass jemand, der das Stichwort "Parameter" im Zusammenhang mit SQL-Befehlen noch nicht gelesen hat, nicht darauf kommt. Deshalb kann die Suchfunktion nur zufällig helfen: Suchbegriffe wie "DateTime-Problem bei SQL-String" liefern zwar auch die richtige Lösung (denn mehrere Poweruser und andere Helfer haben in vielen Beiträgen darauf hingewiesen); aber weil die Suchbegriffe sehr allgemein sind, ist nicht gesagt, dass der Sucher auf dem richtigen Weg landet.

Problemstellungen

Im Forum treten immer wieder solche und ähnliche Fragen auf:

SQL Abfragestring mit Timestamp
Hochkomma im SQL-String
Datentypen in Kriterienausdruck unverträglich
Die Zeichenfolge wurde nicht als gültiges DateTime erkannt.
Währungsfeld und double-Konvertierung
Länderabhängige Trennzeichen für Zahlen
Zahlen aus Exponentialdarstellung nach double konvertieren
Wie verhindere ich SQL-Injection?

Problem: SQL Abfragestring mit Timestamp

Code:
1: 2: 3:	SELECT * FROM srtTitanStats2 where datestamp >= 27.8.2008 00:00:00 AND datestamp <= 27.8.2008 23:59:59

Zitat:

Original siehe SQL Abfragestring
Was ist daran falsch? Ich blick nicht mehr durch, denn genau so lernten wir es im Unterricht, zwar mit MySQL aber das sollte eigentlich ja nicht so einen grossen Unterschied machen.

Problem: Hochkomma im SQL-String

C#-Code:

string strSQLUpdate = "UPDATE Kundenstamm "

   + "SET KDNAME = '" + strKDNAME + "' WHERE KDKDNR = '" + strKDKDNR + "'";

Zitat:

Original siehe Hochkomma im SQL-String
In den Kundennamen befinden sich unzählige Hochkomma, wie z.B. Franz's Würstelbude
Leider bekomme ich nun Probleme bei meinem SqlStatement mit den Hochkomma's
Wie könnte ich hier am besten vorgegen?

Problem: Datentypen in Kriterienausdruck unverträglich

C#-Code:

string sql = "DELETE FROM Fragen WHERE ID = '" + txtID.Text + "'";

Zitat:

Original siehe Daten aus OleDB Datenbank Löschen
Ich habe ein Problem mit dem Löschen aus einer Access-Datenbank per ExecuteNonQuery()

Die Spalte "ID" ist vom Typ "Zahl" (Anzeige von Access 2007)

Problem: Die Zeichenfolge wurde nicht als gültiges DateTime erkannt.

C#-Code:

string str_GeburtsDatum = txt_GeburtsDatum.Text;

str_GeburtsDatum = Convert.ToDateTime(str_GeburtsDatum).ToShortDateString();



SqlCommand cmd_GebUpdate = new SqlCommand();

cmd_GebUpdate.Connection = conn;

cmd_GebUpdate.Parameters.Add("@vrn", txt_VRN.Text);

cmd_GebUpdate.Parameters.Add("@geburtsdatum", str_GeburtsDatum);

cmd_GebUpdate.CommandText = "UPDATE <Tabelle> "

   + "SET geburtsdatum = @geburtsdatum WHERE vrn = @vrn";

Zitat:

Original siehe Kein gültiges DateTime für Update
Allerdings bekomme ich, bei meinem Update (wenn das Feld txt_GeburtsDatum leer ist) diese Fehlermeldung.

Diese Diskussion führte zu weiteren Problemen, vor allem wenn kein Geburtsdatum angegeben wurde.

Problem: Währungsfeld und double-Konvertierung

C#-Code:

double ab = 123.45;

double bestand = (double)(ds.Tables["summe"].Rows[0][0]) + ab;



cmd.CommandText = "UPDATE " + monat + jahr

   + " SET bestand=" + bestand + " WHERE belegnr=" + i + "";

Zitat:

Original siehe Accessdatenbank Double auslesen
ich lese mittels OLEdbAdapter von einer access Datenbank aus, unteranderem auch ein Währungsfeld, welches ich als double konvertiere und zu einem Betrag aus einem Textfeld addiere... Der Gesamtbetrag soll dann wiederrum in die Datenbank geschrieben werden.. Problem ist jetzt nur das er nicht tut, da er irgendwie mit dem Währungsformat aus access probleme hat!

Problem: Länderabhängige Trennzeichen für Zahlen

C#-Code:

//  Wert des Textfeldes in int, double oder decimal 

//  convertieren, etwa so:

string a = " insert into table Menge = '"

   + Convert.ToInt32(txtfeld.text) + "' ...;

Zitat:

Original siehe Länderabhängige Trennzeichen
Beim abspeichern hatte ich keine Probleme Damit weil Oracle das selbstständig unwandelt ("."). Doch wenn ich nun so einen Datensatz editieren möchte wird z.B. für Deutschland auch ein 99.72 angezeigt (funktioniert). Aber wenn ich diesen Datensatz speichern möchte bekomme ich ein Problem. Sobald ich dann den . durch ein , ersteze funktioniert alles Prima.

(Bei o.g. Code) wird das Komma in der Datenbank als Punkt gepeichert. Nachdem ich es wieder hole z.B. zum Editieren habe ich dann eben einen Punkt 5.5 - wenn ich diesen wiederum speichern möchte bekommen ich einen fehler.

Problem: Zahlen aus Exponentialdarstellung nach double konvertieren

C#-Code:

//string[][] data, string Werte werden in double konvertiert

double f = Convert.ToDouble(data[s][z]);

//in datenbank einfügen, fehlermeldung

string sql = "INSERT INTO Messwert(Messwertwert) VALUES ('" + f + "')";

Zitat:

Original siehe Datenbank einfügen, Konvertieren
"Fehler beim Konvertieren des varchar-Datentyps in float."

Die Werte die ich einlese, konvertiere und speichern möchte, sehe so "0,000 0,001121 5,15E-005 0,0006027 0,001153" aus.
Die Zahlen in Exponentialdarstellung, z.B. 5,15E-004 werden korrekt bis "-004" in double konvertiert(0,000515), sieht die Zahl so aus 5,15E-005 also "-005"(5Stellen links) dann passiert nichts und die Zahl wird auch so ausgegeben (5,15E-005). Welchen Datentyp benutze ich da??

Problem: Wie verhindere ich SQL-Injection?

C#-Code:

string sql = "UPDATE Kunden SET Name = '" + TextBoxName.Text

  + "' WHERE ID = " + TextBoxId.Text + ";";

Wenn nun ein böswilliger Anwender in der TextBox als neuen Namen dies eingibt:

Code:
1:	TextBoxName.Text = "Unsinn; DROP TABLE Kunden;"

dann wird doch die gesamte Tabelle gelöscht. Wie kann ich das verhindern?

Nähere Angaben zu diesem Problem sind zu finden unter

Wikipedia: SQL-Injection.

Lösungsweg
Die Lösung für alle diese und viele weitere Probleme ist ganz einfach:

Benutze für variable Inhalte eines SQL-Befehls niemals String-Verknüpfung, sondern immer DbParameter!

Die Verwendung von Parametern verlangt zwar ein wenig mehr Schreibarbeit, auch wenn das durch die IDE weitgehend vereinfacht wird. Aber die erheblich vergrößerte Sicherheit für Programmierer und Anwender macht die Arbeit viel leichter:

Auf Hochkommata (Gänsefüßchen) bei Strings muss überhaupt nicht geachtet werden.
Ständiges, mehrfaches und fehleranfälliges Hin- und Herkonvertieren als String ist überflüssig.
Spezielle Formatierungen für Datumsangaben oder Zahlen sind nicht erforderlich; jeder Wert wird im richtigen Datentyp übergeben.
Bösartige Eingaben haben keine bösartigen Auswirkungen.
Die Performance bei parametrisierten Abfragen ist in vielen Fällen wesentlich höher als bei "zusammengebauten" Abfragen.

Es gibt jedenfalls keinen Grund, auf diesen Weg zu verzichten. Das folgende zusammenfassende Beispiel benutzt Sql-Klassen, das ist aber - wie zu sehen ist - in der Praxis keine Einschränkung und kann sofort auf andere DbProvider übertragen werden. Auf Besonderheiten weise ich im

3. Beitrag: Unterschiede der DbProvider hin.

Das Beispiel habe ich übernommen von

Khalid aus dem

2. Beitrag: Vorteile der Parameter-Lösung in der Praxis; dort erläutert er es ausführlich.

Mit Parametern entsteht ein einfaches und dennoch schönes und sicheres Verfahren:

C#-Code:

public int AddEmployee(string lastName, string firstName, double sallary, DateTime birthday)

{

  using (SqlConnection connection = new SqlConnection(ConnectionSting))

  {

    connection.Open();



    StringBuilder sb = new StringBuilder();

    sb.AppendLine("INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday])");

    sb.AppendLine("OUTPUT INSERTED.ID");

    sb.AppendLine("VALUES (@LastName, @FirstName, @Sallary, @Birthday)");



    using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))

    {

      cmd.Parameters.AddWithValue("LastName", lastName);

      cmd.Parameters.AddWithValue("FirstName", firstName ?? Convert.DBNull);

      cmd.Parameters.AddWithValue("Sallary", sallary);

      cmd.Parameters.AddWithValue("Birthday", birthday);



      return cmd.ExecuteNonQuery();

    }

  }

}

Korrigiert am 03.03.2009 durch juetho: DBNull.Value war falsch und wurde nach Hinweisen im Forum durch Convert.DBNull ersetzt.

Hier ist gleich zu sehen, dass der Code übersichtlich und strukturiert aussieht. Es werden wirklich nur die Werte übergeben. Der DB-Server arbeitet auch intern nur mit den Parametern (siehe die ausführliche Erläuterung). Man muss also gar nicht mehr auf die Formatierung achten; das macht der Server selber.

Aus diesem Grund funktioniert Sql-Injection mit Parametern nicht: Der DB-Server übernimmt zuerst den Befehl selbst und bereitet ihn vor, dann fügt er die Parameter-Werte ein. In dem unter "Problem" genannten Beispiel würde eine einzelne Adresse den Namen "Unsinn; DROP TABLE Kunden;" erhalten; aber weiterer Schaden könnte nicht entstehen.

Auch das Problem von Dezimalzahlen in Strings mit Exponentialdarstellung entfällt. Es ist zwar nicht gesagt, dass ein solcher String richtig erkannt und übersetzt wird. Aber dieses Problem tritt nun dort auf, wohin es gehört, nämlich zu double.TryParse() o.ä., aber nicht bei der Verarbeitung im Sql-Befehl.

Weitere Einführungen

Vor allem gibt es natürlich die

mit den von DbParameter und DbParameterCollection abgeleiteten Klassen sowie den verschiedenen Parameters.Add-Methoden und den dort genannten Beispielen.

Außerdem ist die Dokumentation des jeweils benutzten DB-Systems und des DbProviders zu benutzen: zum einen wegen der unterschiedlichen Syntax der SQL-Dialekte, zum anderen wegen "eigener Regeln" der DbProvider.

Im Index der

Galileo-OpenBook

Kap. 26.3 Parametrisierte Abfragen zur Verfügung.

Ein herzliches Dankeschön gilt Khalid, FZelle und allen anderen Datenbank-Experten, deren vielfältige Hinweise über die Forumssuche zu dieser Artikelserie beigetragen haben.

Gutes Gelingen!

Khalid (für die ausführliche Lösung)
Jürgen (für die Artikel insgesamt)

Khalid

Vorteile der Parameter-Lösung in der Praxis
Das nachfolgende Beispiel soll zeigen, welchen großen Vorteil man hat, wenn man mit parameterisierten Abfragen arbeitet.

Es soll in einer Mitarbeitertabelle ein neuer Mitarbeiter gespeichert werden. Die Tabellenstruktur sieht folgendermaßen aus:

Code:
1: 2: 3: 4: 5: 6:	[EmployeeID] INT IDENTITY [Number] INT NOT NULL [LastName] VARCHAR(50) NOT NULL [FirstName] VARCHAR(50) NOT NULL [Sallary] NUMERIC(10,4) NOT NULL [Birthday] DATETIME NOT NULL

Irgendwo im Sourcecode hat der Entwickler eine Methode eingebaut um einen neuen Mitarbeiter anzulegen.

C#-Code:

public int AddEmployee(string lastName, string firstName, double sallary, DateTime birthday)

{

  using (SqlConnection connection = new SqlConnection(ConnectionSting))

  {

    connection.Open();



    StringBuilder sb = new StringBuilder();

    sb.AppendLine("INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday])");

    sb.AppendLine("OUTPUT INSERTED.ID");

    sb.AppendLine("VALUES ('" + lastName + "', '" + firstName + "', " + sallary.ToString() + ", '" + birthday.ToShortDateString() + "')");



    using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))

    {

      return cmd.ExecuteNonQuery();

    }

  }

}

Als erstes fällt auf, dass der Code unübersichtlich wirkt. Die Gefahr ein Hochkomma zu vergessen, oder eine Klammer ist schon relativ hoch, da durch die Stringverkettung der Überblick verloren geht. Bei dieser kleinen Anweisung mag es so noch gehen, aber wenn eine Tabelle über 10 Felder hat, wird es sehr schnell unübersichtlich. Das ist allerdings noch nicht das Fatale an dieser Methode.
Nehmen wir an, das diese Methode jetzt auf einem englischen System ausgeführt wird.

C#-Code:

string lastName = "Mustermann";

string firstName = "Max";

double sallary = 2500.50;

DateTime birthday = DateTime.Now.AddYears(-30);



int id = AddEmployee(lastName, firstName, sallary, birthday);

Wird dieser Code jetzt ausgeführt wird dieser ohne Fehler durchlaufen. Wenn das Programm jetzt allerdings auf einem deutschen System ausgeführt wird, wird eine Exception auftreten. Um zu verstehen warum, hier erstmal das eigentliche INSERT Statement
Englisches System:

Code:
1: 2: 3:	INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday]) OUTPUT INSERTED.ID VALUES ('Mustermann', 'Max', 2500.50, '1978/11/21')

Deutsches System:

Code:
1: 2: 3:	INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday]) OUTPUT INSERTED.ID VALUES ('Mustermann', 'Max', 2500,50, '21.11.1978')

Der Fehler ist auf dem deutschen System schnell gefunden: Beim Gehalt ist ein Komma, welches das INSERT Statement ungültig macht. Der Entwickler der diese Methode geschrieben hat, denkt sich also, "Gut, machen wir aus ein Komma ein Punkt". Die Methode die bei raus kommt ist:

C#-Code:

public int AddEmployee(string lastName, string firstName, double sallary, DateTime birthday)

{

  using (SqlConnection connection = new SqlConnection(ConnectionSting))

  {

    connection.Open();



    StringBuilder sb = new StringBuilder();

    sb.AppendLine("INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday])");

    sb.AppendLine("OUTPUT INSERTED.ID");

    sb.AppendLine("VALUES ('" + lastName + "', '" + firstName + "', " + sallary.ToString().Replace(',', '.') + ", '" + birthday.ToShortDateString() + "')");



    using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))

    {

      return cmd.ExecuteNonQuery();

    }

  }

}

Damit denkt sich der Entwickler jetzt, "Super! Problem gelöst". Falsch!
Schon bekommt er wieder ein Fehler auf den Tisch, dass das INSERT Statement geknallt hat. Er analysiert den Fall und findet heraus, das folgendes passiert ist

C#-Code:

string lastName = "O'Connor";

string firstName = "Max";

double sallary = 2400.90;

DateTime birthday = DateTime.Now.AddYears(-30);



int id = AddEmployee(lastName, firstName, sallary, birthday);

Im SQL Profiler sieht man, dass folgendes Statement ausgeführt wurde:

Code:
1: 2: 3:	INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday]) OUTPUT INSERTED.ID VALUES ('O'Connor', 'Max', 2400.90, '21.11.1978')

Der String bei "LastName" wird direkt nach dem "O" unterbrochen und somit ist das Statement ungültig. Also baut der Entwickler weiter an seinem Statement rum und erhält folgende Methode

C#-Code:

private string QuoteString(string value)

{

  return value.Replace("'", "''");

}



public int AddEmployee(string lastName, string firstName, double sallary, DateTime birthday)

{

  using (SqlConnection connection = new SqlConnection(ConnectionSting))

  {

    connection.Open();



    StringBuilder sb = new StringBuilder();

    sb.AppendLine("INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday])");

    sb.AppendLine("OUTPUT INSERTED.ID");

    sb.AppendLine("VALUES ('" + QuoteString(lastName) + "', '" + QuoteString(firstName) + "', " +

      sallary.ToString().Replace(",", ".") + ", '" + birthday.ToShortDateString() + "')");



    using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))

    {

      return cmd.ExecuteNonQuery();

    }

  }

}

Man sieht sofort, dass der Code immer unübersichtlicher und fehleranfälliger wird. Jetzt setzt der Datenbankadministrator noch ein oben drauf. Die Spalte "FirstName" darf auf einmal in der Datenbank NULL sein. Der Entwickler muss also sein Statement so umschreiben, dass es auch den Fall annehmen kann, das FirstName null ist. Er setzt sich hin und baut die Methode um:

C#-Code:

public int AddEmployee(string lastName, string firstName, double sallary, DateTime birthday)

{

  using (SqlConnection connection = new SqlConnection(ConnectionSting))

  {

    connection.Open();



    StringBuilder sb = new StringBuilder();

    sb.AppendLine("INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday])");

    sb.AppendLine("OUTPUT INSERTED.ID");

    sb.Append("VALUES ('" + QuoteString(lastName) + "', '" + QuoteString(firstName) + "', ");

    if (firstName != null)

      sb.Append("'" + QuoteString(firstName) + ", ");

    else

      sb.Append(", NULL, ");

    sb.Append("'" + birthday.ToShortDateString() + "')");



    using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))

    {

      return cmd.ExecuteNonQuery();

    }

  }

}

Der Code wird immer unübersichtlicher und "unfreundlicher". Bei jedem neuen Fall der Eintritt, baut der Entwickler weiter und weiter an der Methode rum. Zum Beispiel ist aufgefallen, dass in bestimmten Ländern das INSERT Statement nicht mehr funktioniert, da das Format des Geburtstages nicht mehr vom SQL Server erkannt wird, und somit ungültig wird. Der Entwickler baut also auch diese Stelle um und formatiert das Datum immer in das englische Format:

C#-Code:

//...

sb.Append("'" + birthday.ToString("yyyy/MM/dd") + "')");

//...

Sobald noch verschiedene Logiken hinzukommen, ist hier spätestens Ende. Der Entwickler wird sich immer mehr verstricken. Überall müssen Prüfungen eingebaut werden um zu gewährleisten, dass das INSRERT Statement überall und mit jedem Wert korrekt ausgeführt wird.

Jetzt das ganze nochmals mit SQL Parameter:

Ein anderer Entwickler hat die Aufgabe bekommen, die Methode neu zu schreiben, da diese bereits unwartbar wurde. Der Entwickler kann schon nach einigen Minuten zum Chef gehen und das Ergegnis präsentieren.

C#-Code:

public int AddEmployee(string lastName, string firstName, double sallary, DateTime birthday)

{

  using (SqlConnection connection = new SqlConnection(ConnectionSting))

  {

    connection.Open();



    StringBuilder sb = new StringBuilder();

    sb.AppendLine("INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday])");

    sb.AppendLine("OUTPUT INSERTED.ID");

    sb.AppendLine("VALUES (@LastName, @FirstName, @Sallary, @Birthday)");



    using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))

    {

      cmd.Parameters.AddWithValue("LastName", lastName);

      cmd.Parameters.AddWithValue("FirstName", firstName ?? Convert.DBNull);

      cmd.Parameters.AddWithValue("Sallary", sallary);

      cmd.Parameters.AddWithValue("Birthday", birthday);



      return cmd.ExecuteNonQuery();

    }

  }

}

Korrigiert am 03.03.2009 durch juetho: DBNull.Value war falsch und wurde nach Hinweisen im Forum durch Convert.DBNull ersetzt.

Der Code wirkt auf den ersten Blick wesentlich übersichtlicher als der Vorherige. Aber warum ist das jetzt besser? Wenn man sich das Statement anschaut, wie es im SQL Profiler dargestellt wird, wird man feststellen, das durch die Verwendung von Parametern nicht mehr das Statement direkt aufgerufen wird, sondern über eine Stored Procedure "gemappt" wird.

Code:

1:
2:
3:
4:
5:
6:
7:

EXEC sp_executesql 'INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday])
  OUTPUT INSERTED.ID
  VALUES (@LastName, @FirstName, @Sallary, @Birthday)', 
    N'@LastName VARCHAR(8)' = ''O'Connor'',
    N'@FirstName VARCHAR(3)' = 'Max',
    N'@Sallary NUMERIC(10,4)' = 2400.90,
    N'@Birthday DATETIME' = ''1978-11-21 10:19:28:657''

Die Stored Procedure "sp_executesql" übernimmt jetzt das Statement und die Parameter. Die Parameter werden dynamisch an das eigentliche Statement gehängt. Man kann ganz gut an der Zeile mit dem Datum erkennen, dass das Datum autom. in das Datumsformat des SQL Server umgewandelt wird. Der Entwickler muss sich hier also z.B. nicht mehr um das Datumsformat kümmern, da dies der SQL Server ihm abnimmt. Das gleiche gilt für den Wert von Sallary. Die Umwandlung übernimmt der SQL Provider. Dieser nimmt das SqlCommand an und "merkt" das Parameter verwendet werden und formt das Statement dementsprechend um.

Der Entwickler muss sich also um nichts mehr kümmern. Was vorher ein totaler "Code-Wirr-Warr" war, ist jetzt ein sauberer lesbarer Code. Allerdings gibt es hier noch eine kleine Einschränkung. Beim SQL Server kann man für den Wert des Parameters nicht null angeben. Das ist der Grund für die if-Abfrage auf "firstName != null". Wenn ein Parameter den Wert null erhält, erhält meine eine SqlException:

Code:
1:	{"Die parametrisierte Abfrage '[Abfragetext]' erwartet den @FirstName-Parameter, der nicht bereitgestellt wurde."}

Es muss also immer der Wert DBNull.Value übergeben werden. Siehe dazu auch

Zusätzliche Hinweise

Wie man hier sieht, will der SQL Server bei den Parameternamen ein "@" davor haben. Also "@FirstName". Hier muss man ebenfalls aufpassen, denn das ist von DBMS zu DBMS unterschiedlich (Siehe dazu

Unterschiede der DB-Systeme und DbProvider)

Neben der Typsicherheit im SQL Server haben Parameter noch einen weiteren sehr großen Vorteil: Performance. Die Performance bei parameteresierten Abfragen ist wesentlich höher als bei "zusammengebauten" Abfragen. Der Grund ist, dass jede Abfrage die zu einem Server geschickt wird, gecached wird. Eine Abfrage, die per Hand zusammengebaut wurde ändert sich vom Text her ja permanent, wobei eine parameteresierte Abfrage vom Text her immer gleich ist. Hier ändern sich nur die Werte der Abfrage, nicht die Abfrage selber. Bei Abfragen, die sehr oft ausgeführt werden, macht sich das tatsächlich bemerkbar. Die CPU-Last sinkt dadurch deutlich.

juetho

Unterschiede der DB-Systeme und DbProvider

Beispiele werden vorzugsweise so angegeben, nämlich mit '@' und Parameternamen, wie es vom SqlClient verwendet wird:

C#-Code:

double d = 123.45;

SqlCommand cmd = new SqlCommand("UPDATE MyTable SET myValue = @myValue;");

cmd.Parameters.AddWithValue("@myValue", d);

Bei den unterschiedlichen DB-Systemen ist natürlich die Syntax des jeweiligen SQL-Dialekts zu beachten. Außerdem benutzen auch die dazugehörigen DbProvider eigene Regeln. Es ist dringend zu empfehlen, in der

bzw. der jeweiligen Dokumentation die Hinweise zur konkreten DbParameterCollection und zum DbParameter nachzulesen; dort werden verschiedene Sonderfälle und Bedingungen behandelt.

Die Namen der Parameter müssen (bezogen auf einen DbCommand) eindeutig sein. Meistens wird Groß- und Kleinschreibung nicht unterschieden; weil die Dokumentation dazu aber nicht immer klar ist, empfehlen wir, dass man die Parameter so schreibt wie Variablen im Code und dabei auf die Schreibweise achtet, da es die Lesbarkeit des eigenen Codes erhöht.

Grundsätzlich soll die Anzahl der Parameter im SQL-Befehl und in der Parameter-Liste übereinstimmen. Wenn das nicht gegeben ist, gibt es teilweise eine Exception, teilweise wird der Befehl nicht oder nicht richtig verarbeitet, teilweise funktioniert alles. (Auch die Dokumentationen sind nicht klar: "andernfalls wird möglicherweise ein Fehler ausgelöst".) Es ist deshalb besser, immer auf gleiche Anzahl zu achten.

MS-SQL Server: System.Data.SqlClient Namespace und
MS-SQL Server (Compact Edition): System.Data.SqlServerCe Namespace

Im SQL-Befehl werden Parameter mit '@' und Namen gekennzeichnet. In den Parameters.Add-Methoden und den Parameter-Konstruktoren kann '@' weggelassen werden.

Hinweis: Dass '@' weggelassen werden kann, geht nicht aus der Dokumentation hervor, sondern nur aus der praktischen Erfahrung. Es ist deshalb besser, das '@' immer zu benutzen.

Allgemeiner Datenzugriff (Access, Textdateien, Paradox, dBase):
System.Data.Odbc Namespace und System.Data.OleDb Namespace

Anstelle benannter Parameter werden mit einem Fragezeichen (?) markierte Positionsparameter verwendet. Es ist deshalb besonders wichtig, dass die Reihenfolge und Anzahl der Parameter im SQL-Befehl und in der Parameter-Liste übereinstimmen (große Fehlerquelle!).

C#-Code:

double d = 123.45;

OleDbCommand cmd = new OleDbCommand("UPDATE MyTable SET myValue = ?;");

cmd.Parameters.AddWithValue("@p1", d);

MySQL Connector/Net: MySql.Data.MySqlClient Namespace

Parameter werden mit '?' und Namen gekennzeichnet. Befehle sollten mit Prepare vorbereitet werden.

C#-Code:

MySqlCommand cmd = new MySqlCommand();

cmd.CommandText = "INSERT INTO myTable VALUES(NULL, ?number, ?text)";

cmd.Prepare();



cmd.Parameters.AddWithValue("?number", 1);

cmd.Parameters.AddWithValue("?text", "One");

Hinweis: In der MySql-Dokumentation gibt es Beispiele mit '@'.

FZelle weist z.B. in

INSERT in mySql darauf hin, dass '?' richtig ist und dass Prepare nicht unbedingt benötigt wird.

Dokumentation siehe

MySQL Connector/Net

dotConnect for MySQL (früher CoreLab): Devart.Data.MySql Namespace

Dieser DbProvider kennt Parameter mit und ohne Namen. Unbenannte Parameter werden mit '?' gekennzeichnet:

C#-Code:

MySqlCommand cmd = new MySqlCommand();

...

cmd .CommandText = "INSERT INTO dept (deptno, dname, loc)

   VALUES (?, ?, ?)";

cmd .Parameters.AddWithValue("param1", 30);

cmd .Parameters.AddWithValue("param2", "SALES");

cmd .Parameters.AddWithValue("param3", "CHICAGO");

Benannte Parameter werden wahlweise mit '@' oder mit ':' gekennzeichnet. Das '@' muss in der Parameter-Liste verwendet werden, das ':' darf dort nicht auftauchen. Beispiel:

C#-Code:

cmd .CommandText = "UPDATE dept

   SET dname = :dname, loc = :loc WHERE deptno = @deptno";

cmd .Parameters.AddWithValue("@deptno", 20);

cmd .Parameters.AddWithValue("dname", "SALES");

cmd .Parameters.AddWithValue("loc", "NEW YORK");

Dokumentation siehe

dotConnect

Firebird NET Provider: FirebirdSql.Data.Firebird Namespace

Parameter werden mit '@' und Namen gekennzeichnet. Die Anzahl muss übereinstimmen.

Dokumentation zu holen über

Firebird NET Provider

System.Data.OracleClient Namespace

Dieser DbProvider ist Bestandteil des NET Framework. Vorzuziehen ist einer der speziellen Oracle-Provider.

Im SQL-Befehl ist der Parameter mit ':' und Namen zu kennzeichnen; an anderen Stellen darf der Doppelpunkt nicht verwendet werden.

C#-Code:

double d = 123.45;

OracleCommand cmd = new OracleCommand("UPDATE MyTable

   SET myValue = :myValue;");

cmd.Parameters.AddWithValue("myValue", d);

Oracle Data Provider ODP.NET: Oracle.DataAccess.Client Namespace

Dieser DbProvider kennt Parameter mit und ohne Namen. Unbenannte Parameter werden mit ':' und laufender Nummer gekennzeichnet. (Die Dokumentation wirft Fragen auf: Die Parameter sollen 0-basiert sein, aber das Beispiel beginnt mit 1. Daumen runter

)

C#-Code:

OracleCommand cmd = new OracleCommand();

cmd.CommandText = "insert into emp(empno, ename, deptno) values(:1, :2, :3)";

cmd.Parameters.AddWithValue("paramEmpno", 1234);

cmd.Parameters.AddWithValue("paramEname", "Client");

cmd.Parameters.AddWithValue("paramDeptNo", 10m);

Benannte Parameter werden ebenfalls mit Doppelpunkt ':' gekennzeichnet, der in der Parameter-Liste nicht auftauchen darf.

C#-Code:

OracleCommand cmd = new OracleCommand();

cmd.CommandText = "insert into emp(empno, ename, deptno)

   values (:paramEmpno, :paramEname, :paramDeptNo)";

cmd.Parameters.AddWithValue("paramEmpno", 1234);

cmd.Parameters.AddWithValue("paramEname", "Client");

cmd.Parameters.AddWithValue("paramDeptNo", 10m);

Dokumentation siehe

ODP.NET

dotConnect for Oracle (früher CoreLab): Devart.Data.Oracle Namespace

Parameter werden mit ':' und Namen gekennzeichnet.

C#-Code:

OracleCommand cmd = new OracleCommand();

cmd.CommandText = "UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = :deptno";

cmd.Parameters.AddWithValue("deptno", 20);

cmd.Parameters.AddWithValue("dname", "SALES");

cmd.Parameters.AddWithValue("loc", "NEW YORK");

Dokumentation siehe

dotConnect

SQLite Provider: System.Data.SQLite Namespace

Dieser DbProvider kennt Parameter mit und ohne Namen. Unbenannte Parameter werden im Sql-String mit '?' gekennzeichnet, in der Parameter-Liste mit null als Parameternamen oder einer Überladung, die den Namen nicht enthält.

Benannte Parameter werden mit '@' gekennzeichnet, der auch in der Parameter-Liste auftauchen muss.

Dokumentation zu holen über

SQLite. Diese enthält zwar fast keine Beispiele; da sich die Entwickler aber weitestgehend an die Vorgaben von MS gehalten haben, kann man sich an den Beispielen des SqlClient orientieren.

Der

Finisar-Provider ist veraltet.

Besonderer Dank gilt FZelle, der mir mit seinen Antworten im Forum bei dieser Aufstellung sehr geholfen hat! Jürgen

juetho

Zusätzliche Hinweise

Varianten von Parameters.Add

Bei allen vorigen Beispielen habe ich Parameters.AddWithValue verwendet. Dies kann ab NET 2.0 als Standardverfahren angesehen werden, wenn die Parameter für einen DbCommand nur einmal benutzt werden. Außerdem muss der Datentyp des Wertes prinzipiell eindeutig sein und mit dem Datentyp des Feldes in der Datenbank kompatibel sein.

Hinweis: Auch hier gilt, dass Db-Bezeichner durch die Bezeichner des jeweiligen DbProviders zu ersetzen sind. Im Index der

sind die Db-Methoden nicht immer zu finden, aber die Methoden der abgeleiteten Klasse stehen zur Verfügung. Allerdings ist nicht sicher, dass jeder DbProvider alle Varianten anbietet. Auf die eigene Suche in der jeweiligen Dokumentation darf also nicht verzichtet werden.

Auch mit einem Konstruktor kann ein DbParameter erzeugt, dann mit einem Wert versehen und der Parameter-Liste hinzugefügt werden. Dies wird aber - schon wegen der größeren Schreibarbeit - nur selten gemacht:

C#-Code:

DbParameter param = new DbParameter("@p1", DbType.Int32);

param.Value = 1234;

cmd.Parameters.Add(param);

Vor allem für StoredProcedures mit Output-Parameter ist diese Variante relevant:

C#-Code:

DbParameter param1 = new DbParameter("@p1", DbType.VarChar, 35);

param1.Direction = ParameterDirection.Input;    //  Standardwert, kann entfallen

param1.Value = "Hans Meyer";

cmd.Parameters.Add(param1);



DbParameter param2 = new DbParameter("@p2", DbType.Int32);

param2.Direction = ParameterDirection.Output;   //  kein Standardwert, muss festgelegt werden

cmd.Parameters.Add(param2);

Die wichtigsten Alternativen nenne ich hier.

Ein spezieller Datentyp kann auf die gleiche Weise direkt angegeben werden:

C#-Code:

cmd.Parameters.Add("@p1", DbType.Int32);

cmd.Parameters["@p1"].Value = 1234;

//  oder in einem Schritt:

cmd.Parameters.Add("@p1", DbType.Int32).Value = 1234;

Bei Strings muss die maximale Länge in der Regel berücksichtigt werden:

C#-Code:

cmd.Parameters.Add("@p1", DbType.VarChar, 35);

cmd.Parameters["@p1"].Value = "Hans Meyer";

//  oder in einem Schritt:

cmd.Parameters.Add("@p1", DbType.VarChar, 35).Value = "Hans Meyer";

Eine bestimmte Tabellenspalte kann mit der folgenden Variante angesprochen werden. Achtung: Mit dem letzten Argument SourceColumn ist die Spalte in der DataTable gemeint, nicht diejenige in der DB-Tabelle (die wird immer durch den SQL-Befehl angesteuert):

C#-Code:

cmd.Parameters.Add("@p1", DbType.VarChar, 35, "Name");

cmd.Parameters["@p1"].Value = "Hans Meyer";

//  oder in einem Schritt:

cmd.Parameters.Add("@p1", DbType.VarChar, 35, "Name").Value = "Hans Meyer";

hinzugefügt durch juetho am 02.07.2009
Übrigens gehört bei der LIKE-Bedingung das '%' zum Vergleichswert, nicht in den Befehlstext:

C#-Code:

cmd.CommandText = "SELECT * FROM MyTable WHERE Name LIKE @myValue;");

cmd.Parameters.AddWithValue("@myValue", "%" + content + "%");

Ende der Einfügung

Vorgehen bei DbDataAdapter.Update()

Für das Einlesen von Daten mit DbDataAdapter.Fill() sind keine weiteren Anmerkungen nötig.

Für das Speichern von Daten mit DbDataAdapter.Update() müssen jeweils ein InsertCommand, UpdateCommand, DeleteCommand vorhanden sein. Wenn ein SelectCommand vorhanden ist, der ein Feld mit PrimaryKey enthält, kann der DbDataAdapter in der Regel die Sql-Befehle selbst erstellen. Wenn Befehle manuell erstellt werden müssen, ist ein passender Parameter-Konstruktor ähnlich dem vorigen mit Angabe einer Tabellenspalte zu verwenden.

Der TableAdapter des Visual Studio benutzt sehr ausführliche Konstruktoren, bei denen alle Eigenschaften eines Parameters zugeordnet werden:

C#-Code:

_adapter.DeleteCommand.CommandText =

   "DELETE FROM [dbo].[tblTest]

    WHERE (([ID] = @Original_ID)

      AND ((@IsNull_Title = 1 AND [Title] IS NULL) OR ([Title] = @Original_Title)))";

_adapter.DeleteCommand.CommandType = CommandType.Text;

_adapter.DeleteCommand.Parameters.Add(new SqlParameter(

   "@Original_ID", SqlDbType.BigInt, 0, ParameterDirection.Input, 0, 0, "ID",

   DataRowVersion.Original, false, null, "", "", ""));

Unabdingbar ist die Angabe einer bestimmten Tabellenspalte. Vor allem die Standardwerte eines Parameters müssen nicht ausdrücklich festgelegt werden.

Der Parameter-Wert durch Value wird in dieser Situation nicht durch Programmcode zugeordnet. Den Wert holt sich der DbDataAdapter anhand der Tabellenspalte und DataRowVersion selbständig.

Zusatzproblem, wenn null-Werte eingegeben werden können

Folgende Versuche sind zwar möglich, führen aber unter Umständen zu Problemen, wenn Werte manchmal Inhalt haben und manchmal null sein können:

C#-Code:

cmd.Parameters.AddWithValue("@Vorname", null);

cmd.Parameters.AddWithValue("@Vorname", Convert.DBNull);

cmd.Parameters.Add("@Vorname", DbType.VarChar, 35).Value = null;

Das kann mit einer einfachen Abfrage unterschieden werden. Im TableAdapter wird das so geregelt:

C#-Code:

if (VornameText == null)

    cmd.Parameters[1].Value = DBNull.Value;

else

    cmd.Parameters[1].Value = (string)VornameText;

Dies kann natürlich auch verkürzt werden, je nach Situation mit einer der folgenden oder ähnlichen Arten:

C#-Code:

cmd.Parameters["@Vorname"].Value = VornameText == null ? DBNull.Value : VornameText;

cmd.Parameters.AddWithValue

   ("@Vorname", String.IsNullOrEmpty(VornameText) ? Convert.DBNull : VornameText);

// oder noch kürzer:

cmd.Parameters.Add(new DbParameter("@Vorname", VornameText ?? Convert.DBNull));

Zur letzten Variante siehe

Kanntet ihr den ??-Operator?.

Wiederholte Verwendung der gleichen Parameter

Wenn in einer Schleife ein SQL-Befehl mehrfach manuell ausgeführt werden soll und sich nur die Parameter-Werte ändern, ist es nicht sinnvoll, in jedem Durchgang die Parameter mit Clear zu löschen und mit Add neu hinzuzufügen. Es müssen doch nur die Werte neu zugeordnet werden:

C#-Code:

DbCommand cmd = new DbCommand(commandString);

cmd.Parameters.Add("@Vorname", DbType.VarChar, 35);

//  DbConnection öffnen, DbTransaction starten

foreach(string current in CurrentStrings) {

   cmd.Parameters["@Vorname"].Value = current;

   cmd.ExecuteNonQuery();

}

//  DbTransaction abschließen

Allgemeine Einschränkungen

hinzugefügt durch juetho am 02.07.2009
Parameter können nur verwendet werden für SQL-Befehle der DML (Data Manipulation Language), also Select, Insert, Update, Delete. Sie sind nicht zulässig für DDL-Befehle (Data Definition Language) wie Create Table und auch nicht für DCL (Data Control Language), nämlich Grant und Revoke.

Parameter sind nur vorgesehen, um Inhalte variabel zu übergeben. Sie können nicht statt der Namen von Tabellen oder Spalten benutzt werden. So etwas geht überhaupt nicht:

C#-Code:

cmd.CommandText = "UPDATE @table SET @column = @value;";

cmd.Parameters.AddWithValue("@table", "Adress");

cmd.Parameters.AddWithValue("@column", "Prename");

cmd.Parameters.AddWithValue("@value", "Juergen");

Allenfalls auf dem folgenden Weg können Tabellen und Spalten variabel gestaltet werden:

C#-Code:

cmd.CommandText = String.Format("UPDATE {0} SET {1} = @value;", "Adress", "Prename");

cmd.Parameters.AddWithValue("@value", "Juergen");

Ende der Einfügung

Zusatzproblem: WHERE ... IN ...

Unter

Probleme mit Abfragen bei Firebird wurde dies versucht:

Code:
1: 2: 3:	SELECT a.*, b.id AS cat_id, b.title, b.setting FROM diary a LEFT JOIN diary_categories b ON (a.cat_id = b.id) WHERE startDate >= @startDate AND endDate <= @endDate AND b.id IN (@cats)

C#-Code:

cmd.Parameters.AddWithValue("@startDate", startDate);

cmd.Parameters.AddWithValue("@endDate", endDate);

cmd.Parameters.AddWithValue("@cats", inString);

Der Anwender soll für inString einen String wie "1,3,5,6,7,9,10" einsetzen (b.id wurde als INTEGER NOT NULL deklariert).

So kann es nicht funktionieren, weil als Parameter ein String erzeugt und übergeben wird, aber der DbProvider eine Liste von Werten (im Beispiel von int-Werten) übergeben soll. In einem solchen Fall muss die Liste der IN-Suchwerte in zwei Schleifen übergeben werden: zum einen als Parameter-Deklaration im SQL-String, zum anderen als Parameter-Definition und Wert in der Parameter-Liste. Siehe die "schnelle" Lösung von

Xynratron am Ende von

Probleme mit Abfragen bei Firebird.

Zusatzproblem: Access und decimal führt zu Fehlern

Das hat eigentlich nichts mit Parametern zu tun, kann aber im gleichen Zusammenhang auftreten; deshalb erwähne ich es hier. OleDbType.Currency und OleDbType.Decimal funktionieren unter Access beim Datenaustausch oft nicht korrekt, wie FZelle mehrfach erwähnt. Stattdessen sollte double verwendet werden.