58

Is there a way to retrieve the query that was used to generate a PDO Prepared statement object?

ChrisR
  • 14,370
  • 16
  • 70
  • 107

4 Answers4

36

Try $statement->queryString.

Arkh
  • 8,416
  • 40
  • 45
  • 49
    Whenever I use this, it still shows the params as place-holders. Is there anyway to get the string with the params set? The way the DB gets it? – JD Isaacks Oct 25 '10 at 13:35
  • 3
    You'd have to check your database logs. For MySQL, check this page : http://dev.mysql.com/doc/refman/5.0/en/query-log.html – Arkh Oct 26 '10 at 12:49
  • 71
    Useless answer. – Andrew Liu Jun 19 '14 at 08:50
  • 4
    For those looking for the query string with values, see http://stackoverflow.com/q/210564/457836 for an explanation of why that doesn't quite work, and a function to get it yourself. – Eric G Aug 12 '14 at 20:28
  • 4
    Why so many upvotes and an accepted anser? As JD Isaaca says it shows the params as place-holders. See http://php.net/manual/de/pdostatement.debugdumpparams.php – Mawg says reinstate Monica Jul 16 '15 at 04:57
  • I can't tell from the context if this is what OP was asking for? This answer will NOT get you the query string with the values substituted, but it will get you the query string with the placeholder '?'s still in place. – Ethan Hohensee Sep 22 '17 at 13:56
  • What the ... is `queryString`? – Artfaith Jun 11 '20 at 13:14
20

The simplest way to achieve what you want is:

$statement->debugDumpParams();

Just make sure you add it after executing the statement.

Haddock-san
  • 745
  • 1
  • 12
  • 25
  • 1
    This answer should be the most upvoted/accepted. There are numerous answers out there in the Internet, as such as extending PDO etc., but this is the simpler and quicker answer – tomsihap Feb 18 '20 at 12:25
10

If you aren't opposed to extending the default \PDO and \PDOStatement object, you might consider looking at:

github.com/noahheck/E_PDOStatement

This extension to PDO allows you to see a full query statement as an example of what might be executed at the database level. It uses regex to interpolate the bound parameters of your PDO statement.

By extending the default \PDOStatement definition, E_PDOStatement is able to offer this enhancement to the default functionality without requiring modification to your normal work flow.

Disclaimer: I created this extension.

I just hope it's helpful to someone else.

Noah Heck
  • 516
  • 6
  • 10
0

This procedure works. Since debugDumpParams() doesn't return the output. Here is a little trick i designed.

// get the output before debugDumpParams() get executed 
$before = ob_get_contents();

//start a new buffer
ob_start();

// dump params now
$smt->debugDumpParams();

// save the output in a new variable $data
$data = ob_get_contents();

// clean the output screen
ob_end_clean();

// display what was before debugDumpParams() got executed
printf("%s", $before);

$statement = "";

// Now for prepared statements
if (stristr($data, 'Sent SQL') !== false)
{

// begin extracting from "Sent SQL"
$begin = stristr($data, 'Sent SQL');

// get the first ] square bracket
$square = strpos($begin, "]");

// collect sql
$begin = substr($begin, $square + 1);
$ending = strpos($begin, "Params");

$sql = substr($begin, 0, $ending);
$sql = trim($sql);

  // sql statement here
  $statement = $sql;
}
else
{
  if (stristr($data, 'SQL') !== false)
  {
     $begin = stristr($data, 'SQL');
     // get the first ] square bracket
     $square = strpos($begin, "]");

     // collect sql
     $begin = substr($begin, $square + 1);
     $ending = strpos($begin, "Params");

     $sql = substr($begin, 0, $ending);
     $sql = trim($sql);

     $statement = $sql;
  }

}


// statement here
echo $statement;

Hope this helps.

Ifeanyi Amadi
  • 776
  • 5
  • 10