PHP
AOP

Introduction

Syntaxe

• PDO::LastInsertId()
• PDO::LastInsertId($columnName) // certains pilotes ont besoin du nom de la colonne

Remarques

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

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

Connexion et récupération de base du PDO

Depuis PHP 5.0, PDO est disponible en tant que couche d'accès aux bases de données. Il est indépendant de la base de données, et l'exemple de code de connexion suivant devrait donc fonctionner pour n'importe laquelle de ses bases de données prises en charge simplement en modifiant le 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 fonction prepare crée un objet PDOStatement partir de la chaîne de requête. L'exécution de la requête et la récupération des résultats sont effectuées sur cet objet renvoyé. En cas de défaillance, la fonction renvoie false ou génère une exception (en fonction de la configuration de la connexion PDO).

Empêcher l'injection SQL avec des requêtes paramétrées

L'injection SQL est une sorte d'attaque qui permet à un utilisateur malveillant de modifier la requête SQL en y ajoutant des commandes indésirables. Par exemple, le code suivant est vulnérable :

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

Cela permet à tout utilisateur de ce script de modifier fondamentalement notre base de données à volonté. Par exemple, considérez la chaîne de requête suivante:

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

Cela rend notre exemple de requête comme ceci

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

Bien que ce soit un exemple extrême (la plupart des attaques par injection SQL ne visent pas à supprimer des données, la plupart des fonctions d’exécution de requêtes PHP ne prennent pas en charge les requêtes multiples). la requête. Malheureusement, de telles attaques sont très courantes et sont très efficaces grâce aux codeurs qui ne prennent pas les précautions nécessaires pour protéger leurs données.

Pour empêcher l'injection SQL, les instructions préparées sont la solution recommandée. Au lieu de concaténer les données utilisateur directement dans la requête, un espace réservé est utilisé à la place. Les données sont ensuite envoyées séparément, ce qui signifie que le moteur SQL n'a aucune chance de confondre les données utilisateur avec un ensemble d'instructions.

Bien que le sujet ici soit PDO, veuillez noter que l'extension PHP MySQLi prend également en charge les instructions préparées

PDO prend en charge deux types d'espaces réservés (les espaces réservés ne peuvent pas être utilisés pour les noms de colonne ou de table, uniquement les valeurs):

