PHP
BOB

Invoering

Syntaxis

• PDO::LastInsertId()
• PDO::LastInsertId($columnName) // sommige stuurprogramma's hebben de kolomnaam nodig

Opmerkingen

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

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

Basis PDO-verbinding en ophalen

Sinds PHP 5.0 is PDO beschikbaar als databasetoegangslaag. Het is database-agnostisch, en dus zou de volgende verbindingsvoorbeeldcode moeten werken voor elk van de ondersteunde databases door eenvoudig de DSN te wijzigen.

// 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);
}

De prepare maakt een PDOStatement object op basis van de queryreeks. De uitvoering van de query en het ophalen van de resultaten worden uitgevoerd op dit geretourneerde object. In geval van een fout retourneert de functie false of genereert een exception (afhankelijk van hoe de PDO-verbinding is geconfigureerd).

Voorkomen van SQL-injectie met geparametriseerde zoekopdrachten

SQL-injectie is een soort aanval waarmee een kwaadwillende gebruiker de SQL-query kan wijzigen en er ongewenste opdrachten aan kan toevoegen. De volgende code is bijvoorbeeld kwetsbaar :

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

Hiermee kan elke gebruiker van dit script onze database in principe naar wens aanpassen. Overweeg bijvoorbeeld de volgende queryreeks:

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

Hierdoor ziet onze voorbeeldquery er zo uit

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

Hoewel dit een extreem voorbeeld is (de meeste SQL-injectieaanvallen zijn niet bedoeld om gegevens te verwijderen, noch ondersteunen de meeste PHP-queryuitvoeringsfuncties multi-query), dit is een voorbeeld van hoe een SQL-injectieaanval mogelijk kan worden gemaakt door de onzorgvuldige assemblage van de vraag. Helaas zijn dergelijke aanvallen heel gebruikelijk en zeer effectief vanwege codeerders die niet de juiste voorzorgsmaatregelen nemen om hun gegevens te beschermen.

Om te voorkomen dat SQL-injectie optreedt, zijn voorbereide verklaringen de aanbevolen oplossing. In plaats van gebruikersgegevens rechtstreeks aan de query samen te voegen, wordt in plaats daarvan een tijdelijke aanduiding gebruikt. De gegevens worden vervolgens afzonderlijk verzonden, wat betekent dat er geen kans is dat de SQL-engine gebruikersgegevens verwart voor een reeks instructies.

Hoewel het hier om een BOB gaat, moet u er rekening mee houden dat de PHP MySQLi-extensie ook voorbereide verklaringen ondersteunt

BOB ondersteunt twee soorten tijdelijke aanduidingen (tijdelijke aanduidingen kunnen niet worden gebruikt voor kolom- of tabelnamen, alleen waarden):

1. Genoemde tijdelijke aanduidingen. Een dubbele punt ( : ), gevolgd door een afzonderlijke naam (bijv. :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. Traditionele plaatsaanduidingen voor SQL, weergegeven 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();
   

Als u ooit tabel- of kolomnamen dynamisch moet wijzigen, weet dan dat dit uw eigen beveiligingsrisico's en een slechte gewoonte is. Het kan echter worden gedaan door aaneenschakeling van tekenreeksen. Een manier om de beveiliging van dergelijke query's te verbeteren, is door een tabel met toegestane waarden in te stellen en de waarde die u wilt samenvoegen te vergelijken met deze tabel.

Houd er rekening mee dat het belangrijk is om de verbindingsset alleen via DSN in te stellen, anders kan uw toepassing vatbaar zijn voor een obscure kwetsbaarheid als een vreemde codering wordt gebruikt. Voor PDO-versies ouder dan 5.3.6 is het instellen van een tekenset via DSN niet beschikbaar en daarom is de enige optie om het kenmerk PDO::ATTR_EMULATE_PREPARES te stellen op false op de verbinding direct nadat deze is gemaakt.

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

Hierdoor gebruikt PDO de native voorbereide instructies van het onderliggende DBMS in plaats van deze alleen te emuleren.

Houd er echter rekening mee dat PDO stilzwijgend zal terugvallen op het emuleren van uitspraken die MySQL niet native kan voorbereiden: die welke mogelijk zijn, worden vermeld in de handleiding ( bron ).

BOB: verbinding maken met MySQL / MariaDB-server

Er zijn twee manieren om verbinding te maken met een MySQL / MariaDB-server, afhankelijk van uw infrastructuur.

Standaard (TCP / IP) -verbinding

$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);

Aangezien PDO is ontworpen om compatibel te zijn met oudere MySQL-serverversies (die geen ondersteuning bieden voor voorbereide instructies), moet u de emulatie expliciet uitschakelen. Anders verliest u de extra voordelen voor injectiepreventie , die meestal worden verleend door gebruik te maken van voorbereide verklaringen.

Een ander compromis met het ontwerp, dat u in gedachten moet houden, is het standaard foutafhandelingsgedrag. Indien niet anders geconfigureerd, toont PDO geen aanwijzingen voor SQL-fouten.

