Php PDO, letting users also define the field to select

Question

I am trying to make a function that selects all the data from a table, in which the user defines the field and the value. Like so:

public function fetch($field, $value){

    $query = $this->db->prepare("SELECT * FROM `users` WHERE ? = ?");

    $query->bindValue(1, $field);
    $query->bindValue(2, $value);

    try{

        $query->execute();


    } catch(PDOException $e){

        die($e->getMessage());
    }

    return $query->fetch();

}

I am not getting anything returned, not even an error. Can someone please tell me what I am doing wrong, or if it is even possible in PDO to let the user also choose the field of the table along with the value.

Thank you.

why do you need that? – Your Common Sense May 27 '13 at 09:14 — Your Common Sense, May 27 '13 at 09:14

score 3 · Answer 1 · answered May 27 '13 at 07:16

You cannot use placeholders for identifiers (i.e. field names); only for data. You can only make a whitelist of allowed field names:

$allowed = array('name', 'date', 'price');
if (!in_array($field, $allowed, true)) {
    throw new InvalidArgumentException;
}

$query = $this->db->prepare("SELECT * FROM `users` WHERE $field = ?");

score 1 · Answer 2 · edited May 23 '17 at 12:05

You cannot use the parameters ? in field names.

Table and Column names cannot be replaced by parameters in PDO. In that case you will simply want to filter and sanitize the data manually. Source.

To allow for user field edition directly, you could do:

public function fetch($field, $value){

// To avoid injection
if (!in_array($field, array('these', 'are', 'field', 'names')))
  echo "Sorry, that's not a valid field";
else
  {
  $query = $this->db->prepare("SELECT * FROM `users` WHERE `" . $field . "` = ?");

  $query->bindValue(1, $value);

  try{

    $query->execute();

    } catch(PDOException $e) {

    die($e->getMessage());

    }
  }

return $query->fetch();

}

Furthermore, I have a small function (a method actually) to do this work automatically:

 // Validate the cols names. 
private function setCols($TableName)
  {
  // If this script is still running, $this->Table exists in database and it's sane
  $Cols = array();
  $STH = $this->DB->query('SHOW COLUMNS FROM `' . $this->Table . '`');
  foreach ($STH->fetchAll() as $Name)
    $Cols[] = $Name[0];
  $this->Columns = $Cols;
  }

This will find dynamically the fields for the table.

So it is not possible to let the user also define the field of the table, in anyway? — Sameer Zahid, May 27 '13 at 07:16
Yes, it is, but you should whitelist the field and do some old mysql. I'll edit it to show how. — Francisco Presencia, May 27 '13 at 07:17

Php PDO, letting users also define the field to select

2 Answers2