Getting raw SQL query string from PDO prepared statements

Question

Is there a way to get the raw SQL string executed when calling PDOStatement  execute   on a prepared statement  For debugging purposes this would be extremely useful

User · Answer

preg replace didn t work for me and when binding  was over 9  binding 1 and binding 10 was replaced with str replace  leaving the 0 behind   so I made the replacements backwards   public function interpolateQuery  query   params     keys   array         length   count  params -1      for   i    length   i  gt  0   i--                 query    str replace   binding    string  i        params  i   val          query                            query    str replace  SQL CALC FOUND ROWS        query   count           return  query       Hope someone finds it useful

User · Answer

I modified the method to include handling output of arrays for statements like WHERE IN        UPDATE  Just added check for NULL value and duplicated  params so actual  param values are not modified    Great work bigwebguy and thanks          Replaces any parameter placeholders in a query with the value of that    parameter  Useful for debugging  Assumes anonymous parameters from      params are are in the same order as specified in  query        param string  query The sql query with parameter placeholders     param array  params The array of substitution parameters     return string The interpolated query     public function interpolateQuery  query   params         keys   array         values    params         build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                else                keys                                 if  is string  value                values  key           value                 if  is array  value                values  key          implode         value                  if  is null  value                values  key     NULL               query   preg replace  keys   values   query        return  query

User · Answer

Mike s answer is working good until you are using the  re-use  bind value  For example    SELECT   FROM  an modules  AS  m  LEFT JOIN  an module sites  AS  ms  ON m module id   ms module id WHERE 1 AND  module enable     module enable AND  site id     site id AND   module system name  LIKE  search OR  module version  LIKE  search    The Mike s answer can only replace first  search but not the second  So  I rewrite his answer to work with multiple parameters that can re-used properly   public function interpolateQuery  query   params         keys   array         values    params       values limit             words repeated   array count values str word count  query  1                 build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                   values limit  key     isset  words repeated      key     intval  words repeated      key     1             else                keys                           values limit                          if  is string  value                values  key           value                 if  is array  value                values  key          implode         value                  if  is null  value                values  key     NULL              if  is array  values             foreach   values as  key   gt   val                if  isset  values limit  key                       query   preg replace        key         val    query   values limit  key    count                 else                    query   preg replace        key         val    query  1   count                                   unset  key   val         else            query   preg replace  keys   values   query  1   count             unset  keys   values   values limit   words repeated        return  query

User · Answer

A solution is to voluntarily put an error in the query and to print the error s message     Connection to the database  co   new PDO  mysql dbname myDB host localhost   root         We allow to print the errors whenever there is one  co- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION      We create our prepared statement  stmt    co- gt prepare  ELECT   FROM Person WHERE age  age      I removed the  S  of  SELECT   stmt- gt bindValue   age   18  PDO  PARAM STR   try        stmt- gt execute      catch  PDOException  e        echo  e- gt getMessage        Standard output      SQLSTATE 42000   Syntax error or access violation        near  ELECT   FROM Person WHERE age 18  at line 1   It is important to note that it only prints the first 80 characters of the query

User · Answer

I know this question is a bit old  but  I m using this code since lot time ago  I ve used response from  chris-go   and now  these code are obsolete with PHP 7 2  I ll post an updated version of these code  Credit for the main code are from  bigwebguy   mike and  chris-go  all of them answers of this question           Replaces any parameter placeholders in a query with the value of that    parameter  Useful for debugging  Assumes anonymous parameters from      params are are in the same order as specified in  query        param string  query The sql query with parameter placeholders     param array  params The array of substitution parameters     return string The interpolated query     public function interpolateQuery  query   params         keys   array         values    params         build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                else                keys                                 if  is array  value                values  key    implode       value            if  is null  value                values  key     NULL                Walk the array to see if we can add single-quotes to strings     array walk  values  function  amp  v   k    if   is numeric  v   amp  amp   v     NULL    v           v                   query   preg replace  keys   values   query  1   count        return  query      Note the change on the code are on array walk   function  replacing create function by an anonymous function  This make these good piece of code functional and compatible with PHP 7 2  and hope future versions too

User · Answer

