How to debug PDO database queries

Question

Before moving to PDO  I created SQL queries in PHP by concatenating strings  If I got database syntax error  I could just echo the final SQL query string  try it myself on the database  and tweak it until I fixed the error  then put that back into the code   Prepared PDO statements are faster and better and safer  but one thing bothers me  I never see the final query as it s sent to the database  When I get errors about the syntax in my Apache log or my custom log file  I log errors inside a catch block   I can t see the query that caused them   Is there a way capture the complete SQL query sent by PDO to the database and log it to a file

User · Answer

for example you have this pdo statement :

$query="insert into tblTest (field1, field2, field3)
values (:val1, :val2, :val3)";
$res=$db->prepare($query);
$res->execute(array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
));

now you can get the executed query by defining an array like this :

$assoc=array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
);
$exQuery=str_replace(array_keys($assoc), array_values($assoc), $query);
echo $exQuery;

User · Answer

i use this class to debug PDO  with Log4PHP    lt  php         Extends PDO and logs all queries that are executed and how long    they take  including queries issued via prepared statements     class LoggedPDO extends PDO        public static  log   array         public function   construct  dsn   username   null   password   null   options   null                parent    construct  dsn   username   password   options              public function query  query                 result   parent  query  query           return  result                         return LoggedPDOStatement             public function prepare  statement   options   NULL                if    options                 options   array                      return new  LoggedPDOStatement parent  prepare  statement   options                    PDOStatement decorator that logs when a PDOStatement is    executed  and the time it took to run     see LoggedPDO     class LoggedPDOStatement                   The PDOStatement we decorate             private  statement      protected   debugValues   null       public function   construct PDOStatement  statement                 this- gt statement    statement             public function getLogger                 return  Logger  getLogger  PDO sql                          When execute is called record the time it takes and        then log the query         return PDO result set             public function execute array  params   array                   start   microtime true           if  empty  params                  result    this- gt statement- gt execute              else               foreach   params as  key   gt   value                     this- gt  debugValues  key     value                             result    this- gt statement- gt execute  params                       this- gt getLogger  - gt debug  this- gt  debugQuery               time   microtime true  -  start           ar    int   this- gt statement- gt rowCount             this- gt getLogger  - gt debug  Affected rows       ar     Query took      round  time   1000  3      ms            return  result             public function bindValue  parameter   value   data type   false                 this- gt  debugValues  parameter     value          return  this- gt statement- gt bindValue  parameter   value   data type              public function  debugQuery  replaced   true                 q    this- gt statement- gt queryString           if    replaced                return  q                     return preg replace callback      0-9a-z     i   array  this    debugReplace     q              protected function  debugReplace  m                 v    this- gt  debugValues  m 0             if   v     null                return  NULL                     if   is numeric  v                  v   str replace             v                      return        v                              Other than execute pass all other calls to the PDOStatement object         param string  function name         param array  parameters arguments             public function   call  function name   parameters                return call user func array array  this- gt statement   function name    parameters

User · Answer

Looking in the database log Although Pascal MARTIN is correct that PDO doesn t send the complete query to the database all at once  ryeguy s suggestion to use the DB s logging function actually allowed me to see the complete query as assembled and executed by the database  Here s how   These instructions are for MySQL on a Windows machine - your mileage may vary   In my ini  under the  mysqld  section  add a log command  like log  quot C  Program Files MySQL MySQL Server 5 1 data mysql log quot  Restart MySQL  It will start logging every query in that file   That file will grow quickly  so be sure to delete it and turn off logging when you re done testing

User · Answer

Searching internet I found this as an acceptable solution  A different class is used instead of PDO and PDO functions are called through magic function calls  I am not sure this creates serious performance problems  But it can be used until a sensible logging feature is added to PDO   So as per this thread  you can write a wrapper for your PDO connection which can log and throws an exception when you get a error   Here is simple example   class LoggedPDOSTatement extends PDOStatement       function execute   array           parent  execute   array        errors   parent  errorInfo        if   errors 0      00000            throw new Exception   errors 2        endif           so you can use that class instead of PDOStatement    this- gt db- gt setAttribute  PDO  ATTR STATEMENT CLASS  array   LoggedPDOStatement   array        Here a mentioned PDO decorator implementation   class LoggedPDOStatement       function   construct   stmt            this- gt stmt    stmt     function execute   params   null            result    this- gt stmt- gt execute   params        if   this- gt stmt- gt errorCode      PDO  ERR NONE            errors    this- gt stmt- gt errorInfo             this- gt paint   errors 2        endif      return  result     function bindValue   key   value            this- gt values  key     value          return  this- gt stmt- gt bindValue   key   value      function paint   message   false           echo   lt pre gt        echo   lt table cellpadding  5px  gt        echo   lt tr gt  lt td colspan  2  gt Message       message     lt  td gt  lt  tr gt        echo   lt tr gt  lt td colspan  2  gt Query       this- gt stmt- gt queryString     lt  td gt  lt  tr gt        if  count   this- gt values   gt  0       foreach   this- gt values as  key   gt   value       echo   lt tr gt  lt th align  left  style  background-color   ccc   gt      key     lt  th gt  lt td gt      value     lt  td gt  lt  tr gt        endforeach      endif      echo   lt  table gt        echo   lt  pre gt       function   call   method   params           return call user func array  array   this- gt stmt   method    params

