MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized

Question

I'm trying to write a MySQL search function that builds a dynamic sql value and executes it via a prepared statement. Obviously I want to pass the user input (the search word) via a parameter for security but I can't work out how to match one parameter to multiple ? marks in the query. Probably best to show what I mean:

CREATE DEFINER=`admin`@`localhost` PROCEDURE `WEBSITE_mainSearch`(
IN searchWordIn VARCHAR(128)
)
BEGIN
DECLARE articlesModule BIT;
SET @query = '';
SET @searchWordIn = searchWordIn;
SELECT articlesModuleEnabled INTO articlesModule FROM sys_options WHERE ID = 1;
SET @query = CONCAT(@query, 'SELECT blockName AS itemName, blockPath AS seoName, blockID AS itemID, MATCH(blockName, blockBody) AGAINST (?) AS relevance, \'block\' AS itemType FROM content_blocks WHERE MATCH(blockName, blockBody) AGAINST (?)') ;
IF articlesModule = 1 THEN
    SET @query = CONCAT(@query, 'UNION SELECT articleName AS itemName, seoName, articleID AS itemID, MATCH(articleName, articleBody) AGAINST (?) AS relevance, \'article\' AS itemType FROM news_articles WHERE MATCH(articleName, articleBody) AGAINST (?)') ;
END IF;
PREPARE stmt FROM @query;


EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;


DEALLOCATE PREPARE stmt;
END

As the number of ?s is going to be dynamically determined based on which modules are enabled, how do I know how many time to send searchWordIn as a parameter in this statement EXECUTE stmt USING searchWordIn;?

Thanks !

Answer 1

The EXECUTE statement must be given a fixed list of arguments, so you'll have to prepare and execute the statement in an IF/THEN/ELSE block.

IF articlesModule = 1 THEN
    SET @query = ... UNION ...
    PREPARE stmt FROM @query;
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;
ELSE
    SET @query = ...; /* no UNION */
    PREPARE stmt FROM @query;
    EXECUTE stmt USING @searchWordIn, @searchWordIn;
END IF;
    

I don't know any way to solve this in the limited scope of MySQL stored procedure language. To me, it's another good reason not to use dynamic SQL in stored procedures.

---

Re your comments:

I can't do the suggestion above - the system I am using has about 7 modules.

I see... you could use a CASE statement instead of an IF/THEN/ELSE, but you actually have 2⁷ = 128 potential different cases for query strings, because I assume any of those 7 modules could either be searched or not.

An alternative that would allow you to use query parameters is to forget about using UNION, and instead write the procedure in such a way that runs up to 7 separate SELECT queries and returns all of them as multiple result sets. That's something that stored procedures are intended to do. But you have to write code in your PHP layer to fetch each result set in turn. That is, loop over the result sets, and within that loop, loop over the rows of the current result set. See example at PDO::nextRowset() or mysqli::next_result().

I supposed I'm safe simply CONCATenating the search word in to the dynamic SQL

No, you're not safe if you do that! Using a query parameter in PHP to pass a string to the CALL WEBSITE_mainSearch(?) is useless for protecting against SQL injection, if you then concatenate that parameter value into another string inside the procedure and do a dynamic SQL parse-and-execute. Using query parameters does not make parameter values "safe," they just separate those values from the SQL parse phase.

You're safer if you use MySQL's built-in function QUOTE() when concatenating the strings. QUOTE() does escaping of special characters, just like mysql_real_escape_string(). Except it's slightly different, because it also produces the single-quotes delimiting the string, like PDO::quote() does.

SET @query = CONCAT(@query, 'SELECT blockName AS itemName, blockPath AS seoName, 
  blockID AS itemID, MATCH(blockName, blockBody) AGAINST (',
  QUOTE(searchWordIn), ') AS relevance, \'block\' AS itemType 
  FROM content_blocks WHERE MATCH(blockName, blockBody) AGAINST (',
  QUOTE(searchWordIn),')') ;

---

Update: one more alternative: use UNION to add more subqueries, and keep count of the modules. Then use a CASE to execute the prepared query with a different number of parameters based on the accumulated count.

SET @n = 0;
IF articlesModule = 1 THEN
    SET @query = ... UNION ...
    SET @n = @n+1;
END IF;

IF newsModule = 1 THEN
    SET @query = ... UNION ...
    SET @n = @n+1;
END IF;

... and similar for the other 5 modules ...

PREPARE stmt FROM @query;

CASE @n
WHEN 1:
    EXECUTE stmt USING @searchWordIn, @searchWordIn;
WHEN 2:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;
WHEN 3:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn;
WHEN 4:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;
WHEN 5:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn;
WHEN 6:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;
WHEN 7:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn, 
      @searchWordIn, @searchWordIn;
END;