How can I use a list of variables in the “EXECUTE STATEMENT USING” statement MySQL?

I have the below procedure –

DELIMITER $$
DROP PROCEDURE IF EXISTS testInjection $$
CREATE PROCEDURE testInjection(IN groupList LONGTEXT)
BEGIN
SET @groupList = groupList;
SET @QUERY = "SELECT * FROM ben where groupid IN ? ";
PREPARE stmt FROM @QUERY;
EXECUTE stmt USING @groupList;
DEALLOCATE PREPARE stmt;
END $$

‘groupList‘ is a list of long variables. When calling the above procedure, I get the MySQL syntax error near ‘where groupid in ‘?’ ‘.

Looking on the Internet, I found that ? is a placeholder just for single variable and so we cannot use it for the list. Even if I create multiple ? variable like “?,?,?” and append it to the mysql query. How am I supposed to use the @groupList variable in “EXECUTE STATEMENT USING” statement?

I am calling the procedure as below –

CALL testInjection('(6598924, 6598928)')

Answer

When parsing a comma separated values as a input to aIN it will be considered as a string, so we need to use find_in_set.

Try the below query

DELIMITER $$
CREATE PROCEDURE testInjection(IN groupList LONGTEXT)
BEGIN
SET @groupList = groupList;
SET @QUERY = 'select * from ben where find_in_set (groupid,?)';
PREPARE stmt FROM @QUERY;
EXECUTE stmt USING @groupList; 
DEALLOCATE PREPARE stmt;  
END $$

To Call:
call testInjection('6598924, 6598928');