MariaDB：在過程中創建動態 PrepareStatement

我正在嘗試創建一個儲存過程，它返回與可以在圖層中應用的過濾器匹配的表的記錄。

該過程接收某些變數作為參數，我想構造一個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 //

唯一的安全風險是 title_in，因為所有其他的都被檢查並在它們不是數字時給出錯誤。

所以你不能污染title_in，而是把它變成一個準備好的statennet。

基本上，如果您願意，您可以準備 title_in 和 year_in，但正如我所說，整數不是安全問題

所以你的程式碼看起來像

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 @title := title_in;
       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;
   IF NOT ISNULL(title_in) THEN
   EXECUTE stmt USING @title;
   ELSE
      EXECUTE stmt;
   END IF;
   DEALLOCATE PREPARE stmt;

END //

引用自：https://dba.stackexchange.com/questions/294848