PHP
DOP

introduzione

Sintassi

• PDO::LastInsertId()
• PDO::LastInsertId($columnName) // alcuni driver necessitano del nome della colonna

Osservazioni

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

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

Connessione e recupero base PDO

Dal momento che PHP 5.0, PDO è stato disponibile come livello di accesso al database. È indipendente dal database, quindi il seguente codice di esempio di connessione dovrebbe funzionare per qualsiasi dei suoi database supportati semplicemente modificando il 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);
}

La funzione di prepare crea un oggetto PDOStatement dalla stringa di query. L'esecuzione della query e il recupero dei risultati vengono eseguiti su questo oggetto restituito. In caso di errore, la funzione restituisce false o genera exception (a seconda di come è stata configurata la connessione PDO).

Prevenzione dell'iniezione SQL con query parametrizzate

L'iniezione SQL è un tipo di attacco che consente a un utente malintenzionato di modificare la query SQL, aggiungendo comandi indesiderati. Ad esempio, il seguente codice è vulnerabile :

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

Ciò consente a qualsiasi utente di questo script di modificare il nostro database fondamentalmente a proprio piacimento. Ad esempio, considera la seguente stringa di query:

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

Questo rende la nostra query di esempio simile a questa

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

Anche se questo è un esempio estremo (la maggior parte degli attacchi SQL injection non mira a cancellare i dati, né la maggior parte delle funzioni di esecuzione di query PHP supporta multi-query), questo è un esempio di come un attacco di SQL injection può essere reso possibile dall'assenza di la query. Sfortunatamente, attacchi come questo sono molto comuni e sono molto efficaci a causa dei programmatori che non adottano le dovute precauzioni per proteggere i loro dati.

Per evitare che si verifichi l'iniezione SQL, le istruzioni preparate sono la soluzione consigliata. Invece di concatenare i dati utente direttamente alla query, viene utilizzato un segnaposto . I dati vengono quindi inviati separatamente, il che significa che non vi è alcuna possibilità che il motore SQL possa confondere i dati dell'utente per una serie di istruzioni.

Mentre l'argomento qui è PDO, si noti che l'estensione PHP MySQLi supporta anche istruzioni preparate

PDO supporta due tipi di segnaposto (i segnaposti non possono essere utilizzati per nomi di colonne o tabelle, solo valori):