You can use sprintf str replace         s     sql       params      Here is an example   function mysqli prepared query  link   sql   types      params array          echo sprintf str replace         s     sql       params         prepare  bind  execute     link   new mysqli  server   dbusername   dbpassword   database    sql    SELECT firstname  lastname FROM users WHERE userage  gt     AND favecolor        types    is     integer and string  params   array 20   Brown     if   qry   mysqli prepared query  link   sql   types   params        echo  Failed     else       echo  Success       Note this only works for PHP    5 6

User · Answer

Somewhat related    if you are just trying to sanitize a particular variable you can use PDO  quote  For example  to search for multiple partial LIKE conditions if you re stuck with a limited framework like CakePHP    pdo    this- gt getDataSource  - gt getConnection     results    this- gt find  all   array       conditions    gt  array           Model name LIKE      pdo- gt quote     keyword1               Model name LIKE      pdo- gt quote     keyword2

User · Answer

The  queryString property mentioned will probably only return the query passed in  without the parameters replaced with their values   In  Net  I have the catch part of my query executer do a simple search replace on the parameters with their values which was supplied so that the error log can show actual values that were being used for the query   You should be able to enumerate the parameters in PHP  and replace the parameters with their assigned value

User · Answer

A bit late probably but now there is PDOStatement  debugDumpParams     Dumps the informations contained by a prepared statement directly on   the output  It will provide the SQL query in use  the number of   parameters used  Params   the list of parameters  with their name    type  paramtype  as an integer  their key name or position  and the   position in the query  if this is supported by the PDO driver    otherwise  it will be -1     You can find more on the official php docs  Example    lt  php    Execute a prepared statement by binding PHP variables     calories   150   colour    red    sth    dbh- gt prepare  SELECT name  colour  calories     FROM fruit     WHERE calories  lt   calories AND colour    colour     sth- gt bindParam   calories    calories  PDO  PARAM INT    sth- gt bindValue   colour    colour  PDO  PARAM STR  12    sth- gt execute      sth- gt debugDumpParams       gt

User · Answer

PDOStatement has a public property  queryString  It should be what you want   I ve just notice that PDOStatement has an undocumented method debugDumpParams   which you may also want to look at

User · Answer

The  queryString property mentioned will probably only return the query passed in  without the parameters replaced with their values   In  Net  I have the catch part of my query executer do a simple search replace on the parameters with their values which was supplied so that the error log can show actual values that were being used for the query   You should be able to enumerate the parameters in PHP  and replace the parameters with their assigned value

User · Answer

I assume you mean that you want the final SQL query  with parameter values interpolated into it   I understand that this would be useful for debugging  but it is not the way prepared statements work   Parameters are not combined with a prepared statement on the client-side  so PDO should never have access to the query string combined with its parameters   The SQL statement is sent to the database server when you do prepare    and the parameters are sent separately when you do execute     MySQL s general query log does show the final SQL with values interpolated after you execute     Below is an excerpt from my general query log   I ran the queries from the mysql CLI  not from PDO  but the principle is the same   081016 16 51 28 2 Query       prepare s1 from  select   from foo where i                      2 Prepare      2  select   from foo where i     081016 16 51 39 2 Query       set  a  1 081016 16 51 47 2 Query       execute s1 using  a                 2 Execute      2  select   from foo where i   1   You can also get what you want if you set the PDO attribute PDO  ATTR EMULATE PREPARES   In this mode  PDO interpolate parameters into the SQL query and sends the whole query when you execute     This is not a true prepared query   You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute       Re comment from  afilina   No  the textual SQL query is not combined with the parameters during execution  So there s nothing for PDO to show you   Internally  if you use PDO  ATTR EMULATE PREPARES  PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute  But PDO does not expose this modified SQL query    The PDOStatement object has a property  queryString  but this is set only in the constructor for the PDOStatement  and it s not updated when the query is rewritten with parameters   It would be a reasonable feature request for PDO to ask them to expose the rewritten query  But even that wouldn t give you the  complete  query unless you use PDO  ATTR EMULATE PREPARES   This is why I show the workaround above of using the MySQL server s general query log  because in this case even a prepared query with parameter placeholders is rewritten on the server  with parameter values backfilled into the query string  But this is only done during logging  not during query execution

User · Answer

