cgicorner.ch

Informationen rund um Perl/CGI

Sie sind hier: Home > CGI Hilfe > Tutorial > Kapitel 12 - MySQL

Tutorial - Kapitel 12: MySQL-Datenbanken

Voraussetzungen für dieses Kapitel

  • Webserver mit Perl/CGI-Unterstützung
  • MySQL-Datenbank
  • Grundlegende MySQL-Datenbankkenntnisse

Einleitung

Dieses Kapitel des Tutorials befasst sich mit der Anbindung von Perl an eine MySQL-Datenbank. Wir lernen, wie wir die Verbindung zur MySQL-Datenbank herstellen, Ergebnisse einer SELECT-Abfrage in Perl weiterverarbeiten können und wie wir INSERT-, UPDATE- und DELETE-Statements absetzen und auslesen können, ob das Statement erfolgreich war.

Dieses Tutorial befasst sich nicht mit dem Syntax von MySQL-Statements, dies würde den Rahmen sprengen. Deshalb setze ich grundlegende SQL-Kenntnisse über den Aufbau eines SQL-Statements voraus. Weiterführende Informationen rund um MySQL findet man beispielsweise auf folgenden Seiten (nur ein kleiner Auszug)

Voraussetzungen für den Betrieb von Perl mit einer MySQL-Datenbank

Benutzer, welche sich wie in Kapitel 1 beschrieben, einen Webserver mit XAMPP aufgesetzt haben, werden bereits eine MySQL-Datenbank haben. Ansonsten kann unter http://dev.mysql.com/downloads/ der MySQL-Server für Linux, Windows und diverse andere Betriebssysteme herunter geladen werden. XAMPP-Benutzer müssen für dieses Kapitel jedoch zwingend ActivePerl installiert haben.

Für die Verbindung zwischen Perl und MySQL werden die zwei Perl-Module "DBI" und "DBD::MySQL" benötigt. Module dienen dazu, die Funktionalität von Perl zu erweitern. Es gibt zwei Hauptgründe, wieso Module existieren: 1. können Perl-Module von beliebigen Personen geschrieben und veröffentlicht werden. Dadurch ist es also möglich, dass auch unabhängige Drittpersonen Perl um zusätzliche Funktionen erweitern können, was den Funktionsumfang von Perl stark vergrössert. 2. werden Module nur dann geladen, wenn sie explizit benötigt werden. Dies bewirkt, dass beim Aufruf von Perl nicht 10'000 Befehle geladen werden müssen und entlastet CPU- und RAM-Anforderungen von Perl.

Um zu testen, ob die Module installiert sind, können auf der Kommandozeile folgende Befehle ausgeführt werden:

perl -e "use DBI;"
perl -e "use DBD::MySQL;"

Wenn das Modul installiert ist, erscheint nur eine Leerzeile. Erscheint eine Fehlermeldung, so muss das Modul (bzw. beide Module) nachinstalliert werden.

Unter Linux sucht man sich dazu entweder das passende Paket von den Installations-CDs, Paketmanagern wie "yum" oder "yast" oder Internet-Archiven wie rpmseek.com. Das Paket heisst in der Regel "perl-DBI" oder "perl-DBD-mysql". Alternativ kann man sich das Modul auch im Quelltext herunterladen und compilieren, dazu gibt man auf der Kommandozeile perl -MCPAN -e shell ein und installiert dann das Modul mit "install DBI" bzw. "install DBD-mysql".

Unter Windows nutzt man "ppm", welcher mit ActivePerl automatisch mitinstalliert wurde. Dazu reicht es, wenn man auf der Kommandozeile folgende Befehle eingibt:

ppm install DBI
ppm install DBD-MySQL

Nach der Installation müsste eine erneute Eingabe von perl -e "use DBI;" und perl -e "use DBD::MySQL;" ohne Fehlermeldungen funktionieren.

Die Installation von MySQL ist ebenfalls schnell getestet: dazu reicht die Eingabe von mysql -uroot -p. Mit der MySQL-Installation von ApacheFriend muss man vorher noch in das MySQL-Programmverzeichnis (C:\Programme\xampp\mysql\bin) wechseln. Danach wird man nach dem root-Passwort des MySQL-Servers gefragt. Sofern man dieses nicht geändert hat ist dies leer und kann einfach mit Return bestätigt werden. Wenn alles funktioniert, erscheint nun der MySQL-Prompt "mysql>". Ist dies der Fall funktioniert der MySQL-Server. Der Prompt kann durch die Eingabe von "quit" beendet werden.