1. Segnaposto nominati. I due punti ( : ), seguito da un nome diverso (ad es. :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. Segnaposto posizionali SQL tradizionali, rappresentati come ? :

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

Se mai hai bisogno di cambiare dinamicamente i nomi di tabelle o colonne, sappi che questo è a rischio di sicurezza personale e cattiva pratica. Tuttavia, può essere fatto tramite concatenazione di stringhe. Un modo per migliorare la sicurezza di tali query è impostare una tabella di valori consentiti e confrontare il valore che si desidera concatenare a questa tabella.

Tieni presente che è importante impostare il set di caratteri della connessione solo tramite DSN, altrimenti l'applicazione potrebbe essere soggetta a una vulnerabilità oscura se viene utilizzata una codifica dispari. Per le versioni PDO precedenti alla 5.3.6 l'impostazione di charset tramite DSN non è disponibile e quindi l'unica opzione è impostare l'attributo PDO::ATTR_EMULATE_PREPARES su false sulla connessione subito dopo la sua creazione.

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

Ciò fa in modo che PDO utilizzi le istruzioni preparate native del DBMS sottostante invece di emularlo.

Tuttavia, si tenga presente che PDO eseguirà silenziosamente il backup delle istruzioni di emulazione che MySQL non è in grado di preparare in modo nativo: quelle che possono essere elencate nel manuale ( fonte ).

PDO: connessione al server MySQL / MariaDB

Esistono due modi per connettersi a un server MySQL / MariaDB, a seconda dell'infrastruttura.

Connessione standard (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);

Dal momento che PDO è stato progettato per essere compatibile con versioni di server MySQL precedenti (che non supportano le istruzioni preparate), è necessario disabilitare esplicitamente l'emulazione. In caso contrario, si perderanno i benefici di prevenzione dell'iniezione aggiunti, che di solito vengono concessi utilizzando dichiarazioni preparate.

Un altro compromesso di progettazione, che è necessario tenere a mente, è il comportamento di gestione degli errori predefinito. Se non diversamente configurato, PDO non mostrerà alcuna indicazione di errori SQL.

Si consiglia vivamente di impostarlo su "modalità eccezione", poiché ciò consente di ottenere funzionalità aggiuntive, quando si scrivono le astrazioni di persistenza (ad esempio: avere un'eccezione, quando si viola il vincolo UNIQUE ).

Connessione socket

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

Sui sistemi unix-like, se il nome host è 'localhost' , la connessione al server avviene tramite un socket di dominio.

Transazioni di database con PDO

Le transazioni di database assicurano che un set di modifiche dei dati sarà reso permanente solo se ogni affermazione ha esito positivo. Qualsiasi query o errore di codice durante una transazione può essere intercettato e quindi hai la possibilità di ripristinare le modifiche tentate.

PDO fornisce metodi semplici per l'inizio, l'impegno e il rollback delle transazioni.

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

Durante una transazione, tutte le modifiche apportate ai dati sono visibili solo alla connessione attiva. SELECT istruzioni SELECT restituiranno le modifiche alterate anche se non sono ancora state commesse nel database.

Nota : consultare la documentazione del fornitore del database per i dettagli sul supporto delle transazioni. Alcuni sistemi non supportano affatto le transazioni. Alcune supportano le transazioni nidificate mentre altre no.

Esempio pratico che utilizza le transazioni con PDO

Nella sezione seguente è illustrato un esempio pratico reale nel mondo in cui l'uso delle transazioni garantisce la coerenza del database.

Immagina il seguente scenario, diciamo che stai costruendo un carrello per un sito di e-commerce e hai deciso di mantenere gli ordini in due tabelle di database. Uno ha nominato orders con i campi order_id , name , address , telephone e created_at . E un secondo denominato orders_products con i campi order_id , product_id e quantity . La prima tabella contiene i metadati dell'ordine mentre la seconda contiene i prodotti effettivi ordinati.

Inserimento di un nuovo ordine nel database

Per inserire un nuovo ordine nel database devi fare due cose. Per prima cosa è necessario INSERT un nuovo record all'interno della tabella degli orders che conterrà i metadati dell'ordine ( name , address , ecc.). E poi devi INSERT un record nella tabella orders_products , per ognuno dei prodotti inclusi nell'ordine.

Puoi farlo facendo qualcosa di simile al seguente:

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

Questo funzionerà perfettamente per l'inserimento di un nuovo ordine nel database, fino a quando non accade qualcosa di inatteso e per qualche motivo la seconda query INSERT fallisce. Se ciò accade, ti ritroverai con un nuovo ordine all'interno della tabella degli orders , a cui non saranno associati prodotti. Fortunatamente, la correzione è molto semplice, tutto ciò che devi fare è fare le query sotto forma di una singola transazione di database.

Inserimento di un nuovo ordine nel database con una transazione

Per avviare una transazione utilizzando PDO tutto ciò che devi fare è chiamare il metodo beginTransaction prima di eseguire qualsiasi query nel tuo database. Quindi apporti le modifiche che desideri ai tuoi dati eseguendo le query INSERT e / o UPDATE . E infine si chiama il metodo di commit dell'oggetto PDO per rendere permanenti le modifiche. Finché non si chiama il metodo di commit , tutte le modifiche apportate ai dati fino a questo momento non sono ancora permanenti e possono essere facilmente ripristinate semplicemente chiamando il metodo di rollback dell'oggetto PDO .

Nell'esempio seguente viene dimostrato l'utilizzo di transazioni per l'inserimento di un nuovo ordine nel database, garantendo allo stesso tempo la coerenza dei dati. Se una delle due query fallisce, tutte le modifiche verranno ripristinate.

// 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: Ottieni il numero di righe interessate da una query

Iniziamo con $db , un'istanza della classe DOP. Dopo aver eseguito una query, spesso vogliamo determinare il numero di righe che ne sono state influenzate. Il metodo rowCount() del PDOStatement funzionerà bene:

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

echo "Deleted $count rows named John";

NOTA: questo metodo deve essere utilizzato solo per determinare il numero di righe interessate dalle istruzioni INSERT, DELETE e UPDATE. Sebbene questo metodo possa funzionare anche per le istruzioni SELECT, non è coerente su tutti i database.

PDO :: lastInsertId ()

Spesso è possibile trovare la necessità di ottenere il valore ID auto incrementato per una riga appena inserita nella tabella del database. È possibile ottenere ciò con il metodo 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 e oracle, c'è la parola chiave RETURNING, che restituisce le colonne specificate delle righe attualmente inserite / modificate. Qui esempio per inserire una voce:

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