Las consultas preparadas

La base de datos MySQL soporta las consultas preparadas. Una consulta preparada o consulta parametrizable es utilizada para ejecutar la misma consulta varias veces, con gran eficiencia y protege contra las inyecciones SQL.

Flujo de trabajo básico

La ejecución de una consulta preparada se realiza en dos pasos : la preparación y la ejecución. Durante la preparación, una plantilla de consulta es enviada al servidor de base de datos. El servidor realiza una verificación de la sintaxis, y inicializa los recursos internos del servidor para un uso posterior.

El servidor MySQL soporta el modo anónimo, con marcadores de posición utilizando el carácter ?.

La preparación es seguida por la ejecución. Durante la ejecución, el cliente enlaza los valores de los parámetros y los envía al servidor. El servidor ejecuta la instrucción con los valores enlazados utilizando los recursos internos previamente creados.

Ejemplo #1 Primer paso: la preparación

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Consulta no preparada
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

// Consulta preparada, paso 1: preparación
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");

// Consulta preparada, paso 2: enlaza los valores y ejecuta la consulta
$id = 1;
$label = 'PHP';
$stmt->bind_param("is", $id, $label); // "is" significa que $id está enlazado como un integer y $label como un string

$stmt->execute();

Ejecución repetida

Una consulta preparada puede ser ejecutada varias veces. En cada ejecución, el valor actual de la variable enlazada es evaluado, y enviado al servidor. La consulta no es analizada de nuevo. La plantilla de consulta no es enviada nuevamente al servidor.

Ejemplo #2 Consulta de tipo INSERT preparada una sola vez, y ejecutada varias veces

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Consulta no preparada
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

// Consulta preparada, paso 1: la preparación
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
     echo "Fallo durante la preparación: (" . $mysqli->errno . ") " . $mysqli->error;
}

// Consulta preparada, paso 2: enlaza los valores y ejecuta la consulta
$id = 1;
$stmt->bind_param("is", $id, $label); // "is" significa que $id está enlazado como un integer y $label como un string

$data = [
    1 => 'PHP',
    2 => 'Java',
    3 => 'C++'
];

foreach ($data as $id => $label) {
    $stmt->execute();
}

$result = $mysqli->query('SELECT id, label FROM test');
var_dump($result->fetch_all(MYSQLI_ASSOC));

El ejemplo anterior mostrará:

array(3) {
  array(2) {
    ["id"]=>
    string(1) "1"
    ["label"]=>
    string(3) "PHP"
  }
  [1]=>
  array(2) {
    ["id"]=>
    string(1) "2"
    ["label"]=>
    string(4) "Java"
  }
  [2]=>
  array(2) {
    ["id"]=>
    string(1) "3"
    ["label"]=>
    string(3) "C++"
  }
}

Cada consulta preparada ocupa recursos en el servidor. Deben ser cerradas explícitamente inmediatamente después de su uso. Si no lo hace, la consulta será cerrada cuando el manejador de consulta sea liberado por PHP.

El uso de consultas preparadas no siempre es la forma más eficiente de ejecutar una consulta. Una consulta preparada ejecutada una sola vez provoca más idas y vueltas cliente-servidor que una consulta no preparada. Es por eso que la consulta de tipo SELECT no es ejecutada como consulta preparada en el ejemplo anterior.

Además, debe tener en cuenta el uso de las sintaxis multi-INSERT MySQL para los INSERTs. Por ejemplo, los multi-INSERTs requieren menos idas y vueltas cliente-servidor que la consulta preparada vista en el ejemplo anterior.

Ejemplo #3 Menos idas y vueltas utilizando los multi-INSERTs SQL

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");

$values = [1, 2, 3, 4];

$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?), (?), (?), (?)");
$stmt->bind_param('iiii', ...$values);
$stmt->execute();

Tipos de datos de los valores del juego de resultados

El protocolo servidor cliente MySQL define un protocolo de transferencia de datos diferente para las consultas preparadas y para las consultas no preparadas. Las consultas preparadas utilizan un protocolo llamado binario. El servidor MySQL envía los datos del juego de resultados "tal cual", en formato binario. Los resultados no son serializados en cadenas de caracteres antes de ser enviados. La biblioteca cliente recibe datos binarios y intenta convertir los valores en un tipo de datos PHP apropiado. Por ejemplo, los resultados desde una columna INT SQL serán proporcionados como variables de tipo entero PHP.

Ejemplo #4 Tipos de datos nativos

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Consulta no preparada
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));

El ejemplo anterior mostrará:

id = 1 (integer)
label = PHP (string)

