Mariadb
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_in
oryear_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 //