PHP
PDO

Einführung

Syntax

• PDO::LastInsertId()
• PDO::LastInsertId($columnName) // Einige Treiber benötigen den Spaltennamen

Bemerkungen

// Retrieving the last inserted id
$id = null;

try {
    $id = $pdo->lastInsertId(); // return value is an integer    
}
catch( PDOException $e ) {
    echo $e->getMessage();
}

Grundlegende PDO-Verbindung und -Abfrage

Seit PHP 5.0 steht PDO als Datenbankzugriffsschicht zur Verfügung. Es ist datenbankunabhängig, daher sollte der folgende Verbindungsbeispielcode für jede unterstützte Datenbank funktionieren, indem einfach der DSN geändert wird.

// First, create the database handle

//Using MySQL (connection via local socket):
$dsn = "mysql:host=localhost;dbname=testdb;charset=utf8";

//Using MySQL (connection via network, optionally you can specify the port too):
//$dsn = "mysql:host=127.0.0.1;port=3306;dbname=testdb;charset=utf8";

//Or Postgres
//$dsn = "pgsql:host=localhost;port=5432;dbname=testdb;";

//Or even SQLite
//$dsn = "sqlite:/path/to/database"

$username = "user";
$password = "pass";
$db = new PDO($dsn, $username, $password);

// setup PDO to throw an exception if an invalid query is provided
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Next, let's prepare a statement for execution, with a single placeholder
$query = "SELECT * FROM users WHERE class = ?";
$statement = $db->prepare($query);

// Create some parameters to fill the placeholders, and execute the statement
$parameters = [ "221B" ];
$statement->execute($parameters);

// Now, loop through each record as an associative array
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    do_stuff($row);
}

Die prepare erstellt ein PDOStatement Objekt aus der PDOStatement . Die Ausführung der Abfrage und das Abrufen der Ergebnisse werden für dieses zurückgegebene Objekt ausgeführt. Im Fehlerfall gibt die Funktion entweder false oder gibt eine exception (abhängig von der Konfiguration der PDO-Verbindung).

SQL-Injektion mit parametrisierten Abfragen verhindern

Die SQL-Injection ist eine Art Angriff, bei dem ein böswilliger Benutzer die SQL-Abfrage ändern und unerwünschte Befehle hinzufügen kann. Beispielsweise ist der folgende Code anfällig :

// Do not use this vulnerable code!
$sql = 'SELECT name, email, user_level FROM users WHERE userID = ' . $_GET['user'];
$conn->query($sql);

Dies ermöglicht jedem Benutzer dieses Skripts, unsere Datenbank grundsätzlich nach Belieben zu ändern. Betrachten Sie beispielsweise die folgende Abfragezeichenfolge:

page.php?user=0;%20TRUNCATE%20TABLE%20users;

Dadurch sieht unsere Beispielabfrage so aus

SELECT name, email, user_level FROM users WHERE userID = 0; TRUNCATE TABLE users;

Dies ist zwar ein extremes Beispiel (die meisten SQL-Injection-Angriffe zielen nicht auf das Löschen von Daten ab, noch unterstützen die meisten PHP-Abfrageausführungsfunktionen die Mehrfachabfrage), dies ist jedoch ein Beispiel dafür, wie ein SQL-Injection-Angriff durch die unvorsichtige Assemblierung von möglich wird die Abfrage. Unglücklicherweise sind Angriffe wie diese sehr häufig und sehr effektiv, weil Codierer nicht die richtigen Vorsichtsmaßnahmen zum Schutz ihrer Daten treffen.

Um das Auftreten einer SQL-Injection zu verhindern, sind vorbereitete Anweisungen die empfohlene Lösung. Anstatt Benutzerdaten direkt mit der Abfrage zu verketten, wird stattdessen ein Platzhalter verwendet. Die Daten werden dann separat gesendet. Dies bedeutet, dass die SQL-Engine keine Benutzerdaten für einen Satz von Anweisungen verwirrt.

Während das Thema PDO ist, beachten Sie bitte, dass die PHP-Erweiterung MySQLi auch vorbereitete Anweisungen unterstützt

PDO unterstützt zwei Arten von Platzhaltern (Platzhalter können nicht für Spalten- oder Tabellennamen verwendet werden, nur Werte):

1. Benannte Platzhalter. Ein Doppelpunkt ( : ), gefolgt von einem eindeutigen Namen (z. B. :user )

   // using named placeholders
   $sql = 'SELECT name, email, user_level FROM users WHERE userID = :user';
   $prep = $conn->prepare($sql);
   $prep->execute(['user' => $_GET['user']]); // associative array
   $result = $prep->fetchAll();
   

