Prepared statements

Clase 29 de 34 • Curso de Bases de Datos con MySQL y MariaDB

Contenido del curso

Introducción

Diagramas Entidad Relación

Instalando nuestras herramientas

Sentencias básicas

Consultas anidadas

Geolocalización

Stored Procedures

Triggers

Bonus

33
Confesión personal: phpMyAdmin es superior
08:45 min

Próximos pasos

34
Cómo seguir aprendiendo SQL
03:02 min

Tomar examen

Comentarios

Carlos S. Aldazosa

student•

Hice un pequeño tutorial de la clase, si es que estas iniciando con las bases de datos te puede ser muy util, este es el tutorial.

Andrés Felipe Galvis Galviz

student•

Qué gran tutorial amigo, tal cual comentas, al principio la clase puede tornarse un poco compleja pero siguiendo tu tutorial fue muchísimo más claro comprender todo. Muchas gracias y saludos!

Daniel G. Parrilla

student•

Dos formas de hacerlo usando Store Procedure

América Madahi Guzman Nava

student•

Leyendo los comentarios e investigando encontré que concatenar los Prepared Statement puede ser arriesgado pues nuestro ++código se vuelve vulnerable a la inyección de código malicioso++, para ello se recomienda usar marcadores de posición.

Los marcadores de posición son símbolos o variables temporales que representan valores que se asignarán después.

Como en mi caso usé "?" en vez de concatenar 'line_id', y utilicé la declaración USING en la instrucción ++EXECUTE stmt++ para usar 'line_id' como argumento.

<
USE metro_cdmx;

DELIMITER //
CREATE PROCEDURE stations_for_line (
    IN line_name VARCHAR(20)
)
BEGIN
    DECLARE line_id BIGINT(20);

    SELECT `id` 
    INTO line_id
    FROM `lines`
    WHERE `name` = line_name
    COLLATE utf8mb4_unicode_ci;

    SET @sql = "
        SELECT
            `l_s`.`id` AS id_relation,
            `l`.`name` AS line_name,
            `s`.`name` AS stations_name
        FROM `lines_stations` AS l_s
        INNER JOIN `stations` AS s
        ON `s`.`id` = `l_s`.`station_id`
        INNER JOIN `lines` AS l
        ON `l`.`id` = `l_s`.`line_id`
        WHERE `l_s`.`line_id`=?";

    PREPARE stmt FROM @sql;
    EXECUTE stmt USING line_id;
    DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

Luis Miguel Rodriguez

student•

Yo los prepares statements lo conocía de esta forma y es al momento de recibir el line name, lo utlizo directamente en la consulta, estaría bien? de igual forma me devuelve el listado.

DELIMITER //
CREATE PROCEDURE get_line_station(
    IN line_name VARCHAR(15)
)
BEGIN

    
        SELECT
            `lines_stations`.`id` AS relation_id,
            `lines`.`name` AS line_name,
            `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `stations`
        ON `stations`.`id` = `lines_stations`.`station_id`
        INNER JOIN `lines`
        ON `lines`.`id` = `lines_stations`.`line_id`
        WHERE `lines`.`name`=line_name; 


END //

DELIMITER ;

Christian Yael Laurean Martinez

student•

🚇 Stored Procedure para Calcular Distancias entre Estaciones del Metro CDMX 🚇

Basado en los conceptos aprendidos en clase, he realizado una modificación al Stored Procedure. En lugar de buscar manualmente el código de cada localización para calcular la distancia de una estación a otra, he implementado que las estaciones puedan buscarse solo colocando el nombre de la estación. El codigo 👇

Alejandro Villamizar

student•

que significa el arroba colocado?

Danny Zavid Herrera Hitas

student•

Es la sintaxís que utiliza mysql para asginar un valor a una variable

miguel perez

student•

buenas tardes, hice el seguimiento pero al momento me sigue dando el error de que no es compatible el utf8mb4_unicode_ci, segui los pasos del profe al pie de la letra

Leonel Huerta

student•

Para resolverlo especifica el conjunto de caracteres o collation despues de tu END, de la siguiente forma:

CREATE PROCEDURE get_line_stations()

BEGIN

-- Tu lógica aquí

END

CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

De igual forma asegúrate de que tanto la base de datos como las tablas y columnas estén utilizando el conjunto de caracteres utf8mb4. Puedes hacerlo con el siguiente comando SQL:

ALTER DATABASE nombre_de_tu_base_de_datos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE nombre_de_tu_tabla CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Kebyn Enrique Ajin Elías

student•

USE metro_cdmx;

DELIMITER //

-- GET ALL STATIONS BELONGING TO A LINE
CREATE PROCEDURE get_line_stations(
    IN line_name VARCHAR(15)
)
BEGIN
    DECLARE line_id BIGINT(20);

    SELECT id
    INTO line_id
    FROM `lines`
    WHERE name = line_name
    COLLATE utf8mb4_unicode_ci;

    SET @sql = CONCAT("
        SELECT
            `lines_stations`.`id` AS relation_id,
            `lines`.`name` AS line_name,
            `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `lines` ON `lines_stations`.`line_id` = `lines`.`id`
        INNER JOIN `stations` ON `lines_stations`.`station_id` = `stations`.`id`
        WHERE `lines_stations`.`line_id` = ", line_id);

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END //
DELIMITER ;

