Sentencias Preparadas en PHP

Los sistemas de gestión de bases de datos (DBMS, por sus siglas en inglés) que trabajan con el lenguaje SQL son muy populares, pero también un blanco habitual de la manipulación en el suministro de datos. Las inyecciones SQL son una de las amenazas más críticas para las aplicaciones web, ya que permiten a los atacantes ejecutar comandos SQL no autorizados que pueden comprometer tu base de datos. Para mitigar esta amenaza, lo ideal es utilizar sentencias preparadas y parámetros en las conexiones de PHP a bases de datos MySQL.

¿Qué son las Sentencias Preparadas?

Una prepared statement, también llamada consulta, comando o sentencia preparada, es una plantilla para consultas a sistemas de bases de datos en lenguaje SQL cuyos parámetros están desprovistos de valores. Para reemplazar dichos valores, estas plantillas trabajan con variables o marcadores de posición, que no son sustituidos por los valores reales hasta estar dentro del sistema. La base de datos MySQL soporta las consultas preparadas.

Todos los grandes sistemas de gestión de bases de datos que funcionan con SQL, como MySQL, MariaDB, Oracle, Microsoft SQL Server y PostgreSQL, soportan sentencias preparadas, si bien la mayoría recurre para ello a un protocolo binario NoSQL. Algunos sistemas, entre los que se encuentra MySQL, también utilizan la sintaxis SQL convencional a la hora de implementar las sentencias preparadas. Lenguajes de programación como Java, Perl, Python y PHP prevén el uso de prepared statements en sus bibliotecas estándar o sus extensiones. Si utilizas PHP para acceder a la base de datos, puedes implementar sentencias preparadas con la interfaz orientada a objetos PHP Data Objects (PDO) o con la extensión de PHP MySQLi.

¿Por Qué Utilizar Sentencias Preparadas?

La principal razón para utilizar sentencias preparadas cuando se trabaja con sistemas de gestión de bases de datos como MySQL no es otra que la seguridad.

Seguridad contra Inyecciones SQL

El mayor problema de los métodos convencionales de acceso a las bases de datos basadas en lenguaje SQL es la facilidad con la que pueden ser manipuladas. Este tipo de ataques se denominan inyecciones SQL: el código se completa o se modifica para conseguir acceso a datos sensibles o incluso lograr un control absoluto sobre la base de datos. Así, por ejemplo, las entradas de usuario con un cifrado insuficiente y que contienen metacaracteres, como las comillas o el punto y coma, son una presa fácil para los ciberdelincuentes.

En PHP y en lenguajes similares, las sentencias preparadas no dan lugar a tales lagunas en el sistema de seguridad, ya que no reciben valores concretos hasta que se ejecutan dentro del sistema. Los parámetros de una sentencia preparada son enviados al servidor de forma separada del comando SQL, el controlador gestiona esto, de modo que las comillas especiales no son un problema. Esto significa que la consulta no es analizada de nuevo con los valores, evitando la inyección. Una condición para que una prepared statement o sentencia preparada sea realmente segura es que ninguno de sus componentes haya sido generado desde una fuente externa.

Mejora del Rendimiento

La protección contra las inyecciones SQL no es el único argumento a favor de estas plantillas de solicitudes. Una vez analizada y compilada, una prepared statement puede reutilizarse en el sistema de la base de datos siempre que se quiera (variando los datos correspondientes cada vez), y optimizará su plan para ejecutar la consulta. Al utilizar consultas preparadas, se evita repetir el ciclo análisis/compilación/optimización. Esto hace que sea una forma más eficiente de ejecutar una consulta. De este modo, cuando se trata de tareas en SQL que deben repetirse una y otra vez, las sentencias preparadas requieren muchos menos recursos y son más rápidas que las solicitudes manuales. Una consulta preparada puede ser ejecutada varias veces, enviando los valores de los parámetros al servidor sin analizar la consulta nuevamente.

Funcionamiento General de las Sentencias Preparadas

Sin entrar en los detalles de la sintaxis del lenguaje de programación ni de las características de cada sistema de gestión de base de datos, la incorporación y el uso de sentencias preparadas suele dividirse en las siguientes fases:

Fase 1: Preparación

El primer paso es generar una plantilla de sentencia. En PHP, la función correspondiente es prepare(). En lugar de los valores, a los parámetros relevantes se les asignan los ya mencionados marcadores de posición, también llamados parámetros de sustitución posicionales o variables bind. En general, estos marcadores se caracterizan por un signo de interrogación (?), como en el ejemplo siguiente:

INSERT INTO Producto (Nombre, Precio) VALUES (?, ?);

Las sentencias preparadas ya completas se envían al sistema de gestión de bases de datos correspondiente.

Fase 2: Procesamiento en el DBMS

El sistema de gestión de bases de datos (DBMS) parsea, es decir, analiza sintácticamente la plantilla de sentencia para que en un siguiente paso se pueda compilar, es decir, convertirse en una orden ejecutable. Durante este proceso, además, se optimiza la prepared statement.

Fase 3: Ejecución