2. Traditionelle SQL-Platzhalter, dargestellt als ? :

   // using question-mark placeholders
   $sql = 'SELECT name, user_level FROM users WHERE userID = ? AND user_level = ?';
   $prep = $conn->prepare($sql);
   $prep->execute([$_GET['user'], $_GET['user_level']]); // indexed array
   $result = $prep->fetchAll();
   

Wenn Sie Tabellen- oder Spaltennamen dynamisch ändern müssen, sollten Sie wissen, dass dies zu Ihren eigenen Sicherheitsrisiken und zu einer schlechten Praxis führt. Dies kann jedoch auch durch Verkettung von Strings erfolgen. Eine Möglichkeit zur Verbesserung der Sicherheit solcher Abfragen besteht darin, eine Tabelle mit zulässigen Werten festzulegen und den Wert, den Sie mit dieser Tabelle verketten möchten, zu vergleichen.

Beachten Sie, dass es wichtig ist, den Verbindungs-Zeichensatz nur über DSN festzulegen. Andernfalls kann Ihre Anwendung zu einer verdeckten Sicherheitsanfälligkeit führen, wenn eine ungerade Codierung verwendet wird. Für PDO-Versionen vor 5.3.6 ist das Festlegen des Zeichensatzes über DSN nicht verfügbar. Daher besteht die einzige Option darin, das PDO::ATTR_EMULATE_PREPARES Attribut für die Verbindung gleich nach der PDO::ATTR_EMULATE_PREPARES auf false zu setzen.

$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Dies führt dazu, dass PDO die systemeigenen vorbereiteten Anweisungen des DBMS verwendet, anstatt sie nur zu emulieren.

Beachten Sie jedoch, dass das PDO im Hintergrund auf die Emulation von Anweisungen zurückgreift, die MySQL nicht nativ vorbereiten kann: Diejenigen, die es kann, sind im Handbuch ( Quelle ) aufgeführt.

PDO: Verbindung zum MySQL / MariaDB-Server

Es gibt zwei Möglichkeiten, eine Verbindung zu einem MySQL / MariaDB-Server herzustellen, abhängig von Ihrer Infrastruktur.

Standardverbindung (TCP / IP)

$dsn = 'mysql:dbname=demo;host=server;port=3306;charset=utf8';
$connection = new \PDO($dsn, $username, $password);

// throw exceptions, when SQL error is caused
$connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
// prevent emulation of prepared statements
$connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

Da PDO mit älteren MySQL-Server-Versionen (die keine vorbereiteten Anweisungen unterstützten) kompatibel war, wurde die Emulation explizit deaktiviert. Andernfalls verlieren Sie die zusätzlichen Vorteile der Injektionsverhütung , die normalerweise durch die Verwendung vorbereiteter Anweisungen gewährt werden.

Ein weiterer Konstruktionskompromiss, den Sie berücksichtigen müssen, ist das Standardverhalten bei der Fehlerbehandlung. Wenn nicht anders konfiguriert, zeigt das PDO keine Hinweise auf SQL-Fehler.

Es wird dringend empfohlen, auf "Ausnahmemodus" zu setzen, da Sie dadurch zusätzliche Funktionalität erhalten, wenn Sie Persistenzabstraktionen schreiben (z. B. eine Ausnahme haben, wenn die UNIQUE Einschränkung verletzt wird).

Socket-Verbindung

$dsn = 'mysql:unix_socket=/tmp/mysql.sock;dbname=demo;charset=utf8';
$connection = new \PDO($dsn, $username, $password);

// throw exceptions, when SQL error is caused
$connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
// prevent emulation of prepared statements
$connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

Wenn auf einem Unix-ähnlichen System der Hostname 'localhost' lautet, wird die Verbindung zum Server über einen Domänensocket hergestellt.

Datenbanktransaktionen mit PDO

Datenbanktransaktionen stellen sicher, dass ein Satz von Datenänderungen nur dann dauerhaft ist, wenn jede Anweisung erfolgreich ist. Jede Abfrage oder ein Codefehler während einer Transaktion kann abgefangen werden, und Sie haben dann die Möglichkeit, die versuchten Änderungen rückgängig zu machen.

PDO bietet einfache Methoden zum Starten, Festschreiben und Zurücksetzen von Transaktionen.

$pdo = new PDO(
    $dsn, 
    $username, 
    $password, 
    array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
);