Javier Guevara

student•

Estos procedimientos almacenados permiten dar versatilidad a nuestras búsquedas y permite también enviar operaciones que son habituales en nuestros proyectos. Esto ahorra tiempo y esfuerzo a la hora de consultar nuestra base de datos.

Supervisa S.A.S

student•

Este es mi código mejorado

DELIMITER //

CREATE PROCEDURE get_line_stations(

IN line_name VARCHAR(50)

)

BEGIN

IF EXISTS (SELECT 1 FROM `lines` WHERE name = line_name COLLATE utf8mb4_unicode_ci) THEN

SELECT

ls.id AS relation_id,

l.name AS line_name,

s.name AS station_name

FROM lines_stations ls

INNER JOIN stations s ON s.id = ls.station_id

INNER JOIN `lines` l ON l.id = ls.line_id

WHERE l.name = line_name COLLATE utf8mb4_unicode_ci;

ELSE

SELECT CONCAT('La línea "', line_name, '" no existe en la base de datos.') AS mensaje;

END IF;

END //

DELIMITER ;

Aldo Mauricio Vital Gutierrez

student•

Solo tengo dos preguntas y la primera tiene relación con la segunda.

¿Porque la prepared statements se tiene que manejar como una cadena de texto?
¿Acaso el sistema no reconoce las consultas almacenadas en una variable y es por eso que se tienen que manejas como cadenas de texto con lectura linea por linea?

Gracias por su ayuda compañeros 💪.

Daniela Stornelli

student•

Pero estas son DOS formas de guardar valores en variables, no? en realidad debemos elegir una sola? habria que borrar o la forma de arriba o la forma de abajo?

Carlos S. Aldazosa

student•

Nop, ambas formas se complementan, la primera forma esta hecha para obtener el id de la linea que pedimos y la segunda forma es para almacenar el query que usamos para la logica de la funcion. La segunda forma se utiliza obligatoriamente en un prepared statement si es que no me equivoco.

Donovan Villanueva

student•

El uso de @sql como convención para nombrar una variable en SQL es una práctica común en varios sistemas de bases de datos como SQL Server, donde el símbolo @ indica que lo que sigue es una variable. Sin embargo, no es un nombre obligatorio; puedes utilizar cualquier nombre que desees, siempre que se sigan las reglas para nombrar variables. Es recomendable mantener convenciones claras para facilitar el entendimiento del código.

Klisman Steven Zapata González

student•

Muy buena esta clase!!

Steven Mack Perez Espino

student•

Asi quedo mi Store Procedure de la clase anterior.

USE metro_cdmx;

DELIMITER //

CREATE PROCEDURE Calculate_distance_between_station(
IN station_one VARCHAR(60),
IN station_two VARCHAR(60),
IN meters BOOLEAN
)
	
Begin
IF meters THEN
		
