Prepared statements
Contenido del curso
Carlos S. Aldazosa
Andrés Felipe Galvis Galviz
Daniel G. Parrilla
América Madahi Guzman Nava
Luis Miguel Rodríguez
Christian Yael Laurean Martinez
Alejandro Villamizar
Danny Zavid Herrera Hitas
miguel perez
Leonel Huerta
Kebyn Enrique Ajin Elías
Javier Guevara
Supervisa S.A.S
Aldo Mauricio Vital Gutierrez
Daniela Stornelli
Carlos S. Aldazosa
Donovan Villanueva
Klisman Steven Zapata González
Steven Mack Perez Espino
José Miguel Véliz
Pedro Rozo
Aldo Mauricio Vital Gutierrez
Julian David Alzate Cuervo
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.
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!
Dos formas de hacerlo usando Store Procedure
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 ;
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 ;
🚇 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 👇
que significa el arroba colocado?
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;
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 ;
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.
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 ;
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 💪.
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?
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.
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.
Muy buena esta clase!!
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 ;
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 :)
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.
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:
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.Les agradecería mucho que me diesen retroalimentación si esto funciona así .
Esta forma de obtener consultas me parece genial, una alternativa a estar copiando y pegando consultas muy grandes