You can extend PDOStatement class to capture the bounded variables and store them for later use  Then 2 methods may be added  one for variable sanitizing   debugBindedVariables   and another to print the query with those variables   debugQuery     class DebugPDOStatement extends  PDOStatement    private  bound variables array      protected  pdo     protected function   construct  pdo         this- gt pdo    pdo         public function bindValue  parameter   value   data type  PDO  PARAM STR        this- gt bound variables  parameter     object  array  type   gt  data type   value   gt  value       return parent  bindValue  parameter   value   data type          public function bindParam  parameter   amp  variable   data type  PDO  PARAM STR   length NULL    driver options NULL        this- gt bound variables  parameter     object  array  type   gt  data type   value   gt  amp  variable       return parent  bindParam  parameter   variable   data type   length   driver options          public function debugBindedVariables         vars array         foreach  this- gt bound variables as  key  gt  val          vars  key     val- gt value         if  vars  key    NULL          continue         switch  val- gt type           case  PDO  PARAM STR   type    string   break          case  PDO  PARAM BOOL   type    boolean   break          case  PDO  PARAM INT   type    integer   break          case  PDO  PARAM NULL   type    null   break          default   type   FALSE                 if  type     FALSE          settype  vars  key    type              if is numeric key  vars          ksort  vars        return  vars         public function debugQuery         queryString    this- gt queryString        vars  this- gt debugBindedVariables         params are numeric is numeric key  vars         foreach  vars as  key  gt  amp  var         switch gettype  var            case  string    var       var     break          case  integer    var      var    break          case  boolean    var    var    TRUE     FALSE   break          case  NULL    var    NULL           default                     if  params are numeric          queryString   preg replace callback          function  match  use   amp  vars    return array shift  vars       queryString        else         queryString   strtr  queryString   vars              echo  queryString PHP EOL          class DebugPDO extends  PDO    public function   construct  dsn   username      password      driver options array           driver options  PDO  ATTR STATEMENT CLASS    array  DebugPDOStatement   array  this         driver options  PDO  ATTR PERSISTENT    FALSE      parent    construct  dsn  username  password   driver options           And then you can use this inherited class for debugging purpouses    dbh   new DebugPDO  mysql host localhost dbname test    user   pass      var  user test    sql  dbh- gt prepare  SELECT user FROM users WHERE user    test     sql- gt bindValue   test    var  PDO  PARAM STR    sql- gt execute      sql- gt debugQuery    print r  sql- gt debugBindedVariables       Resulting in     SELECT user FROM users WHERE user    user test        Array           test     user test

User · Answer

I spent a good deal of time researching this situation for my own needs  This and several other SO threads helped me a great deal  so I wanted to share what I came up with   While having access to the interpolated query string is a significant benefit while troubleshooting  we wanted to be able to maintain a log of only certain queries  therefore  using the database logs for this purpose was not ideal   We also wanted to be able to use the logs to recreate the condition of the tables at any given time  therefore  we needed to make certain the interpolated strings were escaped properly  Finally  we wanted to extend this functionality to our entire code base having to re-write as little of it as possible  deadlines  marketing  and such  you know how it is    My solution was to extend the functionality of the default PDOStatement object to cache the parameterized values  or references   and when the statement is executed  use the functionality of the PDO object to properly escape the parameters when they are injected back in to the query string  We could then tie in to execute method of the statement object and log the actual query that was executed at that time  or at least as faithful of a reproduction as possible    As I said  we didn t want to modify the entire code base to add this functionality  so we overwrite the default bindParam   and bindValue   methods of the PDOStatement object  do our caching of the bound data  then call parent  bindParam   or parent  bindValue    This allowed our existing code base to continue to function as normal   Finally  when the execute   method is called  we perform our interpolation and provide the resultant string as a new property E PDOStatement- gt fullQuery  This can be output to view the query or  for example  written to a log file   The extension  along with installation and configuration instructions  are available on github   https   github com noahheck E PDOStatement  DISCLAIMER  Obviously  as I mentioned  I wrote this extension  Because it was developed with help from many threads here  I wanted to post my solution here in case anyone else comes across these threads  just as I did

User · Answer