SELECT ST_DISTANCE_SPHERE(
(SELECT locations.location
FROM locations
INNER JOIN stations
ON stations.station_id = locations.station_id
WHERE stations.`name` = station_one),
					
(SELECT locations.location 
FROM locations
INNER JOIN stations
ON stations.station_id = locations.station_id
WHERE stations.`name` = station_two)	
) AS distancia_km;
			
ELSE
		
SELECT ST_DISTANCE_SPHERE(
(SELECT locations.location
FROM locations
INNER JOIN stations
ON stations.station_id = locations.station_id
WHERE stations.`name` = station_one),
					
(SELECT locations.location 
FROM locations
INNER JOIN stations
ON stations.station_id = locations.station_id
WHERE stations.`name` = station_two)	
)/ 1000 AS distancia_km; 
			
END IF;
	
END //
	
DELIMITER ;

José Miguel Véliz

student•

Como aporte me gustaría decir que yo en lo personal no he estado usando las `` a los lados de los nombres de las tablas al momento de hacer mis consultas, pero cuando intenté hacer la consulta con la tabla lines, no pude, porque al parecer lines es un comando reservado para SQL.

Es decir, este código no funcionaría:

USE metro_cdmx; DELIMITER //CREATE PROCEDURE get_line_stations( IN line_name VARCHAR(15))BEGIN DECLARE line_id BIGINT(20); SELECT id INTO line_id FROM lines WHERE name = line_name COLLATE utf8mb4_unicode_ci; SET @sql = CONCAT(" SELECT lines_stations.id AS relation_id, lines.name AS line_name, stations.name AS station_name FROM lines_stations INNER JOIN stations ON stations.id = lines_stations.station_id INNER JOIN lines ON lines.id = lines_stations.line_id WHERE lines_stations.line_id = ", line_id); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END //DELIMITER ;

Pero este sí:

USE metro_cdmx; DELIMITER //CREATE PROCEDURE get_line_stations( IN line_name VARCHAR(15))BEGIN DECLARE line_id BIGINT(20); SELECT id INTO line_id FROM `lines` WHERE name = line_name COLLATE utf8mb4_unicode_ci; SET @sql = CONCAT(" SELECT lines_stations.id AS relation_id, `lines`.name AS line_name, stations.name AS station_name FROM lines_stations INNER JOIN stations ON stations.id = lines_stations.station_id INNER JOIN `lines` ON `lines`.id = lines_stations.line_id WHERE lines_stations.line_id = ", line_id); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END //DELIMITER ;

La diferencia es que en el primero lines no tiene las , pero en el segundo sí lo declaramos como lines``, espero que esto sirva :)

Pedro Rozo

student•

Me parece recordar que concatenar parámetros de entrada es una mala práctica en los prepared statement, debido a que facilitan el sql injection. Reemplazaría la concatenación con el uso de parámetros.

Aldo Mauricio Vital Gutierrez

student•

Me puse a jugar con la tabla creada en esta clase, quite , puse modifique y aplique lo de la primera consulta en la segunda y viceversa, y note lo siguiente:

Si declaramos un SET @variable en la primera consulta, no reconocerá el campo line_name creado en la cabecera para obtener el id de nuestra consulta a partir del nombre.
Las prepared statements son consultas almacenadas en una variable con estructura de texto. Esto es importante recalcarlo, imaginemos que en nuestro procedimiento necesitamos hace uso de múltiples consultas y que estén relacionadas unas con otras. Bueno pues es aquí donde juegan un papel importante ya que es mas sencillo hacerlo a través de una variable con una consulta almacenada.

Les agradecería mucho que me diesen retroalimentación si esto funciona así .

Julian David Alzate Cuervo

student•

Esta forma de obtener consultas me parece genial, una alternativa a estar copiando y pegando consultas muy grandes

<
USE metro_cdmx;

DELIMITER //
CREATE PROCEDURE stations_for_line (
    IN line_name VARCHAR(20)
)
BEGIN
    DECLARE line_id BIGINT(20);

    SELECT `id` 
    INTO line_id
    FROM `lines`
    WHERE `name` = line_name
    COLLATE utf8mb4_unicode_ci;

    SET @sql = "
        SELECT
            `l_s`.`id` AS id_relation,
            `l`.`name` AS line_name,
            `s`.`name` AS stations_name
        FROM `lines_stations` AS l_s
        INNER JOIN `stations` AS s
        ON `s`.`id` = `l_s`.`station_id`
        INNER JOIN `lines` AS l
        ON `l`.`id` = `l_s`.`line_id`
        WHERE `l_s`.`line_id`=?";

    PREPARE stmt FROM @sql;
    EXECUTE stmt USING line_id;
    DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

