Getting a PDO query string with bound parameters without executing it

Question

Is it possible to get a query string from a PDO object with bound parameters without executing it first? I have code similar to the following (where $dbc is the PDO object):

$query = 'SELECT * FROM users WHERE username = ?';
$result = $dbc->prepare($query);
$username = 'bob';
$result->bindParam(1, $username);
echo $result->queryString;

Currently, this will echo out a SQL statement like: "SELECT * FROM users WHERE username = ?". However, I would like to have the bound parameter included so that it looks like: 'SELECT * FROM users WHERE username = 'bob'". Is there a way to do that without executing it or replacing the question marks with the parameters through something like preg_replace?

Answer 1

In short: no. See Getting raw SQL query string from PDO prepared statements

If you want to just emulate it, try:

echo preg_replace('?', $username, $result->queryString);

Answer 2

This is a generic variation of the regexp technique, for a numbered array of parameters.

It was a bit more paranoid than the accepted answer because quoting everything, numbers included, has bitten me in the backside more than once; in MySQL as well as elsewhere¹, '123' is less than '13'. Same goes for 'NULL', which is not NULL, and 'false', which is obviously true.

It has now been pointed out to me that I was not paranoid enough :-), and my ? replacement technique ("#\\?#") was naïve, because the source query might contain question marks as text in the body for whatever reason:

$query = "SELECT CONCAT('Is ', @value, ' ', ?, '? ', 
       IF(@check != ? AND 123 > '13', 'Yes!', 'Uh, no?')) 
       ;

$values = array('correct', false, 123);

// Expecting valid SQL, selecting 'correct' if check is not false for 123
// and answering Yes if @check is true.

Output:

SELECT CONCAT('Is ', @value, ' ', 'correct', '? ',
   IF(check != false AND 123 > '13', 'Yes!', 'Uh, no?')) 
   ;

Is THIS_TEST correct? Yes!

My simpler implementation would have thrown an exception seeing too many question marks. An even simpler implementation would have returned something like

Is THIS_TEST correcttrue Uh, no

So this is the amended function. NOTE: I know there are things regexes shouldn't do. I do not claim this function to be working in all instances and for all border cases. I claim it is a reasonable attempt. Feel free to comment or email with non-working test cases.

function boundQuery($db, $query, $values) {
    $ret = preg_replace_callback(
        "#(\\?)(?=(?:[^']|['][^']*')*$)#ms",
        // Notice the &$values - here, we want to modify it.
        function($match) use ($db, &$values) {
            if (empty($values)) {
                throw new PDOException('not enough values for query');
            }
            $value  = array_shift($values);

            // Handle special cases: do not quote numbers, booleans, or NULL.
            if (is_null($value)) return 'NULL';
            if (true === $value) return 'true';
            if (false === $value) return 'false';
            if (is_numeric($value)) return $value;

            // Handle default case with $db charset
            return $db->quote($value);
        },
        $query
    );
    if (!empty($values)) {
        throw new PDOException('not enough placeholders for values');
    }
    return $ret;
}

One could also extend PDOStatement in order to supply a $stmt->boundString($values) method.

---

(1) since this is PHP, have you ever tried $a = 1...1; print $a;?

Answer 3

Hello from the future :)

$sth->debugDumpParams(); 

https://www.php.net/manual/ru/pdostatement.debugdumpparams.php

Just write this line without any wardump and you will be fine.