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
Advertisement
Answer
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.