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.

PDOStatement has a public property $queryString. It should be what you want.

I've just notice that PDOStatement has an undocumented method debugDumpParams() which you may also want to look at.

You can use sprintf(str_replace('?', '"%s"', $sql), ...$params);

Here is an example:

function mysqli_prepared_query($link, $sql, $types='', $params=array()) {
    echo sprintf(str_replace('?', '"%s"', $sql), ...$params);
    //prepare, bind, execute
}

$link = new mysqli($server, $dbusername, $dbpassword, $database);
$sql = "SELECT firstname, lastname FROM users WHERE userage >= ? AND favecolor = ?";
$types = "is"; //integer and string
$params = array(20, "Brown");

if(!$qry = mysqli_prepared_query($link, $sql, $types, $params)){
    echo "Failed";
} else {
    echo "Success";
}

Note this only works for PHP >= 5.6

None of the existing answers seemed complete or safe, so I came up with this function, which has the following improvements:

• works with both unnamed (?) and named (:foo) parameters.

• using PDO::quote() to properly escape values which are not NULL, int, float or bool.

• properly handles string values containing "?" and ":foo" without mistaking them for placeholders.

    function interpolateSQL(PDO $pdo, string $query, array $params) : string {
        $s = chr(2); // Escape sequence for start of placeholder
        $e = chr(3); // Escape sequence for end of placeholder
        $keys = [];
        $values = [];

        // Make sure we use escape sequences that are not present in any value
        // to escape the placeholders.
        foreach ($params as $key => $value) {
            while( mb_stripos($value, $s) !== false ) $s .= $s;
            while( mb_stripos($value, $e) !== false ) $e .= $e;
        }
        
        
        foreach ($params as $key => $value) {
            // Build a regular expression for each parameter
            $keys[] = is_string($key) ? "/$s:$key$e/" : "/$s\?$e/";

            // Treat each value depending on what type it is. 
            // While PDO::quote() has a second parameter for type hinting, 
            // it doesn't seem reliable (at least for the SQLite driver).
            if( is_null($value) ){
                $values[$key] = 'NULL';
            }
            elseif( is_int($value) || is_float($value) ){
                $values[$key] = $value;
            }
            elseif( is_bool($value) ){
                $values[$key] = $value ? 'true' : 'false';
            }
            else{
                $value = str_replace('\\', '\\\\', $value);
                $values[$key] = $pdo->quote($value);
            }
        }

        // Surround placehodlers with escape sequence, so we don't accidentally match
        // "?" or ":foo" inside any of the values.
        $query = preg_replace(['/\?/', '/(:[a-zA-Z0-9_]+)/'], ["$s?$e", "$s$1$e"], $query);

        // Replace placeholders with actual values
        $query = preg_replace($keys, $values, $query, 1, $count);

        // Verify that we replaced exactly as many placeholders as there are keys and values
        if( $count !== count($keys) || $count !== count($values) ){
            throw new \Exception('Number of replacements not same as number of keys and/or values');
        }

        return $query;
    }

I'm sure it can be improved further.

In my case, I eventually ended up just logging the actual "unprepared query" (i.e. SQL containing placeholders) along with JSON-encoded parameters. However, this code might come in use for some use cases where you really need to interpolate the final SQL query.

The $queryString property mentioned will probably only return the query passed in, without the parameters replaced with their values. In .Net, I have the catch part of my query executer do a simple search replace on the parameters with their values which was supplied so that the error log can show actual values that were being used for the query. You should be able to enumerate the parameters in PHP, and replace the parameters with their assigned value.

I know this question is a bit old, but, I'm using this code since lot time ago (I've used response from @chris-go), and now, these code are obsolete with PHP 7.2

I'll post an updated version of these code (Credit for the main code are from @bigwebguy, @mike and @chris-go, all of them answers of this question):

/**
 * Replaces any parameter placeholders in a query with the value of that
 * parameter. Useful for debugging. Assumes anonymous parameters from 
 * $params are are in the same order as specified in $query
 *
 * @param string $query The sql query with parameter placeholders
 * @param array $params The array of substitution parameters
 * @return string The interpolated query
 */
public function interpolateQuery($query, $params) {
    $keys = array();
    $values = $params;

    # build a regular expression for each parameter
    foreach ($params as $key => $value) {
        if (is_string($key)) {
            $keys[] = '/:'.$key.'/';
        } else {
            $keys[] = '/[?]/';
        }

        if (is_array($value))
            $values[$key] = implode(',', $value);

        if (is_null($value))
            $values[$key] = 'NULL';
    }
    // Walk the array to see if we can add single-quotes to strings
    array_walk($values, function(&$v, $k) { if (!is_numeric($v) && $v != "NULL") $v = "\'" . $v . "\'"; });

    $query = preg_replace($keys, $values, $query, 1, $count);

    return $query;
}

Note the change on the code are on array_walk() function, replacing create_function by an anonymous function. This make these good piece of code functional and compatible with PHP 7.2 (and hope future versions too).