Wenn bisher alles funktioniert, ist die MySQL-Datenbank sowie die Schnittstelle zu Perl einsatzbereit und wir können mit den einzelnen Scripts beginnen.

Die DEMO-Datenbank

Die hier aufgeführten Beispiele gehen stets von einer kleinen Demo-Tabelle "adressen" aus. Wer diese Beispiele 1:1 nachvollziehen möchte, sollte sich deshalb die folgenden Daten in seine MySQL-Datenbank laden:

USE test;
DROP TABLE if exists adressen;
CREATE TABLE adressen (
  id int(5) NOT NULL auto_increment,
  vorname varchar(30),
  name varchar(30),
  strasse varchar(30),
  plz varchar(30),
  ort varchar(30),
  telefon varchar(30),
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
);
INSERT INTO adressen VALUES("1", "Hans", "Muster", "Mustergasse 99",
  "9999", "Mustershausen", "099 999 99 99");
INSERT INTO adressen VALUES("2", "Fritz", "Müller", "Hauptstrasse 90",
  "8888", "Testeshausen", "088 888 88 88");

Dies geht entweder über phpMyAdmin (bei ApacheFriends unter http://localhost/phpMyAdmin defaultmässig installiert), indem man in der Hauptmaske die "test" Datenbank auswählt, dann auf "SQL" klickt und die oberen Zeilen unverändert einfügt. Natürlich kann man die Tabelle auch in jeder anderen Datenbank erstellen, wichtig ist aber, dass a) die Datenbank existiert und dass b) der korrekte Name anstelle von USE test; eingetragen wird.

Alternativ kann man sich die Daten auch über die Kommandozeile importieren, indem man die oberen Zeilen in eine Textdatei speichert und mittels mysql -uroot -p < c:\dbdaten.txt importiert.

Wichtig: Der Einfachkeit halber gehen diese Beispiele stets davon aus, dass sich die Tabelle "adressen" in der Datenbank "test" befindet und der Benutzer "root" kein Passwort besitzt. Dies sind die Standard-Einstellungen. Natürlich sollte in Produktivumgebungen stets ein eigener Benutzer für Scriptzugriffe erstellt werden (da ja Benutzername und Passwort unverschlüsselt im Script stehen) und sowohl der root-Benutzer als auch der neue Script-Benutzer mit Passwort versehen werden.

Die erste Abfrage

In einem ersten Beispiel widmen wir uns nun mal der Abfrage der Daten mittels einem CGI-Script. Dabei geben wir einfach alle Daten unverändert aus: nicht spektakulär, trotzdem sehen wir hier bereits, wie wir die Datenbankverbindung herstellen und die Ergebnisse in einer Schleife verarbeiten (sqlselect.cgi).

#!/usr/bin/perl
print "Content-type: text/html\n\n";
print "<html><body>";
print "<table border=\"0\" width=\"100%\">";
use DBI;
my ($dbh, $sqlData, $sqlResult);
$dbh = DBI->connect("DBI:mysql:test:localhost", "root", "") or
  die("Verbindungsfehler: ".DBI->errstr);

$sqlData = $dbh->prepare("SELECT * FROM adressen;");
$sqlData->execute;
while ($sqlResult = $sqlData->fetchrow_hashref) {
  print "<tr valign=\"top\">";
  print "<td>".$sqlResult->{"vorname"}." ".$sqlResult->{"name"}."</td>".
    "<td>".$sqlResult->{"strasse"}.
    "</td><td>".$sqlResult->{"plz"}." ".$sqlResult->{"ort"}."</td>".
    "<td>".$sqlResult->{"telefon"}."</td>";
  print "</tr>";
}
$sqlData->finish;
$dbh->disconnect;

print "</table></html></body>";

Beginnen wir mit der Erklärung zuoberst: use DBI; bedeutet, dass wir das Modul für die Datenbank laden wollen. Dies stellt uns zusätzliche Funktionen zur Verfügung.

Mit der Zeile DBI->connect. Bei der Verbindung müssen Datenbank (test), Server (localhost), Benutzername (root) und Passwort übergeben werden. Wenn die Verbindung fehl schlägt, wird die "or" Aktion ausgeführt, in diesem Fall ein Crash des Scriptes mit entsprechender Fehlermeldung. Wenn alles klappt, haben wir ein Objekt $dbh. Wie auch bei Variablen ist der Name des Objektes (beinahe) beliebig.

Das eigentliche SQL-Statement wird dann mit $sqlData = $dbh->prepare("SELECT * FROM adressen;"); definiert (wiederum in ein neues Objekt) und dann mit $sqlData->execute; ausgeführt. Auch hier gilt: der Name dieses neuen Objekts kann frei definiert werden.

In diesem Fall verarbeiten wir die einzelnen Ergebniszeilen in einer While-Schleife: fetchrow_hashref dient dazu, dass auf die einzelnen Ergebnisspalten durch Angabe ihrer Feldbezeichnung zugegriffen werden kann, wie wir im Beispiel sehen ($sqlResult->{"vorname"}). Alternativ kann auch fetchrow_array verwendet werden, welches das Ergebnis in einem Array speichert: dies ist geringfügig performanter, jedoch ziemlich unübersichtlich und gerade nach Anpassungen des Datenbank-Layouts häufig sehr fehleranfällig. Der Vollständigkeit halber die While-Schleife nun aber noch mit "fetchrow_array":

while ($myArray = $sqlData->fetchrow_array) {
  print "<tr valign=\"top\">";
  print "<td>".$myArray[0]." ".$myArray[1]."</td><td>".$myArray[2].
    "</td><td>".$myArray[2]." ".$myArray[4]."</td><td>"$myArray[5]."</td>";
  print "</tr>";
}

Hier ist deutlich ersichtlich, dass wir in diesem Fall erst bei der Ansicht der Datenbank sehen, an welcher Array-Position sich welches Feld befindet. Kommen wir nun auf die Idee, ein neues Feld (z.B. "Mittelname" nach "Vorname") einzufügen, verschieben sich die Positionen der nachfolgenden Felder. Bei einem "SELECT * FROM" ist die "fetchrow_array"-Variante deshalb nicht empfehlenswert. Bei definierten Abfragen ("SELECT vorname, name, strasse FROM ...") ist die Anwendung von fetchrow-Array möglich (neue Felder können am Ende der Abfrage eingefügt werden, Position im Array ist anhand des SELECT-Statements sichtbar). Ob man mit "fetchrow_hashref" oder "fetchrow_array" arbeitet ist schlussendlich wohl Geschmacksache, ich persönlich bevorzuge definitiv "fetchrow_hashref".

Die weiteren Zeilen diesen dazu, die erstellten Objekte für die Abfrage sauber zu beenden und die Datenbank-Verbindung zu schliessen. Mit Ende des Scripts sollte dies sowieso der Fall sein, gerade wenn aber später das Objekt noch für weitere Abfragen benutzt wird, ist ein sauberes schliessen des Objekts notwendig.

Weitere SQL-Statements

Das Grundprinzip der Abfrage gilt auch für alle anderen SQL-Statements (UPDATE, INSERT, DELETE): Query mit "prepare" vorbereiten, mit "execute" ausfühlen, ggf. Ergebnis mit "fetchrow_hashref" abfragen und dann mit "finish" abschliessen.

Bei UPDATE, INSERT oder DELETE Statements möchte man wahrscheinlich noch den Status der Abfrage speichern und später prüfen wollen.

Dies erreicht man am besten mit $rowsAffected=$sqlData->execute;. Die Variable $rowsAffected enthält die Anzahl Datensätze, welche vom Statement betroffen waren: funktioniert übrigens auch mit SELECT-Abfragen. Ist dieser Wert "0" liegt wohl ein Fehler vor, der in $dbh->errstr genauer beschrieben wird und ausgegeben werden kann.

Wenn wir bei INSERT-Statements eine Tabelle mit einer "auto_increment" Spalte haben, können wir mit $dbh->{'mysql_insertid'} die ID abfragen, welche die MySQL-Datenbank diesem Datensatz vergeben hat.

Sicherheit - ein wichtiges Thema

Bei SQL-Abfragen, welche anhand Benutzer-Eingaben zusammengestellt werden ist es notwendig, die Benutzereingaben ganz genau zu prüfen und ggf. eine Fehlermeldung auszugeben. Ansonsten ist mit wenig Aufwand ein SQL-Injecton möglich. Ein Beispiel-Statement:

SELECT * FROM adressen WHERE name = '$FORM{search}';
Bei der Eingabe von "Muster" lautet die Abfrage korrekt:
SELECT * FROM adressen WHERE name = 'Muster';
Dies ist gewollt und funktioniert einwandfrei. Nun gibt aber der Besucher in das Eingabefeld folgendes ein: "'; DELETE FROM adressen; SELECT '", passiert folgendes:
SELECT * FROM adressen WHERE name = ''; DELETE FROM adressen; SELECT '';
Nun haben wir mehrere Abfragen: zuerst werden alle Datensätze mit leerem Namen selektiert. Dann aber, werden ALLE Datensätze dieser Tabelle gelöscht: definitiv nicht im Sinne des Erfinders!

Deshalb gilt:

  • 1. Benutzereingaben, welche in einer SQL-Abfrage verwendet werden sorgfältig prüfen
  • 2. Benutzereingaben, welche in einer SQL-Abfrage verwendet werden noch sorgfältig prüfen
  • 3. Benutzereingaben, welche in einer SQL-Abfrage verwendet werden können nie sorgfältig genug geprüft werden
  • TEXT immer in Single-Quotes '', Single-Quotes aus Variablen-Inhalt entfernen oder Backquoten (\'): $wert =~ s/([\'\"\`])/\\$1/g;
  • ZAHLEN immer mit int() übergeben oder allenfalls alle nicht numerischen Zeichen aus Variable löschen: $wert =~ s/[^0-9\.\,]//g;

Beispiel: INSERT-Statement

Damit wir unser kleines Script von vorhin (sqlselect.cgi) auch sinnvoll nutzen können, schreiben wir nun noch ein zweites Script, welches neue Datensätze in die Datenbank speichert. Dazu benötigen wir ein Formular (sqlform.htm) sowie ein CGI-Script (sqlinsert.cgi). Beginnen wir mit dem Formular - mittlerweile ein Kinderspiel:

<html>
<body>
<form action="/cgi-bin/sqlinsert.cgi" method="post">
Vorname <input type="text" name="vorname"/><br/>
Name <input type="text" name="name"/><br/>
Strasse <input type="text" name="strasse"/><br/>
PLZ/Ort <input type="text" name="plz"/> <input type="text" name="ort"/><br/>
Telefon <input type="text" name="telefon"/><br/>
<input type="submit" value="absenden"/>
</form>
</body>
</html>

Nun widmen wir uns dem Kernstück: dem Script. Hier lesen wir zuerst die Formulardaten in %FORM ein, prüfen dann die Werte resp. eliminieren Einfallstore und fügen dann den Datensatz in die Tabelle ein und liefern die ID des Datensatzes zurück. Die Plausibilität der Eingaben wird aus Platzgrünen nicht genauer geprüft: würde das Script produktiv eingesetzt werden, würde man wohl noch Pflichtfelder definieren, PLZ und Telefonnummer prüfen (nur Zahlen) etc.

#!/usr/bin/perl
print "Content-type: text/html\n\n";
print "<html><body>";
use DBI;
my ($dbh, $sqlData, $sqlResult, $rowsAffected, $errorCode, %FORM);
$dbh = DBI->connect("DBI:mysql:test:localhost", "root", "") or
  die("Verbindungsfehler: ".DBI->errstr);

&readForm;  # Formulardaten einlesen
foreach (keys(%FORM)) {  # Werte backquoten
  $FORM{$_} =~ s/([\'\"\`\\])/\\$1/g;
}

# Werte einfügen
$sqlData = $dbh->prepare("INSERT INTO adressen (vorname, name, strasse, plz, ort,".
   ".telefon) VALUES ('".$FORM{"vorname"}."','".$FORM{"name"}."','".$FORM{"strasse"}.
   "','".$FORM{"plz"}."','".$FORM{"ort"}."','".
  $FORM{"telefon"}."');");
$rowsAffected=$sqlData->execute;

if ($rowsAffected > 0) {  # 1 oder mehr Datensätze betroffen: alles OK
  print "Abfrage erfolgreich: $rowsAffected Datensätze betroffen.";
  print "ID des Datensatzes: ".$dbh->{'mysql_insertid'}.".";
} else {  # Fehler
  print "Es ist ein Fehler aufgetreten: ".$dbh->errstr;
}

$sqlData->finish;
$dbh->disconnect;

print "</html></body>";

sub readForm {  # Parameter in %FORM einlesen
  my ($buffer,@pairs,@pair,$i);
  if ($ENV{'REQUEST_METHOD'} eq "GET") {
    $buffer = $ENV{'QUERY_STRING'};
  } else {
    read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
  }
  @pairs = split(/&/, $buffer);
  foreach (@pairs) {
    @pair = split(/=/, $_, 2);
    for ($i=0;$i<2;$i++) {
      $pair[$i] =~ tr/+/ /;
      $pair[$i] =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
    }
    $FORM{$pair[0]} = $pair[1];
  }
}

Der Script beginn und das Einlesen der Formular-Daten in %FORM ist uns ja aus früheren Kapiteln bekannt. Der Aufbau der Datenbankverbindung kennen wir aus dem SELECT-Script.

Mit der Foreach-Schleife geben wir dem Script minimale Sicherheit: alle Versuche, mit " oder ' aus der Abfrage auszubrechen werden durch konsequentes Backquoten (" wird immer \") unterbunden. Nochmals: eine Prüfung des Variablen-Inhaltes wird immer empfohlen, im Zusammenhang mit Datenbanken ist er sogar unerlässlich: ein Vorname wird genau so selten ein \ enthalten, wie ein @-Zeichen.

Als nächstes schreiben wir die einzelnen Werte mit einem SQL-Befehl in die Datenbank: INSERT INTO tabelle (feld1, feld2, feld3) VALUES ('wert1','wert2','wert3');

Mit "execute" wird dieses SQL-Statement ausgeführt und der Rückgabewert enthält die Anzahl Datensätze, welche von dieser Funktion betroffen waren (bei INSERT ist das immer nur einer, oder 0 bei einem Fehler. Bei SELECT-, UPDATE- oder DELETE-Statements sind aber häfig mehrere Datensätze betroffen.

Am Ende des Scriptes folgt dann noch die Ausgabe an den Benutzer: waren mehr als 0 Zeilen betroffen, war die Abfrage erfolgreich und eine entsprechende Meldung wird ausgegeben. $dbh->{'mysql_insertid'} enthält die ID (auto_increment-Feld) des Datensatzes. Bei einem Fehler wird mit $dbh->errstr die (in der Regel englische) Fehlermeldung des SQL-Servers ausgegeben.

Zusammenfassung

Um mit Perl eine MySQL-Datenbank anzubinden werden zwei zusätzliche Perl-Module benötigt, welche auf dem Server installiert sein müssen. Module dienen dazu, Drittpersonen die Möglichkeit zu geben, Perl zu erweitern und Befehle erst dann zu laden, wenn sie auch wirklich benötigt werden (Performance).

Es werden nur sehr wenige Perl-Befehle benötigt, um (fast) alle Funktionen von MySQL nutzen zu können. Mit Rückgabewerten kann einfach geprüft werden, ob ein SQL-Statement erfolgreich war oder nicht: bei einem Fehler steht eine detaillierte Fehlermeldung des MySQL-Servers zur Verfügung.

Für Datenbankanfragen an eine MySQL-Datenbank mit Perl sind SQL-Kenntnisse erforderlich: Abfragen etc. werden in SQL an den Server gesendet, als würde man sie auf der Kommandozeile des SQL-Servers ausführen.

Im SQL-Injektions zu vermeiden, sollte dem Thema Sicherheit eine grosse Rolle beigemessen werden.

Wie geht's weiter?

In Kapitel 13 gibt es Tipps, wie man sich die Arbeit beim Programmieren erleichtern kann.
Das vorherige Kapitel (11) beschäftigte sich mit Perl Regular-Expressions.
Sie können aber auch zurück zum Inhaltsverzeichnis und dort ein beliebiges anderes Kapitel auswählen.