Mysqli prepared statement column with variable

Question

My code has to update a query with a custom variable as column.

How can I safely bind the column name?

$username = 'MyUsername'; 
$rank = 'Administrator';
$server = 'Node5';

$stmt = $connection->prepare("UPDATE staff_members SET ?=? WHERE Username=? LIMIT 1");
$stmt->bind_param("sss", $server, $rank, $username);
$stmt->execute();

Answer 1

It's impossible to use a parameter for a column or a table name. Instead, they must be explicitly filtered out before use, using a white list approach.

// define a "white list"
$allowed = ['Node5', 'Node4'];

// Check the input variable against it 
if (!in_array($server, $allowed)) {
    throw new Exception("Invalid column name");
}

// now $server could be used in the SQL string
$sqlString = "UPDATE staff_members SET $server=? WHERE Username=?";
$stmt = $connection->prepare($sqlString);
$stmt->bind_param("ss", $rank, $username);
$stmt->execute();