try {
    $statement = $pdo->prepare("UPDATE user SET name = :name");

    $pdo->beginTransaction();

    $statement->execute(["name"=>'Bob']);
    $statement->execute(["name"=>'Joe']);

    $pdo->commit();
} 
catch (\Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollback();
        // If we got here our two data updates are not in the database
    }
    throw $e;
}

Während einer Transaktion sind alle vorgenommenen Datenänderungen nur für die aktive Verbindung sichtbar. SELECT Anweisungen geben die geänderten Änderungen zurück, auch wenn sie noch nicht für die Datenbank festgeschrieben sind.

Hinweis : Einzelheiten zur Transaktionsunterstützung finden Sie in der Dokumentation des Datenbankanbieters. Einige Systeme unterstützen keine Transaktionen. Einige unterstützen geschachtelte Transaktionen, andere dagegen nicht.

Praktisches Beispiel mit Transaktionen mit PDO

Im folgenden Abschnitt wird ein praktisches Beispiel gezeigt, bei dem die Verwendung von Transaktionen die Konsistenz der Datenbank gewährleistet.

Stellen Sie sich das folgende Szenario vor: Nehmen Sie an, Sie erstellen einen Einkaufswagen für eine E-Commerce-Website und haben sich dafür entschieden, die Bestellungen in zwei Datenbanktabellen zu speichern. Eine benannte orders mit den Feldern order_id , name , address , telephone und created_at . Und eine zweite namens orders_products mit den Feldern order_id , product_id und quantity . Die erste Tabelle enthält die Metadaten der Bestellung, die zweite die tatsächlichen Produkte , die bestellt wurden.

Einen neuen Auftrag in die Datenbank einfügen

Um eine neue Bestellung in die Datenbank einzufügen, müssen Sie zwei Schritte ausführen. Zuerst müssen Sie INSERT einen neuen Datensatz in der orders - Tabelle , die die Metadaten der Reihenfolge enthalten ( name , address , etc.). Und dann müssen Sie INSERT einen Datensatz in die orders_products Tabelle, für jedes der Produkte , die in der Reihenfolge enthalten sind.

Sie können dies tun, indem Sie Folgendes tun:

// Insert the metadata of the order into the database
$preparedStatement = $db->prepare(
    'INSERT INTO `orders` (`name`, `address`, `telephone`, `created_at`)
     VALUES (:name, :address, :telephone, :created_at)'
);

$preparedStatement->execute([
    'name' => $name,
    'address' => $address,
    'telephone' => $telephone,
    'created_at' => time(),
]);

// Get the generated `order_id`
$orderId = $db->lastInsertId();

// Construct the query for inserting the products of the order
$insertProductsQuery = 'INSERT INTO `orders_products` (`order_id`, `product_id`, `quantity`) VALUES';

$count = 0;
foreach ( $products as $productId => $quantity ) {
    $insertProductsQuery .= ' (:order_id' . $count . ', :product_id' . $count . ', :quantity' . $count . ')';
    
    $insertProductsParams['order_id' . $count] = $orderId;
    $insertProductsParams['product_id' . $count] = $productId;
    $insertProductsParams['quantity' . $count] = $quantity;
    
    ++$count;
}

// Insert the products included in the order into the database
$preparedStatement = $db->prepare($insertProductsQuery);
$preparedStatement->execute($insertProductsParams);

Dies funktioniert hervorragend, wenn Sie eine neue Bestellung in die Datenbank einfügen, bis etwas Unerwartetes eintritt und aus irgendeinem Grund die zweite INSERT Abfrage fehlschlägt. In diesem Fall erhalten Sie eine neue Bestellung in der orders , der keine Produkte zugeordnet sind. Glücklicherweise ist das Update sehr einfach. Alles, was Sie tun müssen, ist, die Abfragen in Form einer einzelnen Datenbanktransaktion durchzuführen.

Einfügen einer neuen Bestellung in die Datenbank mit einer Transaktion

Um eine Transaktion mit PDO zu starten, müssen Sie nur die Methode beginTransaction , bevor Sie Abfragen an Ihre Datenbank ausführen. Anschließend nehmen Sie die gewünschten Änderungen an Ihren Daten vor, indem Sie INSERT und / oder UPDATE Abfragen ausführen. Zum Schluss rufen Sie die commit des PDO Objekts auf, um die Änderungen dauerhaft zu machen. Bis Sie die commit aufrufen commit ist jede Änderung, die Sie bis zu diesem Zeitpunkt an Ihren Daten vorgenommen haben, noch nicht dauerhaft und kann einfach durch Aufrufen der rollback Methode des PDO Objekts zurückgesetzt werden.

