CODEWITHSUNDEEP
phpmysqlpdo
Tatiana Frank
Tatiana Frank

Reputation: 418

Using PDO prepared statements MySQL error

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

Upvotes: 0

Views: 94

Answers (1)

Elias Van Ootegem
Elias Van Ootegem

Reputation: 76408

First off:

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

Why are you quoting your query? It should be:

$sql = 'SELECT * FROM ?';

Next:

$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

Upvotes: 1

Related Questions