Prepared statements the right way?

Question

I have problem using prepared statements in my project. I've created a class named DB and inside this class I have a function called "where" and in this form it does not work:

public function where($table_name, $key, $value) {
    try {
        $stmt = $this->connection->prepare("SELECT * FROM $table_name WHERE :key = :value ORDER BY id DESC");
        $stmt->execute(array(":key" => $key, ":value" => $value));
        return ($stmt->rowCount() > 0) ? $stmt : false; 
    } catch(Exception $e) {
        return false;
    }
}

but when I change the function to just work with one placeholder it works! Why this happens?

public function where($table_name, $key, $value) {
    try {
        $stmt = $this->connection->prepare("SELECT * FROM $table_name WHERE $key = :value ORDER BY id DESC");
        $stmt->execute(array(":value" => $value));
        return ($stmt->rowCount() > 0) ? $stmt : false; 
    } catch(Exception $e) {
        return false;
    }
}

I don't think you can use placeholders for field names in PDO, only for value parameters. See the accepted answer here: http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter — jeoj, Sep 16 '15 at 12:11

score 1 · Answer 1 · answered Sep 16 '15 at 12:13

1

You cannot have fields in prepared statements. You can however insert it using PDO::quote:

$stmt = $this->connection->prepare("SELECT * FROM $table_name WHERE " . $this->connection->quote($key) . " = :value ORDER BY id DESC");

answered Sep 16 '15 at 12:13

laurent

88,262
77
290
428

I tried your suggestion but it doesn't work. – Arash Naderi Sep 16 '15 at 20:33
Maybe PDO::quote doesn't work well for field names either. As a first step, try without the quote() call, as in `$stmt = $this->connection->prepare("SELECT * FROM $table_name WHERE " . $key . " = :value ORDER BY id DESC");` – laurent Sep 16 '15 at 20:34
Without the quote() method it words but when using quote() method, it doesn't. – Arash Naderi Sep 16 '15 at 21:41

Prepared statements the right way?

1 Answers1