How to SELECT a value into a variable using a Dynamyc SQL statement to do the assignment in MariaDB?-CodePudding

I'm trying to use a Dinamic SQL statement to retrieve a value and assign it to a variable, in a stored procedure. The problem is, when executing the dynamic statement, it can't access the variable declared in the procedure scope.

Here is a fragment of the code (inside a procedure), for clarification:

DECLARE v_amount_of_samples_that_require_revision INTEGER;

SET query = CONCAT('SELECT count(sample_id) INTO v_amount_of_samples_that_require_revision
        FROM ', v_table_name,
        'WHERE state = REQUIRES_REVISION_STATE
        AND form_id = ', p_form_id);                    

PREPARE stmt FROM query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The error given, of course, is:

SQL Error [1327] [42000]: (conn:7575) Undeclared variable: v_amount_of_samples_that_require_revision

Is there a workaround?

CodePudding user response：

instead of regularly variables use user defned session variables

still v_table_name could be used for sql injection and should be tested against a whitelist

When you use variables in the where lause use prepared statements

SET @query = CONCAT('SELECT count(sample_id) INTO @v_amount_of_samples_that_require_revision
        FROM ', v_table_name,
        ' WHERE state = REQUIRES_REVISION_STATE
        AND form_id = ?');                    
SET @p_form_id =  p_form_id;
PREPARE stmt FROM @query;
EXECUTE stmt USING @p_form_id;
DEALLOCATE PREPARE stmt;
SELECT @v_amount_of_samples_that_require_revision;