Added a little bit more to the code by Mike - walk the values to add single quotes          Replaces any parameter placeholders in a query with the value of that    parameter  Useful for debugging  Assumes anonymous parameters from      params are are in the same order as specified in  query        param string  query The sql query with parameter placeholders     param array  params The array of substitution parameters     return string The interpolated query     public function interpolateQuery  query   params         keys   array         values    params         build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                else                keys                                 if  is array  value                values  key    implode       value            if  is null  value                values  key     NULL                Walk the array to see if we can add single-quotes to strings     array walk  values  create function   amp  v   k    if   is numeric  v   amp  amp   v   NULL    v         v                 query   preg replace  keys   values   query  1   count        return  query

User · Answer

Mike s answer is working good until you are using the  re-use  bind value  For example    SELECT   FROM  an modules  AS  m  LEFT JOIN  an module sites  AS  ms  ON m module id   ms module id WHERE 1 AND  module enable     module enable AND  site id     site id AND   module system name  LIKE  search OR  module version  LIKE  search    The Mike s answer can only replace first  search but not the second  So  I rewrite his answer to work with multiple parameters that can re-used properly   public function interpolateQuery  query   params         keys   array         values    params       values limit             words repeated   array count values str word count  query  1                 build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                   values limit  key     isset  words repeated      key     intval  words repeated      key     1             else                keys                           values limit                          if  is string  value                values  key           value                 if  is array  value                values  key          implode         value                  if  is null  value                values  key     NULL              if  is array  values             foreach   values as  key   gt   val                if  isset  values limit  key                       query   preg replace        key         val    query   values limit  key    count                 else                    query   preg replace        key         val    query  1   count                                   unset  key   val         else            query   preg replace  keys   values   query  1   count             unset  keys   values   values limit   words repeated        return  query

User · Answer

Replaces any parameter placeholders in a query with the value of that    parameter  Useful for debugging  Assumes anonymous parameters from      params are are in the same order as specified in  query        param string  query The sql query with parameter placeholders     param array  params The array of substitution parameters     return string The interpolated query     public static function interpolateQuery  query   params         keys   array           build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                else                keys                                    query   preg replace  keys   params   query  1   count         trigger error  replaced    count   keys         return  query

User · Answer

I assume you mean that you want the final SQL query  with parameter values interpolated into it   I understand that this would be useful for debugging  but it is not the way prepared statements work   Parameters are not combined with a prepared statement on the client-side  so PDO should never have access to the query string combined with its parameters   The SQL statement is sent to the database server when you do prepare    and the parameters are sent separately when you do execute     MySQL s general query log does show the final SQL with values interpolated after you execute     Below is an excerpt from my general query log   I ran the queries from the mysql CLI  not from PDO  but the principle is the same   081016 16 51 28 2 Query       prepare s1 from  select   from foo where i                      2 Prepare      2  select   from foo where i     081016 16 51 39 2 Query       set  a  1 081016 16 51 47 2 Query       execute s1 using  a                 2 Execute      2  select   from foo where i   1   You can also get what you want if you set the PDO attribute PDO  ATTR EMULATE PREPARES   In this mode  PDO interpolate parameters into the SQL query and sends the whole query when you execute     This is not a true prepared query   You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute       Re comment from  afilina   No  the textual SQL query is not combined with the parameters during execution  So there s nothing for PDO to show you   Internally  if you use PDO  ATTR EMULATE PREPARES  PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute  But PDO does not expose this modified SQL query    The PDOStatement object has a property  queryString  but this is set only in the constructor for the PDOStatement  and it s not updated when the query is rewritten with parameters   It would be a reasonable feature request for PDO to ask them to expose the rewritten query  But even that wouldn t give you the  complete  query unless you use PDO  ATTR EMULATE PREPARES   This is why I show the workaround above of using the MySQL server s general query log  because in this case even a prepared query with parameter placeholders is rewritten on the server  with parameter values backfilled into the query string  But this is only done during logging  not during query execution

User · Answer

PDOStatement has a public property  queryString  It should be what you want   I ve just notice that PDOStatement has an undocumented method debugDumpParams   which you may also want to look at

User · Answer

