Grundlagen CGI-Programmierung mit Perl von Prof. Jürgen Plate

5 Perl und MySQL

Die Anweisung

#!/usr/bin/perl
use DBI;

Anmerkung: Es wird vorausgesetzt, daß der MySQL-Server installiert ist. Zusätzlich sind zwei Perl-Module nötig: das allgemeine Datenbankinterface DBI und der spezielle DBD::mysql-Treiber vom CPAN. Die Installation vom CPAN ist recht einfach:

perl -MCPAN -e shell
cpan> install DBD::mysql
cpan> install DBI

Dazu packen wir noch eine laufende Fahrzeugnummer (Integer) in die Tabelle (als Primärindex, siehe unten). Das Kennzeichen wird als Text-String und die Anzahl der Sitze als Integer definiert. Der Autoverleih legt die Daten aller Fahrzeuge in der Tabelle "fahrzeuge" in der Datenbank ab. Jede Zeile der Tabelle entspricht genau einem Fahrzeug des Fuhrparks:

ID     INT
kennz  VARCHAR(20)
sitze  INT

Das Programm braucht eine logische Datenbank namens "bring_m_backalive". Das Anlegen kann direkt mit dem mysql-Client erfolgen (CREATE DATABASE bring_m_backalive;). Oder Sie installieren sich das Tool PHPMyAdmin, mit dem man alle Datenbankoperationen über den Browser erledigen kann.

5.1 MySQL-Tabellen anlegen

#!/usr/bin/perl
use warnings;
use strict;

my $USER = "root";
my $PASS = "secret";

use DBI;

my @dsn = ("DBI:mysql:database=bring_m_backalive;" .
           "host=localhost", $USER, $PASS);

# Datenbank andocken
my $dbh = DBI->connect(@dsn,
    { PrintError => 0,
      AutoCommit => 1,
    }
    ) or die $DBI::errstr;

# Alte Tabelle loeschen
$dbh->do("DROP TABLE fahrzeuge");

# Neu anlegen
$dbh->do("CREATE TABLE fahrzeuge (
      ID INTEGER AUTO_INCREMENT,
      kennz VARCHAR(20),
      sitze INTEGER,
      PRIMARY KEY (ID))"
      ) or die $dbh->errstr();

$dbh->disconnect();

"DBI:mysql:database=bring_m_backalive;host=localhost"

"DBI:mysql:bring_m_backalive;localhost"

Die connect-Funktion der DBI-Klasse verbindet das Skript mit der laufenden Datenbank. Sie nimmt die eben definierten Konfigurationsparameter in "@dsn" und eine Referenz auf einen Hash mit weiteren Steuerungsparametern entgegen. Die Option "PrintError" wird auf 0 (False) gesetzt, damit die DBI-Methoden Fehlermeldungen unterdrücken. Die von connect() zurückgelieferte Referenz auf ein DBI-Objekt erlaubt es nun, mit der Datenbank zu kommunizieren.

Die do()-Methode setzt SQL-Befehle ab, die von der Datenbankengine ausgefüht werden. Nach dem Löschen einer etwa vorhandenen Tabelle wird eine leere Tabelle "fahrzeuge" mit allen Felddefinitionen angelegt. Den mehrzeiligen String zwischen den Anführungszeichen gibt Perl an die Methode weiter. Die Oder-Bedingung fängt eventuell von do() gelieferte Fehler ab.

Mit errstr() des Datenbank-Handles $dbh erhält man den Text des letzten Fehlers zurück, so daß das Konstrukt oben einfach mit die() und der Fehlermeldung der Datenbank abbricht, falls etwas schief geht. Bei CGI-Programmen landen solche Fehlermeldungen im Error-Log des Webservers.

"ID" in der Tabelle ist die laufende Nummer des Fahrzeugs. Das Attribut "AUTO_INCREMENT" ist eine MySQL-spezifische SQL-Erweiterung und gibt an, dass die Datenbank den numerischen Wert dieses Feldes selbst erzeugen soll: Beginnend mit "1" wird jeder neue Tabelleneintrag um eins erhöht. Das Attribut "PRIMARY KEY" (Primärschlüssel) bestimmt, über welche Spalte die Datenbank die Tabelle primär indiziert. Da die Seriennummer "ID" numerisch und für jede Zeile eindeutig ist, bietet sie sich als Primärschlüssel an. Es ist übrigens in der Regel sinnvoll bei jeder Tabelle einen solchen Primary Key anzulegen, über den sich Datensätze eindeutig refernziert lassen.

