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

Question

In PHP  when accessing MySQL database with PDO with parametrized query  how can you check the final query  after having replaced all tokens    Is there a way to check what gets really executed by the database

User · Accepted Answer

So I think I ll finally answer my own question in order to have a full solution for the record  But have to thank Ben James and Kailash Badu which provided the clues for this    Short Answer As mentioned by Ben James  NO  The full SQL query does not exist on the PHP side  because the query-with-tokens and the parameters are sent separately to the database  Only on the database side the full query exists   Even trying to create a function to replace tokens on the PHP side would not guarantee the replacement process is the same as the SQL one  tricky stuff like token-type  bindValue vs bindParam        Workaround This is where I elaborate on Kailash Badu s answer  By logging all SQL queries  we can see what is really run on the server  With mySQL  this can be done by updating the my cnf  or my ini in my case with Wamp server   and adding a line like   log  REPLACE BY PATH   REPLACE BY FILE NAME    Just do not run this in production

User · Answer

What I did to print that actual query is a bit complicated but it works     In method that assigns variables to my statement I have another variable that looks a bit like this    this- gt fullStmt   str replace  column         str replace                param           this- gt fullStmt     Where   column is my token  param is the actual value being assigned to token  this- gt fullStmt is my print only statement with replaced tokens  What it does is a simply replace tokens with values when the real PDO assignment happens   I hope I did not confuse you and at least pointed you in right direction

User · Answer

I think easiest way to see final query text when you use pdo is to  make special error and look error message  I don t know how to do that  but when i make sql error in yii framework that use pdo i could see query text

User · Answer

You might be able to use PDOStatement- gt debugDumpParams   See the PHP documentation

User · Answer

I initially avoided turning on logging to monitor PDO because I thought that it would be a hassle but it is not hard at all  You don t need to reboot MySQL  after 5 1 9    Execute this SQL in phpMyAdmin or any other environment where you may have high db privileges   SET GLOBAL general log    ON     In a terminal  tail your log file   Mine was here    gt sudo tail -f  usr local mysql data myMacComputerName log   You can search for your mysql files with this terminal command    gt ps auxww grep  m ysqld   I found that PDO escapes everything  so you can t write    dynamicField    userName    sql    SELECT   FROM  example  WHERE   field     value    this- gt statement    this- gt db- gt prepare  sql    this- gt statement- gt bindValue   field    dynamicField    this- gt statement- gt bindValue   value    mick     this- gt statement- gt execute      Because it creates   SELECT   FROM  example  WHERE   userName      mick      Which did not create an error  just an empty result  Instead I needed to use   sql    SELECT   FROM  example  WHERE   dynamicField     value     to get  SELECT   FROM  example  WHERE  userName     mick      When you are done execute   SET GLOBAL general log    OFF     or else your logs will get huge

User · Answer

The easiest way it can be done is by reading mysql execution log file and you can do that in runtime   There is a nice explanation here   How to show the last queries executed on MySQL

User · Answer

I don t believe you can  though I hope that someone will prove me wrong   I know you can print the query and its toString method will show you the sql without the replacements  That can be handy if you re building complex query strings  but it doesn t give you the full query with values

User · Answer

I check Query Log to see the exact query that was executed as prepared statement

User · Answer

Using prepared statements with parametrised values is not simply another way to dynamically create a string of SQL  You create a prepared statement at the database  and then send the parameter values alone   So what is probably sent to the database will be a PREPARE      then SET     and finally EXECUTE       You won t be able to get some SQL string like SELECT   FROM      even if it would produce equivalent results  because no such query was ever actually sent to the database

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

Examples related to php

Examples related to mysql

Examples related to pdo

Examples related to sql-parametrized-query