I assume you mean that you want the final SQL query  with parameter values interpolated into it   I understand that this would be useful for debugging  but it is not the way prepared statements work   Parameters are not combined with a prepared statement on the client-side  so PDO should never have access to the query string combined with its parameters   The SQL statement is sent to the database server when you do prepare    and the parameters are sent separately when you do execute     MySQL s general query log does show the final SQL with values interpolated after you execute     Below is an excerpt from my general query log   I ran the queries from the mysql CLI  not from PDO  but the principle is the same   081016 16 51 28 2 Query       prepare s1 from  select   from foo where i                      2 Prepare      2  select   from foo where i     081016 16 51 39 2 Query       set  a  1 081016 16 51 47 2 Query       execute s1 using  a                 2 Execute      2  select   from foo where i   1   You can also get what you want if you set the PDO attribute PDO  ATTR EMULATE PREPARES   In this mode  PDO interpolate parameters into the SQL query and sends the whole query when you execute     This is not a true prepared query   You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute       Re comment from  afilina   No  the textual SQL query is not combined with the parameters during execution  So there s nothing for PDO to show you   Internally  if you use PDO  ATTR EMULATE PREPARES  PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute  But PDO does not expose this modified SQL query    The PDOStatement object has a property  queryString  but this is set only in the constructor for the PDOStatement  and it s not updated when the query is rewritten with parameters   It would be a reasonable feature request for PDO to ask them to expose the rewritten query  But even that wouldn t give you the  complete  query unless you use PDO  ATTR EMULATE PREPARES   This is why I show the workaround above of using the MySQL server s general query log  because in this case even a prepared query with parameter placeholders is rewritten on the server  with parameter values backfilled into the query string  But this is only done during logging  not during query execution

User · Answer

You can extend PDOStatement class to capture the bounded variables and store them for later use  Then 2 methods may be added  one for variable sanitizing   debugBindedVariables   and another to print the query with those variables   debugQuery     class DebugPDOStatement extends  PDOStatement    private  bound variables array      protected  pdo     protected function   construct  pdo         this- gt pdo    pdo         public function bindValue  parameter   value   data type  PDO  PARAM STR        this- gt bound variables  parameter     object  array  type   gt  data type   value   gt  value       return parent  bindValue  parameter   value   data type          public function bindParam  parameter   amp  variable   data type  PDO  PARAM STR   length NULL    driver options NULL        this- gt bound variables  parameter     object  array  type   gt  data type   value   gt  amp  variable       return parent  bindParam  parameter   variable   data type   length   driver options          public function debugBindedVariables         vars array         foreach  this- gt bound variables as  key  gt  val          vars  key     val- gt value         if  vars  key    NULL          continue         switch  val- gt type           case  PDO  PARAM STR   type    string   break          case  PDO  PARAM BOOL   type    boolean   break          case  PDO  PARAM INT   type    integer   break          case  PDO  PARAM NULL   type    null   break          default   type   FALSE                 if  type     FALSE          settype  vars  key    type              if is numeric key  vars          ksort  vars        return  vars         public function debugQuery         queryString    this- gt queryString        vars  this- gt debugBindedVariables         params are numeric is numeric key  vars         foreach  vars as  key  gt  amp  var         switch gettype  var            case  string    var       var     break          case  integer    var      var    break          case  boolean    var    var    TRUE     FALSE   break          case  NULL    var    NULL           default                     if  params are numeric          queryString   preg replace callback          function  match  use   amp  vars    return array shift  vars       queryString        else         queryString   strtr  queryString   vars              echo  queryString PHP EOL          class DebugPDO extends  PDO    public function   construct  dsn   username      password      driver options array           driver options  PDO  ATTR STATEMENT CLASS    array  DebugPDOStatement   array  this         driver options  PDO  ATTR PERSISTENT    FALSE      parent    construct  dsn  username  password   driver options           And then you can use this inherited class for debugging purpouses    dbh   new DebugPDO  mysql host localhost dbname test    user   pass      var  user test    sql  dbh- gt prepare  SELECT user FROM users WHERE user    test     sql- gt bindValue   test    var  PDO  PARAM STR    sql- gt execute      sql- gt debugQuery    print r  sql- gt debugBindedVariables       Resulting in     SELECT user FROM users WHERE user    user test        Array           test     user test

User · Answer

A solution is to voluntarily put an error in the query and to print the error s message     Connection to the database  co   new PDO  mysql dbname myDB host localhost   root         We allow to print the errors whenever there is one  co- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION      We create our prepared statement  stmt    co- gt prepare  ELECT   FROM Person WHERE age  age      I removed the  S  of  SELECT   stmt- gt bindValue   age   18  PDO  PARAM STR   try        stmt- gt execute      catch  PDOException  e        echo  e- gt getMessage        Standard output      SQLSTATE 42000   Syntax error or access violation        near  ELECT   FROM Person WHERE age 18  at line 1   It is important to note that it only prints the first 80 characters of the query

