BD UD7 Sentencias Prepared

Sentencias preparadas (prepared statements)

Nota: Todos los ejemplos están basados en esta base de datos.

• Más información en este enlace.

• Este tipo de sentencias se basan en el concepto de que cuando un programa cliente (aplicación de escritorio, página web con PHP, ASP.NET, JSP,...) va a realizar una operación SQL (SELECT, INSERT,....) normalmente siempre va a ejecutar la misma sentencia cambiando los datos que se le envían al servidor.

Imaginemos que damos de alta a 10 empleados. Tendríamos que enviar 10 sentencias INSERT con los datos de cada uno de los empleados. El gestor tendrá que analizar esa sentencia por cada empleado, comprobar si está bien escrita y ejecutarla...

Con las sentencias 'prepared', lo que hacemos en enviar al servidor la sentencia SQL sin datos, enviamos la sentencia con parámetros. El gestor la analiza una sola vez para comprobar que no tiene problemas sintácticos y una vez hecho, el cliente sólo tiene que enviar los datos que van a ser ejecutados por la sentencia.

• El uso de sentencias 'prepared' nos va a permitir:

• Una mayor velocidad de ejecución (en el caso de usar procedimientos almacenados, ya no tenemos esta ventaja) porque las sentencias están pre-compiladas.
• Una mayor seguridad, ya que impide la inyección de código (en el caso de usar procedimientos almacenados, ya no tenemos esta ventaja, ya comenté antes este tipo de ataque).
• La posibilidad de crear sentencias SQL dinámicas, es decir, que podemos construir la sentencia como si fuera una cadena a la que vamos añadiendo partes hasta tenerla completa y ejecutarla (esta es la mayor ventaja si estamos usando procedimientos almacenados y necesitamos este tipo de sentencias, pero debemos de tener cuidado con la inyección de código).

Por lo tanto, si estamos haciendo uso de procedimientos, el único sentido de utilizar sentencias prepared es el de crear sentencias dinámicas dentro del mismo.

La sentencia PREPARE es muy recomendada si estamos programando un programa cliente y llamamos múltiples veces al servidor enviando la misma sentencia SQL, cambiando sólo los datos entre cada llamada o si queremos, desde el lado cliente, crear una sentencia SQL dinámicamente.

Proceso para emplear estas sentencias

