PHP
DOP

Introducción

Sintaxis

• PDO::LastInsertId()
• PDO::LastInsertId($columnName) // algunos controladores necesitan el nombre de columna

Observaciones

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

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

Conexión y recuperación de PDO básica

Desde PHP 5.0, PDO ha estado disponible como una capa de acceso a la base de datos. Es independiente de la base de datos, por lo que el siguiente código de ejemplo de conexión debería funcionar para cualquiera de sus bases de datos compatibles simplemente cambiando el 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 función de prepare crea un objeto PDOStatement partir de la cadena de consulta. La ejecución de la consulta y la recuperación de los resultados se realizan en este objeto devuelto. En caso de una falla, la función devuelve false o lanza una exception (dependiendo de cómo se configuró la conexión PDO).

Prevención de la inyección SQL con consultas parametrizadas

La inyección SQL es un tipo de ataque que permite a un usuario malintencionado modificar la consulta SQL, agregándole comandos no deseados. Por ejemplo, el siguiente código es vulnerable :

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

Esto permite a cualquier usuario de este script modificar nuestra base de datos básicamente a voluntad. Por ejemplo, considere la siguiente cadena de consulta:

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

Esto hace que nuestra consulta de ejemplo se vea así.

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

Si bien este es un ejemplo extremo (la mayoría de los ataques de inyección de SQL no pretenden eliminar datos, ni la mayoría de las funciones de ejecución de consultas de PHP son compatibles con las consultas múltiples), este es un ejemplo de cómo un ataque de inyección de SQL puede ser posible por el montaje descuidado de la consulta. Desafortunadamente, los ataques de este tipo son muy comunes y son altamente efectivos debido a los codificadores que no toman las precauciones adecuadas para proteger sus datos.

Para evitar que se produzca la inyección de SQL, las declaraciones preparadas son la solución recomendada. En lugar de concatenar datos de usuario directamente a la consulta, se utiliza un marcador de posición en su lugar. Los datos se envían por separado, lo que significa que no hay posibilidad de que el motor de SQL confunda los datos del usuario para un conjunto de instrucciones.

Si bien el tema aquí es PDO, tenga en cuenta que la extensión MySQLi de PHP también admite declaraciones preparadas

PDO admite dos tipos de marcadores de posición (los marcadores de posición no se pueden usar para nombres de columnas o tablas, solo valores):

