0

I created the following reusable code to get the value from a single database field using dynamic input:

function query_column($columnName,$tableName,$whereColumn,$whereValue) {
   global $db;
   $query = $db->prepare("SELECT :columnName FROM " . htmlspecialchars($tableName) . " WHERE :whereColumn = :whereValue LIMIT 1");
   $query->execute(array(':columnName' => $columnName, ':whereColumn' => $whereColumn, ':whereValue' => $whereValue));
   if($query->rowCount() > 0) {
      while($result = $query->fetch(PDO::FETCH_ASSOC)) {
         return $result['$columName'];
      }
   } else {
      return Null;
   }
}

I call it like this:

$this->author = query_column("name","author","authorid",$authorId);

I already figured out that you can't bind the table name to a parameter with PDO, but what else could I be doing wrong? It keeps returning Null even though it should be returning data.

voodoo-burger
  • 2,123
  • 3
  • 22
  • 29
  • You can't bind the column name either. Only scalar values like `:whereValue`. You cannot bind `:whereColumn` or `:columName`. – Michael Berkowski Nov 29 '14 at 03:31
  • An aside - `htmlspecialchars()` isn't helpful on the table name. Instead of trying to escape it, you should check the value of `$tableName` against an array of acceptable values. Same goes for the column names, but that becomes _a lot_ more complicated if you are trying to make this a universal function. You would either need to keep arrays of all possible tables' columns, or query the `information_schema.COLUMNS` table to get a list. – Michael Berkowski Nov 29 '14 at 03:33
  • Thanks for those insights! I will keep them in mind. – voodoo-burger Nov 29 '14 at 16:55

1 Answers1

1

prepare the string outside the ->prepare() statement

function query_column($columnName,$tableName,$whereColumn,$whereValue) {
    global $db;
    $sql = "SELECT $columnName FROM " . htmlspecialchars($tableName) . " WHERE $whereColumn = :whereValue LIMIT 1";
    $query = $db->prepare($sql);
    $query->execute(array(':whereValue' => $whereValue));
    if($query->rowCount() > 0) {
      while($result = $query->fetch(PDO::FETCH_ASSOC)) {
        return $result['$columName'];
      }
    } else {
      return Null;
      }
}
JayRod
  • 51
  • 1
  • This works, thanks! I also had to remove the single quotes around $columnName on line 8 but aside from that it works perfectly. – voodoo-burger Nov 29 '14 at 16:53