Prepared statements: introducción y ejemplos

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. 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. Una posible solución a este problema la brinda el uso de prepared statements o sentencias preparadas, es decir, plantillas de instrucciones a las bases de datos a las que no se asignan datos hasta el momento de su ejecución.

¿Qué tiene esta técnica de especial y en qué situaciones se usa? Como ejemplo, te mostramos cómo funcionan las prepared statements en MySQL y cómo pueden usarse en la gestión de bases de datos.

¿Qué es una prepared statement?

Las prepared statements, también llamadas consultas, comandos o sentencias preparadas, son plantillas 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. Cuando las consultas se introducen a mano, en cambio, los valores se asignan en el mismo momento de ejecutarlas.

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 prepared statements, 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. Y algunos 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é conviene utilizar prepared statements en MySQL y sistemas similares?

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

Nota

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.

No obstante, 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). 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.

¿Cómo se utiliza exactamente una prepared statement?

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 de la plantilla 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.

Tutorial: cómo utilizar prepared statements en MySQL con MySQLi

Ahora que ya hemos explicado cómo funcionan las sentencias preparadas, en este tutorial explicamos cómo usar estas plantillas con ejemplos concretos. Para ello, tomaremos:

  • MySQL como sistema de gestión de base de datos y
  • PHP como lenguaje de las prepared statements.

Las versiones más recientes de MySQL soportan el uso de prepared statements del lado del servidor basándose en un protocolo binario que contiene todas las órdenes SQL de actualización de datos y que, además, registra todas las actualizaciones desde la última vez que se guardaron los datos. Este tutorial toma como interfaz de acceso MySQLi, la extensión de PHP que también soporta sentencias preparadas por medio del protocolo binario.

Nota

Una buena y muy popular alternativa a MySQLi como API para prepared statements es la interfaz orientada a objetos PDO (PHP Data Objects), que, por lo general, es más fácil de usar para los principiantes.

PREPARE, EXECUTE y DEALLOCATE PREPARE: las tres sentencias SQL básicas para utilizar una sentencia preparada

Existen tres comandos o sentencias SQL que tienen un papel decisivo en el uso de prepared statements en bases de datos MySQL:

La sentencia PREPARE sirve para preparar, nunca mejor dicho, una prepared statement para su aplicación y, entre otras cosas, darle un nombre unívoco que sirva para recuperar y controlar la sentencia más adelante.

PREPARE stmt_name FROM preparable_stmt

Para ejecutar sentencias SQL ya preparadas se requiere el comando EXECUTE. Para indicar qué prepared statement en concreto se quiere ejecutar, se introduce el nombre generado con PREPARE. Ahora puedes decidir con qué frecuencia quieres ejecutar una sentencia: puedes generar tantas variables distintas como quieras y asignarles respectivamente tantos valores como quieras.

EXECUTE stmt_name
	[USING @var_name [, @var_name] ...]

Para anular una prepared statement en PHP se utiliza el comando DEALLOCATE PREPARE. Las sentencias se borran también automáticamente al cerrar cada sesión. 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.

{DEALLOCATE | DROP} PREPARE stmt_name

Sentencias SQL que se pueden usar como prepared statements en MySQL

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. En concreto, se trata de los siguientes comandos:

  • SHOW WARNINGS
  • SHOW COUNT(*) WARNINGS
  • SHOW ERRORS
  • SHOW COUNT(*) ERRORS

Además, tampoco se pueden generar plantillas para solicitudes SQL referidas a las variables de sistema warning_count ni error_count.

Las sentencias o comandos que sí se pueden usar son los siguientes: 

ALTER TABLE
ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
	| LOGS | STATUS | MASTER | SLAVE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE}
REVOKE
SELECT
SET
SHOW {WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE

Particularidades de la sintaxis SQL de las prepared statements en PHP

Al compararla con la sintaxis SQL estándar, la sintaxis de una sentencia preparada tiene algunas particularidades que no pueden pasarse por alto. En primer lugar, destaca el uso de marcadores de posición para los valores de los parámetros, gracias a los cuales las sentencias preparadas ofrecen una gran ventaja en lo que al acceso a los sistemas de gestión de bases de datos se refiere. A partir de MySQL 8.0, estos 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. Otras características específicas de la sintaxis de las prepared statements son las siguientes:

  • 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.
  • Las prepared statements pueden usarse en procedimientos almacenados (función para solicitar procesos completos de comandos).
  • 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.

Prepared statements en MySQLi: ejemplo

El siguiente ejemplo muestra qué forma tiene una entrada en MySQLi con prepared statements en PHP: 

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Establecer conexión 
$conn = new mysqli($servername, $username, $password, $dbname);
// Comprobar conexión 
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
// Preparación de las prepared statements
$stmt = $conn->prepare("INSERT INTO MisClientes (Nombre, Apellido, Correo) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $nombre, $apellido, $correo);
// Asignación de parámetros y ejecución
$Nombre = "Alicia";
$Apellido = "Torres";
$Correo = "aliciaperez@ejemplo.com";
$stmt->execute();
$Nombre = "Juan";
$Apellido = "Martínez";
$Correo= "juanmartinez@ejemplo.com";
$stmt->execute();
$Nombre = "Ana";
$Apellido = "López";
$Correo = "analopez@ejemplo.com";
$stmt->execute();
echo "Nuevas entradas añadidas con éxito";
$stmt->close();
$conn->close();
?>

Esta secuencia de comandos PHP establece en primer lugar la conexión con la base de datos MySQL ($conn), 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.

Con la línea “INSERT INTO MisClientes (Nombre, Apellido, Correo) VALUES (?, ?, ?)” comienza la parte decisiva de la sentencia preparada: la base de datos de clientes MisClientes deberá recibir input (INSERT INTO) en cada uno de los parámetros Nombre, Apellido y Correo. Los valores (VALUES) serán reemplazados en primer lugar por marcadores de posición, que se pueden reconocer por el signo de interrogación (?).

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, sss, muestra que los tres parámetros son strings, es decir, cadenas de caracteres. 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)
aN5KqxK1slc.jpg Para mostrar este video, se requieren cookies de terceros. Puede acceder y cambiar sus ajustes de cookies aquí.
¿Le ha resultado útil este artículo?
Utilizamos cookies propias y de terceros para mejorar nuestros servicios y mostrarle publicidad relacionada con sus preferencias mediante el análisis de sus hábitos de navegación. Si continua navegando, consideramos que acepta su uso. Puede obtener más información, o bien conocer cómo cambiar la configuración de su navegador en nuestra. Política de Cookies.