User · Answer

I need to log full query string after bind param so this is a piece in my code  Hope  it is useful for everyone hat has the same issue               param string  str     return string     public function quote  str        if   is array  str             return  this- gt pdo- gt quote  str         else            str   implode      array map function  v                        return  this- gt quote  v                       str             if  empty  str                 return  NULL                      return  str                       param string  query     param array  params     return string     throws Exception     public function interpolateQuery  query   params         ps   preg split     is    query        pieces            prev   null      foreach   ps as  p             lastChar   substr  p  strlen  p  - 1            if   lastChar                        if   prev     null                     pieces      p                else                    pieces      prev          p                   prev   null                          else                prev      prev     null                p                        arr            indexQuestionMark   -1       matches            for   i   0   i  lt  count  pieces    i              if   i   2     0                 arr            pieces  i                   else                st                    s    pieces  i               while   empty  s                     if  preg match         A-Z0-9  -    is    s   matches  PREG OFFSET CAPTURE                          index    matches 0  1                        st    substr  s  0   index                        key    matches 0  0                        s   substr  s   index   strlen  key                         if   key                                    indexQuestionMark                            if  array key exists  indexQuestionMark   params                                  st     this- gt quote  params  indexQuestionMark                              else                               throw new Exception  Wrong params in query at      index                                                   else                           if  array key exists  key   params                                  st     this- gt quote  params  key                              else                               throw new Exception  Wrong params in query with key      key                                                                     else                        st     s                       s   null                                               arr      st                       return implode      arr

User · Answer

PDOStatement has a public property  queryString  It should be what you want   I ve just notice that PDOStatement has an undocumented method debugDumpParams   which you may also want to look at

User · Answer

I assume you mean that you want the final SQL query  with parameter values interpolated into it   I understand that this would be useful for debugging  but it is not the way prepared statements work   Parameters are not combined with a prepared statement on the client-side  so PDO should never have access to the query string combined with its parameters   The SQL statement is sent to the database server when you do prepare    and the parameters are sent separately when you do execute     MySQL s general query log does show the final SQL with values interpolated after you execute     Below is an excerpt from my general query log   I ran the queries from the mysql CLI  not from PDO  but the principle is the same   081016 16 51 28 2 Query       prepare s1 from  select   from foo where i                      2 Prepare      2  select   from foo where i     081016 16 51 39 2 Query       set  a  1 081016 16 51 47 2 Query       execute s1 using  a                 2 Execute      2  select   from foo where i   1   You can also get what you want if you set the PDO attribute PDO  ATTR EMULATE PREPARES   In this mode  PDO interpolate parameters into the SQL query and sends the whole query when you execute     This is not a true prepared query   You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute       Re comment from  afilina   No  the textual SQL query is not combined with the parameters during execution  So there s nothing for PDO to show you   Internally  if you use PDO  ATTR EMULATE PREPARES  PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute  But PDO does not expose this modified SQL query    The PDOStatement object has a property  queryString  but this is set only in the constructor for the PDOStatement  and it s not updated when the query is rewritten with parameters   It would be a reasonable feature request for PDO to ask them to expose the rewritten query  But even that wouldn t give you the  complete  query unless you use PDO  ATTR EMULATE PREPARES   This is why I show the workaround above of using the MySQL server s general query log  because in this case even a prepared query with parameter placeholders is rewritten on the server  with parameter values backfilled into the query string  But this is only done during logging  not during query execution

User · Answer

I modified the method to include handling output of arrays for statements like WHERE IN        UPDATE  Just added check for NULL value and duplicated  params so actual  param values are not modified    Great work bigwebguy and thanks          Replaces any parameter placeholders in a query with the value of that    parameter  Useful for debugging  Assumes anonymous parameters from      params are are in the same order as specified in  query        param string  query The sql query with parameter placeholders     param array  params The array of substitution parameters     return string The interpolated query     public function interpolateQuery  query   params         keys   array         values    params         build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                else                keys                                 if  is string  value                values  key           value                 if  is array  value                values  key          implode         value                  if  is null  value                values  key     NULL               query   preg replace  keys   values   query        return  query