Ein erzeugtes Handle wird durch die Methode disconnect() wieder geschlossen.

Wer will, kan sich auch eine Funktion für das Öffnen der Datenbank schreiben, in der alle unveränderlichen Angaben enthalten sind. Die folgenden Funktion öffnet eine Datenbank auf einem angegebenen Host und liefert ein Datenbankhandle $dbh zurück:

sub open_dbase
  {
  my %Datenbank =
    ( dbname => 'fahrzeuge',
      user   => $ENV{'LOGNAME'},
      # ggf. hier einen festen User eintragen
      pass   => 'Geheim',
      host   => 'localhost',
      driver => 'mysql',
      @_
    );
  # alternativ kann das Passwort auch von Datei eingelesen werden
  # (guenstig, wenn mehrere Programme auf die DB zugreifen  
  my $dsn = "DBI:$Datenbank{'driver'}:$Datenbank{'dbname'}:$Datenbank{'host'}";
  my $dbh = DBI->connect($dsn, $Datenbank{'user'}, $Datenbank{'pass'});
  print STDERR "Error: $DBI::errstr\n"  unless $dbh;
  return $dbh;
  }

$dbh = open_dbase( dbname => 'lager' );
$dbh = open_dbase( dbname => 'lager', pw => 'secret' );

Will man sehen, was beim Datenbankzugtiff geschieht, kann noch die Zeile

$dbh->trace(LEVEL);

Sind alle Tabellen angelegt, kann die Datenbank mit Informationen gefüllt werden. Zuvor sollen aber noch zwei nützliche Hilfsdienste vorgestellt werden. Eine Liste der Tabelle bekommt man mit der Zeile:

my @tables = $dbh->func('_ListTables');

Eine Beschreibung einer Tabelle $table bekommt man mit:

my $sth = $dbh->prepare("DESC $table");
$sth->execute();
die "$DBI::errstr\n" if ($DBI::err);
... wie unten gezeigt auslesen

5.2 Die Datenbank füttern

$dbh->do("INSERT INTO fahrzeuge (kennz, sitze) VALUES ('M-AX 007','8') ")
            or die $dbh->errstr();

my $sitze = 8;
my $kennz  = "M-AX 007";

my $kennz_q = $dbh->quote($kennz);

$dbh->do("INSERT INTO fahrzeuge (kennz, sitze) VALUES ($kennz_q, $sitze)")
            or die $dbh->errstr();

Will man die geschilderten Ouotierungs-Probleme bei der do()-Methode vermeiden, verwendet man stattdessen eine Kombination aus prepare() und execute(). Grundsätzlich entspricht die Kombination dem do():

my $sth = $dbh->prepare("INSERT INTO fahrzeuge (kennz, sitze) VALUES ('M-AX 007','8')")
          or die $dbh->errstr();
$sth->execute() or die $dbh->errstr();

my $sth = $dbh->prepare("INSERT INTO fahrzeuge (kennz, sitze) VALUES (?,?)")
          or die $dbh->errstr();
$sth->execute($kennz, $sitze) or die $dbh->errstr();

• prepare() liefert ein Statement-Handle $sth zurück.
• execute() nimmt anschließend die Parameterliste entgegen, füllt die Fragezeichen im Statement mit den aktuellen Werten und fügt bei Bedarf automatisch Quotes ein und verhinder "SQL-Injection" (siehe später).

use warnings;
use strict;
use DBI;

my $USER     = "root";
my $PASSWORD = "";

my $dbh = DBI->connect("DBI:mysql:database=bring_m_backalive;" .
                       "host=localhost",$USER, $PASSWORD,
                       {PrintError => 0,
                        AutoCommit => 1}
                       );

my @fuhrpark = (
    [ "M-AX 007", 8 ],
    [ "M-N 1718", 4 ],
    [ "M-OP 471", 4 ],
    [ "M-DD 313", 2 ],
    [ "M-IX 707", 2 ],
    );