Este comportamiento difiere para las consultas no preparadas. Por omisión, las consultas no preparadas devuelven todos los resultados en forma de cadenas de caracteres. Este comportamiento por omisión puede ser modificado utilizando una opción durante la conexión. Si esta opción es utilizada, entonces no habrá diferencia entre una consulta preparada y una consulta no preparada.

Recuperación de los resultados utilizando variables enlazadas

Los resultados desde las consultas preparadas pueden ser recuperados enlazando las variables de salida, o interrogando el objeto mysqli_result.

Las variables de salida deben ser enlazadas después de la ejecución de la consulta. Una variable debe ser enlazada para cada columna del juego de resultados de la consulta.

Ejemplo #5 Enlace de las variables de salida

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Consulta no preparada
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();

$stmt->bind_result($out_id, $out_label);

$out_id    = NULL;
$out_label = NULL;
if (!$stmt->bind_result($out_id, $out_label)) {
    echo "Fallo durante el enlace de los parámetros de salida: (" . $stmt->errno . ") " . $stmt->error;
}

while ($stmt->fetch()) {
    printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
}

El ejemplo anterior mostrará:

id = 1 (integer), label = a (string)

Las consultas preparadas devuelven juegos de resultados no en memoria tamponada por omisión. Los resultados de la consulta no son recuperados implícitamente y transferidos desde el servidor hacia el cliente para una memoria tamponada del lado-cliente. El juego de resultados ocupa recursos del servidor hasta que todos los resultados no sean recuperados por el cliente. Por lo tanto, es recomendable recuperarlos rápidamente. Si un cliente falla en la recuperación de todos los resultados, o si el cliente cierra la consulta antes de haber recuperado todos los datos, los datos deben ser recuperados implícitamente por mysqli.

También es posible poner en memoria tamponada los resultados de una consulta preparada utilizando la función mysqli_stmt::store_result().

Recuperación de los resultados utilizando la interfaz mysqli_result

En lugar de utilizar resultados enlazados, los resultados pueden también ser recuperados a través de la interfaz mysqli_result. La función mysqli_stmt::get_result() devuelve un juego de resultados en memoria tamponada.

Ejemplo #6 Uso de mysqli_result para recuperar los resultados

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Consulta no preparada
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();

$result = $stmt->get_result();

var_dump($result->fetch_all(MYSQLI_ASSOC));

El ejemplo anterior mostrará:

array(1) {
  [0]=>
  array(2) {
    ["id"]=>
    int(1)
    ["label"]=>
    string(3) "PHP"
  }
}

El uso de la interfaz mysqli_result ofrece otras ventajas en términos de flexibilidad en la navegación en el juego de resultados del lado-cliente.

Ejemplo #7 Juego de resultados en memoria tamponada para más flexibilidad en la lectura

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Consulta no preparada
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP'), (2, 'Java'), (3, 'C++')");

$stmt = $mysqli->prepare("SELECT id, label FROM test");
$stmt->execute();

$result = $stmt->get_result();

for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
    $result->data_seek($row_no);
    var_dump($result->fetch_assoc());
}

El ejemplo anterior mostrará:

array(2) {
  ["id"]=>
  int(3)
  ["label"]=>
  string(1) "C++"
}
array(2) {
  ["id"]=>
  int(2)
  ["label"]=>
  string(1) "Java"
}
array(2) {
  ["id"]=>
  int(1)
  ["label"]=>
  string(1) "PHP"
}

Escape y inyección SQL

Las variables enlazadas son enviadas al servidor separadamente de la consulta, no pudiendo así interferir con esta. El servidor utiliza estos valores directamente al momento de la ejecución, después de que la plantilla sea analizada. Los parámetros enlazados no necesitan ser escapados ya que nunca son colocados en la cadena de consulta directamente. Una pista debe ser proporcionada al servidor para especificar el tipo de variable enlazada, para realizar la conversión apropiada. Ver la función mysqli_stmt::bind_param() para más información.

Tal separación es a menudo considerada como la única funcionalidad para protegerse contra las inyecciones SQL, pero el mismo grado de seguridad puede ser alcanzado con las consultas no preparadas, si todas las valores son correctamente formateadas. Tenga en cuenta que un formateo correcto no es lo mismo que un escape y requiere más lógica que un simple escape. Por lo tanto, las consultas preparadas son simplemente un método más sencillo y menos propenso a errores en cuanto a este enfoque seguro.

Emulación del lado-cliente de la preparación de una consulta

La API no incluye una emulación del lado-cliente de la preparación de una consulta.

Ver también