User · Answer

preg replace didn t work for me and when binding  was over 9  binding 1 and binding 10 was replaced with str replace  leaving the 0 behind   so I made the replacements backwards   public function interpolateQuery  query   params     keys   array         length   count  params -1      for   i    length   i  gt  0   i--                 query    str replace   binding    string  i        params  i   val          query                            query    str replace  SQL CALC FOUND ROWS        query   count           return  query       Hope someone finds it useful

User · Answer

Added a little bit more to the code by Mike - walk the values to add single quotes          Replaces any parameter placeholders in a query with the value of that    parameter  Useful for debugging  Assumes anonymous parameters from      params are are in the same order as specified in  query        param string  query The sql query with parameter placeholders     param array  params The array of substitution parameters     return string The interpolated query     public function interpolateQuery  query   params         keys   array         values    params         build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                else                keys                                 if  is array  value                values  key    implode       value            if  is null  value                values  key     NULL                Walk the array to see if we can add single-quotes to strings     array walk  values  create function   amp  v   k    if   is numeric  v   amp  amp   v   NULL    v         v                 query   preg replace  keys   values   query  1   count        return  query

User · Answer

I spent a good deal of time researching this situation for my own needs  This and several other SO threads helped me a great deal  so I wanted to share what I came up with   While having access to the interpolated query string is a significant benefit while troubleshooting  we wanted to be able to maintain a log of only certain queries  therefore  using the database logs for this purpose was not ideal   We also wanted to be able to use the logs to recreate the condition of the tables at any given time  therefore  we needed to make certain the interpolated strings were escaped properly  Finally  we wanted to extend this functionality to our entire code base having to re-write as little of it as possible  deadlines  marketing  and such  you know how it is    My solution was to extend the functionality of the default PDOStatement object to cache the parameterized values  or references   and when the statement is executed  use the functionality of the PDO object to properly escape the parameters when they are injected back in to the query string  We could then tie in to execute method of the statement object and log the actual query that was executed at that time  or at least as faithful of a reproduction as possible    As I said  we didn t want to modify the entire code base to add this functionality  so we overwrite the default bindParam   and bindValue   methods of the PDOStatement object  do our caching of the bound data  then call parent  bindParam   or parent  bindValue    This allowed our existing code base to continue to function as normal   Finally  when the execute   method is called  we perform our interpolation and provide the resultant string as a new property E PDOStatement- gt fullQuery  This can be output to view the query or  for example  written to a log file   The extension  along with installation and configuration instructions  are available on github   https   github com noahheck E PDOStatement  DISCLAIMER  Obviously  as I mentioned  I wrote this extension  Because it was developed with help from many threads here  I wanted to post my solution here in case anyone else comes across these threads  just as I did

User · Answer

Replaces any parameter placeholders in a query with the value of that    parameter  Useful for debugging  Assumes anonymous parameters from      params are are in the same order as specified in  query        param string  query The sql query with parameter placeholders     param array  params The array of substitution parameters     return string The interpolated query     public static function interpolateQuery  query   params         keys   array           build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                else                keys                                    query   preg replace  keys   params   query  1   count         trigger error  replaced    count   keys         return  query

User · Answer

I know this question is a bit old  but  I m using this code since lot time ago  I ve used response from  chris-go   and now  these code are obsolete with PHP 7 2  I ll post an updated version of these code  Credit for the main code are from  bigwebguy   mike and  chris-go  all of them answers of this question           Replaces any parameter placeholders in a query with the value of that    parameter  Useful for debugging  Assumes anonymous parameters from      params are are in the same order as specified in  query        param string  query The sql query with parameter placeholders     param array  params The array of substitution parameters     return string The interpolated query     public function interpolateQuery  query   params         keys   array         values    params         build a regular expression for each parameter     foreach   params as  key   gt   value            if  is string  key                  keys           key                else                keys                                 if  is array  value                values  key    implode       value            if  is null  value                values  key     NULL                Walk the array to see if we can add single-quotes to strings     array walk  values  function  amp  v   k    if   is numeric  v   amp  amp   v     NULL    v           v                   query   preg replace  keys   values   query  1   count        return  query      Note the change on the code are on array walk   function  replacing create function by an anonymous function  This make these good piece of code functional and compatible with PHP 7 2  and hope future versions too

