Is there any way to recover which Pdostatement in the PHP PDO was created?

Question

You see, I use PDO in PHP to run my Querys, sometimes I use the same query in other windows and then I need to rebuild the same Query on the other page. What I have in mind:

I want to save select on a Sesssion to reuse on the next page, so I’m sure I’m using the same query (to bring the same result) and avoid rewriting which could lead to errors.

So I have:

$SQL = "select * from tabela where (codcli = :codcli) and (pedido in(:codigos)))";

$Query = Database::Prepare($SQL);
$Query->bindValue(":codcli", "234");
$Query->bindValue(":codigos", "1,2,4,6,7,8");
$Query->Execute();

Eu consigo guardar o $SQL

$_SESSION["SQL_TEXT"] = $SQL;

Has a way to get which Pdostatement (bindValue) were created and their respective values?

I tried to var_dump $Query->bindValue() but without success, in Query it only shows the text

I want to give a foreach in the binvValue to store the walls and their due values

$parametros = array();

    foreach ($Query->bindValue as $key=>$value){
      $parametros[$key] = $value;
    }

 

Answer 1

It is possible from version 7.2 onwards to view the information contained in an instruction that has been mounted on $dbh->prepare which prepares an SQL statement to be executed by the method PDOStatement::execute().

About PDOStatement::debugDumpParams: responsible for dumping an SQL command - It will provide the SQL query in use, the number of parameters used (Params), the list of parameters with their name or key position, their name, their position in the query (if this is supported by the PDO driver, otherwise it will be -1)

An example taken from the PHP documentation, but without the output completely formatted with values(Sent SQL), parameters... See:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

$sth->debugDumpParams();

?>

The above example will print:

SQL: [96] SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour
Params:  2
Key: Name: [9] :calories
paramno=-1
name=[9] ":calories"
is_param=1
param_type=1
Key: Name: [7] :colour
paramno=-1
name=[7] ":colour"
is_param=1
param_type=2

Starting from version 7.2, now returns the SQL sent to the database, you view where it contains Sent SQL:

...
$sth->bindValue(':campo', "Um valor qualquer", PDO::PARAM_STR);
...
$sth->debugDumpParams();

Exit of $sth->debugging():

SQL: [60] SELECT campo
    FROM  tabela
    WHERE campo LIKE :campo
Sent SQL: [79] SELECT campo
    FROM  tabela
    WHERE campo LIKE 'Um valor qualquer'
Params:  1
Key: Name: [5] :campo
paramno=-1
name=[5] ":campo"
is_param=1
param_type=2

Note that this will only be available if the "Emulated Prepared statements"(https://stackoverflow.com/a/15718418/3658278) are activated.

In your case we would have to use a regular expression or another way to pick up the value that starts from the Sent SQL:. Knowing that public PDOStatement::debugDumpParams(void):void does not return anything, because the result is sent directly to the browser, so it is necessary to activate the output buffer (ob_start()) to capture the output of this function and save it in a string. Hence perform the processing and retrieve the excerpt from the final SQL command.

Behold:

$SQL = "select * from tabela where (codcli = :codcli) and (pedido in(:codigos)))";

$Query = Database::Prepare($SQL);
$Query->bindValue(":codcli", "234");
$Query->bindValue(":codigos", "1,2,4,6,7,8");
$Query->Execute();


ob_start();
$Query->debugDumpParams();
$conteudo = ob_get_contents();
ob_end_clean();


$iniciaEm = strpos($conteudo, "Sent SQL:") + strlen("Sent SQL:");
$finalizaEm = strpos($conteudo, "Params:", $iniciaEm);
$resultadoParcial = substr($conteudo, $iniciaEm, $finalizaEm - $iniciaEm);

$res = preg_replace("/[][0-9]/", "", $resultadoParcial );

//Aqui está sua SQL...
//$_SESSION["SQL_TEXT"] = $res;
echo $res;

See how that’s possible? However, this in a project in production is necessary to be aware of possible changes in future versions, such as a simple change in the output of "Sent SQL" text to any other text.