How to select from a dynamic column through a variable with PDO?

Question

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;
    }
}

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.

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

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.

(Sorry, but I couldn't get the code block right marked up with the backticks.) — BasC, Feb 02 '15 at 17:22
You can't do this through parameter binding - see [this answer](http://stackoverflow.com/a/15990488/1233508) for reasons. You will need to sanitize that column name and add it to the query manually. — DCoder, Feb 02 '15 at 17:58
Not possible. placeholders can only represent VALUES in a query, never a column or field name. You'll have to use good-old [sql-injection-vulnerable](http://bobby-tables.com) `SELECT $field FROM $table`-type string construction methods — Marc B, Feb 02 '15 at 17:59

score 2 · Accepted Answer · answered Feb 02 '15 at 22:39

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;
    }
}

Miguel · Answer 2 · 2017-11-03T13:49:57.583

1

I would use array_intersect something like a sanitizing function that would extract only the allowed fields. Example:

function get_fields_allowed($input_fields,$allowed){
  $input_fields=explode(",",$input_fields);
  $allowed=explode(",",$allowed);
  return array_intersect($allowed,$input_fields);
}


$select_fields=$_POST["fields"];  //example: "id,name,email"

$fields=get_fields_allowed ($select_fields, "id,name" ) ;

So you then use the $fields as in:

$sql="Select $fields FROM [table] WHERE id=:id etc...";

edited Nov 03 '17 at 13:49

answered Sep 08 '17 at 12:42

Miguel

3,349
2
32
28

That is an easy one, but that does not use PDO. – BasC Nov 01 '17 at 01:00
@BasC yes you can use PDO. This only refers to the $fields because you are only allowing the "cols" that you list there, so its safe. The other part of the string (e.g Where id=:id) which can be from user input you can still compose your self, using whatever you like. – Miguel Nov 02 '17 at 12:14
@BasC there is NO solution that could use PDO. Go figure. – Your Common Sense Nov 02 '17 at 13:16

How to select from a dynamic column through a variable with PDO?

2 Answers2

Linked