User · Answer

PDOStatement has a public property  queryString  It should be what you want   I ve just notice that PDOStatement has an undocumented method debugDumpParams   which you may also want to look at

User · Answer

You can use sprintf str replace         s     sql       params      Here is an example   function mysqli prepared query  link   sql   types      params array          echo sprintf str replace         s     sql       params         prepare  bind  execute     link   new mysqli  server   dbusername   dbpassword   database    sql    SELECT firstname  lastname FROM users WHERE userage  gt     AND favecolor        types    is     integer and string  params   array 20   Brown     if   qry   mysqli prepared query  link   sql   types   params        echo  Failed     else       echo  Success       Note this only works for PHP    5 6

User · Answer

Somewhat related    if you are just trying to sanitize a particular variable you can use PDO  quote  For example  to search for multiple partial LIKE conditions if you re stuck with a limited framework like CakePHP    pdo    this- gt getDataSource  - gt getConnection     results    this- gt find  all   array       conditions    gt  array           Model name LIKE      pdo- gt quote     keyword1               Model name LIKE      pdo- gt quote     keyword2

User · Answer

A bit late probably but now there is PDOStatement  debugDumpParams     Dumps the informations contained by a prepared statement directly on   the output  It will provide the SQL query in use  the number of   parameters used  Params   the list of parameters  with their name    type  paramtype  as an integer  their key name or position  and the   position in the query  if this is supported by the PDO driver    otherwise  it will be -1     You can find more on the official php docs  Example    lt  php    Execute a prepared statement by binding PHP variables     calories   150   colour    red    sth    dbh- gt prepare  SELECT name  colour  calories     FROM fruit     WHERE calories  lt   calories AND colour    colour     sth- gt bindParam   calories    calories  PDO  PARAM INT    sth- gt bindValue   colour    colour  PDO  PARAM STR  12    sth- gt execute      sth- gt debugDumpParams       gt

User · Answer

The  queryString property mentioned will probably only return the query passed in  without the parameters replaced with their values   In  Net  I have the catch part of my query executer do a simple search replace on the parameters with their values which was supplied so that the error log can show actual values that were being used for the query   You should be able to enumerate the parameters in PHP  and replace the parameters with their assigned value

User · Answer

The  queryString property mentioned will probably only return the query passed in  without the parameters replaced with their values   In  Net  I have the catch part of my query executer do a simple search replace on the parameters with their values which was supplied so that the error log can show actual values that were being used for the query   You should be able to enumerate the parameters in PHP  and replace the parameters with their assigned value

User · Answer

I need to log full query string after bind param so this is a piece in my code  Hope  it is useful for everyone hat has the same issue               param string  str     return string     public function quote  str        if   is array  str             return  this- gt pdo- gt quote  str         else            str   implode      array map function  v                        return  this- gt quote  v                       str             if  empty  str                 return  NULL                      return  str                       param string  query     param array  params     return string     throws Exception     public function interpolateQuery  query   params         ps   preg split     is    query        pieces            prev   null      foreach   ps as  p             lastChar   substr  p  strlen  p  - 1            if   lastChar                        if   prev     null                     pieces      p                else                    pieces      prev          p                   prev   null                          else                prev      prev     null                p                        arr            indexQuestionMark   -1       matches            for   i   0   i  lt  count  pieces    i              if   i   2     0                 arr            pieces  i                   else                st                    s    pieces  i               while   empty  s                     if  preg match         A-Z0-9  -    is    s   matches  PREG OFFSET CAPTURE                          index    matches 0  1                        st    substr  s  0   index                        key    matches 0  0                        s   substr  s   index   strlen  key                         if   key                                    indexQuestionMark                            if  array key exists  indexQuestionMark   params                                  st     this- gt quote  params  indexQuestionMark                              else                               throw new Exception  Wrong params in query at      index                                                   else                           if  array key exists  key   params                                  st     this- gt quote  params  key                              else                               throw new Exception  Wrong params in query with key      key                                                                     else                        st     s                       s   null                                               arr      st                       return implode      arr

[php] Getting raw SQL query string from PDO prepared statements

Examples related to php

Examples related to sql

Examples related to mysql

Examples related to pdo

Examples related to pdostatement