PHP + PDO Is it possible to set a variable column in a query? Those. prepare a query with a column unknown in advance?

Question

PHP

For example, I want to do this

$res = $pdo->prepare("UPDATE table SET :column = :value;");
$res->execute(Array(':column'=>'col1', ':value'=>'val1'));

Is that possible? Or does the column name always need to be known in advance?

Answer 1

In general, for one field, the task looks somewhat artificial, but to update several fields, you need to use the standard whitelist check

// the list of allowed field names
$allowed = ["name","surname","email"];

// initialize an array with values:
$params = [];

// initialize a string with `fieldname` = :placeholder pairs
$setStr = "";

// loop over source data array
foreach ($allowed as $key)
{
    if (isset($_POST[$key]) && $key != "id")
    {
        $setStr .= "`$key` = :$key,";
        $params[$key] = $_POST[$key];
    }
}
$setStr = rtrim($setStr, ",");

$params['id'] = $_POST['id'];
$pdo->prepare("UPDATE users SET $setStr WHERE id = :id")->execute($params);