Im folgenden Beispiel wird die Verwendung von Transaktionen zum Einfügen einer neuen Bestellung in die Datenbank demonstriert, während gleichzeitig die Konsistenz der Daten sichergestellt wird. Wenn eine der beiden Abfragen fehlschlägt, werden alle Änderungen zurückgesetzt.

// In this example we are using MySQL but this applies to any database that has support for transactions
$db = new PDO('mysql:host=' . $host . ';dbname=' . $dbname . ';charset=utf8', $username, $password);    

// Make sure that PDO will throw an exception in case of error to make error handling easier
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    // From this point and until the transaction is being committed every change to the database can be reverted
    $db->beginTransaction();    
    
    // Insert the metadata of the order into the database
    $preparedStatement = $db->prepare(
        'INSERT INTO `orders` (`order_id`, `name`, `address`, `created_at`)
         VALUES (:name, :address, :telephone, :created_at)'
    );
    
    $preparedStatement->execute([
        'name' => $name,
        'address' => $address,
        'telephone' => $telephone,
        'created_at' => time(),
    ]);
    
    // Get the generated `order_id`
    $orderId = $db->lastInsertId();

    // Construct the query for inserting the products of the order
    $insertProductsQuery = 'INSERT INTO `orders_products` (`order_id`, `product_id`, `quantity`) VALUES';
    
    $count = 0;
    foreach ( $products as $productId => $quantity ) {
        $insertProductsQuery .= ' (:order_id' . $count . ', :product_id' . $count . ', :quantity' . $count . ')';
        
        $insertProductsParams['order_id' . $count] = $orderId;
        $insertProductsParams['product_id' . $count] = $productId;
        $insertProductsParams['quantity' . $count] = $quantity;
        
        ++$count;
    }
    
    // Insert the products included in the order into the database
    $preparedStatement = $db->prepare($insertProductsQuery);
    $preparedStatement->execute($insertProductsParams);
    
    // Make the changes to the database permanent
    $db->commit();
}
catch ( PDOException $e ) { 
    // Failed to insert the order into the database so we rollback any changes
    $db->rollback();
    throw $e;
}

PDO: Anzahl der betroffenen Zeilen durch eine Abfrage abrufen

Wir beginnen mit $db , einer Instanz der PDO-Klasse. Nach dem Ausführen einer Abfrage möchten wir häufig die Anzahl der betroffenen Zeilen ermitteln. Die rowCount() -Methode des PDOStatement wird gut funktionieren:

$query = $db->query("DELETE FROM table WHERE name = 'John'");
$count = $query->rowCount();

echo "Deleted $count rows named John";

HINWEIS: Diese Methode sollte nur verwendet werden, um die Anzahl der Zeilen zu bestimmen, die von den Anweisungen INSERT, DELETE und UPDATE betroffen sind. Obwohl diese Methode möglicherweise auch für SELECT-Anweisungen funktioniert, ist sie nicht für alle Datenbanken konsistent.

PDO :: lastInsertId ()

Für eine Zeile, die Sie gerade in Ihre Datenbanktabelle eingefügt haben, müssen Sie möglicherweise den automatisch erhöhten ID-Wert abrufen. Sie können dies mit der lastInsertId () -Methode erreichen.

// 1. Basic connection opening (for MySQL)
$host = 'localhost';
$database = 'foo';
$user = 'root'
$password = '';
$dsn = "mysql:host=$host;dbname=$database;charset=utf8";
$pdo = new PDO($dsn, $user, $password);

// 2. Inserting an entry in the hypothetical table 'foo_user'
$query = "INSERT INTO foo_user(pseudo, email) VALUES ('anonymous', '[email protected]')";
$query_success = $pdo->query($query);

// 3. Retrieving the last inserted id
$id = $pdo->lastInsertId(); // return value is an integer

In postgresql und oracle gibt es das RETURNING-Schlüsselwort, das die angegebenen Spalten der aktuell eingefügten / geänderten Zeilen zurückgibt. Hier ein Beispiel zum Einfügen eines Eintrags:

// 1. Basic connection opening (for PGSQL)
$host = 'localhost';
$database = 'foo';
$user = 'root'
$password = '';
$dsn = "pgsql:host=$host;dbname=$database;charset=utf8";
$pdo = new PDO($dsn, $user, $password);

// 2. Inserting an entry in the hypothetical table 'foo_user'
$query = "INSERT INTO foo_user(pseudo, email) VALUES ('anonymous', '[email protected]') RETURNING id";
$statement = $pdo->query($query);

// 3. Retrieving the last inserted id
$id = $statement->fetchColumn();  // return the value of the id column of the new row in foo_user