PHP
PDO

Introduktion

Syntax

• PDO::LastInsertId()
• PDO::LastInsertId($columnName) // vissa drivrutiner behöver kolumnnamnet

Anmärkningar

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

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

Grundläggande PDO-anslutning och återhämtning

Sedan PHP 5.0 har PDO varit tillgängligt som ett databasåtkomstlager. Det är databasagnostiskt, och följande anslutningsexempelkod bör fungera för någon av dess databaser som stöds helt enkelt genom att ändra DSN.

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

Funktionen prepare skapar ett PDOStatement objekt från frågesträngen. Exekveringen av frågan och hämtningen av resultaten utförs på detta returnerade objekt. I händelse av ett fel returnerar funktionen antingen false eller kastar ett exception (beroende på hur PDO-anslutningen var konfigurerad).

Förhindrar SQL-injektion med parametriserade frågor

SQL-injektion är en typ av attack som gör det möjligt för en skadlig användare att ändra SQL-frågan och lägga till oönskade kommandon till den. Till exempel är följande kod sårbar :

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

Detta tillåter alla användare av detta skript att ändra vår databas i grund och botten. Tänk till exempel på följande frågesträng:

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

Detta får vårt exempelfråga att se ut så här

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

Även om detta är ett extremt exempel (de flesta SQL-injektionsattacker syftar inte till att ta bort data, och inte heller de flesta PHP-frågeställningsfunktioner stöder flera frågor), är detta ett exempel på hur en SQL-injektionsattack kan möjliggöras av den slarviga montering av frågan. Tyvärr är attacker som detta mycket vanliga och är mycket effektiva på grund av kodare som inte vidtar lämpliga försiktighetsåtgärder för att skydda deras data.

För att förhindra att SQL-injektion inträffar är förberedda uttalanden den rekommenderade lösningen. I stället för att sammanfoga användardata direkt till frågan används en platshållare istället. Uppgifterna skickas sedan separat, vilket innebär att det inte finns någon chans att SQL-motorn förvirrar användardata för en uppsättning instruktioner.

Även om ämnet här är BOB, observera att PHP MySQLi-förlängningen också stöder förberedda uttalanden

PDO stöder två typer av platshållare (platshållare kan inte användas för kolumn- eller tabellnamn, endast värden):

