CONSULTAS PREPARADAS EN MYSQL: CÓMO USARLAS DE FORMA SEGURA

Las consultas preparadas en MySQL definida y que se compila una vez consultas SQL de manera dinámica y segura. Son especialmente útiles para ejecutar consultas repetitivas con diferentes parámetros, mejorar la seguridad contra inyecciones SQL y optimizar el rendimiento en ciertos casos. Estas consultas pueden ejecutarse múltiples veces con diferentes valores. En lugar de escribir la consulta completa cada vez, se usan marcadores de posición ("?" o "nombres") para los valores que serán diferentes cada vez, según las necesidades.

Ventajas

  • Seguridad: Evitan inyecciones SQL al separar los datos de la consulta.
  • Rendimiento: La consulta se compila una vez y se reutiliza, lo que reduce el tiempo de procesamiento en ejecuciones repetitivas.
  • Flexibilidad: Permiten ejecutar consultas dinámicas con parámetros variables.
  • Mantenimiento: Facilitan el manejo de consultas complejas o dinámicas.

Componentes principales

Las consultas preparadas en MySQL se manejan con tres instrucciones principales:
  • PREPARE: Define y compila la consulta con marcadores de posición.
  • EXECUTE: Ejecuta la consulta preparada, opcionalmente con valores para los parámetros.
  • DEALLOCATE PREPARE: Libera los recursos asociados con la consulta preparada.

Sintaxis básica

-- Preparar la consulta
PREPARE nombre_statement FROM 'consulta_sql_con_marcadores';

-- Ejecutar la consulta
EXECUTE nombre_statement [USING @variable1, @variable2, ...];

-- Liberar la consulta
DEALLOCATE PREPARE nombre_statement;
  • nombre_statement: Un nombre único para identificar la consulta preparada.
  • consulta_sql_con_marcadores: La consulta SQL con ? como marcadores de posición para parámetros.
  • USING @variable1, @variable2, ...: Variables que contienen los valores para los marcadores.

Ejemplo de consulta preparada simple

Supongamos que existe una tabla ventas con columnas producto, mes y cantidad, y quieres consultar las ventas de un producto y mes específicos.

SET @sql = 'SELECT cantidad FROM ventas WHERE producto = ? AND mes = ?';
PREPARE stmt FROM @sql;

-- Asignar valores a los parámetros
SET @producto = 'Laptop';
SET @mes = 'Enero';

-- Ejecutar la consulta
EXECUTE stmt USING @producto, @mes;

-- Liberar la consulta
DEALLOCATE PREPARE stmt;
  • ? son marcadores de posición para producto y mes.
  • SET @producto y SET @mes asignan valores a los parámetros.
  • EXECUTE stmt USING ... reemplaza los ? con los valores de las variables.

Ejemplo de consulta preparada en un procedimiento almacenado

Las consultas preparadas son comunes en procedimientos almacenados para manejar parámetros dinámicos.

DELIMITER //
CREATE PROCEDURE obtener_ventas(IN p_producto VARCHAR(50), IN p_mes VARCHAR(20))
BEGIN
-- Preparar la consulta
PREPARE stmt FROM 'SELECT cantidad FROM ventas WHERE producto = ? AND mes = ?';

-- Ejecutar con parámetros
EXECUTE stmt USING p_producto, p_mes;

-- Liberar la consulta
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- Llamar al procedimiento
CALL obtener_ventas('Laptop', 'Enero');
  • El procedimiento acepta p_producto y p_mes como parámetros.
  • La consulta preparada usa estos parámetros en lugar de valores fijos.
  • DEALLOCATE PREPARE limpia los recursos al final.

Ejemplo de consulta preparada dinámica

Si se necesita generar una consulta completamente dinámica (por ejemplo, para una tabla dinámica), se puede concatenar el texto de la consulta antes de prepararla.

-- Tabla de ejemplo: ventas
SET @sql = NULL;

-- Generar dinámicamente las columnas para una tabla dinámica
SELECT 
GROUP_CONCAT(DISTINCT
CONCAT(
  'SUM(CASE WHEN mes = ''',
  mes,
  ''' THEN cantidad ELSE 0 END) AS `',
  mes,
  '`'
)
) INTO @sql
FROM ventas;

-- Completar la consulta
SET @sql = CONCAT('SELECT producto, ', @sql, ' FROM ventas GROUP BY producto');

-- Preparar y ejecutar
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
  • GROUP_CONCAT genera una lista de columnas dinámicas (por ejemplo, SUM(CASE WHEN mes = 'Enero' ...)).
  • La consulta completa se construye en @sql.
  • PREPARE, EXECUTE y DEALLOCATE PREPARE manejan la ejecución.

Consideraciones

  • Alcance: Las consultas preparadas solo existen en la sesión actual de MySQL. Cuando la sesión termina, se liberan automáticamente.
  • Seguridad: Al usar marcadores de posición (?), los valores se escapan automáticamente, previniendo inyecciones de SQL.
  • Limitaciones: No se pueden usar >b>consultas preparadas para ciertos comandos como CREATE TABLE o DROP TABLE en algunas versiones de MySQL. Los nombres de tablas o columnas no pueden ser parámetros; solo los valores.
  • Liberación de recursos: Siempre usar DEALLOCATE PREPARE para liberar consultas preparadas y evitar consumo innecesario de memoria.
  • Se puede usar DROP PREPARE "nombre_statement" como alternativa a DEALLOCATE:
Las consultas preparadas hay que usarlas cuando hay consultas repetitiva como por ejemplo, buscar registros con diferentes filtros en un bucle. Cuando los datos provienen de fuentes no confiables (entradas de usuarios). Para operaciones como INSERT, UPDATE, SELECT o DELETE con parámetros dinámicos.
No todas las consultas pueden ser preparadas (por ejemplo, nombres de tablas o columnas no pueden ser marcadores de posición). Requieren un poco más de código que las consultas concatenadas, pero la seguridad y eficiencia lo justifican.