Stored procedure that only updates chosen column if user is allowed

Question

I need to write a stored procedure that would allow a user to change the values in a table, but only in certain rows. Basically this is for investigators to define the sensor parameters they want and so they cannot change the parameters of other users.

I have two tables: a Parameters table with CultureID, Code (each culture has 3 rows so this distinguishes which row user wants to change), Max_Alert and Min_Alert columns; and a Users table that shows with UserID and CultureID (to see who's responsible for what culture and what rows each person will be able to alter).

This was the code I wrote but I keep getting errors, especially with the column parameter.

CREATE PROCEDURE Alter_Param 
    (IN id INT, 
     IN cat INT, 
     IN column VARCHAR(20), 
     IN value INT)
BEGIN
    SELECT UserID 
    FROM Users 
    WHERE CultureID = id 
    INTO @v1;

    IF @v1 = USER() 
    THEN
        UPDATE Parameters
        SET Column = value
        WHERE id = 'CultureID' AND 'CODE' = cat ;
    END IF;  
END

Would really appreciate the help :)

Answer 1

SET Column = value - mysql does not do variable substitution for column name you either need to use dynamic sql or test and update for every column to be changed

WHERE id = 'CultureID' - This should probably be the column name - remove quotes.

Answer 2

Thanks for the tip. I followed what you said and instead opted to make that part of the procedure into a variable in order to go around the error. This was the final version which works great!

BEGIN

SELECT IdUser 
FROM users 
WHERE IdCulture = id 
INTO @v1;

IF @v1 = SESSION_USER() 
THEN
    SET @alt := CONCAT('UPDATE test.parameters SET ', column, ' =', value,' WHERE IdCulture =', id,' AND Code =',cod,';');

    PREPARE stmt FROM @alt;
    EXECUTE stmt;
END IF;

END

Thanks for the help!