How can I view the contents of a prepared statement?

Question

I'm working on learning to use prepared statements with mysqli in PHP and usually, if I'm having a problem with a query I just echo it to the screen to see what it looks like as a first step.

How can I do that with a prepared statement?

I'd like to see the SQL statement after the variables are substituted.

Answer 1

Using prepared statements:

• When you prepare the statement, it is sent to the MySQL server
• When you bind the variables + execute the statement, only the variables are sent to the MySQL server
• And the statement + bound variables are executed on the MySQL server -- without it re-doing the "preparation" each time the statement is executed (which is why prepared statements can be good for performance when the same statement is executed several times)

There is no "building" of an SQL query on the PHP side, so, there is no way to actually get that query.

Which means that if you want to see an SQL query, you have to use, well, SQL queries, and not prepared statements.

Answer 2

• You can use PDOStatement->debugDumpParams to get some informations about the prepared statement (in case you're using pdo).
• Prepared statements are logged in MySQL's general log:

For prepared statements that are executed with the mysql_stmt_prepare() and mysql_stmt_execute() C API functions, the server writes Prepare and Execute lines to the general query log so that you can tell when statements are prepared and executed.
[...] the server writes the following lines to the general query log:
Prepare [1] SELECT ?
Execute [1] SELECT 3

So for debugging purposes active the general log and keep an eye on that file.

edit: oh, the question has a [mysqli] tag... completely overlooked that.
If the statement isn't executed at all have you (double/tripple) checked that no error occurred along the way?

echo "<pre>Debug: start</pre>\n";

$mysqli = new mysqli('localhost', 'localonly', 'localonly', 'test');
if ($mysqli->connect_error) {
  die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

$result = $mysqli->query('CREATE TEMPORARY TABLE foo (id int auto_increment, x int, primary key(id))');
if ( false=== $result) { 
 die('error : '. $mysqli->error);
}

$stmt = $mysqli->prepare('INSERT INTO foo (x) VALUES (?)');
if ( false===$stmt ) {
  die ('prepare() failed: ' . $mysqli->error);
}

$result = $stmt->bind_param('i', $x);
if ( false===$result ) {
  die('bind_param() failed');
}

$x = 1;
$result = $stmt->execute();
if ( false===$result ) {
  die('execute() failed: '.$stmt->error);
}

echo "<pre>Debug: end</pre>\n";

Answer 3

I usually do this when I need to debug a prepared sql with parameters.

Example of prepare and execute:

$sql = "SELECT VAL1, VAL2 FROM TABLE(?, '?', '?', '?', '?', ?, '?', '?', '?')";
$prep = ibase_prepare( $sql ) or die("Error");
$query = ibase_execute($prep, $param1, $param2, .....) or $err = true;
                              ^^^^^^^^^^^^^^^^^^^^^^^

The easy way to debug the resulting SQL of the sentence it's:

printf( str_replace('?', '%s', $sql), $param1, $param2, ....);
                                      ^^^^^^^^^^^^^^^^^^^^^^

You only need to do one printf, replacing the ? on the prepared SQL string by one %s. printf will interpret all as one string, taking each parameter as placing it on each replaced %s.

Answer 4

I recently updated this project to include composer integration, unit testing and to better handle accepting arguments by reference (this requires updating to php 5.6).

---

I've created a set of classes that extend the default mysqli and mysqli_stmt classes to allow you to view a rendition of the potential query string, which should provide what you're looking for:

https://github.com/noahheck/E_mysqli

This is a (close to) drop-in replacement for you normal mysqli object that returns a custom mysqli_stmt object when you prepare() the query string. After binding your parameters, E_mysqli will allow you to view the resultant query string as a new property of the stmt object:

$mysqli = new E_mysqli($dbHost, $dbUser, $dbPass, $dbName);

$query = "INSERT INTO registration SET name = ?, email = ?";

$stmt = $mysqli->prepare($query);

$stmt->bind_param("ss", $_POST['name'], $_POST['email']);

$stmt->execute();

echo $stmt->fullQuery;

would result in:

INSERT INTO registration SET name = 'John Doe', email = 'john.doe@example.com'

There are some caveats with using this extension (explained in the README at the github project), but for troubleshooting troublesome areas of your application, or transitioning to an object oriented style from procedural, this should provide a level of help for most users.

As I've outlined in the github project, I don't have any practical experience using the mysqli extension, and this project was created at the request of users of it's sister project, so any feedback that can be provided from devs using this in production would be greatly appreciated.

Disclaimer - As I said, I made this extension.

Answer 5

Agreeing with Pascal MARTIN (+1) so I suggest another technique for debugging: var_dump() or log every variable you're inserting into the statement, that way you should be able to figure out if it is wrong data or logically wrong SQL.

Answer 6

You can use tool like Lottip. The idea is act like MySQL proxy. It parses MySQL packets, extracts query and it's params so you can see prepared statements with it's content.