如何使用PDO通过变量从动态列中进行选择? [英] How to select from a dynamic column through a variable with PDO?

问题描述

我有一个带有列名的PHP变量$col.我想用PDO创建一个查询，该查询选择该列的值.我知道如何使用bindValue()，并尝试了以下方法:

I have a PHP variable $col with a column name. I want to create a query with PDO, that selects the value of that column. I know how to use bindValue(), and tried the following:

$db = new PDO('mysql:host='. $db_host . ';dbname=' . $db_name . ';charset=utf8', $db_user, $db_password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

function get_user($id, $column){

    $sql = "
        SELECT :col
        FROM users
        WHERE `id` = :id;";

    try {
        $st = $db->prepare($sql);
        $st->bindValue('col', $column, PDO::PARAM_STR);
        $st->bindValue(':id', $id, PDO::PARAM_INT);
        $st->execute();
        $result = $st->fetch();
        return $result;
    } catch (PDOException $e) {
        echo "Database query exception: " . $e->getMessage();
        return false;
    }
}

这将导致以下异常:Database query exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column ''name'' in 'field list'表示$col = 'name'.当然，列name确实存在.

That results in the following exception: Database query exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column ''name'' in 'field list' for $col = 'name'. Of course, the column name does exist.

它在WHERE = :value上运行良好，但是我无法在列上使用它.如何实现呢?

It works well on WHERE = :value, but I can not get it working for a column. How to achieve this?

添加:我确实找到了函数bindColumn()，但我认为相反，将列名绑定到PHP变量，而不是将变量绑定到列.

Addition: I did found the function bindColumn(), but I think that does the opposite, binding the column name to a PHP variable instead of binding a variable to the column.

推荐答案

您可以使用允许的列名数组来清理查询.

You can use an array of allowed column names to sanitize the query.

$allowed_columns = array('name', 'type1',etc);//Array of allowed columns to sanatise query 

然后检查列名是否在数组中.

Then check if column name is in array.

if (in_array($column, $allowed_columns)){
      $result= get_user($id, $column);
}
function get_user($id, $column){

    $sql = "
        SELECT $column
        FROM users
        WHERE `id` = :id;";

    try {
        $st = $db->prepare($sql);
        $st->bindValue(':id', $id, PDO::PARAM_INT);
        $st->execute();
        $result = $st->fetch();
        return $result;
    } catch (PDOException $e) {
        echo "Database query exception: " . $e->getMessage();
        return false;
    }
}

这篇关于如何使用PDO通过变量从动态列中进行选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！