Using PDO prepared statements MySQL error

Question

I'm trying to create an OOP style CRUD class in PHP and using PDO prepared statements to protect against injections. My connection is working and I can perform regular SQL queries from the class but when I try to incorporate PDO's prepare function, I get an error that I either have a MySQL syntax error or PDO prepare is undefined.

The error gets thrown at $p_query = $db->prepare($sql) line. Can anyone spot what I am doing wrong?

<?php
require_once 'dbconfig.php';

class Crud {
    protected $db;

    private static function fetchQuery($sql, $values) {
        echo $sql;
        var_dump($values);
        $db = Db_conn::pdoBuilder();
        $p_query = $db->prepare($sql);
        $p_query->execute($values);
        $results = $p_query->fetch(PDO::FETCH_OBJ);
        return $results;    
    }
    public static function show($tbl, $id) {
        $sql = '"SELECT * FROM (:tbl) WHERE id = (:id)"';
        $values = [':tbl' => $tbl, ':id' => $id];
        $results = self::fetchQuery($sql, $values);
        return $results;

    }

    public static function listAll($tbl) {
        $sql = '"SELECT * FROM (:tbl)"';
        $values = [':tbl' => $tbl];
        $results = self::fetchQuery($sql, $values);
        return $results;
    }
}

did you try removing double quotation marks from the query? What about removing the parenthesis from table name and column filters? — Marco Aurélio Deleu
@MarcoAurélioDeleu sql: "SELECT * FROM (:tbl)" values: array(1) { [":tbl"]=> string(6) "client" } — Tatiana Frank
Table names can't be parametrized. A prepared statement basically is a partial query, where all that is missing is the data that goes in the where clause or VALUES (in case of an insert)\ — Elias Van Ootegem

Elias Van Ootegem Elias Van Ootegem · Accepted Answer · 2015-10-14T16:34:00

First off:

$sql = '"SELECT * FROM ?"';

Why are you quoting your query? It should be:

$sql = 'SELECT * FROM ?';

$values = [':tbl' => $tbl];

Where is the placeholder :tbl in your query? You're using ?, change the $sql string to:

$sql = 'SELECT * FROM :tbl';

Which immediately poses another problem: the table you're using in your prepared statement can't be bound after you create the prepared statement. Impossiburu. Never going to happen. The best you can do is something like:

$sql = sprintf(
    'SELECT * FROM `%s`',
    //remove illegal chars
    str_replace([' ', '\\', '`', '"', "'"], '', trim($tbl))
);

Lastly, applied to this bit:

$sql = '"SELECT * FROM (:tbl) WHERE id = (:id)"';
$values = [':tbl' => $tbl, ':id' => $id];
$results = self::fetchQuery($sql, $values);

This means you'll have to write:

$sql = sprintf(
    'SELECT * FROM `%s` WHERE id = :id',
    $tbl
);
$values = [':id' => $id];
return self::fetchQuery($sql, $values);

But really, you're not using prepared statements as well as perhaps you could. The best thing about prepared statements is that they're reusable. Wrapping PDO to get a cleaner API hasn't been done as far as I know. Most attempts turn out to actually lessen the power of the extension, or build a bloated abstraction layer around it. That needn't be a bad thing, provided you're building a full-blown ORM/DBAL. I've been quite vocal about this stuff here, you might want to read through it

Using PDO prepared statements MySQL error

1 Answers