How to monitor SQL (which uses prepared stataments)?

Question

When first developing an PHP app (MySQl, but using ODBC interfaces to allow for future expansion), I was simply assigning my SQL to a variable and calling odbc_exec().

That made debugging simple, as I just had to examine my variable $sql.

Of course, I soon realized that I have to use prepared statements to sanitize user input.

My question is how to discover the exact SQL which is being executed in the databse, in order to debug my prepared statements.

I reazlise that I can't do it from PHP, but are their any external monitor tools which can interecpt the SQL? Or even a MySql command to echo, if I leave a console window open?

score 1 · Accepted Answer · answered Oct 18 '11 at 03:29

1

Use the MySQL query log.

answered Oct 18 '11 at 03:29

rid

61,078
31
152
193

score 1 · Answer 2 · answered Oct 18 '11 at 03:30

1

You can start mysql server by --log[=file_name] to have a log file.

answered Oct 18 '11 at 03:30

xdazz

158,678
38
247
274

Mawg says reinstate Monica · Answer 3 · 2011-10-19T03:08:16.197

Here's some code I cam up with to aid debugging within PHP code.

However, to be absolutely certin of what is being executed by MySql, the other posters are correct. Look at the query log.

function OdbcPrepareAndExecute($sql, $parameter_array)
{
   if (substr_count($sql, '?') != count($parameter_array))
   {
      ob_start();
      var_dump($parameter_array);
      $parameter_array_dump .= ob_get_clean();

      ReportErrorAndDie('Invalid parameters', 
                        'Request to prepare ODBC statement "' . $sql .'" with ' .
                        substr_count($sql, '?') . ' place-holders, but ' . 
                        count($parameter_array) . ' parameters in array : ' .
                        $parameter_array_dump
                       );
   }


   LogDatabaseActivity('Prepare SQL "' . $sql . '"');

   $prepared_statement = @odbc_prepare($_SESSION['connection'], $sql);
   if($prepared_statement === False) 
   {
      ReportErrorAndDie('Database problem', 
                        'Could not prepare ODBC statement "' . $sql .'"');
   }

   LogDatabaseActivity('Execute prepared SQL statement with the following parameters:');
   ob_start();
   var_dump($parameter_array);
   $parameter_array_dump = ob_get_clean();
   LogDatabaseActivity($parameter_array_dump);

   $expanded_sql = $sql;
   for ($i = 0; $i < count($parameter_array); $i++)
   $expanded_sql = substr_replace($expanded_sql, 
                                  $parameter_array[$i], 
                                  strpos($expanded_sql, '?'), 
                                  1);

   LogDatabaseActivity('(preapred statement expands to "' . $expanded_sql . '")');

   $result = @odbc_execute($prepared_statement, $parameter_array);

How to monitor SQL (which uses prepared stataments)?

3 Answers3