Het wordt sterk aanbevolen om het op "uitzonderingsmodus" in te stellen, omdat dat u extra functionaliteit oplevert bij het schrijven van persistentie-abstracties (bijvoorbeeld: een uitzondering hebben, wanneer u de UNIQUE beperking schendt).

Socket verbinding

$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);

Op unix-achtige systemen, als de hostnaam 'localhost' , wordt de verbinding met de server gemaakt via een domeinsocket.

Databasetransacties met BOB

Databasetransacties zorgen ervoor dat een reeks gegevenswijzigingen alleen permanent wordt gemaakt als elke verklaring succesvol is. Elke vraag of codefout tijdens een transactie kan worden opgevangen en u hebt dan de optie om de gepoogde wijzigingen terug te draaien.

BOB biedt eenvoudige methoden voor het starten, vastleggen en terugdraaien van transacties.

$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;
}

Tijdens een transactie zijn gegevenswijzigingen alleen zichtbaar voor de actieve verbinding. SELECT instructies retourneren de gewijzigde wijzigingen, zelfs als ze nog niet zijn vastgelegd in de database.

Opmerking : zie documentatie van databaseverkoper voor details over transactieondersteuning. Sommige systemen ondersteunen helemaal geen transacties. Sommige ondersteunen geneste transacties, andere niet.

Praktijkvoorbeeld met transacties met BOB

In het volgende gedeelte wordt een praktisch voorbeeld uit de praktijk getoond waarbij het gebruik van transacties de consistentie van de database waarborgt.

Stel je het volgende scenario voor, laten we zeggen dat je een winkelwagentje aan het bouwen bent voor een e-commerce website en je hebt besloten de bestellingen in twee databasetabellen te bewaren. Een genoemde orders met de velden order_id , name , address , telephone en created_at . En een tweede met de naam orders_products met de velden order_id , product_id en quantity . De eerste tabel bevat de metagegevens van de bestelling en de tweede de werkelijke producten die zijn besteld.

Een nieuwe bestelling in de database invoegen

Om een nieuwe bestelling in de database in te voegen, moet u twee dingen doen. Eerst moet je INSERT een nieuw record in de orders tabel die de metadata van de bestelling (zal bevatten name , address , etc). En dan moet u één record INSERT in de tabel orders_products , voor elk van de producten die in de order zijn opgenomen.

Je kunt dit doen door iets te doen dat lijkt op het volgende:

// 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);

Dit werkt geweldig voor het invoegen van een nieuwe bestelling in de database, totdat er iets onverwachts gebeurt en om een of andere reden de tweede INSERT zoekopdracht mislukt. Als dat gebeurt, krijgt u een nieuwe bestelling in de orders , waaraan geen producten zijn gekoppeld. Gelukkig is de oplossing heel eenvoudig, het enige wat u hoeft te doen is om de vragen in de vorm van een enkele database-transactie te doen.

Een nieuwe bestelling invoegen in de database met een transactie

Om een transactie met PDO te starten, hoeft u alleen de methode beginTransaction aan te roepen voordat u vragen aan uw database uitvoert. Vervolgens brengt u de gewenste wijzigingen aan in uw gegevens door INSERT en / of UPDATE zoekopdrachten uit te voeren. En tot slot roept u de commit methode van het PDO object aan om de wijzigingen permanent te maken. Totdat u de commit methode aanroept, is elke wijziging die u tot nu toe in uw gegevens hebt aangebracht nog niet permanent en kunt u deze eenvoudig rollback door eenvoudigweg de rollback methode van het PDO object aan te roepen.

In het volgende voorbeeld wordt het gebruik van transacties getoond voor het invoegen van een nieuwe bestelling in de database, terwijl tegelijkertijd de consistentie van de gegevens wordt gewaarborgd. Als een van de twee vragen mislukt, worden alle wijzigingen teruggezet.

// 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;
}

BOB: krijg het aantal getroffen rijen door een zoekopdracht

We beginnen met $db , een exemplaar van de PDO-klasse. Na het uitvoeren van een query willen we vaak het aantal rijen bepalen waarop de query betrekking heeft. De methode rowCount() van de PDOStatement werkt goed:

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

echo "Deleted $count rows named John";

OPMERKING: deze methode moet alleen worden gebruikt om het aantal rijen te bepalen waarop INSERT-, DELETE- en UPDATE-instructies van invloed zijn. Hoewel deze methode mogelijk ook werkt voor SELECT-instructies, is deze niet consistent in alle databases.

BOB :: lastInsertId ()

Het kan vaak nodig zijn om de automatisch verhoogde ID-waarde te krijgen voor een rij die u zojuist in uw databasetabel hebt ingevoegd. U kunt dit bereiken met de methode lastInsertId ().

// 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 en orakel is er het RETURNING-sleutelwoord, dat de opgegeven kolommen van de momenteel ingevoegde / gewijzigde rijen retourneert. Hier een voorbeeld voor het invoegen van één invoer:

// 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