Getting a PDO query string with bound parameters without executing it [duplicate]

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?

Dup of stackoverflow.com/questions/210564/pdo-prepared-statements — Crescent Fresh
– Crescent Fresh, Commented Feb 10, 2009 at 0:47
For PHP >= 5.1, take a look at php.net/manual/en/pdostatement.debugdumpparams.php — Mawg
– Mawg, Commented Jun 3, 2014 at 9:00

2 revs · Accepted Answer · 2017-05-23 12:31:52Z

19

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

edited May 23, 2017 at 12:31

community wiki

2 revs
Crescent Fresh

Sign up to request clarification or add additional context in comments.

2 Comments

VirtuosiMedia Over a year ago

Thanks for the link, I didn't see it in the related questions part.

Paul Over a year ago

The code looks wrong. preg_replace expects a regex pattern, which '?' isn't.

LSerni · Accepted Answer · 2017-09-18 08:14:32Z

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

Virtual Gluck · Accepted Answer · 2022-09-04 20:03:25Z

7

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.

edited Sep 4, 2022 at 20:03

answered Aug 30, 2022 at 13:48

Virtual Gluck

1031 silver badge5 bronze badges

2 Comments

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Lajos Mészáros Over a year ago

One extra bit of info is that in order to get the "Sent SQL" you need to call debugDumpParams after calling execute() on the statement.

Collectives™ on Stack Overflow

Getting a PDO query string with bound parameters without executing it [duplicate]

3 Answers 3

2 Comments

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Linked

Related