Retrieve (Or Simulate) Full Query from Pdo Prepared Statement

Retrieve (or simulate) full query from PDO prepared statement

I believe this is mentioned in the original question that was reference in this one. However
there is actually supposed to be a method for retrieving this data.

PDOStatement::debugDumpParams

However it isn't currently working as documented. There is a bug report and patch submitted for it here http://bugs.php.net/bug.php?id=52384 in case anyone is interested in voting on it. Until it's fixed it seems like you are left to use query logging or setting a custom statement class using the PDO::ATTR_STATEMENT_CLASS attribute.

Get query back from PDO prepared statement

Try $statement->queryString.

Getting raw SQL query string from PDO prepared statements

I assume you mean that you want the final SQL query, with parameter values interpolated into it. I understand that this would be useful for debugging, but it is not the way prepared statements work. Parameters are not combined with a prepared statement on the client-side, so PDO should never have access to the query string combined with its parameters.

The SQL statement is sent to the database server when you do prepare(), and the parameters are sent separately when you do execute(). MySQL's general query log does show the final SQL with values interpolated after you execute(). Below is an excerpt from my general query log. I ran the queries from the mysql CLI, not from PDO, but the principle is the same.

081016 16:51:28 2 Query       prepare s1 from 'select * from foo where i = ?'
                2 Prepare     [2] select * from foo where i = ?
081016 16:51:39 2 Query       set @a =1
081016 16:51:47 2 Query       execute s1 using @a
                2 Execute     [2] select * from foo where i = 1

You can also get what you want if you set the PDO attribute PDO::ATTR_EMULATE_PREPARES. In this mode, PDO interpolate parameters into the SQL query and sends the whole query when you execute(). This is not a true prepared query. You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute().

---

Re comment from @afilina:

No, the textual SQL query is not combined with the parameters during execution. So there's nothing for PDO to show you.

Internally, if you use PDO::ATTR_EMULATE_PREPARES, PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute. But PDO does not expose this modified SQL query.

The PDOStatement object has a property $queryString, but this is set only in the constructor for the PDOStatement, and it's not updated when the query is rewritten with parameters.

It would be a reasonable feature request for PDO to ask them to expose the rewritten query. But even that wouldn't give you the "complete" query unless you use PDO::ATTR_EMULATE_PREPARES.

This is why I show the workaround above of using the MySQL server's general query log, because in this case even a prepared query with parameter placeholders is rewritten on the server, with parameter values backfilled into the query string. But this is only done during logging, not during query execution.

In PHP with PDO, how to check the final SQL parametrized query?

So I think I'll finally answer my own question in order to have a full solution for the record. But have to thank Ben James and Kailash Badu which provided the clues for this.

Short Answer

As mentioned by Ben James: NO.

The full SQL query does not exist on the PHP side, because the query-with-tokens and the parameters are sent separately to the database.
Only on the database side the full query exists.

Even trying to create a function to replace tokens on the PHP side would not guarantee the replacement process is the same as the SQL one (tricky stuff like token-type, bindValue vs bindParam, ...)

Workaround

This is where I elaborate on Kailash Badu's answer.
By logging all SQL queries, we can see what is really run on the server.
With mySQL, this can be done by updating the my.cnf (or my.ini in my case with Wamp server), and adding a line like:

log=[REPLACE_BY_PATH]/[REPLACE_BY_FILE_NAME]

Just do not run this in production!!!

PDO Debugging - View Query AFTER Bind?

That's the single most common myth about SQL debugging. "I need to see the query after preparation to be able to tell if an error occurred". The fact is, you don't, and I'll tell you why.

Once a query has been prepared, the placeholder can be considered as a valid string/integer. You don't care what's in it.

Also, if you set up PDO correctly, you'll get a detailed PDOException detailing the error you've had along with a complete backtrace of where the error happened, plus you get the error string from MySQL, which makes syntax errors very easy to find.

To enable PDO Exceptions and disable emulated prepares:

$pdo = new PDO("mysql:host=localhost;dbname=database_name", "user", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

How to create a pdo mysql prepared statement with a placeholder in a custom equation?

All values convert to string values in execute method. You must use bindParam method to set a value type, for example:

$sth->bindParam(1, $_POST['rating'], PDO::PARAM_INT);

PDO prepared statement trouble

You don't need to pass that as a parameter, just do:

$sql = "UPDATE `table` SET quantity=quantity+1 WHERE id=:userid";
$sql_prep = $db->prepare($sql);
$sql_prep->bindParam(":userid", $id);
$sql_prep->execute();

Simulate a PDO fetch failure situation

Finally, I found a case, which allowed me to test, if PDOStatement::fetch would indeed throw an exception on failure.

Credits:

The article Taking advantage of PDO’s fetch modes presents such a situation. It is based on the use of PDOStatement::fetchAll with PDO::FETCH_KEY_PAIR constant passed as argument.

Test:

So, I ran a test myself. But I used the PDOStatement::fetch method instead. Per definition, the PDO::FETCH_KEY_PAIR constant requires that the data source table contains only two columns. In my test I defined three table columns. PDOStatement::fetch has recognized this situation as a failure and had thrown an exception:

SQLSTATE[HY000]: General error: PDO::FETCH_KEY_PAIR fetch mode
requires the result set to contain extactly 2 columns.

Conclusion:

• PDOStatement::fetch returns FALSE, if no records are found.
• PDOStatement::fetch throws - indeed - an exception in case of failure.

Notes:

• Instead, PDOStatement::fetchAll returns an empty array, if no records are found.
• The PDO::FETCH_KEY_PAIR constant is not documented on the PDOStatement::fetch official page.

P.S:

I want to thank to all the users who tried to help me finding the answer to my question. You have my appreciation!

---

The code used for testing:

<?php

// Activate error reporting.
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {

    // Create a PDO instance as db connection to a MySQL db.
    $connection = new PDO(
            'mysql:host=localhost;port=3306;dbname=tests;charset=utf8'
            , 'root'
            , 'root'
            , array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => FALSE,
        PDO::ATTR_PERSISTENT => TRUE
            )
    );

    // Define the sql statement.
    $sql = 'SELECT * FROM users WHERE name = :name';

    /*
     * Prepare the sql statement.
     * 
     * --------------------------------------------------------------------------------
     * If the database server cannot successfully prepare the statement, PDO::prepare() 
     * returns FALSE or emits PDOException (depending on error handling settings).
     * --------------------------------------------------------------------------------
     */
    $statement = $connection->prepare($sql);

    // Validate the preparation of the sql statement.
    if (!$statement) {
        throw new UnexpectedValueException('The sql statement could not be prepared!');
    }

    // Bind the input parameter to the prepared statement.
    $bound = $statement->bindValue(':name', 'Sarah', PDO::PARAM_STR);

    // Validate the binding of the input parameter.
    if (!$bound) {
        throw new UnexpectedValueException('An input parameter can not be bound!');
    }

    /*
     * Execute the prepared statement.
     * 
     * ------------------------------------------------------------------
     * PDOStatement::execute returns TRUE on success or FALSE on failure.
     * ------------------------------------------------------------------
     */
    $executed = $statement->execute();

    // Validate the execution of the prepared statement.
    if (!$executed) {
        throw new UnexpectedValueException('The prepared statement can not be executed!');
    }

    // Fetch the result set.
    $resultset = $statement->fetch(PDO::FETCH_KEY_PAIR);

    // If no records found, define the result set as an empty array.
    if ($resultset === FALSE) {
        $resultset = [];
    }

    // Display the result set.
    var_dump($resultset);

    // Close connection.
    $connection = NULL;
} catch (PDOException $exc) {
    echo '<pre>' . print_r($exc->getMessage(), TRUE) . '</pre>';
    exit();
} catch (Exception $exc) {
    echo '<pre>' . print_r($exc->getMessage(), TRUE) . '</pre>';
    exit();
}

Create table syntax:

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Insert values syntax:

INSERT INTO `users` (`id`, `name`, `phone`)
VALUES
    (1,'Sarah','12345'),
    (2,'John','67890');

Table values:

id  name    phone
-----------------
1   Sarah   12345
2   John    67890

---

Related Topics