Can I call a stored procedure on each iteration of an array? PHP

Question

I want to loop through an associative array that represents a table's columns and values, and call a stored procedure on each iteration that will insert each value into its respective column. The assoc. array and loop looks like this:

    public static function update(
        int $auctionId,
        string $title,
        string $description
        ) : void
    {
        $new = [
            'auction_title' => $title,
            'description' => $description
        ];

        foreach ($new as $columnName => $value) {
            Database::conn()->callSP('sp_auctions_update', [$auctionId, $columnName, $value]);
        }
    }

The stored procedure looks like this:

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_auctions_update $$
CREATE PROCEDURE sp_auctions_update(
    IN auctionId INT UNSIGNED,
    IN columnName,
    IN value,
    )

    SQL SECURITY INVOKER
    MODIFIES SQL DATA

BEGIN

    UPDATE auctions SET @columnName=@value, WHERE id=@auctionId;

END $$

DELIMITER ;

Is this possible to do? Or is there a better alternative? Many thanks

Answer 1

do you know what the prepared statement would look like in this instance?

CREATE PROCEDURE sp_auctions_update(
    IN auctionId INT UNSIGNED,
    IN columnName VARCHAR(64),
    IN `value` INT UNSIGNED
    )

    SQL SECURITY INVOKER
    MODIFIES SQL DATA

BEGIN

-- Build SQL query text, insert column name from variable into it
    SET @sql := CONCAT('UPDATE auctions SET ', columnName, '=? WHERE id=?;');
-- Reassign parameters values from local variables to user-defined ones
-- because local variables cannot be used in USING
    SET @value := `value`;
    SET @auctionId := auctionId;
-- Prepare and execute the query
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @value, @auctionId;
    DEALLOCATE PREPARE stmt;

END

Alternatively you may concat ALL parameters values into the query text:

CREATE PROCEDURE sp_auctions_update(
    IN auctionId INT UNSIGNED,
    IN columnName VARCHAR(64),
    IN `value` INT UNSIGNED
    )

    SQL SECURITY INVOKER
    MODIFIES SQL DATA

BEGIN

-- Build SQL query text, insert parameters from variables into it
    SET @sql := CONCAT('UPDATE auctions SET ', columnName, '=\'', `value`, '\' WHERE id=', auctionId, ';');
-- Prepare and execute the query
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END

---

Caution.

The columnName parameter value is inserted into SQL query text as-is - so injection is possible. You must add the code which controls this parameter value. For example you may check that the column with provided name exists in table structure.

Another 2 parameters cannot be a source of injection due to their numeric datatype.