Where does bindParam store the binding?

Question

When using either bindParam or bindValue in PDO, where exactly is the "binding" stored in the PDOStatement object? Is it possible to display this set property/reference/etc.?

I'm trying to understand where these and other PDOStatement methods store/locate their data within a PDOStatement object (if it's even possible to access it outside of setting it through a PDOStatement method).

(I suppose this question extends to other PDO/PDOStatement methods, as well, curious where things are getting stored, like if into properties or whatnot)

hek2mgl · Answer 1 · 2013-07-18T00:36:38.913

A prepared statement is either a protocol feature (mysql for example) or will run in a C library behind the scenes. Meaning the work of parsing the statement isn't done by PDO code. It is done by the database server or a C library like libsqlite. As PDO doesn't parse the statement by itself and just passes it to the lower level component, param information isn't available to it.

So, using plain PHP it is not possible to get the association placeholder => replaced by value in query using the PDOStatement object. This is by design of a prepared statement.

A workaround might be to extend PDOStatement and add a method that replaces param placeholders by their values in the current query string. I have prepared an example below. Note that this example isn't bullet-proof as it would for example replace :bar even if it occurs in quoted strings. But for internal usage, a solution like this has done a good job for me so far.

Custom Statement Class:

class MyStatement extends PDOStatement
{

    protected $params;
    protected $pdo;

    protected function __construct($pdo) {
        $this->pdo = $pdo;
    }

    public function execute($params = null) {
        $this->params = $params;
        return parent::execute($params);
    }


    public function printQuery(){
        $_params = $this->params;
        $sql = $this->queryString;
        foreach($this->params as $key => $value) {
            $_value = is_null($value) ?
                'NULL' : '\'' . $value . '\'';
            $sql = str_replace(':'. $key, $_value, $sql);
        }
        return $sql;
    }
}

You need a modified PDO:

class MyPDO extends PDO
{

    public function __construct($dsn, $username="", $password="", $driver_options=array()) {
        parent::__construct($dsn, $username, $password, array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_STATEMENT_CLASS => array('MyStatement', array($this))
        ));
    }
}

Test Code:

$pdo = new MyPDO('mysql:host=localhost;dbname=test', 'root', '******');

// preapre stupid query
$stmt = $pdo->prepare('SELECT FROM `foo` WHERE name = :bar');

try {
    // execute stmt
    $stmt->execute(array('bar' => 'hek2mgl'));
} catch (PDOException $e) {
    echo $stmt->printQuery();
}

Yeah, that's not bullet-proof... because the SQL might contain `:$key` for example within quoted strings or comments, where the parameter value shouldn't be substituted. But +1 for the point that bound parameter values are sent via the C library to the server separately from the SQL string. — Bill Karwin, Jul 17 '13 at 21:59
@BillKarwin `because the SQL might contain :$key` Yep, that was what I meant.. ;) I should have mentioned this probably. To make it bullet proof it requires an SQL parser imo. Would be a nice task to craft one written in PHP I'm currently thinking.. Do you see better approaches? e.g. using MySQL for that? (when mysql is the db) — hek2mgl, Jul 17 '13 at 22:06
I implemented the code in Zend Framework, for Zend_Db_Profiler. I added a method to retrieve the SQL, and then a different method to retrieve an array of parameters. I did not attempt to merge them, because I wanted developers to understand that parameters are *not* interpolated into the SQL. — Bill Karwin, Jul 17 '13 at 22:14
The MySQL query log does actually interpolate parameter values into the SQL as it logs the query. See [Export “query” from “mysqli->prepare”](http://stackoverflow.com/questions/11508752/export-query-from-mysqli-prepare/17098261#17098261) — Bill Karwin, Jul 17 '13 at 22:16
@BillKarwin `I implemented the code in Zend Framework, for Zend_Db_Profiler. I added a method to retrieve the SQL...` This sounds like a good compromise that allows debugging and is bullet proof. And it may reflect more the nature of a prepared statement, as it separeates params from query code. however, for internal use the above code may be helpful as well. I had not so much problems with it, but I know it is not "releasable" code. Accessing the query log is a good tip as well but might get messy if mysql is clustered. thx for your comments. I appreciated them! :) — hek2mgl, Jul 17 '13 at 22:25

Where does bindParam store the binding?

1 Answers1