Eduardo Jiménez's questions

我有一个可以从任何主机访问的用户（我们目前正在开发中）。但我想限制存储过程的执行仅限于本地主机。问题是localhost无法识别该用户。

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;
.
.
.

执行此代码会引发下一个错误。

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

更改主机“解决”了错误，但我想将执行限制在本地主机。我不知道是否只需像这样添加新用户。

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;
.
.
.

或者是否有另一种方法，无需重复用户定义

我正在尝试创建一个存储过程，它返回与可以在图层中应用的过滤器匹配的表的记录。

该过程接收某些变量作为参数，我想构造一个PrepareStatement，将非空变量添加为过滤器。我正在使用MariaDB 10.6.2

我正在处理的表（删除外键）如下所示：

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;

目标本身就是以这种方式创建它

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 ;

问题是以下行将是动态的，也就是说，它可能有也可能没有title_inor 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;

这个例子可以通过一个或两个是否为空的组合来解决，但问题是我必须应用更多的过滤器。总共有5 个过滤器，但每个组合的情况最终都很糟糕。有什么想法可以实现这一目标吗？

在第一个链接中，他们使用了 CONCAT，但我不知道这是否会使该过程容易受到 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 //