my $sth = $dbh->prepare("INSERT INTO fahrzeuge (kennz, sitze)
                         VALUES (?,?)") or die $dbh->errstr();

for (@fuhrpark)
  {
  my($kennz, $sitze) = @$_;
  $sth->execute($kennz, $sitze) or die $dbh->errstr();
  }

$dbh->disconnect();

$dbh->do("DELETE FROM fahrzeuge WHERE sitze = '2'")
       or die $dbh->errstr();

Der SQL-"UPDATE"-Befehl verändert Spaltenwerte bestimmter Datensätze. Er ist immer dann anzuwenden, wenn ein Datensatz schon existiert und sich nur einzelne Ausprägungen ändern sollen. Ändert sich beispielsweise das Kennzeichen der Limousine M-AX 007 nach MA-X 008, erledigt das folgende Programmstück die Korrektur:

$dbh->do("UPDATE fahrzeuge SET kennz = 'MA-X 008' WHERE kennz = 'M-AX 4712'")
          or die $dbh->errstr();

5.3 Daten auslesen mit SELECT

[ [z1s1, z1s2, ...],
  [z2s2, z2s2, ...],
  ...,
]

my $aref = $dbh->selectall_arrayref("SELECT * FROM fahrzeuge")
               or die $dbh->errstr();

for my $row (@$aref)
  {
  my($id, $kennz, $sitze) = @$row;
  printf "%02d %-10s %d\n", $id, $kennz, $sitze;
  }

Selektiert die Datenbank-Abfrage nur eine einzige Zeile, geht es auch einfacher. Um die Zahl der Sitze des Fahrzeugs M-AX 007 herauszufinden, holt selectrow_array() die Daten der ersten passenden Zeile ein:

my ($ID, $sitze) = $dbh->selectrow_array
                    ("SELECT ID, sitze FROM fahrzeuge WHERE kennz = 'M-AX 007'")
                   or die $dbh->errstr();
print "$ID $sitze\n";

my ($count) = $dbh->selectrow_array("SELECT count(*) FROM fahrzeuge");
die $dbh->errstr() unless defined $count;
print "$count Einträge in der Tabelle.\n";

Bei vielen Ergebnis-Datensätzen muß man nicht alle auf einmal in ein Array oder Hash einzulesen. Stattdessen geht man Satz für Satz vor. Die zuvor benutzte Methode selectall_arrayref() ist eigentlich eine Zusammenfassung dreier Befehle: prepare() bereitet eine SQL-Query vor und liefert eine Objektreferenz zurück. Per execute() wird die Query an die Datenbank geschickt, von der die fetchrow_array()-Methode das Ergebnis zeilenweise abholt und jeweils eine Liste der Spaltenwerte zurückgibt:

my $sth = $dbh->prepare("SELECT * FROM fahrzeuge WHERE kennz LIKE 'M-%'")
          or die $dbh->errstr();
$sth->execute() or die $dbh->errstr;

while (my($id, $kennz, $sitze) = $sth->fetchrow_array())
  {
  print("$id, $kennz, $sitze\n");
  }

Mit selectrow_hashref() erhält man die Ergebnisse in einem Hash, der den Spalten-Namen die Werte zuweist. Das folgende Beispiel selektiert alle Mietwagen mit 4 bis 8 Sitzen.

my $sth = $dbh->prepare
            ("SELECT * FROM fahrzeuge WHERE sitze >= 4 AND sitze <= 8")
          or die $dbh->errstr();
$sth->execute() or die $dbh->errstr;

while (my $h = $sth->fetchrow_hashref())
  {
   print "$h->{kennz} $h->{sitze}\n";
  }
die $dbh->errstr() if $dbh->err();

5.4 Fehler abfangen

$dbh->methode(...) or die "Fehler: ", $dbh->errstr();

my $dbh = DBI->connect(@DSN,
    { RaiseError => 1,
      AutoCommit => 1,
    };

$dbh->do("...");
$dbh->do("...");
# ...

eval
  {
  $dbh = DBI->connect(@DSN,
    { RaiseError => 1,
      AutoCommit => 1,
    };


    $dbh->do(...);
    $dbh->do(...);
  };

print "Fehler: $@\n" if($@);

5.5 SQL-Injection

$sql = "select count(*) from Users where Username='" . $User .
       ' AND Password='" $Pass "';"

select count(*) from users where Username='admin' AND Password='geheim';

select count(*) from users where
    Username='' OR '1'='1' AND
    Password='' OR '1'='1';

Aber es geht noch besser! Wie wär es mit dem Benutzernamen '; DROP TABLE users; --? Das ergibt die SQL-Abfrage:

select count(*) from users where Username='';
      DROP TABLE users; --' AND Password='';

select count(*) from users where Username='';
   insert into Users values('Joe','topsecret'); --' AND Password='';

SQL-Injection ist also das Einfügen von beliebigen SQL-Befehlen in Formulare, die dann am Server ausgeführt werden. Warum passiert das? Weil die Formulare dem Input der Benutzer vertrauen, und ihn nicht entsprechend validieren! Generell sind alle Daten, die von außen kommen, "böse". Die Validierung erfolgt mit Stringfunktionen, Regulären Ausdrücken, Plausbilitäts-Checks usw.

Haben Sie den Input auf Gültigkeit abgeklopft, sollten Sie nicht den Fehler machen, trotzdem die alten, fehleranfälligen SQL-Konstrukte weiterzuverwenden. Sie könnten ja ein Angriffs-Szenario vergessen haben. Für die Umstellung bieten sich parametrisierte Kommandos an. Generell sieht ein solches Kommando für das Loginformular aus, wie wir es schon bei der Dateneingabe gesehen haben:

SELECT COUNT(*) FROM Users WHERE Username=? AND Password=?

check($User);  # Validierung
check($Pass);  # Validierung
$sth = $dbh->prepare("SELECT COUNT(*) FROM Users WHERE Username=? AND Password=?")
          or die $dbh->errstr();
$sth->execute($User, $Pass) or die $dbh->errstr();

Auch völlig ohne Validierung funktioniert hier keinerlei SQL-Injection mehr (vergessen Sie die Idee gleich wieder, All input is evil, until proven otherwise!). Der gezeigte Code macht etwas mehr Aufwand als das einfache Zusammenbauen eines SQL-Statements. Allerdings ersparen Sie sich einiges an Kopfschmerzen. Und er funktioniert nicht nur für SELECT, sondern genauso für INSERT, UPDATE oder DELETE.

5.6 Übersicht: Portable DBI-Methoden

$dbh	Datenbank-Handle
$sth	Statement-Handle
$rc	Rückgabe-Code (Status)
$rv	Rückgabewert (Status)

connect($datenquelle, $benutzername, $passwort)

Benutzen Sie die connect-Methode, um eine Verbindung zur Datenbank der Datenquelle herzustellen. Der $datenquelle-Wert sollte mit DBI:Treiber_name: beginnen. Beispielanwendungen von connect mit dem DBD::mysql Treiber:

$dbh = DBI->connect("DBI:mysql:$datenbank", $benutzer, $passwort);
$dbh = DBI->connect("DBI:mysql:$datenbank:$hostname",
                    $benutzer, $passwort);
$dbh = DBI->connect("DBI:mysql:$datenbank:$hostname:$port",
                    $benutzer, $passwort);

Wenn der Benutzername und/oder das Passwort nicht angegeben werden, verwendet DBI die Werte der DBI_USER- und DBI_PASS- Umgebungsvariablen. Wenn Sie keinen Hostnamen angeben, wird 'localhost' verwendet. Wenn Sie keine Portnummer angeben, wird der MySQL-Port verwendet. Modifikatoren:

mysql_read_default_file=datei	Liest `datei' als eine Optionsdatei.
mysql_read_default_group=group_name	Beim Lesen einer Optionsdatei ist die Standardgruppe normalerweise die[client]-Gruppe. Wenn Sie die mysql_read_default_group- Option angeben, wird die Standardgruppe [gruppenname].
mysql_compression=1	Aktiviert die Kompression während der Kommunikation zwischen Client und Server.
mysql_socket=/pfad/zur/socket	Gibt den Pfad des Unix-Sockets an, der zum Verbinden mit dem Ser verwendet wird.

Sie können mehrere Modifikatoren angeben, dabei muss jedem ein Semikolon vorangestellt sein. Wenn Sie zum Beispiel vermeiden wollen, dass sie Benutzername und Passwort im DBI-Skript angeben müssen, können Sie sie aus der Optionsdatei '~/.my.cnf' nehmen. Ihr connect-Aufruf sieht folgendermaßen aus:

$dbh = DBI->connect("DBI:mysql:$datenbank"
               . ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
                $benutzer, $passwort);

Dieser Aufruf liest die Optionen für die [client]-Gruppe aus der Optionsdatei. Wenn Sie dasselbe für die [perl]-Gruppe tun wollen, könnte Ihr Code so aussehen:

$dbh = DBI->connect("DBI:mysql:$Datenbank"
                . ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
                . ";mysql_read_default_group=perl",
                $benutzer, $passwort);

disconnect

Die disconnect-Methode beendet die Verbindung mit der Datenbank. Dies wird typischerweise kurz vor dem Ende eines Scripts ausgeführt. Beispiel:

$rc = $dbh->disconnect;

prepare($statement)

Bereitet ein SQL-Statement zum Ausführen durch den Datenbankserver vor und gibt ein "Statement-Handle" ($sth) zurück, mit der Sie die execute-Methode aufrufen. Normalerweise werden Sie SELECT-Statements (und SELECT-ähnliche Statements so wie SHOW, DESCRIBE und EXPLAIN) mit der Bedeutung von prepare und execute verwenden. Beispiel:

$sth = $dbh->prepare($statement)
    or die "$statement: $dbh->errstr kann nicht vorbereitet werden\n";

execute

Die execute-Methode führt ein vorbereitetes Statement aus. Bei Nicht-SELECT-Statements gibt execute die Anzahl der betroffenen Zeilen zurück. Wenn Zeilen betroffen sind, gibt execute "0E0" zurück, was in Perl als 0 und true erkannt wird. Wenn ein Fehler auftritt, gibt execute undef zurück. Bei SELECT-Statements beginnt execute die SQL-Anfrage in der Datenbank; Sie müssen eine der fetch_*-Methoden nutzen, die weiter unten beschrieben sind, um Daten erhalten. Beispiel:

$rv = $sth->execute
          or die "Die Query: $sth->errstr kann nicht ausgeführt werden.";

do($statement)

Die do-Methode bereitet ein Statement vor, führt es aus und gibt die Anzahl der betroffenen Zeilen zurück. Wenn Zeilen betroffen sind, gibt execute "0E0" zurück, was in Perl als 0 und true erkannt wird. Diese Methode wird normalerweise verwendet, um Nicht-SELECT-Statements zu bearbeiten, die (z. B. wegen Treiber-Beschränkungen) nicht vorbereitet werden können, oder die nicht mehr als einmal vorbereitet werden müssen (INSERTS, DELETE usw.). Beispiel:

$rv = $dbh->do($statement)
        or die "$statement: $dbh- >errstr kann nicht vorbereitet werden\n";

Im Allgemeinen ist die do-Methode VIEL schneller (und vorzuziehen) als die prepare/execute-Methoden, die ohne Parameter aufgerufen werden.

quote($string)

Die quote-Methode wird verwendet, um Sonderzeichen zu "escapen", die in Zeichenketten enthalten sein können, und um notwendige äußere Anführungszeichen hinzuzufügen. Beispiel:

$rv = $dbh->quote($string)

fetchrow_array

Die Methode holt die nächste Datenzeile und gibt sie als ein Array mit den Feldwerten zurück. Beispiel:

while(@row = $sth->fetchrow_array)
  {
  print "$row[0]  $row[1]  $row[2]\n";
  }

fetchrow_arrayref

Die Methode holt die nächste Datenzeile und gibt sie als eine Referenz auf ein Array mit den Feldwerten zurück. Beispiel:

while($row_ref = $sth->fetchrow_arrayref)
  {
  print "$row_ref->[0]  $row_ref->[1]  $row_ref->[2]\n";
  }

fetchrow_hashref

Diese Methode holt eine Datenzeile und gibt eine Referenz auf einen Hash zurück, der Name-/Wert-Paare enthält. Die Methode ist lange nicht so performant wie das Verwenden von Referenzen auf ein Array, wie weiter oben beschrieben ist. Beispiel:

while($hash_ref = $sth->fetchrow_hashref)
  {
  print "$hash_ref->{vorname}";
  print "$hash_ref->{nachname}";
  print "$hash_ref->{title}\n";
  }

fetchall_arrayref

Diese Methode gibt alle Zeilen eines Ergebnisses einer SQL-Anfrage zurück. Sie gibt eine Referenz auf ein Array mit Referenzen auf Arrays mit den Werten der einzelnen Zeilen zurück. Sie können mit zwei verschachtelten Schleifen auf die Werte zugreifen. Beispiel:

my $table = $sth->fetchall_arrayref
                or die "$sth->errstr\n";
my($i, $j);
for $i ( 0 .. $#{$table} )
  {
  for $j ( 0 .. $#{$table->[$i]} )
    {
    print "$table->[$i][$j]  ";
    }
  print "\n";
  }

finish

Bewirkt, dass keine weiteren Daten von dem SQL-Anfrageergebnis geholt werden. Sie können diese Methode aufrufen, um Systemressourcen freizugeben. Beispiel:

$rc = $sth->finish;

rows

Gibt die Anzahl der veränderten Zeilen (die aktualisiert oder gelöscht wurden) des letzten Befehls zurück. Dies wird normalerweise nach Nicht-SELECT-execute-Statements verwendet. Beispiel:

$rv = $sth->rows;

NULLABLE

Gibt eine Referenz auf ein Array mit Boole'schen Werten zurück; für jedes Element TRUE kann die Spalte NULL-Werte enthalten. Beispiel:

$rv = $sth->{NULLABLE};

NUM_OF_FIELDS

Dieses Attribut enthält die Anzahl der Zeilen, die eine SELECT- oder SHOW FIELDS-SQL-Anfrage zurückgibt. Sie können es verwenden, um zu prüfen, ob eine Anfrage ein Ergebnis zurückgegeben hat: 0 weist auf eine Nicht-SELECT-Anfrage hin, wie INSERT, DELETE oder UPDATE. Beispiel:

$rv = $sth->{NUM_OF_FIELDS};

datasource($Treiber_name)

Diese Methode gibt einen Array zurück, der die Namen der verfügbaren Datenbanken auf 'localhost' enthält. Beispiel:

@dbs = DBI->datasource("mysql");

ChopBlanks

Dieses Attribut gibt an, ob die fetchrow_*-Methoden vor- und nachstehende Leerzeichen entfernen. Beispiel:

$sth->{'ChopBlanks'} = 1;

trace($trace_ebene)

trace($trace_ebene, $trace_dateiname)

trace aktiviert oder deaktiviert "Tracing". Wenn DBI als eine Klassenmethode aufgerufen wird, steuert es das "Tracing" mit allen Datenbankverbindungen. Wenn es als Datenbank- oder Statement-Handle-Methode aufgerufen wird, steuert es nur die verwendete Verbindung (und deren spätere Ableitungen). Wenn Sie $trace_ebene auf 2 setzen, bewirkt es detaillierte Informationen. Der Wert 0 stellt "Tracing" ab. Die Ausgabe des "Tracing" wird vorgabemäßig nach "standard error" geleitet. Wenn $trace_dateiname angegeben ist, wird die Ausgabe für alle "getraceten" Verbindungen an das Ende dieser Datei geschrieben. Beispiel:

DBI->trace(2);                # alles tracen
DBI->trace(2,"/tmp/dbi.out"); # alles nach /tmp/dbi.out tracen
$dth->trace(2);               # diese Datenbankverbindung tracen
$sth->trace(2);               # dieses Statement-Handle tracen.

Sie können DBI-Tracing auch anschalten, indem Sie die DBI_TRACE-Umgebungsvariable setzen. Wenn Sie sie auf einen numerischen Wert setzen, ist das dasselbe, wie DBI->(wert) aufzurufen. Wenn Sie sie auf einen Pfadnamen setzen, ist das dasselbe, wie DBI->(2,wert) aufzurufen.

Zum vorhergehenden Abschnitt

Zum Inhaltsverzeichnis

Zum nächsten Abschnitt

Copyright © FH München, FB 04, Prof. Jürgen Plate

Letzte Aktualisierung: 06. Aug 2007