Las bases de datos MySQL soportan sentencias preparadas. Una sentencia preparada o una sentencia parametrizada se usa para ejecutar la misma sentencia repetidamente con gran eficiencia.
Flujo de trabajo básico
La ejecución de sentencias preparadas consiste en dor etapas: la preparación y la ejecución. En la etapa de preparación se envía una plantilla de sentencia al servidor de bases de datos. El servidor realiza una comprobación de sintaxis e inicializa los recursos internos del servidor para su uso posterior.
El servidor de MySQL soporta el uso de parámetros de sustitución posicionales anónimos con ?.
Ejemplo #1 Primera etapa: prepación
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
/* Sentencia no preparada */
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
echo "Falló la creación de la tabla: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Sentencia preparada, etapa 1: preparación */
if (!($sentencia = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
echo "Falló la preparación: (" . $mysqli->errno . ") " . $mysqli->error;
}
?>
La preparación es seguida de la ejecución. Durante la ejecución el cliente vincula los valores de los parámetros y los envía al servidor. El servidor crea una sentencia desde la plantilla de la sentencia y los valores vinculados para ejecutarla usando los recursos internos previamente creados.
Ejemplo #2 Segunda etapa: vincular y ejecutar
<?php
/* Sentencia preparada, etapa 2: vincular y ejecutar */
$id = 1;
if (!$sentencia->bind_param("i", $id)) {
echo "Falló la vinculación de parámetros: (" . $sentencia->errno . ") " . $sentencia->error;
}
if (!$sentencia->execute()) {
echo "Falló la ejecución: (" . $sentencia->errno . ") " . $sentencia->error;
}
?>
Ejecución repetida
Una sentencia preparada se puede ejecutar repetidamente. En cada ejecución el valor actual de la variable vinculada se evalúa y se envía al servidor. La sentencia no se analiza de nuevo. La plantilla de la sentencia no es transferida otra vez al servidor.
Ejemplo #3 INSERT preparada una vez, ejecutada múltiples veces
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
/* Sentencia no preparada */
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
echo "Falló la creación de la tabla: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Sentencia preparada, etapa 1: preparación */
if (!($sentencia = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
echo "Falló la preparación: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Sentencia preparada, etapa 2: vinculación y ejecución */
$id = 1;
if (!$sentencia->bind_param("i", $id)) {
echo "Falló la vinculación de parámetros: (" . $sentencia->errno . ") " . $sentencia->error;
}
if (!$sentencia->execute()) {
echo "Falló la ejecución: (" . $sentencia->errno . ") " . $sentencia->error;
}
/* Sentencia preparada: ejecución repetida, sólo datos transferidos desde el cliente al servidor */
for ($id = 2; $id < 5; $id++) {
if (!$sentencia->execute()) {
echo "Falló la ejecución: (" . $sentencia->errno . ") " . $sentencia->error;
}
}
/* se recomienda el cierre explícito */
$sentencia->close();
/* Sentencia no preparada */
$resultado = $mysqli->query("SELECT id FROM test");
var_dump($resultado->fetch_all());
?>
El resultado del ejemplo sería:
array(4) {
[0]=>
array(1) {
[0]=>
string(1) "1"
}
[1]=>
array(1) {
[0]=>
string(1) "2"
}
[2]=>
array(1) {
[0]=>
string(1) "3"
}
[3]=>
array(1) {
[0]=>
string(1) "4"
}
}
Cada sentencia preparada ocupa recursos del servidor. Las sentencias deberían cerrarse explícitamente inmediatamente después de su uso. Si no se realiza explícitamente, la sentencia será cerrada cuando el gestor de la sentencia sea liberado por PHP.
Usar una sentencia preparada no es siempre la manera más eficiente de ejecutar una sentencia. Una sentencia preparada ejecutada una sola vez causa más viajes de ida y vuelta desde el cliente al servidor que una sentencia no preparada. Es por esta razón por lo que SELECT no se ejecuta arriba como una sentencia preparada.
También se ha de considerar el uso de la sintaxis SQL multi-INSERT de MySQL para sentencias INSERT. Por ejemplo, multi-INSERT requiere menos viajes de ida y vuelta entre el servidor y el cliente que la sentencia preparada mostrada arriba.
Ejemplo #4 Menos viajes de ida y vuelta usando SQL multi-INSERT
<?php
if (!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)")) {
echo "Falló multi-INSERT: (" . $mysqli->errno . ") " . $mysqli->error;
}
?>
Tipos de datos de los valores del conjunto de resultados
El Protocolo Cliente Servidor de MySQL define un protocolo de transporte de datos diferente para sentencias preparadas y no preparadas. Las sentencias preparadas usan el llamado protocolo binario. El servidor de MySQL envía los datos del conjunto de resultados "tal cual" en formato binario. Los resultados no son serializados en cadenas antes del envío. Las bibliotecas cliente no reciben cadenas solamente. En su lugar, recibirán datos binarios e intentarán convertir los valores a los tipos de datos de PHP apropiados. Por ejemplo, los resultados de una columna de SQL INT serán proporcionados como variables de tipo integer de PHP.
Ejemplo #5 Tipos de datos nativos
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, etiqueta CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, etiqueta) VALUES (1, 'a')")) {
echo "Falló la creación de la tabla: (" . $mysqli->errno . ") " . $mysqli->error;
}
$sentencia = $mysqli->prepare("SELECT id, etiqueta FROM test WHERE id = 1");
$sentencia->execute();
$resultado = $sentencia->get_result();
$fila = $resultado->fetch_assoc();
printf("id = %s (%s)\n", $fila['id'], gettype($fila['id']));
printf("etiqueta = %s (%s)\n", $fila['etiqueta'], gettype($fila['etiqueta']));
?>
El resultado del ejemplo sería:
id = 1 (integer) etiqueta = a (string)
Este comportamiento difiere de las sentencias no preparadas. Por defecto, las sentencias no preparadas devolverán todos los resultados como cadenas. Este comportamiento predeterminado se puede cambiar usand una opción de conexión. Si se utiliza la opción de conexión no existirán diferencias.
Obtener resultados usando variables vinculadas
Los resultados de las sentencias preparadas pueden ser recuperados mediante varibles de salida vinculadas, o por la petición de un objeto mysqli_result.
Las variables de salida deben ser vinculadas después de la ejecución de la sentencia. Una variable debe estar vinculada para cada columna del conjunto de resultados de las sentencias.
Ejemplo #6 Vinculación de variables de salida
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, etiqueta CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, etiqueta) VALUES (1, 'a')")) {
echo "Falló la creación de la tabla: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($sentencia = $mysqli->prepare("SELECT id, etiqueta FROM test"))) {
echo "Falló la preparación: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$sentencia->execute()) {
echo "Falló la ejecución: (" . $mysqli->errno . ") " . $mysqli->error;
}
$id_salida = NULL;
$etiqueta_salida = NULL;
if (!$sentencia->bind_result($id_salida, $etiqueta_salida)) {
echo "Falló la vinculación de los parámetros de salida: (" . $sentencia->errno . ") " . $sentencia->error;
}
while ($sentencia->fetch()) {
printf("id = %s (%s), etiqueta = %s (%s)\n", $id_salida, gettype($id_salida), $etiqueta_salida, gettype($etiqueta_salida));
}
?>
El resultado del ejemplo sería:
id = 1 (integer), etiqueta = a (string)
Las sentencias preparadas devuelven de manera predeterminada conjuntos de resultados no almacenados en buffer. Los resultados de la sentencia no son obtenidas y transferidas implícitamente desde el servidor al cliente para el almacenamiento en buffer de lado del cliente. El conjunto de resultados toma recursos del servidor hasta que todos los resultados hayan sido obtenidos por el cliente. Por lo que se recomienda consumir resultados cuando sea oportuno. Si un cliente falla al obtener todos los resultados o el cliente cierra la consulta antes de haber obtenido todos los datos, los datos han de ser obtenidos implícitamente por mysqli.
También es posible almacenar en buffer los resutados de una sentencia preparada usando mysqli_stmt_store_result().
Obtener los resultados usando la interfaz mysqli_result
En lugar de usar resultados vinculados, los resultados también se pueden recuperar a través de la interfaz mysqli_result. mysqli_stmt_get_result() devuelve un conjunto de resultados almacenado en buffer.
Ejemplo #7 Usar mysqli_result para obtener los resultados
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, etiqueta CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, etiqueta) VALUES (1, 'a')")) {
echo "Falló la creación de la tabla: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($sentencia = $mysqli->prepare("SELECT id, etiqueta FROM test ORDER BY id ASC"))) {
echo "Falló la preparación: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$sentencia->execute()) {
echo "Falló la ejecución: (" . $sentencia->errno . ") " . $sentencia->error;
}
if (!($resultado = $sentencia->get_result())) {
echo "Falló la obtención del conjunto de resultados: (" . $sentencia->errno . ") " . $sentencia->error;
}
var_dump($resultado->fetch_all());
?>
El resultado del ejemplo sería:
array(1) {
[0]=>
array(2) {
[0]=>
int(1)
[1]=>
string(1) "a"
}
}
Usar la interfaz mysqli_result ofrece el beneficio adicional de la navegación flexible del conjunto de resultados en el lado del cliente.
Ejemplo #8 Conjunto de resultados almacenado en buffer para la lectura flexible
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, etiqueta CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, etiqueta) VALUES (1, 'a'), (2, 'b'), (3, 'c')")) {
echo "Falló la creación de la tabla: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($sentencia = $mysqli->prepare("SELECT id, etiqueta FROM test"))) {
echo "Falló la preparación: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$sentencia->execute()) {
echo "Falló la ejecución: (" . $sentencia->errno . ") " . $sentencia->error;
}
if (!($resultado = $sentencia->get_result())) {
echo "Falló la obtención del conjunto de resultados: (" . $sentencia->errno . ") " . $sentencia->error;
}
for ($num_fila = ($resultado->num_rows - 1); $num_fila >= 0; $num_fila--) {
$resultado->data_seek($num_fila);
var_dump($resultado->fetch_assoc());
}
$resultado->close();
?>
El resultado del ejemplo sería:
array(2) {
["id"]=>
int(3)
["etiqueta"]=>
string(1) "c"
}
array(2) {
["id"]=>
int(2)
["etiqueta"]=>
string(1) "b"
}
array(2) {
["id"]=>
int(1)
["etiqueta"]=>
string(1) "a"
}
Escape de valores e inyección SQL
Las variables vinculadas son escapadas automáticamente por el servidor. Éste inserta sus valores escapados en los lugares apropiados dentro de la plantilla de la sentencia antes de la ejecución. Se debe proporcinar al servidor una sugerencia del tipo de variable vinculada, para crear una conversión apropiada. Vea la función mysqli_stmt_bind_param() para más información.
El escape automático de valores en el servidor algunas veces es considerado una característica de seguridad para prevenir inyecciones SQL. Se puede alcanzar el mismo grado de seguridad con sentencias no preparadas, si los valores de entrada son escapados correctamente.
Emulación de sentencias preparadas en el lado del cliente
La API no inclye la emulación para sentencias preparadas en el lado del cliente.
Comparación entre sentencias preparadas y no preparadas
La tabla de abajo compara las sentencias preparadas y no preparadas del lado del servidor.
| Sentencia preparada | Sentencia no preparada | |
|---|---|---|
| Viajes de ida y vuelta desde el cliente al servidor, SELECT, ejecución única | 2 | 1 |
| Cadenas de sentencias tranferidas desde el cliente al servidor | 1 | 1 |
| Viajes de ida y vuelta desde el cliente al servidor, SELECT, ejecución repetida (n) | 1 + n | n |
| Cadenas de sentencias tranferidas desde el cliente al servidor | 1 plantilla, n veces parametro vinculado, si existe | n veces junto con el parámetro, si existe |
| API de vinculación de parámetros de entrada | Sí, escape de entradas automático | No, escape de entradas manual |
| API de vinculación de variables de salida | Sí | No |
| Soporte para el uso de la API mysqli_result | Sí, use mysqli_stmt_get_result() | Sí |
| Conjuntos de resultados almacenados en buffer | Sí, use mysqli_stmt_get_result() o la vinculación con mysqli_stmt_store_result() | Sí, lo predeterminado de mysqli_query() |
| Conjuntos de resultados no almacenados en buffer | Sí, use la API de vinculación de salida | Sí, use mysqli_real_query() con mysqli_use_result() |
| "Sabor" de la transferencia de datos del protocolo Cliente Servidor de MySQL | Protocolo binario | Protocolo de texto |
| Tipos de datos SQL de los valores del conjunto de resultados | Preservados al obtenerlos | Convertidos a cadena o preservados al obetenerlos |
| Soporta todas las sentencia SQL | Versiones reciente de MySQL soportan muchas pero no todas | Sí |
Véase también