debugging of SQL queries in PHP (var_dump and debugDumpParams)

Question

debugging of SQL queries in PHP (var_dump and debugDumpParams)

Navigation

#1 by (2 votes)

2

When we are writing our SQL queries, we may need to check how the dynamic data we are sending is behaving, to validate that the result is expected.

EXAMPLE

I have the following script to bring all the posts of a certain user working 2 tables by means of a JOIN

<?php

$conexion = new PDO("mysql:host=localhost;dbname=blog;port=3309", "root", "password");

$id = 1;

$consulta = $conexion->prepare("SELECT nameUser, namePost
                                FROM users
                                JOIN posts ON users.id = posts.user_id
                                WHERE users.id = :id");
$consulta->bindParam(":id", $id, PDO::PARAM_INT);
$consulta->execute();

var_dump($consulta);

However, if I want to dump from console the SQL query that is being assembled by the dynamic data that arrives by $id we can do the following

var_dump($consulta);

RESULT

object(PDOStatement)#2 (1) {
  ["queryString"]=>
  string(96) "SELECT nameUser, namePost FROM users JOIN posts ON users.id 
= posts.user_id WHERE users.id = :id"
}

or you can also use debugDumpParams that will give you a structure similar to the following

SQL: [82] SELECT nameUser, namePost
    FROM users
    JOIN posts ON users.id = posts.user_id
    WHERE users.id  = ?
Params:  2
Key: Position #0:
paramno=0
name=[0] ""
is_param=1
param_type=1
Key: Position #1:
paramno=1
id=[0] ""
is_param=1
param_type=2

This last option is also showing me that there is a value at the end dynamic, however it does not show exactly what that value is, to check if it is the one that should be received

How could I do that in the latter case, I can see instead of the name / position marker, the direct value that is being sent?

sql php php7 pdo

asked by element 03.10.2018 в 03:40

source

1 answer

response string is cut in 2033 characters when it is XML Problem with functions and loop for

score 2 · Accepted Answer

Since version 7.2 of PHP, the function debugDumpParams() in addition to displaying the SQL query sent, adds the possibility to see the dynamic values that are being sent to it, that is, you will be able to visualize instead of :id this 12 that would be the value that the user is sending

EXAMPLE

$consulta = $conexion->prepare("SELECT nameUser, namePost FROM users 
                               JOIN posts ON users.id = posts.user_id 
                               WHERE users.id = :id");
$consulta->bindParam(":id", $id, PDO::PARAM_INT);
$consulta->execute();

However, if I now execute the following query:

var_dump($consulta->debugDumpParams());

I will get a result similar to the following

SQL: [96] SELECT nameUser, namePost FROM users JOIN posts ON users.id = posts.user_id WHERE users.id = :id
Sent SQL: [94] SELECT nameUser, namePost FROM users JOIN posts ON users.id = posts.user_id WHERE users.id = 1
Params:  1
Key: Name: [3] :id
paramno=-1
name=[3] ":id"
is_param=1
param_type=1
NULL

OBSERVATIONS

Send the SQL query first with the placeholder / name

Followed by the SQL query but instead of the markers, it sends the dynamic value that is arriving by means of some   variable

This will work only if the prepared statement emulation is enabled