User · Answer

this code works great for me    echo str replace array keys  data   array values  data    query- gt queryString     Don t forget to replace  data and  query by your names

User · Answer

Here s a function to see what the effective SQL will be  adpated from a comment by  Mark  at php net   function sql debug  sql string  array  params   null        if   empty  params              indexed    params    array values  params           foreach  params as  k  gt  v                if  is object  v                     if   v instanceof  DateTime   v    v- gt format  Y-m-d H i s                    else continue                            elseif  is string  v    v    v                elseif   v     null   v  NULL               elseif  is array  v    v   implode       v                if   indexed                     sql string   preg replace          v   sql string  1                             else                   if   k 0           k        k    add leading colon if it was left out                  sql string   str replace  k  v  sql string                                     return  sql string

User · Answer

In Debian NGINX environment i did the following   Goto  etc mysql mysql conf d edit mysqld cnf if you find log-error    var log mysql error log add the following 2 lines bellow it   general log file           var log mysql mysql log general log               1   To see the logs goto  var log mysql and  tail -f mysql log  Remember to comment these lines out once you are done with debugging if you are in production environment delete mysql log as this log file will grow quickly and can be huge

User · Answer

Probably what you want to do is use debugDumpParams   on the statement handle  You can run that any time after binding values to the prepared query  no need to execute   the statement   It doesn t build the prepared statement for you  but it will show your parameters

User · Answer

You say this       I never see the final query as it s   sent to the database   Well  actually  when using prepared statements  there is no such thing as a  final query      First  a statement is sent to the DB  and prepared there   The database parses the query  and builds an internal representation of it  And  when you bind variables and execute the statement  only the variables are sent to the database   And the database  injects  the values into its internal representation of the statement     So  to answer your question       Is there a way capture the complete   SQL query sent by PDO to the database   and log it to a file    No   as there is no  complete SQL query  anywhere  there is no way to capture it    The best thing you can do  for debugging purposes  is  re-construct  an  real  SQL query  by injecting the values into the SQL string of the statement   What I usually do  in this kind of situations  is     echo the SQL code that corresponds to the statement  with placeholders and use var dump  or an equivalent  just after  to display the values of the parameters This is generally enough to see a possible error  even if you don t have any  real  query that you can execute    This is not great  when it comes to debugging -- but that s the price of prepared statements and the advantages they bring

User · Answer

To log MySQL in WAMP  you will need to edit the my ini  e g  under wamp bin mysql mysql5 6 17 my ini   and add to  mysqld    general log   1 general log file  c   tmp  mysql log

User · Answer

almost nothing was said about error displaying except check error logs  but there s a rather helpful functionality    lt  php    Provoke an error -- bogus SQL syntax     stmt    dbh- gt prepare  bogus sql    if    stmt        echo   PDO  errorInfo    n       print r  dbh- gt errorInfo         gt     source link   it is clear that this code can be modified to be used as exception message  or any other kind of error handling

User · Answer

How to debug PDO mysql database queries in Ubuntu  TL DR Log all your queries and tail the mysql log   These directions are for my install of Ubuntu 14 04  Issue command lsb release -a to get your version  Your install might be different    Turn on logging in mysql   Go to your dev server cmd line Change directories cd  etc mysql  You should see a file called my cnf  That   s the file we   re gonna change  Verify you   re in the right place by typing cat my cnf   grep general log  This filters the my cnf file for you  You should see two entries   general log file    var log mysql mysql log  amp  amp   general log   1  Uncomment those two lines and save via your editor of choice  Restart mysql  sudo service mysql restart   You might need to restart your webserver too   I can   t recall the sequence I used   For my install  that   s nginx  sudo service nginx restart    Nice work  You   re all set  Now all you have to do is tail the log file so you can see the PDO queries your app makes in real time   Tail the log to see your queries  Enter this cmd tail -f  var log mysql mysql log   Your output will look something like this   73 Connect  xyz localhost on your db 73 Query    SET NAMES utf8mb4 74 Connect  xyz localhost on your db 75 Connect  xyz localhost on your db 74 Quit  75 Prepare  SELECT email FROM customer WHERE email   LIMIT   75 Execute  SELECT email FROM customer WHERE email  a b co  LIMIT 5 75 Close stmt    75 Quit  73 Quit    Any new queries your app makes will automatically pop into view  as long as you continue tailing the log  To exit the tail  hit cmd ctrl c   Notes   Careful  this log file can get huge  I   m only running this on my dev server  Log file getting too big  Truncate it  That means the file stays  but the contents are deleted  truncate --size 0 mysql log  Cool that the log file lists the mysql connections  I know one of those is from my legacy mysqli code from which I m transitioning  The third is from my new PDO connection  However  not sure where the second is coming from  If you know a quick way to find it  let me know    Credit  amp  thanks  Huge shout out to Nathan Long   s answer above for the inspo to figure this out on Ubuntu  Also to dikirill for his comment on Nathan   s post which lead me to this solution   Love you stackoverflow

