Eduardo Jiménez's questions

Tenho um usuário que pode ser acessado de qualquer host (estamos atualmente em desenvolvimento). Mas quero limitar a execução de procedimentos armazenados apenas para localhost. O problema é que o localhostusuário não é reconhecido.

DROP USER IF EXISTS 'admin'@'%';
CREATE USER IF NOT EXISTS 'admin'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE TO 'admin'@'%' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'%' WITH GRANT OPTION;
.
.
.

Executar esse código me gera o próximo erro.

[2024-11-23 20:59:09] [42000][1410] You are not allowed to create a user with GRANT

Alterar o host "resolve" o erro, mas quero limitar a execução ao localhost. Não sei se preciso apenas adicionar um novo usuário assim.

CREATE USER IF NOT EXISTS 'admin'@'127.0.0.1' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE1 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
.
.
.

Ou se houver outra maneira sem repetir a definição do usuário

Estou tentando fazer um procedimento armazenado que me retorne os registros de uma tabela que corresponda aos filtros que podem ser aplicados em camadas.

O procedimento recebe certas variáveis ​​como parâmetros e eu quero construir um PrepareStatement que adiciona as variáveis ​​não nulas como filtros. Estou usando o MariaDB 10.6.2

A tabela em que estou trabalhando (removendo as chaves estrangeiras) se parece com:

CREATE OR REPLACE TABLE Thesis_Detail(
    thesis_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    year SMALLINT NOT NULL,
    file VARCHAR(255) NOT NULL UNIQUE,
    abstract TEXT NOT NULL,
    uploaded_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX(year),
    FULLTEXT(title)
) DEFAULT CHARACTER SET utf8mb4;

O objetivo em si é criá-lo dessa maneira

DELIMITER //
CREATE OR REPLACE PROCEDURE UThesis.searchThesisByFilters(
    IN year_in SMALLINT,
    IN title_in VARCHAR(255),
    IN limit_in TINYINT,
    IN offset_in TINYINT
)
BEGIN
    DECLARE first BIT DEFAULT 0;

    SET @sql = 'SELECT TD.title AS title,' ||
               'TD.year AS year,' ||
               'TD.file AS path,' ||
               'TD.abstract AS abstract,' ||
               'TD.thesis_id AS thesis_id ' ||
               'FROM Thesis_Detail TD ';

    IF NOT ISNULL(title_in) THEN
        SET first = 1;
        SET @sql = @sql + ' WHERE MATCH(title) AGAINST(? IN NATURAL LANGUAGE MODE)';
    END IF;

    IF NOT ISNULL(year_in) THEN
        IF first THEN
            SET @sql = @sql + ' WHERE';
        ELSE
            SET @sql = @sql + ' AND';
        END IF;
        SET @sql = @sql + ' TD.year = ?';
    END IF;

    SET @sql = @sql + ' LIMIT ?  OFFSET  ?';

    PREPARE stmt FROM @sql;
    EXECUTE stmt using title_in, year_in, limit_in, offset_in;
    DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

O problema é que a linha a seguir seria dinâmica, ou seja, pode ou não ter o title_inou year_in

EXECUTE stmt using title_in, year_in, limit_in, offset_in;
EXECUTE stmt using year_in, limit_in, offset_in;
EXECUTE stmt using title_in, limit_in, offset_in;
EXECUTE stmt using limit_in, offset_in;

Este exemplo pode ser resolvido com combinações de se um ou dois são nulos, mas o problema é que tenho que aplicar mais filtros. No total são 5 filtros mas fazer o caso de cada combinação acaba sendo terrível. Alguma idéia de como posso conseguir isso?

No primeiro link eles fazem uso do CONCAT, mas não sei se isso torna o procedimento vulnerável a injeções de SQL.

CREATE OR REPLACE PROCEDURE UThesis.searchThesisByFilters(
    IN year_in SMALLINT,
    IN title_in VARCHAR(255),
    IN limit_in TINYINT,
    IN offset_in TINYINT
)
BEGIN
    DECLARE first BIT DEFAULT 0;

    SET @sql = 'SELECT TD.title AS title,' ||
               'TD.year AS year,' ||
               'TD.file AS path,' ||
               'TD.abstract AS abstract,' ||
               'TD.thesis_id AS thesis_id ' ||
               'FROM Thesis_Detail TD ';

    IF NOT ISNULL(title_in) THEN
        SET first = 1;
        SET @sql = @sql + ' WHERE MATCH(title) AGAINST(? IN NATURAL LANGUAGE MODE)';
    END IF;

    IF NOT ISNULL(title_in) THEN
        IF first THEN
            SET @sql = @sql + ' WHERE';
        ELSE
            SET @sql = @sql + ' AND';
        END IF;
        SET @sql = @sql + CONCAT(' TD.year = ', year_in);
    END IF;

    SET @sql = @sql + CONCAT(' LIMIT', limit_in, ' OFFSET ', offset_in);

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

END //