Webtechnologien Teil 12: PHP 5 mit PDO/MySQL
Transcrição
Webtechnologien Teil 12: PHP 5 mit PDO/MySQL
Webtechnologien Teil 12: PHP 5 mit PDO/MySQL Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 03.12.15 1 Übersicht • • • • Arten der Kommunikation mit dem Datenbank-Server Grundsätzliche Operationen Weitere Routinen Tricks und Tipps Siehe: http://dev.mysql.com/doc/refman/5.5/en/apis-php.html http://de3.php.net/manual/en/book.mysqli.php Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 2 Einführung I • PHP hat jeweils eine eigene API zum Zugriff auf die Datenbanken in Form eines Treibers. • Bei MySQL ist es die MySQLi-Schnittstelle. • Da sich die Schnittstellen zu verschiedenen Datenbanken unterscheiden, wurde eine einheitliche PDO-Schnittstelle geschaffen. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 3 PHP-Schnittstellen zu Datenbanken (Auszug) Datenbank Datenbank Datenbank Adabas D Oracle MySQL dBase PostgreSQL MySQLi Empress Sybase MariaDB FrontBase Interbase FilePro Informix DB2 Ingres Und natürlich auch ODBC... Siehe dazu: http://de.wikipedia.org/wiki/Open_Database_Connectivity Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 4 Schnittstellen zu MySQL Es gibt aus historischen Gründen drei Schnittstellen zu MySQL: • mysql-Schnittstelle (Veraltet, wird nicht mehr unterstützt) • mysqli-Schnittstelle (improved, die aktuelle Version) • PDO-Schnittstelle (Portable Data Object) Diese abstrahiert von den verschiedenen Schnittstellen zu den Datenbanken – aber nur syntaktisch - den Aufruf. Die speziellen Eigenschaften der SQL-Dialekte der jeweiligen Datenbanken bleiben jedoch erhalten! Diese Schnittstelle wird hier beschrieben. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 5 Das PDO-Objekt • Die Benutzung der Schnittstelle erfolgt nur Objekt-orientiert. • Durch das Erzeugen des Objekts wird eine Verbindung zur Datenbank aufgebaut, die beim Entfernen des Objekts geschlossen wird. • Das allgemeine Schema des Zugriffs sieht daher so aus: Aufbau der Verbindung Zugriffe auf die Datenbank Abbau der Verbindung Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 6 Das PDO-Objekt - Vorbereitungen • In der php.ini-Datei müssen die Module für den Datenbankzugriff aktiviert sein: extension=php_mysqli.dll extension=php_pdo_mysql.dll extension=php_pdo_odbc.dll • Das lässt sich durch einen Aufruf von phpinfo() anzeigen (explizit programmiert oder über die XAMPP-Webschnittstelle): Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 7 Bemerkungen • In diesem Foliensatz wird ein Teil eines Logins für eine Website beschrieben. • • Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 8 Verbindung zur Datenbank I object PDO(DSN [,Nutzer [,Passwort[,Optionen]]]); • • • DSN ist der Data Source Name: die Definition des Zugriffs auf die Datenbank. Dies ist ein String mit dem Aufbau: Datenbanktyp: Keywort=Wert; Keywort=Wert; … – Datenbanktyp ist hier mysql – Für Keywort können hier eingesetzt werden: – Host: Angabe der IP-Adresse der Datenbank dbname: Name der ausgewählten Datenbank Nutzer und Passwort sind Strings Der letzte Parameter definiert Optionen, die für die Kommunikation mit der Datenbank wichtig sind (ein Hash mit definierten Keys) Siehe: http://www.php.net/manual/de/book.pdo.php http://de.wikipedia.org/wiki/Data_Source_Name Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 9 Verbindung zur Datenbank II - Beispiel (1) $DSN= 'mysql:host=localhost;dbname=accounts'; (2) $DB_USER= 'nobody'; (3) $DB_PW= 'blabla426'; (4) $DB_options = array( (5) PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' (6) ); (7) try { (8) $db= new PDO($DSN,$DB_USER,$DB_PW,$DB_options); (9) $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); (10)} catch(PDOException $err) { (11) echo 'DB ERROR: '.$err->getMessage().PHP_EOL; (12)} • • • • Die Datenbank liegt auf der eigenen Maschine und heißt "accounts": (1). Der User ist "nobody" mit dem Passwort "blabla426" (2) und (3). Mit den Optionen wird der Zeichensatz auf UTF-8 gesetzt: (4) bis (6). Es wird der Warn-Modus eingeschaltet: (9). Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 10 Bemerkungen zu PHP PDO::MYSQL_ATTR_INIT_COMMAND Klassenname Name der Konstante PDO::MYSQL_ATTR_INIT_COMMAND ist in PHP eine static-Konstante. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 11 Verbindung zur Datenbank III - Erläuterungen (4) $DB_options = array( (5) PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' (6) ); MySQL-spezifische Konstante(n) • • Es sind noch andere Kommandos an MySQL möglich, z.B.: – PDO::MYSQL_ATTR_LOCAL_INFILE (alternative Konfigurationsdatei) um z.B. zum Testen eine andere Konfiguration als bei normalen Betrieb zu benutzen. – PDO::MYSQL_ATTR_COMPRESS Daten der Verbindung werden komprimiert Siehe dazu: http://de2.php.net/pdo_mysql Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 12 Verbindung zur Datenbank IV - Erläuterungen (7) try { (8) $db= new PDO($DSN,$DB_USER,$DB_PW,$DB_options); (10)} catch(PDOException $err) { (11) echo 'DB ERROR: '.$err->getMessage().PHP_EOL; (12)} • • Im Fehlerfalle wirft das PDO-Objekt eine Exception, die mit der obigen Konstruktion gefangen werden kann. getMessage() erzeugt einen String mit der Fehlermeldung. • PHP_EOL ist die Konstante mit der Bedeutung End-of-Line. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 13 Verbindung zur Datenbank V - Erläuterungen (7) try { (8) $db= new PDO($DSN,$DB_USER,$DB_PW,$DB_options); (9) $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); (10)} catch(PDOException $err) { • • • Nachdem das Objekt generiert wurde, können dem Objekt Eigenschaften zugewiesen werden, die aber nur das PDO-Objekt betreffen, nicht die Kommunikation mit MySQL. Es gibt drei Modi: – PDO::ERRMODE_SILENT Es wird lediglich ein Errorcode geliefert, der explizit nach jedem PDOMethodenaufruf abgefragt werden muss. Default! – PDO::ERRMODE_WARNING Es wird eine Warn-Meldung ausgegeben sowie der Errorcode geliefert. – PDO::ERRMODE_EXCEPTION Es wird eine Exception geworfen (siehe erstes Beispiel). Siehe dazu: http://www.php.net/manual/de/pdo.error-handling.php Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 14 Hinweise • Ob mit Exceptions oder mit einem der anderen Modi gearbeitet wird, hängt von der Programmierkonvention im betreffenden Projekt ab. • Die Benutzung von Exceptions führt zu einem umständlichen Code; dafür entfallen die vielen Fehlerabfragen. • Wer sowieso immer den Erfolg einer Routine in einer Fehlerabfrage abfragt, der sollte – während der Entwicklung im Warn-Modus – während der Produktion im Silent-Modus arbeiten. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 15 Unser Beispiel weiter • Der MySQL-Server muss natürlich laufen. • Auch muss die Datenbank mit dem User "nobody" und dem Passwort "blabla426" eingerichtet sein. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 16 Einrichten einer Datenbank I Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 17 Einrichten einer Datenbank II Es hat geklappt! Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 18 Einrichten einer Datenbank III Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 19 Einrichten einer Datenbank III Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 20 Hinweis • Das Gewähren aller Rechte für einen Benutzer ist nur dann sinnvoll, wenn es – wie hier – um eine Spielanwendung geht. • In der Realität müssen die Rechte beschränkt werden. • Diese Beschränkung lässt sich gut mit phpMyAdmin konfigurieren. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 21 Einrichten einer Datenbank IV So sieht es schon einmal ganz gut aus... Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 22 Unser Beispiel weiter II – Create Table Nun bauen wir eine Tabelle in SQL: CREATE TABLE account ( id int(11) NOT name varchar(45) pw varchar(64) groupID int(11) NOT ) ENGINE=InnoDB DEFAULT • • • • • NULL PRIMARY KEY, NOT NULL, NOT NULL, NULL CHARSET=utf8; id ist der Schlüsselwert in der Tabelle. Der Login-Name und das Passwort dürfen nicht leer sein(!). Dann bereiten wir noch Gruppen vor, indem eine Gruppen-ID als Integer zugelassen wird. Da wird Transaktionen haben wollen muss die InnoDB-Version von MySQL benutzt werden. Und natürlich UTF-8 als Zeichensatz. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 23 Unser Beispiel weiter III – Create Table (1) (2) (3) (4) (5) (6) $create_table = "CREATE TABLE account ("; $create_table.= "id int(11) NOT NULL PRIMARY KEY,"; $create_table.= "name varchar(45) NOT NULL,"; $create_table.= "pw varchar(64) NOT NULL,"; $create_table.= "groupID int(11) NOT NULL"; $create_table.= ") ENGINE=InnoDB DEFAULT CHARSET=utf8;"; (7) $rtn= $db->exec($create_table); (8) if($rtn===false) { (9) $err= $db->errorInfo(); (10) echo 'DB ERROR: #'.$err[1]." ".$err[2].PHP_EOL; (11)} • Die Tabelle wird mit exec() angelegt. • Der String für $create_table wird stückweise mit der verkürzten .-Notation zusammengesetzt. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 24 Create Table I - Erläuterungen (7) $rtn= $db->exec($create_table); (8) if($rtn===false) { • exec(„SQL-String“) führt eine SQL-Operation aus, bei der keine Werte anschließend abgefragt werden, d.h. exec() kann nicht für Abfragen von Tabelleninhalten benutzt werden. • In der Variablen $rtn wird der Return-Code abgespeichert, der – im Fehlerfall den boole'schen Wert false (0) hat, – ansonsten die Anzahl der betroffenen Zeilen in der Tabelle enthält. • Daher muss mit === abgefragt werden (weil die Anzahl ja auch 0 sein kann, was dann als false interpretiert wird) Siehe: http://www.php.net/manual/de/pdo.exec.php • Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 25 Create Table II - Erläuterungen (8) if($rtn===false) { (9) $err= $db->errorInfo(); (10) echo 'DB ERROR: #'.$err[1]." ".$err[2].PHP_EOL; (11)} • • Die Methode errorInfo() liefert ein Array zurück: – [0] enthält den SQL-Error-Code – [1] enthält den Error-Code des Treibers – [2] enthält die Fehlermeldung Siehe: http://www.php.net/manual/de/pdo.errorinfo.php • Dann gibt es noch die Methode errorCode(), die lediglich den SQL-ErrorCode liefert. Dazu gibt es eine Dokumentation: http://docstore.mik.ua/orelly/java-ent/jenut/ch08_06.htm • Siehe: http://www.php.net/manual/de/pdo.errorcode.php Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 26 Unser Beispiel weiter IV - Drop Table (1) $create_table = "CREATE TABLE account ("; ... (6) $create_table.= ") ENGINE=InnoDB DEFAULT CHARSET=utf8;"; (7) $rtn= $db->exec($create_table); (8) if($rtn===false) { (9) $err= $db->errorInfo(); (10) echo 'DB ERROR: #'.$err[1]." ".$err[2].PHP_EOL; (11)} (12)$delete_table= "DROP TABLE account;"; (13)$rtn= $db->exec($delete_table); (14)if($rtn===false) { (15) $err= $db->errorInfo(); ...} • • Die Tabelle account wird hier gelöscht. Das machen wir hier nur für unser Beispiel, damit wir in mehreren Durchläufen etwas ausprobieren können. Für ein Login-Modul ist das natürlich unsinnig. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 27 Unser Beispiel weiter V – Einfügen von Daten (1) (2) (3) (4) (5) (6) (7) • • $insert = "INSERT INTO account(id,name,pw,groupID)" $insert.= "VALUES(0,'root','bitte!',0)"; $rtn= $db->exec($insert); if($rtn===false) { $err= $db->errorInfo(); echo 'DB ERROR: #'.$err[1]." ".$err[2].PHP_EOL; } In die Tabelle account wird ein einziger Record (Zeile) eingefügt. Bitte beachten Sie, dass in SQL andere String-Begrenzer als in PHP benutzt werden. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 28 Wir glauben ja nichts – sehen wir nach • Das ist die Ausgabe der Tabelle in phpMyAdmin. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 29 Unser Beispiel weiter VI – Einfügen von Daten $val[]=array('id'=>0,'name'=>'root', 'pw'=>'bitte!','groupID'=>0); $val[]=array('id'=>1,'name'=>'user', 'pw'=>'danke!','groupID'=>0); $val[]=array('id'=>2,'name'=>'helmut','pw'=>'sdgafg','groupID'=>0); $val[]=array('id'=>3,'name'=>'evelyn','pw'=>'tztrff','groupID'=>0); (1) foreach($val as $v) { (2) $insert = "INSERT INTO account(id,name,pw,groupID) "; (3) $insert.= "VALUES(${v['id']},'${v['name']}','${v['pw']}'"; (4) $insert.= ",${v['groupID']})"; (5) $rtn= $db->exec($insert); (6) if($rtn===false) { (7) $err= $db->errorInfo(); (8) echo 'DB ERROR: #'.$err[1]." ".$err[2].PHP_EOL; (9) } (10)} Es wird eine Tabelle zum Füllen der Datenbank-Tabelle verwendet. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 30 Einfügen von Daten - Erläuterungen (1) foreach($val as $v) { (2) $insert = "INSERT INTO account(id,name,pw,groupID) "; (3) $insert.= "VALUES(${v['id']},'${v['name']}','${v['pw']}'"; (4) $insert.= ",${v['groupID']})"; • • Die Konstruktion ${v['id']} ist erforderlich, weil ein Ausdruck zur Auswertung innerhalb des Strings benutzt wird, daher die {}. Ohne diese Auswertung wäre $v ausreichend. Die einzelnen Elemente des Hash sind ja benannt, so dass relativ schnell klar wird, was wann in das Insert-SQL-Statement eingefügt wird. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 31 Wir glauben ja nichts – sehen wir nach • Das ist die Ausgabe der Tabelle in phpMyAdmin. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 32 Unser Beispiel weiter VII – Auslesen von Daten (1) $db= new PDO($DSN,$DB_USER,$DB_PW,$DB_options); (2) $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); (3) $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); ... (4) $query= "SELECT * FROM account"; (5) $data= $db->query($query); (6) foreach($data as $row) { (7) print_r($row); (8) } • Es wird das Attribut PDO::ATTR_DEFAULT_FETCH_MODE beim Erzeugen des PDO-Objekts gesetzt, das die Art des Objekts beim Auslesen von Informationen mit der Methode query() bestimmt. Möglich sind: – PDO::FETCH_ASSOC: Es wird ein Hash mit den Namen der Spalten geliefert (das ist zu empfehlen). – PDO::FETCH_BOTH: Hash mit Array-Indices wird geliefert. – PDO::FETCH_OBJ: Ein Objekt wird generiert. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 33 Unser Beispiel weiter VIII – Auslesen von Daten Array ( [id] => 0 [name] => root [pw] => bitte! [groupID] => 0 ) Array ( [id] => 1 [name] => user [pw] => danke! [groupID] => 0 ) Array ( [id] => 2 [name] => helmut [pw] => sdgafg [groupID] => 0 ) Array ( [id] => 3 [name] => evelyn [pw] => tztrff [groupID] => 0 ) Das ist die Ausgabe des letzten Beispiels. Mit jedem Schleifendurchlauf wird eine Zeile aus der Tabelle gelesen und als Hash geliefert. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 34 Explizites Auslesen von Daten (5) $data= $db->query($query); $row= $data->fetch($fetch_style); $table= $data->fetchall($fetch_style); • • • • Ein query() liefert den Inhalt einer Tabelle als ein Objekt vom Typ PDOStatement. Das foreach des Beispiels Zeile (6) durchläuft in Wirklichkeit ein Objekt. Mit fetch() wird die nächste Zeile des letzten Querys gelesen. Der optionale Parameter gibt an, von welchem Typ das Ergebnis sein soll. Hier sind dieselben Werte wie beim setAttribute() möglich. Der Defaultwert ist der, der als Attribut beim Erzeugen des Objektes angegeben wurde. fetchall() liest die ganze Tabelle in ein Array ein. Die Art der Elemente dieses Arrays werden genauso bestimmt wie bei fetch(). fetchall() ist natürlich nur bei kleinen Tabellen anwendbar. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 35 Abstraktes Auslesen I – Meta-Information (1) (2) (3) (4) (5) (6) • • • • $query= "SELECT * FROM account"; $data= $db->query($query); echo $data->columnCount().PHP_EOL; for($i= 0;$i<$data->columnCount();$i++) { var_export($data->getColumnMeta($i)); } Meta-Informationen sind solche über etwas. Mit columnCount() wird die Anzahl der Spalten ausgelesen, mit getColumnMeta() eine Beschreibung der Spalte. Rechts steht der Beginn der Ausgabe. Es ist der Aufbau des Deskriptors erkennbar. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 4 array ( 'native_type' => 'LONG', 'pdo_type' => 2, 'flags' => array ( 0 => 'not_null', 1 => 'primary_key', ), 'table' => 'account', 'name' => 'id', 'len' => 11, 'precision' => 0, ) 36 Abstraktes Auslesen II – Ausgabe der Tabelle (1) $data= $db->query($query); (2) $limit= $data->columnCount(); (3) for($i= 0;$i<$limit;$i++) { (4) $meta= $data->getColumnMeta($i); (5) echo $meta['name']." "; (6) } (7) echo PHP_EOL; (8) foreach($data as $row) { (9) foreach($row as $val) { (10) echo $val." "; (11) } (12) echo PHP_EOL; (13)} • • Es wird eine Überschrift für die Ausgabe aus der Datenbank generiert. Dann werden in einer doppelten for-Schleife die Zeilen ausgegeben: jeweils eine Zeile mit mehreren Einträgen. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL id name pw groupID 0 root bitte! 0 1 user danke! 0 2 helmut sdgafg 0 3 evelyn tztrff 0 37 Abstraktes Auslesen III – Ausgabe der Tabelle (1) $data= $db->query($query); (2) $limit= $data->columnCount(); (3) for($i= 0;$i<$limit;$i++) { (4) $meta= $data->getColumnMeta($i); (5) printf('%10s ',$meta['name']); (6) } (7) echo PHP_EOL; (8) foreach($data as $row) { (9) foreach($row as $val) { id name (10) printf('%10s ',$val); 0 root (11) } 1 user (12) echo PHP_EOL; 2 helmut (13)} 3 evelyn • • pw bitte! danke! sdgafg tztrff groupID 0 0 0 0 Damit es etwas schöner aussieht, wird zur Ausgabe die Routine printf() verwendet, die einen Format-String zur Definition des Layouts erlaubt. Die einzelnen Optionen des ersten Parameters von printf() sind http://de3.php.net/manual/de/function.printf.php zu entnehmen. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 38 Nun endlich zum Login I (1) $LoginName= 'root'; // Simulation des Formulars (2) $LoginPW= 'bitte!'; (3) $query= "SELECT * FROM account WHERE name='$LoginName'"; (4) $data= $db->query($query); (5) $rows= $data->fetchAll(); (6) echo count($rows).PHP_EOL; (7) var_export($rows); (8) if((count($rows)==1)&&($LoginPW==$rows[0]['pw'])) { (9) echo "loggt in".PHP_EOL; (10)} else { 1 (11) echo "NOT loggt in".PHP_EOL; array ( (12)} • • $LoginName und $LoginPW kommen aus dem Formular, was hier durch die Zuweisungen simuliert wird. Beachten Sie die Abfrage in (8): es muss ein einziger Eintrag da sein (count()) und das PW muss stimmen. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 0 => array ( 'id' => '0', 'name' => 'root', 'pw' => 'bitte!', 'groupID' => '0', ), )loggt in 39 Nun endlich zum Login II (1) $LoginName= 'root'; (2) $LoginPW= 'bitte!'; (3) $query= "SELECT * FROM account WHERE name='$LoginName'"; (4) $data= $db->query($query); (5) $rows= $data->fetch(); (6) echo gettype($rows).PHP_EOL; (7) var_export($rows); (8) if(($rows!==false)&&($LoginPW==$rows['pw'])) { (9) echo "loggt in".PHP_EOL; (10)} else { (11) echo "NOT loggt in".PHP_EOL; (12)} array • • • Das ganze geht auch mit einem fetch(), nur dass der Datentyp des Ergebnisses anders ist. Das zeigt sich in der Abfrage. var_export($rows) gibt die Struktur des Ergebnisses aus. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL array ( 'id' => '0', 'name' => 'root', 'pw' => 'bitte!', 'groupID' => '0', )loggt in 40 Alternative zum Füllen der Tabelle I (1) (2) (3) (4) (5) (6) (7) (8) (9) $insert = "INSERT INTO account(id,name,pw,groupID)"; $insert.= "VALUES(?,?,?,?)"; $stmt= $db->prepare($insert); $id= 0; $name= 'root'; $pw= 'bitte!'; $groupID= 0; $stmt->bindValue(1,$id, PDO::PARAM_INT); $stmt->bindValue(2,$name, PDO::PARAM_STR); PDO::PARAM_STR); $stmt->bindValue(3,$pw, $stmt->bindValue(4,$groupID,PDO::PARAM_INT); $stmt->execute(); id name pw 0 • • • • root bitte! groupID 0 Bei den gebundenen Parametern wird eine Schablone (1-2) erstellt, die dann dem Datenbank-Server mitgeteilt wird (3). In der Schablone wird an die Parameter-Positionen ein ? gesetzt. In der Reihenfolge der ? werden die Parameter per bindValue(), also als Wertekopie, den ? mit einer Typangabe (z.B. PDO::PARAM_INT) zugeordnet. Beim execute() werden die Werte zum Datenbank-Server geschickt, das SQLStatement dort ausgefüllt und dann ausgeführt. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 41 Alternative zum Füllen der Tabelle II Schablone erzeugen bindValue() bindParam() Parameter zuordnen Ausführen Integer: PDO::PARAM_INT String: PDO::PARAM_STR Bool: PDO::PARAM_BOOL • Das ist das allgemeine Schema bei der Benutzung von gebundenen Parametern. • • Neben dem schon eingeführten bindValue() gibt es noch das bindParam(), das die Bindung über eine Referenz realisiert. Neben den beiden Typen für String und Integer gibt es noch den Typ Boolean. • Siehe dazu: http://www.php.net/manual/en/pdostatement.bindparam.php Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 42 Alternative zum Füllen der Tabelle III (1) (2) (3) (4) (5) (6) (7) $insert = "INSERT INTO account(id,name,pw,groupID)"; $insert.= "VALUES(?,?,?,?)"; $stmt= $db->prepare($insert); $stmt->bindParam(1,$id, PDO::PARAM_INT); $stmt->bindParam(2,$name, PDO::PARAM_STR); $stmt->bindParam(3,$pw, PDO::PARAM_STR); $stmt->bindParam(4,$groupID,PDO::PARAM_INT); (8) foreach($val as $row) { (9) $id= $row['id']; (10) $name= $row['name']; (11) $pw= $row['pw']; (12) $groupID= $row['groupID']; id (13) $stmt->execute(); 0 (14)} 1 Das Hash $val ist wie oben gesetzt. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 2 3 name root user helmut evelyn pw bitte! danke! sdgafg tztrff groupID 0 0 0 0 43 Alternative zum Füllen der Tabelle IV - Bemerkung (3) $stmt= $db->prepare($insert); (4) $stmt->bindParam(1,$id, PDO::PARAM_INT); ... (8) foreach($val as $row) { (9) $id= $row['id']; (10) $name= $row['name']; (11) $pw= $row['pw']; (12) $groupID= $row['groupID']; (13) $stmt->execute(); (14)} • • • In der Schleife wird immer wieder auf die gebundenen Variablen zugegriffen. Bei bindValue() wird der Wert zum Zeitpunkt des Bindens fest dem ? zugeordnet. Bei bindParam() wird der aktuelle Wert zum Zeitpunkt des execute() benutzt. $val[]=array('id'=>0,'name'=>'root', $val[]=array('id'=>1,'name'=>'user', Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 'pw'=>'bitte!','groupID'=>0); 'pw'=>'danke!','groupID'=>0); 44 Alternative zum Füllen der Tabelle V (1) (2) (3) (4) (5) (6) (7) $insert = "INSERT INTO account(id,name,pw,groupID)"; $insert.= "VALUES(:id,:name,:pw,:groupID)"; $stmt= $db->prepare($insert); $stmt->bindParam(':id', $id, PDO::PARAM_INT); $stmt->bindParam(':name', $name, PDO::PARAM_STR); $pw, PDO::PARAM_STR); $stmt->bindParam(':pw', $stmt->bindParam(':groupID',$groupID,PDO::PARAM_INT); (8) foreach($val as $row) { (9) foreach($row as $key=>$val) { (10) $$key= $val; (11) } Das Hash $val ist wie oben gesetzt. (12) $stmt->execute(); (13)} Mit einem Doppelpunkt eingeleitet lassen sich die ? wie Variablen adressieren. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 45 Alternative zum Füllen der Tabelle VI - Erläuterung (8) foreach($val as $row) { (9) foreach($row as $key=>$val) { (10) $$key= $val; (11) } (12) $stmt->execute(); (13)} Hier wird der Name der Variablen anhand des Wertes einer anderen Variablen bestimmt! So etwas geht nur in Skriptsprachen. Auszug vom Setzen des Hash $val: $val[]=array('id'=>0,'name'=>'root', $val[]=array('id'=>1,'name'=>'user', Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 'pw'=>'bitte!','groupID'=>0); 'pw'=>'danke!','groupID'=>0); 46 SQL Injection I (1) $LoginName= 'root'; $LoginPW= 'bitte!'; (2) $update= "UPDATE account SET pw = '*balla*' WHERE id = 0"; (3) $data= $db->exec($update); id 0 1 2 3 • • name root user helmut evelyn pw *balla* danke! sdgafg tztrff groupID 0 0 0 0 Zur Vorbereitung einer kleinen Demo für SQL-Injection wird ein Update ausprobiert. SQL-Updates werden ganz analog zum Insert – also auch mit gebundenen Parametern - ausgeführt. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 47 SQL Injection II (1) $LoginPW= 'bitte!'; (2) $update= "UPDATE account SET pw = '*balla*' WHERE id = 0"; (3) $LoginName= "a';$update"; (4) $query= "SELECT * FROM account WHERE name='$LoginName'"; (5) echo $query.PHP_EOL; (6) $data= $db->query($query); Quotes (7) $rows= $data->fetch(); (8) if(($rows!==false)&&($LoginPW==$rows['pw'])) { (9) echo "loggt in".PHP_EOL; (10)} else { (11) echo "NOT loggt in".PHP_EOL; (12)} Ausgabe: • SELECT * FROM account WHERE name='a'; UPDATE account SET pw = '*balla*' WHERE id = 0' Statt eines vernünftigen Loginnamens wird wie in (3) simuliert ein Mischung aus einem Namen und einer SQL-Anweisung – hier ein Update – eingegeben. Die Zusammensetzung wird unten in 2 Zeilen dargestellt. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 48 SQL Injection III - Abwehr (1) (2) (3) (4) (5) (6) $LoginPW= 'bitte!'; $update= "UPDATE account SET pw = '*balla*' WHERE id = 0"; $LoginName= "a';$update"; $LoginName= $db->quote($LoginName); $query= "SELECT * FROM account WHERE name=$LoginName"; echo $query.PHP_EOL; Keine Ausgabe: SELECT * FROM account WHERE name='a\'; Quotes UPDATE account SET pw = \'*balla*\' WHERE id = 0' • • • quote() fügt vor allen "gefährlichen" Zeichen ein Backslash ein, der die Funktion des Zeichens ausschaltet. Daher ist der Loginname der gesamte unterstrichene Bereich. Es wird kein weiteres SQL-Kommando ausgeführt. Zum Glück – oder aus Absicht – ist MySQL so konfiguriert, dass auch ohne quote() nichts passiert wäre, da jeweils nur ein Kommando ausgeführt wird. Es müssen immer Formulareingaben vor dem Einsetzen in SQLStrings geprüft werden! Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 49 SQL Injection IV – Beste Abwehr (1) $LoginPW= 'bitte!'; (2) $update= "UPDATE account SET pw = '*balla*' WHERE id = 0"; (3) $LoginName= "a';$update"; (4) $query= "SELECT name, pw FROM account WHERE name=:LoginName"; (5) $stmt= $db->prepare($query); (6) $stmt->bindValue(':LoginName',$LoginName,PDO::PARAM_STR); (7) $stmt->execute(); (8) $stmt->bindColumn('name',$RealName,PDO::PARAM_STR); (9) $stmt->bindColumn('pw' ,$RealPW, PDO::PARAM_STR); (10)$rows= $stmt->fetch(); (11)echo $RealName." ".$RealPW.PHP_EOL; (12)if($LoginPW==$RealPW) { (13) echo "loggt in".PHP_EOL; (14)... • Die Benutzung von Prepared Statements ist das Beste, da die Formulardaten immer als Daten behandelt werden. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 50 SQL Injection V – Erläuterungen (4) (6) (8) (9) • • • • • $query= "SELECT name, pw FROM account WHERE name=:LoginName"; $stmt->bindValue(':LoginName',$LoginName,PDO::PARAM_STR); $stmt->bindColumn('name',$RealName,PDO::PARAM_STR); $stmt->bindColumn('pw' ,$RealPW, PDO::PARAM_STR); Spaltennamen von erzeugten temporären Tabellen (durch SELECT) lassen sich auch PHP-Variablen zuordnen. bindColumn() verbindet den Namen einer SQL-Spalte mit einer Variablen. Nach jedem fetch() werden die aktuellen Werte der gelesenen Zeile den Variablen zugewiesen. Der Angriff via SQL-Injection funktioniert nun nicht mehr, da das SQLUPDATE als Datum in der WHERE-Klausel behandelt wird. Das ändert nichts daran, alle Formulardaten zu prüfen! Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 51 Transaktionen I (1) $db->beginTransaction(); (2) $update= "UPDATE account SET pw = '*balla*' WHERE id = 0"; (3) $data= $db->exec($update); (4) $update= "UPDATE account SET pw = '*' WHERE id = 3"; (5) $data= $db->exec($update); (6) var_dump($data); (7) $update= "UPDATE account SET pw = '??' WHERE id = 4"; (8) $data= $db->exec($update); (9) var_dump($data); (10)$db->commit(); Fehlerhaftes UPDATE (11)//$db->rollBack(); • • • • Mit beginTransaction() wird eine Transaktion eingeleitet und mit commit() positiv abgeschlossen; erst dann sind die Daten geändert sichtbar. Mit rollBack() werden alle Änderungen verworfen. Der Rückgabewert von UPDATE ist die Anzahl der betroffenen Zeilen. Das funktioniert nur bei Tabellentypen, die Transaktionen unterstützen. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 52 Transaktionen II Fehlerhaftes UPDATE int(1) int(0) id 0 1 2 3 name root user helmut evelyn pw *balla* danke! sdgafg * groupID 0 0 0 0 id 0 1 2 3 name root user helmut evelyn pw bitte! danke! sdgafg tztrff groupID 0 0 0 0 commit() int(1) int(0) Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL rollBack() 53 Tipps I – Globales Definieren <?php $Param['host']= $Param['uid'] = $Param['pass']= $Param['db'] = 'localhost'; 'nobody'; 'bitte!'; 'account'; $Param['dsn'] = "mysql:host=${Param['host']}"; $Param['dsn'].= ";dbname=${Param['db']}"; ?> • • Globale Parameterwerte sollten in eine Include-Datei mit der Endung ".inc.php" ausgelagert und dann als "Kopf" eingebunden werden. Aus Sicherheitsgründen sollte die Endung immer ".php" sein (was für ".inc.php" ja gilt), da der Server diese Datei nie(?) uninterpretiert zum Browser sendet. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 54 Tipps II – Benutzung der globalen Definitionen (1) (2) (3) (4) (5) (6) (7) require('myParameter.inc.php'); $DB_options = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' ); try { $db= new PDO($Param['dsn'],$Param['uid'], $Param['pass'],$DB_options); • Durch die Auslagerung der Parameter in eine externe Datei wird der Code noch allgemein gültiger und dadurch leichter zu warten. • Die Optionen könnten in einem weiteren Schritt auch ausgelagert werden. Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 55 Nach dieser Anstrengung etwas Entspannung... Webtechnologien – WS 2015/16 - Teil 12/PHP und MySQL 56