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_in
ou 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 //
O único risco de segurança é title_in, pois todos os outros são verificados e dão um erro se não forem um número.
Portanto, você não pode contaminar o title_in, em vez disso, você o torna um statennet preparado.
Basicamente, você pode preparar title_in e year_in, se desejar, mas como eu disse, os ints não são um problema de segurança
Então seu código ficaria assim