Más adelante, la plantilla procesada puede volver a utilizarse en el sistema de base de datos siempre que se quiera, con la única condición de que la aplicación o fuente de datos conectada proporcione el input adecuado, es decir, los datos que reemplazarán a los marcadores de posición. Retomando el código del ejemplo anterior (fase 1), al marcador o parámetro Nombre podría asignársele el valor "Libro" y, al parámetro Precio, el valor "10"; o también podría tratarse de un "ordenador" con el valor de precio "1000".

Implementación de Sentencias Preparadas en PHP

En PHP, se pueden implementar sentencias preparadas principalmente con PDO y MySQLi.

Con PDO (PHP Data Objects)

PDO te ofrece una interfaz para acceder a bases de datos en PHP que soporta múltiples sistemas de gestión de bases de datos, incluyendo MySQL. Una sentencia preparada en PDO sigue unos pasos bastante sencillos:

1. Preparación de la consulta: Se envía una plantilla de consulta al servidor con marcadores de posición (? para posicionales o :nombre para nombrados) en lugar de los valores reales. Esto se hace con $dbh->prepare().
2. Vinculación de los parámetros: Los valores reales se vinculan a los marcadores de posición usando bindParam() o bindValue().
3. Ejecución: La plantilla preparada se ejecuta con los valores vinculados usando $stmt->execute(). Se puede pasar un array de valores directamente a PDOStatement::execute(), lo que también es seguro contra inyección SQL.

PDO emula las sentencias preparadas para los controladores que no las soportan nativamente. Es importante mencionar que cuando se usan parámetros nombrados con bindParam, el nombre en sí no puede contener un guion ('-').

<?php/* Ejecuta una consulta preparada pasando un array de valores */$sth = $dbh->prepare('SELECT nombre, color, calorias FROM fruta WHERE calorias < ?');$sth->execute([150]);$result = $sth->fetchAll();// ...?>

Con MySQLi (Extensión PHP)

La extensión de PHP MySQLi soporta sentencias preparadas por medio del protocolo binario. La implementación en MySQLi generalmente sigue los siguientes pasos:

1. Conexión a la base de datos: Establece la conexión a MySQL.
2. Crear una sentencia: Invoca el método prepare() en el objeto de conexión MySQLi, pasándole la consulta SQL con signos de interrogación (?) como marcadores de posición.
3. Vincular parámetros (`mysqli_stmt_bind_param`): Utiliza bind_param() para vincular los valores a los marcadores de posición. Esta función recibe el tipo de dato de cada parámetro, en el orden en que aparece en la consulta, seguido de los valores verdaderos.
4. Ejecutar la consulta (`mysqli_stmt_execute`): Ejecuta la sentencia preparada.
5. Obtener resultados (opcional): Si la consulta devuelve datos (por ejemplo, un SELECT), se pueden vincular variables de salida con bind_result() y recuperar los resultados.
6. Cerrar la sentencia: Libera los recursos del servidor cerrando la sentencia.

Ejemplo de Inserción con MySQLi

Esta secuencia de comandos PHP establece en primer lugar la conexión con la base de datos MySQL ($mysqli), en la cual hay que indicar los datos del servidor en cuestión, como el nombre de equipo, el nombre de usuario, la contraseña y el nombre de la base de datos.

<?phpmysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);$mysqli = new mysqli("example.com", "user", "password", "database");// Consulta no preparada (ejemplo para contraste, no recomendado)$mysqli->query("DROP TABLE IF EXISTS test");$mysqli->query("CREATE TABLE test(id INT, label TEXT, name VARCHAR(50), lastname VARCHAR(50), email VARCHAR(100))");// Consulta preparada, paso 1: la preparaciónif (!($stmt = $mysqli->prepare("INSERT INTO test(id, label, name, lastname, email) VALUES (?, ?, ?, ?, ?)"))) { echo "Fallo durante la preparación: (" . $mysqli->errno . ") " . $mysqli->error;}// Paso 2: Vincular parámetros (bind_param)// Los tipos de datos son: s para cadena, i para entero, d para doble (decimal), b para blob.$id = 1;$label = 'PHP';$name = 'Juan';$lastname = 'Pérez';$email = '[email protected]';if (!$stmt->bind_param("issss", $id, $label, $name, $lastname, $email)) { echo "Fallo durante el enlace de los parámetros: (" . $stmt->errno . ") " . $stmt->error;}// Paso 3: Ejecutar la consultaif (!$stmt->execute()) { echo "Fallo durante la ejecución: (" . $stmt->errno . ") " . $stmt->error;}echo "Nuevo registro insertado correctamente.";// Cerrar la sentencia$stmt->close();$mysqli->close();?>

En el ejemplo anterior, la base de datos de clientes deberá recibir input (INSERT INTO) en cada uno de los parámetros. A continuación, hay que enlazar los parámetros (bind_param). Para ello, la base de datos necesita información acerca del tipo de datos que tratará. El argumento usado con este fin en el ejemplo, issss, muestra que el primer parámetro es un entero (i) y los siguientes cuatro son cadenas de caracteres (s). Otras alternativas de enlace serían las siguientes:

• i: INTEGER (número entero)
• d: DOUBLE (valor numérico aproximado)
• b: BLOB (gran objeto binario de datos)

Ejemplo de Selección con MySQLi

<?phpmysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);$mysqli = new mysqli("example.com", "user", "password", "database");// Consulta preparada para seleccionar$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = ?");$search_id = 1;$stmt->bind_param("i", $search_id);$stmt->execute();// Vincular resultados$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;}// Almacenar el resultado (necesario para usar num_rows)$stmt->store_result();// Mostrar datosif ($stmt->num_rows > 0) { while ($stmt->fetch()) { echo "ID: " . $out_id . ", Label: " . $out_label . "<br>"; }} else { echo "No se encontraron registros.";}// Cerrar la sentencia$stmt->close();$mysqli->close();?>

Para verificar si nuestra tabla está vacía (0 registros), ocuparemos la constante num_rows que nos muestra la cantidad de registros en nuestra tabla, pero esta debemos acompañarla de la función store_result. Sin esta, num_rows podría no devolver el valor esperado o ser 0 por omisión, ya que los resultados no serían recuperados por el cliente.

Desasignación de Sentencias

Cada consulta preparada ocupa recursos en el servidor. Las sentencias se borran automáticamente al cerrar cada sesión. Sin embargo, es una buena práctica que sean cerradas explícitamente inmediatamente después de su uso. Esto es importante porque, de lo contrario, se alcanzaría pronto el número máximo de sentencias establecido por la variable max_prepared_stmt_count y ya no se podría crear ninguna sentencia preparada nueva. Para desasignar una sentencia preparada explícitamente, se puede usar:

{DEALLOCATE | DROP} PREPARE stmt_name

Consideraciones Avanzadas y Particularidades

Sentencias SQL Soportadas

Prácticamente todas las sentencias SQL aplicables a MySQL pueden prepararse y ejecutarse como prepared statements. Una excepción son los llamados comandos de diagnóstico, que no pueden usarse como prepared statements según el estándar SQL. Las sentencias UPDATE y INSERT son muy comunes con sentencias preparadas.

Sintaxis y Limitaciones Específicas

• No anidaciones: La sintaxis SQL de las prepared statements en PHP no permite anidaciones. Por lo tanto, una sentencia a la que se haya referido un comando PREPARE no podrá ser ella misma PREPARE, EXECUTE ni DEALLOCATE PREPARE.
• Procedimientos almacenados: Las prepared statements pueden usarse en procedimientos almacenados (función para solicitar procesos completos de comandos).
• No multi-sentencias: Las llamadas sentencias múltiples, que son varias sentencias enviadas de una vez, no son posibles dentro de una sentencia preparada ni dentro de una cadena de caracteres con separación mediante punto y coma.
• Parámetros OUT e INOUT: A partir de MySQL 8.0, los marcadores de posición también pueden usarse, por ejemplo, en los parámetros OUT e INOUT, en las sentencias PREPARE y EXECUTE. En el caso de los parámetros IN, incluso están disponibles con independencia del sistema de la base de datos. Se deben conocer las longitudes máximas que un parámetro dado podrá alcanzar cuando se vincule como parámetro de salida.
• Reutilización de marcadores: En algunas bases de datos, usar un marcador de posición (? o :nombre) múltiples veces dentro de la misma sentencia no funciona. Debes vincular cada instancia por separado si tu driver no lo soporta.
• Identificadores dinámicos: Los identificadores de columna y tabla no pueden pasarse como parámetros en consultas preparadas. Para referencias dinámicas de columna, se deben "listar en blanco" explícitamente las columnas conocidas en la tabla.

Gestión de Recursos y Protocolo Binario

Las consultas preparadas utilizan un protocolo llamado binario. Este protocolo envía los datos del juego de resultados "tal cual", en formato binario. Los datos no son serializados en cadenas de caracteres antes de ser enviados, sino que se envían en su formato PHP apropiado. Este comportamiento difiere para las consultas no preparadas, que a menudo serializan los datos en cadenas de caracteres.

Emulación y Rendimiento del Driver

PDO ofrece emulación de sentencias preparadas para los controladores que no las soportan de forma nativa. Esto puede tener implicaciones en el rendimiento.

Para PostgreSQL, aunque soporta sentencias preparadas, el driver PDO de PHP nunca envía la sentencia preparada al servidor de PostgreSQL por adelantado de la llamada a PDO::execute(). Por lo tanto, PDO::prepare() nunca lanzará un error por errores de sintaxis SQL en ese punto.

Existe una idea errónea común sobre cómo funcionan los marcadores de posición en las sentencias preparadas: no se sustituyen simplemente como cadenas (escapadas) y se ejecuta el SQL resultante. Los valores son enviados por separado.

Si solo se va a enviar una consulta, usar PDO::query() con PDO::quote() puede ser más rápido (aproximadamente 3 veces más rápido en algunas pruebas con MySQL) que las sentencias preparadas. Sin embargo, esto solo es cierto para un número pequeño de consultas; para un gran número de consultas idénticas (con diferentes parámetros), las sentencias preparadas son siempre más eficientes.

tags: #sentencias #preparadas #php