1. Namngivna platshållare. Ett kolon ( : ), följt av en distinkt namn (t.ex.. :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. Traditionella SQL-positioner, representerade som ? :

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

Om du någonsin behöver ändra tabell- eller kolumnnamn, vet att det är på din egen säkerhetsrisk och dålig praxis. Men det kan göras genom strängen sammanlänkning. Ett sätt att förbättra säkerheten för sådana frågor är att ställa in en tabell med tillåtna värden och jämföra värdet du vill sammanfoga med den här tabellen.

Var medveten om att det bara är viktigt att ställa in anslutningsuppsättningen via DSN, annars kan din applikation vara benägna att en otydlig sårbarhet om någon udda kodning används. För PDO-versioner före 5.3.6 är inte inställning av charset via DSN tillgängligt och det enda alternativet är att ställa in PDO::ATTR_EMULATE_PREPARES attribut till false på anslutningen direkt efter att den har skapats.

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

Detta gör att PDO använder det underliggande DBMS: s ursprungliga beredda uttalanden istället för att bara emulera det.

Var dock medveten om att PDO tyst kommer att återfalla till emulering av uttalanden som MySQL inte kan förbereda nativt: de som den kan listas i manualen ( källa ).

PDO: ansluter till MySQL / MariaDB-server

Det finns två sätt att ansluta till en MySQL / MariaDB-server, beroende på din infrastruktur.

Standardanslutning (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);

Eftersom PDO var utformad för att vara kompatibel med äldre MySQL-serverversioner (som inte hade stöd för förberedda uttalanden) måste du uttryckligen inaktivera emuleringen. Annars förlorar du de extra fördelarna med injektionsförebyggande , som vanligtvis beviljas genom att använda förberedda uttalanden.

En annan designkompromiss, som du måste komma ihåg, är standardfelhanteringsbeteendet. Om inget annat är konfigurerat kommer PDO inte att visa några indikationer på SQL-fel.

Det rekommenderas starkt att ställa det till "undantagsläge", eftersom det ger dig ytterligare funktionalitet när du skriver uthållighetsabstraktioner (till exempel: ha ett undantag, när du bryter mot UNIQUE begränsning).

Socketanslutning

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

Om unix-liknande system, om värdnamnet är 'localhost' , görs anslutningen till servern via ett domänuttag.

Databastransaktioner med PDO

Databastransaktioner säkerställer att en uppsättning dataändringar endast görs permanent om varje uttalande är framgångsrikt. Varje fråga eller kodfel under en transaktion kan fångas och du har då möjlighet att rulla tillbaka de försökte ändringarna.

PDO tillhandahåller enkla metoder för att börja, begå och återuppta transaktioner.

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

Under en transaktion är alla dataändringar endast synliga för den aktiva anslutningen. SELECT uttalanden kommer att returnera de ändrade ändringarna även om de ännu inte har åtagit sig till databasen.

Obs : Se dokumentationsleverantörens dokumentation för information om transaktionsstöd. Vissa system stöder inte transaktioner alls. Vissa stöder kapslade transaktioner medan andra inte gör det.

Praktiskt exempel med transaktioner med PDO

I följande avsnitt visas ett praktiskt exempel i verklig värld där användningen av transaktioner säkerställer konsistensen i databasen.

Föreställ dig följande scenario, låt oss säga att du bygger en kundvagn för en e-handelswebbplats och du bestämde dig för att behålla ordern i två databastabeller. En namngivna orders med fälten order_id , name , address , telephone och created_at . Och en andra som heter orders_products med fälten order_id , product_id och quantity . Den första tabellen innehåller metadata för beställningen medan den andra de faktiska produkterna som har beställts.

Infoga en ny order i databasen

För att infoga en ny ordning i databasen måste du göra två saker. Först måste du INSERT en ny post i orders som kommer att innehålla metadata för beställningen ( name , address osv.). Och sedan måste du INSERT en post i tabellen orders_products , för var och en av de produkter som ingår i beställningen.

Du kan göra detta genom att göra något liknande följande:

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

Detta fungerar bra för att infoga en ny ordning i databasen, tills något oväntat händer och av någon anledning misslyckas den andra INSERT frågan. Om det händer kommer du att sluta med en ny beställning i orders , som inte har några produkter kopplade till den. Lyckligtvis är fixen väldigt enkel, allt du behöver göra är att göra frågorna i form av en enda databastransaktion.

Infoga en ny order i databasen med en transaktion

För att starta en transaktion med PDO behöver du bara ringa metoden beginTransaction innan du utför några frågor till din databas. Sedan gör du alla ändringar du vill ha i dina data genom att utföra INSERT och / eller UPDATE frågor. Och slutligen kallar du commit för PDO objektet för att göra ändringarna permanenta. Tills du anropar commit alla ändringar du har gjort i dina data fram till denna punkt ännu inte permanenta och kan enkelt återställas genom att helt enkelt ringa rollback metoden för PDO objektet.

På följande exempel visas användningen av transaktioner för att infoga en ny order i databasen, samtidigt som datornas konsistens garanteras samtidigt. Om en av de två frågorna misslyckas kommer alla ändringar att återställas.

// 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: Få antalet påverkade rader med en fråga

Vi börjar med $db , en instans av PDO-klassen. Efter att en fråga har utförts vill vi ofta bestämma antalet rader som har påverkats av den. rowCount() för PDOStatement fungerar fint:

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

echo "Deleted $count rows named John";

OBS: Den här metoden bör endast användas för att bestämma antalet rader som påverkas av INSERT, DELETE och UPDATE. Även om denna metod också fungerar för SELECT-uttalanden, är den inte konsekvent i alla databaser.

SUB :: lastInsertId ()

Du kan ofta hitta behovet av att få det automatiskt ökade ID-värdet för en rad som du just har lagt in i din databastabell. Du kan uppnå detta med metoden 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

I postgresql och oracle finns det RETURNING Key som returnerar de angivna kolumnerna för de för närvarande infogade / modifierade raderna. Här exempel för att infoga en post:

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