DELIMITER //
CREATE PROCEDURE get_line_station(
    IN line_name VARCHAR(15)
)
BEGIN

    
        SELECT
            `lines_stations`.`id` AS relation_id,
            `lines`.`name` AS line_name,
            `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `stations`
        ON `stations`.`id` = `lines_stations`.`station_id`
        INNER JOIN `lines`
        ON `lines`.`id` = `lines_stations`.`line_id`
        WHERE `lines`.`name`=line_name; 


END //

DELIMITER ; 

USE metro_cdmx;

DELIMITER //

-- GET ALL STATIONS BELONGING TO A LINE
CREATE PROCEDURE get_line_stations(
    IN line_name VARCHAR(15)
)
BEGIN
    DECLARE line_id BIGINT(20);

    SELECT id
    INTO line_id
    FROM `lines`
    WHERE name = line_name
    COLLATE utf8mb4_unicode_ci;

    SET @sql = CONCAT("
        SELECT
            `lines_stations`.`id` AS relation_id,
            `lines`.`name` AS line_name,
            `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `lines` ON `lines_stations`.`line_id` = `lines`.`id`
        INNER JOIN `stations` ON `lines_stations`.`station_id` = `stations`.`id`
        WHERE `lines_stations`.`line_id` = ", line_id);

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END //
DELIMITER ;

USE metro_cdmx;

DELIMITER //

CREATE PROCEDURE Calculate_distance_between_station(
IN station_one VARCHAR(60),
IN station_two VARCHAR(60),
IN meters BOOLEAN
)
	
Begin
IF meters THEN
		
SELECT ST_DISTANCE_SPHERE(
(SELECT locations.location
FROM locations
INNER JOIN stations
ON stations.station_id = locations.station_id
WHERE stations.`name` = station_one),
					
(SELECT locations.location 
FROM locations
INNER JOIN stations
ON stations.station_id = locations.station_id
WHERE stations.`name` = station_two)	
) AS distancia_km;
			
ELSE
		
SELECT ST_DISTANCE_SPHERE(
(SELECT locations.location
FROM locations
INNER JOIN stations
ON stations.station_id = locations.station_id
WHERE stations.`name` = station_one),
					
(SELECT locations.location 
FROM locations
INNER JOIN stations
ON stations.station_id = locations.station_id
WHERE stations.`name` = station_two)	
)/ 1000 AS distancia_km; 
			
END IF;
	
END //
	
DELIMITER ;
	
	

Prepared statements

Introducción

¿Cuál es la diferencia entre MySQL y MariaDB?

Proyecto del curso: metro de CDMX

Diagramas Entidad Relación

Diagramas conceptuales vs. diagramas físicos

Diagrama conceptual: estableciendo relaciones

Diagrama físico: detalles en las tablas

Reto: relaciones en el diagrama físico

Instalando nuestras herramientas

Instalación de MariaDB en Windows

Instalación de MariaDB en Linux

Instalación de MariaDB en macOS

Sentencias básicas

Archivos SQL: CREATE DATABASE

Manejo de usuarios para la base de datos

CREATE TABLE

Modificando tablas con ALTER TABLE

Playground: Manejo de Tablas con SQL

Insertando información en la base de datos

Actualizando información: UPDATE

Borrando datos: DELETE

Playground: INSERT, UPDATE y DELETE en SQL

Listando datos: SELECT

Playground: Metro de CDMX en SQL

Consultas anidadas

¿Qué son las consultas anidadas?

Creando nuestra primer consulta anidada

Playground: Metro de Bogotá en SQL

Geolocalización

¿Cómo funciona la geolocalización?

Creando nuestras consultas de geolocalización

Reto: geolocalización con consultas anidadas

Stored Procedures

¿Qué son los Stored Procedures?

Crea tus primeras Stored Procedures