MariaDB: Create dynamic PrepareStatement inside a Procedure

I am trying to make a stored procedure which returns me the records of a table that match filters that can be applied in layers.

The procedure receives certain variables as parameters and I want to construct a PrepareStatement that adds the non-null variables as filters. I am using MariaDB 10.6.2

The table I am working on (removing the foreign keys) looks like:

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; 

The goal itself is to create it this way

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 ; 

The problem is that the following line would be dynamic, that is, it may or may not have the title_in or 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; 

This example can be solved with combinations of whether one or two are null, but the problem is that I have to apply more filters. In total there are 5 filters but doing the case of each combination ends up being terrible. Any ideas how I can achieve this?

In the first link they make use of CONCAT, but I don’t know if that makes the procedure vulnerable to SQL injections.

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 //