User · Answer

An old post but perhaps someone will find this useful   function pdo sql debug  sql  placeholders       foreach  placeholders as  k   gt   v            sql   preg replace       k          v      sql             return  sql

User · Answer

Here is a function I made to return a SQL query with  resolved  parameters   function paramToString  query   parameters        if  empty  parameters             foreach  parameters as  key   gt   value                preg match             i    query   match  PREG OFFSET CAPTURE                query   substr replace  query   value   match 0  1   1                       return  query       query    SELECT email FROM table WHERE id     AND username            values    1   Super         echo paramToString  query   values     Assuming you execute like this   values   array 1   SomeUsername     smth- gt execute  values     This function DOES NOT add quotes to queries but does the job for me

User · Answer

No  PDO queries are not prepared on the client side  PDO simply sends the SQL query and the parameters to the database server  The database is what does the substitution  of the   s   You have two options      Use your DB s logging function  but even then it s normally shown as two separate statements  ie   not final   at least with Postgres  Output the SQL query and the paramaters and piece it together yourself

User · Answer

I ve created a modern Composer-loaded project   repository for exactly this here   pdo-debug  Find the project s GitHub home here  see a blog post explaining it here  One line to add in your composer json  and then you can use it like this   echo debugPDO  sql   parameters      sql is the raw SQL statement   parameters is an array of your parameters  The key is the placeholder name    user id   or the number of the unnamed parameter        the value is    well  the value   The logic behind  This script will simply grad the parameters and replace them into the SQL string provided  Super-simple  but super-effective for 99  of your use-cases  Note  This is just a basic emulation  not a real PDO debugging  as this is not possible as PHP sends raw SQL and parameters to the MySQL server seperated    A big thanks to bigwebguy and Mike from the StackOverflow thread Getting raw SQL query string from PDO for writing basically the entire main function behind this script  Big up

User · Answer

Sure you can debug using this mode    PDO  ATTR ERRMODE    Just add new line before your query then you will show the debug lines    db- gt setAttribute  PDO  ATTR ERRMODE  PDO  ERRMODE WARNING     db- gt query  SELECT

User · Answer

The problem I had with the solution to catch PDO exemptions for debuging purposes is that it only caught PDO exemptions  duh   but didn t catch syntax errors which were registered as php errors  I m not sure why this is  but  why  is irrelevant to the solution   All my PDO calls come from a single table model class that I extended for all my interactions with all tables    this complicated things when I was trying to debug code  because the error would register the line of php code where my execute call was called  but didn t tell me where the call was  actually  being made from  I used the following code to solve this problem          Executes a line of sql with PDO          param string  sql     param array  params     class TableModel      var   db    PDO connection     var   query    PDO query      function execute  sql   params               we re saving this as a global  so it s available to the error handler         global   tm            setting these so they re available to the error handler as well          this- gt  sql    sql           this- gt  paramArray    params                        this- gt  db- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION            this- gt  query    this- gt  db- gt prepare  sql            try                 set a custom error handler for pdo to catch any php errors             set error handler  pdoErrorHandler                   save the table model object to make it available to the pdoErrorHandler               tm    this               this- gt  query- gt execute  params                  now we restore the normal error handler             restore error handler              catch  Exception  ex                pdoErrorHandler                return false                                  So  the above code catches BOTH PDO exceptions AND php syntax errors and treats them the same way  My error handler looks something like this   function pdoErrorHandler           get all the stuff that we set in the table model     global   tm       sql     tm- gt  sql       params     tm- gt  params       query    tm- gt  query        message    PDO error       sql          implode        params        n          get trace info  so we can know where the sql call originated from     ob start        debug backtrace      I have a custom method here that parses debug backtrace  but this will work as well      trace   ob get clean           log the error in a civilized manner     error log  message        if admin              print error to screen based on your environment  logged in credentials  etc          print r  message             If anyone has any better ideas on how to get relevant info to my error handler than setting the table model as a global variable  I would be happy to hear it and edit my code

[php] How to debug PDO database queries?

Examples related to php

Examples related to sql

Examples related to pdo