1. Espaces réservés nommés. A deux points ( : ), suivi par un nom distinct (par ex. :user L' :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. Caractères de position SQL traditionnels représentés sous la forme ? :

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

Si vous avez besoin de modifier dynamiquement les noms de tables ou de colonnes, sachez que cela présente des risques pour votre sécurité et une mauvaise pratique. Cependant, cela peut être fait par concaténation de chaînes. Un moyen d'améliorer la sécurité de ces requêtes consiste à définir une table de valeurs autorisées et à comparer la valeur que vous souhaitez concaténer à cette table.

Sachez qu'il est important de définir le charset de connexion uniquement via DSN, sinon votre application pourrait être sujette à une vulnérabilité obscure si un codage impair est utilisé. Pour les versions PDO antérieures à 5.3.6, le paramètre charset via DSN n'est pas disponible et la seule option consiste à définir l'attribut PDO::ATTR_EMULATE_PREPARES sur false à la connexion juste après sa création.

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

Cela amène PDO à utiliser les instructions préparées natives du SGBD sous-jacent au lieu de simplement les émuler.

Cependant, sachez que PDO se rabat silencieusement sur des instructions que MySQL ne peut pas préparer en mode natif: celles qui le sont sont listées dans le manuel ( source ).

PDO: connexion au serveur MySQL / MariaDB

Il existe deux manières de se connecter à un serveur MySQL / MariaDB, selon votre infrastructure.

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

Étant donné que PDO a été conçu pour être compatible avec les anciennes versions de serveur MySQL (qui ne prennent pas en charge les instructions préparées), vous devez désactiver explicitement l'émulation. Sinon, vous perdrez les avantages de prévention d'injection supplémentaires, qui sont généralement accordés à l'aide d'instructions préparées.

Un autre compromis de conception, que vous devez garder à l’esprit, est le comportement de gestion des erreurs par défaut. S'il n'est pas configuré autrement, PDO ne présentera aucune indication d'erreurs SQL.

Il est fortement recommandé de le définir sur "mode exception", car cela vous permet d’obtenir des fonctionnalités supplémentaires lors de l’écriture d’abstractions de persistance (par exemple: avoir une exception en cas de violation de la contrainte UNIQUE ).

Connexion de prise

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

Sur les systèmes de type Unix, si le nom d'hôte est 'localhost' , la connexion au serveur s'effectue via un socket de domaine.

Transactions de base de données avec PDO

Les transactions de base de données garantissent qu'un ensemble de modifications de données ne sera permanent que si chaque instruction aboutit. Toute requête ou tout échec de code au cours d'une transaction peut être intercepté et vous avez alors la possibilité d'annuler les modifications tentées.

PDO fournit des méthodes simples pour commencer, valider et annuler des transactions.

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

Lors d'une transaction, les modifications de données effectuées ne sont visibles que par la connexion active. SELECT renverront les modifications modifiées même si elles ne sont pas encore validées dans la base de données.

Remarque : Consultez la documentation du fournisseur de base de données pour plus d'informations sur le support des transactions. Certains systèmes ne supportent aucune transaction. Certains prennent en charge les transactions imbriquées, d'autres non.

Exemple pratique d'utilisation de transactions avec PDO

Dans la section suivante, nous montrons un exemple concret où l’utilisation de transactions assure la cohérence de la base de données.

Imaginez le scénario suivant, disons que vous créez un panier pour un site de commerce électronique et que vous avez décidé de conserver les commandes dans deux tables de base de données. On nommait les orders avec les champs order_id , name , address , telephone et created_at . Et un second nommé orders_products avec les champs order_id , product_id et quantity . Le premier tableau contient les métadonnées de la commande et le second les produits réels qui ont été commandés.

Insérer une nouvelle commande dans la base de données

Pour insérer une nouvelle commande dans la base de données, vous devez faire deux choses. Vous devez d'abord INSERT un nouvel enregistrement dans la table des orders qui contiendra les métadonnées de la commande ( name , address , etc.). Vous devez ensuite INSERT un enregistrement dans la table orders_products , pour chacun des produits inclus dans la commande.

Vous pouvez le faire en faisant quelque chose de similaire à ce qui suit:

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

Cela fonctionnera parfaitement pour insérer une nouvelle commande dans la base de données, jusqu'à ce que quelque chose d'inattendu se produise et pour une raison quelconque, la deuxième requête INSERT échoue. Si cela se produit, vous obtiendrez une nouvelle commande dans la table des orders , sans aucun produit associé. Heureusement, le correctif est très simple, tout ce que vous avez à faire est de créer les requêtes sous la forme d'une transaction de base de données unique.

Insertion d'une nouvelle commande dans la base de données avec une transaction

Pour démarrer une transaction à l'aide de PDO vous suffit d'appeler la méthode beginTransaction avant d'exécuter des requêtes sur votre base de données. Ensuite, vous apportez les modifications souhaitées à vos données en exécutant des requêtes INSERT et / ou UPDATE . Et enfin, vous appelez la méthode de commit de l'objet PDO pour rendre les modifications permanentes. Tant que vous n’appelez pas la méthode de commit , toutes les modifications que vous avez apportées à vos données jusqu’à ce stade ne sont pas encore permanentes et peuvent être facilement annulées simplement en appelant la méthode de rollback de l’objet PDO .

Dans l'exemple suivant, l'utilisation des transactions pour insérer une nouvelle commande dans la base de données est démontrée, tout en assurant la cohérence des données. Si l'une des deux requêtes échoue, toutes les modifications seront annulées.

// 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: obtenir le nombre de lignes affectées par une requête

Nous commençons avec $db , une instance de la classe PDO. Après avoir exécuté une requête, nous souhaitons souvent déterminer le nombre de lignes affectées. La méthode rowCount() de PDOStatement fonctionnera parfaitement:

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

echo "Deleted $count rows named John";

REMARQUE: Cette méthode ne doit être utilisée que pour déterminer le nombre de lignes affectées par les instructions INSERT, DELETE et UPDATE. Bien que cette méthode puisse également fonctionner pour les instructions SELECT, elle n'est pas cohérente dans toutes les bases de données.

PDO :: lastInsertId ()

Vous trouverez peut-être souvent le besoin d'obtenir la valeur ID incrémentée automatiquement pour une ligne que vous venez d'insérer dans votre table de base de données. Vous pouvez y parvenir avec la méthode 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

Dans postgresql et oracle, il y a le mot-clé RETURNING, qui renvoie les colonnes spécifiées des lignes actuellement insérées / modifiées. Voici un exemple pour insérer une entrée:

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