带有变量的 Mysqli 准备语句列 [英] Mysqli prepared statement column with variable

问题描述

我的代码必须使用自定义变量作为列更新查询.

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

如何安全绑定列名?

$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();

推荐答案

不能对列名或表名使用参数.相反，它们必须在使用前明确过滤掉，使用白名单 方法.

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();

这篇关于带有变量的 Mysqli 准备语句列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！