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

PHP

PDO

SQL

parametrized query

debugging

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

Master System Design with Codemia

Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.

Start Practicing Learn More

Introduction

PDO (PHP Data Objects) sends parameterized queries to the database in two separate steps — the SQL template and the bound parameters — so there is no single "final query" string in PHP's memory. The database server performs the substitution internally. To see the interpolated query for debugging, you can use PDOStatement::debugDumpParams(), enable the database's query log, or build a manual interpolation function that reconstructs the query from the template and bound values.

Why There Is No Final Query

php

1$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND status = :status");
2$stmt->execute([':email' => '[email protected]', ':status' => 'active']);
3
4// PDO sends TWO things to the database:
5// 1. The SQL template: SELECT * FROM users WHERE email = ? AND status = ?
6// 2. The parameter values: ['[email protected]', 'active']
7// The database combines them internally — PHP never sees the final query

This separation is what makes prepared statements safe against SQL injection. The parameters are never concatenated into the SQL string — the database handles them as data, not code.

Method 1: debugDumpParams()

php

1$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id AND role = :role");
2$stmt->bindValue(':id', 42, PDO::PARAM_INT);
3$stmt->bindValue(':role', 'admin', PDO::PARAM_STR);
4$stmt->execute();
5
6ob_start();
7$stmt->debugDumpParams();
8$debug = ob_get_clean();
9echo $debug;
10
11// Output:
12// SQL: [49] SELECT * FROM users WHERE id = :id AND role = :role
13// Sent SQL: [47] SELECT * FROM users WHERE id = 42 AND role = 'admin'
14// Params:  2
15// Key: Name: [3] :id
16// paramno=-1
17// name=[3] ":id"
18// is_param=1
19// param_type=1

debugDumpParams() prints to stdout. Wrap it in ob_start() / ob_get_clean() to capture it as a string. The "Sent SQL" line only appears when using emulated prepares (PDO::ATTR_EMULATE_PREPARES = true).

Method 2: Enable MySQL General Query Log

sql

1-- Enable the general log (shows all queries the server receives)
2SET GLOBAL general_log = 'ON';
3SET GLOBAL log_output = 'TABLE';
4
5-- Run your PHP code, then check the log
6SELECT event_time, argument FROM mysql.general_log
7WHERE argument LIKE '%users%'
8ORDER BY event_time DESC LIMIT 10;
9
10-- Disable when done (performance impact)
11SET GLOBAL general_log = 'OFF';

The general log shows the exact query the server executed, with parameters substituted. This works for native prepared statements where PHP never sees the final SQL.

Method 3: Manual Interpolation Function

php

1function interpolateQuery(string $query, array $params): string {
2    foreach ($params as $key => $value) {
3        if (is_string($key)) {
4            $placeholder = $key;
5        } else {
6            // Positional ? placeholders — replace one at a time
7            $pos = strpos($query, '?');
8            if ($pos !== false) {
9                $replacement = is_null($value) ? 'NULL' :
10                    (is_int($value) || is_float($value) ? $value : "'" . addslashes($value) . "'");
11                $query = substr_replace($query, $replacement, $pos, 1);
12                continue;
13            }
14        }
15
16        $replacement = is_null($value) ? 'NULL' :
17            (is_int($value) || is_float($value) ? (string)$value : "'" . addslashes($value) . "'");
18        $query = str_replace($placeholder, $replacement, $query);
19    }
20    return $query;
21}
22
23// Usage
24$sql = "SELECT * FROM orders WHERE user_id = :uid AND total > :min";
25$params = [':uid' => 42, ':min' => 99.99];
26
27echo interpolateQuery($sql, $params);
28// SELECT * FROM orders WHERE user_id = 42 AND total > 99.99

This is for debugging only — the output is not the exact query the database runs, and it should never be executed directly.

Method 4: PDO Emulated Prepares

php

1// Enable emulated prepares — PDO interpolates the query in PHP
2$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
3
4$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
5$stmt->execute(['Alice']);
6
7ob_start();
8$stmt->debugDumpParams();
9$output = ob_get_clean();
10// "Sent SQL" line now shows the full interpolated query

With emulated prepares enabled, PDO builds the final SQL string in PHP before sending it to the database. This makes debugDumpParams() show the complete query, but it bypasses the security benefits of true server-side prepared statements. Use this only for debugging, not in production.

Method 5: PDO Wrapper Class

php

1class DebuggablePDO extends PDO {
2    public function prepare($statement, $options = []): PDOStatement|false {
3        error_log("PDO Prepare: " . $statement);
4        return parent::prepare($statement, $options);
5    }
6}
7
8class DebuggableStatement extends PDOStatement {
9    private array $boundParams = [];
10
11    public function bindValue($param, $value, $type = PDO::PARAM_STR): bool {
12        $this->boundParams[$param] = $value;
13        return parent::bindValue($param, $value, $type);
14    }
15
16    public function execute($params = null): bool {
17        $allParams = $params ?? $this->boundParams;
18        error_log("PDO Execute with params: " . json_encode($allParams));
19        return parent::execute($params);
20    }
21}

Common Pitfalls

Expecting a "final query" method on PDOStatement: PDO does not have a method that returns the interpolated SQL string. The separation of template and parameters is by design, not a missing feature. Use debugDumpParams() or database logs instead.
Using emulated prepares in production for debugging: Enabling ATTR_EMULATE_PREPARES makes the full query visible but removes the security guarantee of server-side parameter binding. Never leave this enabled in production for debugging purposes.
Forgetting that debugDumpParams() prints to stdout: The method outputs directly — it does not return a string. Use output buffering (ob_start / ob_get_clean) to capture the result into a variable for logging.
Trusting manual interpolation as the actual query: A hand-built interpolation function approximates the query but does not account for database-specific quoting, character encoding, or type casting. The database may execute something slightly different.
Leaving the MySQL general log enabled: The general query log records every query and has significant performance overhead. Always disable it after debugging with SET GLOBAL general_log = 'OFF'.

Summary

PDO never builds a single interpolated SQL string — the template and parameters are sent separately to the database
Use debugDumpParams() to inspect bound parameters and (with emulated prepares) the sent SQL
Enable the database's general query log to see the exact query the server executed
Build a manual interpolation function for approximate debugging output
Emulated prepares (ATTR_EMULATE_PREPARES = true) make the full query visible in debugDumpParams() but sacrifice server-side parameter binding
All of these are debugging techniques — never execute manually interpolated queries in production