Zoeken…
Invoering
Met de extensie PDO (PHP Data Objects) kunnen ontwikkelaars verbinding maken met een groot aantal verschillende soorten databases en er op een uniforme, objectgeoriënteerde manier zoekopdrachten op uitvoeren.
Syntaxis
-
PDO::LastInsertId()
-
PDO::LastInsertId($columnName)
// sommige stuurprogramma's hebben de kolomnaam nodig
Opmerkingen
Waarschuwing Mis het niet om te controleren op uitzonderingen tijdens het gebruik van lastInsertId()
. Dit kan de volgende fout veroorzaken:
SQLSTATE IM001: Driver ondersteunt deze functie niet
Hier is hoe je op deze manier goed moet controleren op uitzonderingen:
// 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):
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();
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