1. Nombrados marcadores de posición. Un colon ( : ), seguido por un nombre distinto (por ejemplo. :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. Marcadores de posición posicionales de SQL tradicionales, representados como ? :

   // 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 alguna vez necesita cambiar dinámicamente los nombres de tablas o columnas, sepa que esto es responsabilidad de su propio riesgo y una mala práctica. Sin embargo, se puede hacer por concatenación de cuerdas. Una forma de mejorar la seguridad de dichas consultas es establecer una tabla de valores permitidos y comparar el valor que desea concatenar con esta tabla.

Tenga en cuenta que es importante establecer el conjunto de caracteres de la conexión solo a través de DSN, de lo contrario, su aplicación podría estar expuesta a una vulnerabilidad poco clara si se utiliza alguna codificación impar. Para versiones PDO anteriores a 5.3.6, la configuración del conjunto de caracteres a través de DSN no está disponible y, por lo tanto, la única opción es establecer el atributo PDO::ATTR_EMULATE_PREPARES en false en la conexión inmediatamente después de su creación.

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

Esto hace que PDO utilice las declaraciones preparadas nativas del DBMS subyacente en lugar de simplemente emularlo.

Sin embargo, tenga en cuenta que la DOP retrocederá silenciosamente para emular las declaraciones que MySQL no puede preparar de forma nativa: las que pueden aparecer en el manual ( fuente ).

DOP: conexión a servidor MySQL / MariaDB

Hay dos formas de conectarse a un servidor MySQL / MariaDB, dependiendo de su infraestructura.

Conexión estándar (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);

Dado que PDO fue diseñado para ser compatible con versiones anteriores del servidor MySQL (que no tenía soporte para sentencias preparadas), debe deshabilitar explícitamente la emulación. De lo contrario, perderá los beneficios adicionales de prevención de inyecciones , que generalmente se otorgan utilizando declaraciones preparadas.

Otro compromiso de diseño, que debe tener en cuenta, es el comportamiento de manejo de errores predeterminado. Si no se configura de otra manera, PDO no mostrará ninguna indicación de errores de SQL.

Se recomienda encarecidamente configurarlo en "modo de excepción", porque eso le otorga una funcionalidad adicional al escribir abstracciones de persistencia (por ejemplo: tener una excepción, al violar la restricción UNIQUE ).

Conexión de zócalo

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

En sistemas similares a Unix, si el nombre de host es 'localhost' , entonces la conexión al servidor se realiza a través de un socket de dominio.

Transacciones de base de datos con DOP

Las transacciones de la base de datos aseguran que un conjunto de cambios en los datos solo se harán permanentes si cada declaración es exitosa. Se puede detectar cualquier error de consulta o código durante una transacción y, a continuación, tiene la opción de revertir los cambios intentados.

La DOP proporciona métodos simples para iniciar, confirmar y revertir transacciones.

$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 transacción, los cambios de datos realizados solo son visibles para la conexión activa. SELECT instrucciones SELECT devolverán los cambios modificados incluso si aún no están comprometidos con la base de datos.

Nota : consulte la documentación del proveedor de la base de datos para obtener detalles sobre el soporte de transacciones. Algunos sistemas no admiten transacciones en absoluto. Algunos admiten transacciones anidadas, mientras que otros no.

Ejemplo práctico del uso de transacciones con DOP

En la siguiente sección se muestra un ejemplo práctico del mundo real donde el uso de transacciones garantiza la consistencia de la base de datos.

Imagine el siguiente escenario, digamos que está construyendo un carrito de compras para un sitio web de comercio electrónico y decidió mantener los pedidos en dos tablas de base de datos. Una orders nombrada con los campos order_id , name , address , telephone y created_at . Y una segunda llamada orders_products con los campos order_id , product_id y quantity . La primera tabla contiene los metadatos del pedido, mientras que la segunda contiene los productos reales que se han pedido.

Inserción de un nuevo pedido en la base de datos.

Para insertar un nuevo pedido en la base de datos debe hacer dos cosas. Primero necesita INSERT un nuevo registro dentro de la tabla de orders que contendrá los metadatos del pedido ( name , address , etc.). Y luego necesita INSERT un registro en la tabla orders_products , para cada uno de los productos que se incluyen en el pedido.

Puedes hacer esto haciendo algo similar a lo siguiente:

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

Esto funcionará muy bien para insertar un nuevo pedido en la base de datos, hasta que ocurra algo inesperado y, por alguna razón, la segunda consulta INSERT falle. Si eso sucede, terminará con un nuevo pedido dentro de la tabla de orders , que no tendrá productos asociados. Afortunadamente, la solución es muy simple, todo lo que tiene que hacer es hacer las consultas en forma de una sola transacción de base de datos.

Inserción de un nuevo pedido en la base de datos con una transacción

Para iniciar una transacción utilizando PDO todo lo que tiene que hacer es llamar al método beginTransaction antes de ejecutar cualquier consulta en su base de datos. Luego, realice los cambios que desee en sus datos ejecutando las consultas INSERT y / o UPDATE . Y, finalmente, se llama al método commit del objeto PDO para que los cambios sean permanentes. Hasta que llame al método de commit , todos los cambios que haya realizado en sus datos hasta este momento aún no son permanentes, y pueden revertirse fácilmente simplemente llamando al método de rollback del objeto PDO .

En el siguiente ejemplo, se muestra el uso de transacciones para insertar un nuevo pedido en la base de datos, al tiempo que se garantiza la coherencia de los datos. Si una de las dos consultas falla, todos los cambios serán revertidos.

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

DOP: obtener el número de filas afectadas por una consulta

Comenzamos con $db , una instancia de la clase PDO. Después de ejecutar una consulta, a menudo queremos determinar el número de filas que se han visto afectadas por ella. El método rowCount() de PDOStatement funcionará bien:

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

echo "Deleted $count rows named John";

NOTA: este método solo se debe usar para determinar el número de filas afectadas por las instrucciones INSERT, DELETE y UPDATE. Aunque este método también puede funcionar para sentencias SELECT, no es consistente en todas las bases de datos.

DOP :: lastInsertId ()

A menudo puede encontrar la necesidad de obtener el valor de ID incrementado automáticamente para una fila que acaba de insertar en su tabla de base de datos. Puedes lograr esto con el método 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

En postgresql y oracle, está la palabra clave RETURNING, que devuelve las columnas especificadas de las filas actualmente insertadas / modificadas. Aquí el ejemplo para insertar una entrada:

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