Prepared statements

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.

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 👇

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.

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:

1. 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.
2. 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í .

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