• El proceso para poder emplear este tipo de sentencias es el siguiente (tanto si lo hacemos desde el cliente (php, java,...) como desde un procedimiento almacenado:

• PREPARE: Donde definimos la sentencia SQL con parámetros que queremos ejecutar (por ejemplo, una sentencia INSERT lleva como parámetros los valores a añadir, o una sentencia SELECT lleva parámetros en la parte WHERE).
• Paso de datos a los parámetros que están definidos en la sentencia anterior.
• EXECUTE: Para ejecutar la sentencia.
• DEALLOCATE PREPARE: Para liberar la memoria del servidor de la sentencia preparada.

Ejemplo genérico

• Veamos un ejemplo genérico:

SET @sentencia = 'SELECT * FROM TABLA WHERE col1=? AND col2=?';
PREPARE stPrepared FROM @sentencia;     -- Preparamos la sentencia. El nombre stPrepared se lo damos nosotros. Podemos poner lo que queramos.
SET @valorParam1 = 2;                    -- Estamos usando una variable para establecer el valor que se le va a enviar al parámetro. Podríamos pasarlo directamente en la siguiente intrucción.
SET @valorParam2 = 'Angel';                    -- Estamos usando una variable para establecer el valor que se le va a enviar al parámetro. Podríamos pasarlo directamente en la siguiente intrucción.
EXECUTE stPrepared USING @valorParam1,@valorParam2;
DEALLOCATE PREPARE stPrepared;     -- Liberamos la memoria

Importante

• En la sentencia PREPARE sólo podemos emplear variables de sesión o literales (es decir, entre comillas si son cadenas o directamente el valor si son numéricos). No podemos emplear variables locales si estamos dentro de un procedimiento. En la sentencia EXECUTE ... USING sólo podremos emplear variables de sesión.
• La sentencia 'PREPARE' es visible a nivel de sesión. Quiere esto decir que si no hacemos un DEALLOCATE, podríamos emplearla en cualquier pestaña SQL de la conexión activa.

Ejemplo aplicado a nuestra base de datos

• Veamos un ejemplo aplicado a nuestra base de datos.

Crea un procedimiento de nombre animales_getListPorFiltro que devuelva la lista de animales filtrados por columnas dinámicamente. Los datos a enviar será el nombre de la columna, el tipo de operación y el valor que debe cumplir. Por ejemplo, 'anhos','=','2'.

USE CIRCO;

DELIMITER $$
CREATE PROCEDURE animales_getListPorFiltro (p_nombreColum varchar(20),p_operacion char(2),p_valorParam varchar(10))		
    COMMENT 'Devuelve todos los animales que cumplan la condición que se la va pasar como parámetro.'
BEGIN
    
    SET @v_consulta = CONCAT('SELECT * FROM ANIMALES WHERE ',p_nombreColum,p_operacion,'?');	-- Podríamos poner como tercer parámetro directamente el parámetro p_valorParam
    SET @v_valor = p_valorParam;
    PREPARE prepConsulta FROM @v_consulta;
    EXECUTE prepConsulta USING @v_valor;
    DEALLOCATE PREPARE prepConsulta;


END $$
DELIMITER ;

Ejemplo de uso:

CALL animales_getListPorFiltro('tipo','=','Jirafa');

CALL animales_getListPorFiltro('anhos','>=','3');

Ejercicios propuestos Sentencias preparadas

Ejercicio 1

Crea un procedimiento de nombre animales_getListFiltroNumeroColum que devuelva el número de animales indicado por el parámetro (emplear LIMIT). También se le pasará en otro parámetro las columnas que debe mostrar de la forma 'col1,col3,col5'.

Nota: Este procedimiento sería un buen candidato para aplicar el control de excepciones de Mysql y controlar si enviamos una columna que no existe.

Ejercicio 2

Crea un procedimiento de nombre animales_getListFiltroPista que devuelva la lista de animales que trabajan en la pista que se envía como parámetro. En caso de que no se envíe dato (valor null) debe mostrar todos los animales.

Atención: En este caso, la sentencia SQL a emplear es IN ('pista1,'pista2',...). Si o fijáis, la sentencia IN espera recibir cada uno de los datos entre comillas.

Por lo tanto, nosotros no podemos enviar como dato: 'pista1,pista2,...'. Si quisiéramos enviar así los datos, tendríamos que descomponerlos dentro del procedimiento con un bucle y guardar cada una de las pistas en una tabla temporal (CREATE TEMPORARY TABLE IF NOT EXISTS) y después hacer una consulta IN (Subconsulta a la tabla temporal).

Para poder enviar cada dato separado por comilla simple necesitamos emplear el carácter 'escape' que en Mysql es \ de la forma: '\'pista1\',\'pista2\',...'

No podemos hacer uso de parámetros ya que no podemos emplearlo en la cláusula IN de la forma: IN (?), sino que tendremos que concatener a la sentencia SQL el dato enviado.

Esto puede dar lugar a problemas de seguridad ya que se podría inyectar código SQL como parte de la sentencia.

Ejercicio 3

En este ejemplo vamos a ver como podemos aprovechar una de las ventajas del empleo de sentencias prepared. En este caso, vamos a crear un filtro dinámico (vamos a construir dinámicamente la parte WHERE de una sentencia. Dependiendo de las opciones de búsqueda iremos concatenando condiciones a la parte WHERE. El problema que siempre vamos a tener es de cuando concatenar AND a las condiciones, ya que la primera condición que se ponga no llevará. Para evitar tener que controlar cuando se ha añadido una condición previa, podemos hacer que la parte WHERE siempre lleve una condición y después añadiremos el resto de condiciones. Podremos poner algo como SELECT columnas FROM tabla WHERE 1=1. De esta forma, si enviamos algún campo de búsqueda siempre tenemos que poner: AND col=valor

Crea un procedimiento de nombre animales_getByAnoPeso al que se le pasen dos parámetros (peso y año) y en función de si llevan valor o no, haz que devuelva los animales que cumplan los criterios de búsqueda.

Solución Ejercicios propuestos Sentencias preparadas

Ejercicio 1

Crea un procedimiento de nombre animales_getListFiltroNumeroColum que devuelva el número de animales indicado por el parámetro. También se le pasará en otro parámetro las columnas que debe mostrar de la forma 'col1,col3,col5'.

Nota: Este procedimiento sería un buen candidato para aplicar el control de excepciones de Mysql y controlar si enviamos una columna que no existe.

USE CIRCO;
DROP PROCEDURE IF EXISTS animales_getListFiltroNumeroColum;
DELIMITER $$
CREATE PROCEDURE animales_getListFiltroNumeroColum (p_columnas varchar(100),p_numero int)		
    COMMENT 'Devuelve el número de animales indicado por el parámetro. También se le pasará en otro parámetro las columnas que debe mostrar de la forma col1,col3,col5'
BEGIN
    SET @v_consulta = CONCAT('SELECT ',p_columnas,' FROM ANIMALES LIMIT ?');	-- Añadir espacios en blanco despúes del SELECT y antes del FROM
    SET @v_numero = p_numero;
    PREPARE prepConsulta_animales_getListFiltroNumeroColum FROM @v_consulta;	
    EXECUTE prepConsulta_animales_getListFiltroNumeroColum USING @v_numero;
    DEALLOCATE PREPARE prepConsulta_animales_getListFiltroNumeroColum;


END $$
DELIMITER ;

Ejemplo de uso:

CALL animales_getListFiltroNumeroColum('nombre,anhos',2);

CALL animales_getListFiltroNumeroColum('nombre,tipo,peso',4);

Ejercicio 2

Crea un procedimiento de nombre animales_getListFiltroPista que devuelva la lista de animales que trabajan en las pistas que se envían como parámetro en formato 'pista1,pista2,..'. En caso de que no se envíe dato (valor null) debe mostrar todos los animales.

USE CIRCO;
DROP PROCEDURE IF EXISTS animales_getListFiltroPista;
DELIMITER $$
CREATE PROCEDURE animales_getListFiltroPista (p_pistas varchar(100))		
    COMMENT 'la lista de animales que trabajan en las pistas que se envían como parámetro en formato pista1,pista2,... En caso de que no se envíe dato (valor null) debe mostrar todos los animales'
BEGIN

	IF (p_pistas IS NOT NULL) THEN
		SET @v_consulta = CONCAT('SELECT * FROM ANIMALES WHERE nombre_pista IN (',p_pistas,')');
        select @v_consulta;
		-- SET @v_consulta = 'SELECT * FROM ANIMALES WHERE nombre_pista IN (?)'; => No se puede de esta forma, ya que cambiamos la ? por una cadena 'valor1,valor2,....'
		PREPARE prepConsulta_animales_getListFiltroPista FROM @v_consulta;
		EXECUTE prepConsulta_animales_getListFiltroPista;
		DEALLOCATE PREPARE prepConsulta_animales_getListFiltroPista;
	ELSE
		SELECT * FROM ANIMALES ORDER BY nombre;
    END IF;
    
END $$
DELIMITER ;

Ejemplo de uso:

CALL animales_getListFiltroPista('\'LATERAL1\'');

CALL animales_getListFiltroPista('\'LATERAL2\',\'SUPER\'');

Ejercicio 3

Crea un procedimiento de nombre animales_getByAnoPeso al que se le pasen dos parámetros (peso y año) y en función de si llevan valor o no, haz que devuelva los animales que cumplan los criterios de búsqueda.

CREATE PROCEDURE animales_getByAnoPeso(p_ano smallint, p_peso float)
BEGIN
     SET @consulta = 'SELECT * FROM animales WHERE 1=1';

     IF (p_peso is not null) THEN
         SET @consulta = CONCAT(@consulta,' AND peso = ',p_peso);
     END IF;
     IF (p_ano is not null) THEN
         SET @consulta = CONCAT(@consulta,' AND anhos = ',p_ano);
     END IF;

     PREPARE prepareConsulta FROM @consulta;
     EXECUTE prepareConsulta;
     DEALLOCATE PREPARE prepareConsulta;

END

Ejemplo de uso:

CALL animales_getByAnoPeso(2,null);

CALL animales_getByAnoPeso(null,2.3);

CALL animales_getByAnoPeso(4,1);

Enlace a la página principal de la UD7

Enlace a la página principal del curso

-- Ángel D. Fernández González -- (2020).