I want to create a Stored Procedure that takes the IDs as inputs, and depending on the list of IDs will behave accordingly.
DROP PROCEDURE IF EXISTS sp_G;
CREATE PROCEDURE sp_G (
IN IDs VARCHAR(20) -- Example: '1, 2, 3'
SET @Query='SELECT * FROM table_users as t';
SET @Query=CONCAT(@Query, ' WHERE t.ID in(', IDs, ')'); -- SQL Injection problem
PREPARE stmt FROM @Query;
You can use a regular expression match to check that the parameter only contains allowed characters.
IF IDs IS NOT NULL AND IDs RLIKE '^[0-9, ]+$' THEN SET @Query=CONCAT(@Query, ' WHERE t.ID in(', IDs, ')'); END IF
This works for a simple parameter like a list of IDs. It doesn't generalize to more complicated situations, though. The best solution is to design your application so you don't need to substitute user-provided input into the queries in the first place.
For instance, instead of a procedure that takes a comma-separated list of values to match against, the caller could put the information in a temporary table. Then the query would be
SELECT u.* FROM table_users AS u JOIN temp_table AS t ON u.id = t.id