$code = 'SQL: ' . $sql . "\r\n";
if ($bind) {
     $code .= 'BIND: ' . print_r($bind, true) . "\r\n";
}
$this->_debugWriteToFile("[".date('Y-m-d H:i:s')."] ".$code);

public function query($sql, $bind = array())
{
     $this->_debugTimer();

     try {
          $sql = (string)$sql;
          if (strpos($sql, ':') !== false || strpos($sql, '?') !== false) {
               $this->_bindParams = $bind;
               $sql = preg_replace_callback('#(([\'"])((\\2)|((.*?[^\\\\])\\2)))#', array($this, 'proccessBindCallback'), $sql);
               $bind = $this->_bindParams;
          }

          $code = 'SQL: ' . $sql . "\r\n";
          if ($bind) {
               $code .= 'BIND: ' . print_r($bind, true) . "\r\n";
          }
          $this->_debugWriteToFile("[".date('Y-m-d H:i:s')."] ".$code);

          $result = parent::query($sql, $bind);
     }
     catch (Exception $e) {
          $this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
          $this->_debugException($e);
     }
     $this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
     return $result;
 }

Comments

• 

  Fabien Petermann

  

  Thanks for that post, that gave me a clue as to where I should start off.

  I have slightly enhanced your code to get control over query logging through the configuration file. Just replace your code with :

  $this->_logQuery($sql, $bind);

  and add this at the bottom of the /path/to/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php file :

  protected function _logQuery($sql, $bind = array(), $result = null)
  {
  // configuration of the query logger
  static $logConfig = null;

  if (null === $logConfig) {
  // first time function is executed
  // query logger needs to be configured
  if (array_key_exists(‘log’, $this->_config)) {
  $logConfig['enabled'] = (bool) $this->_config['log'];
  }
  if (array_key_exists(‘log_file’, $this->_config)) {
  $logConfig['file_name'] = $this->_config['log_file'];
  }
  }

  if (!$logConfig['enabled']) {
  // query logger is not enabled, nothing to do
  return $this;
  }

  // build the log message
  // note: from this part on, code is inspired by _debugStat() and
  // _debugWriteFile() methods of the Varien_Db_Adapter_Pdo_Mysql
  // class, with only small modifications
  $code = ‘## ‘ . getmypid() . ‘ ## ‘;
  $nl = “\n”;

  $code .= ‘QUERY’ . $nl;
  $code .= ‘SQL: ‘ . $sql . $nl;
  if ($bind) {
  $code .= ‘BIND: ‘ . print_r($bind, true) . $nl;
  }
  if ($result instanceof Zend_Db_Statement_Pdo) {
  $code .= ‘AFF: ‘ . $result->rowCount() . $nl;
  }
  $code .= $nl . $nl;

  if (!array_key_exists(‘adapter’, $logConfig)) {
  $logConfig['adapter'] = new Varien_Io_File();
  $dir = $logConfig['adapter']->dirname($logConfig['file_name']);
  $logConfig['adapter']->checkAndCreateFolder($dir);
  $logConfig['adapter']->open(array(‘path’ => $dir));
  $logConfig['file_name'] = basename($logConfig['file_name']);
  }

  $logConfig['adapter']->streamOpen($logConfig['file_name'], ‘a’);
  $logConfig['adapter']->streamLock();
  $logConfig['adapter']->streamWrite($code);
  $logConfig['adapter']->streamUnlock();
  $logConfig['adapter']->streamClose();

  return $this;
  }

  Now, edit your /path/to/magento/app/etc/local.xml and add these two lines to your database connection configuration :

  1

  like in the following :

  <![CDATA[]]>
  <![CDATA[]]>
  1
  1

  Now, all queries can be seen from that sql.txt file. Sweet!

  By the way, did you notice this ** HUGE ** security hole: for some reason that I do not understand, on Magento version 1.3.2.3 (maybe this behaviour has changed in newer versions), the default value of Varien_Db_Adapter_Pdo_Mysql::$_debugFile is ‘var/debug/sql.txt’. If you have multiple stores configured like I do , this file will be located in the DOCUMENT_ROOT of your website (that is /path/to/magento/stores/store1/var/store/sql.txt), hence, making this file ** PUBLIC ** at this URL : http://store1.your-magento-shop.com/var/debug/sql.txt !

  Knowing this file may contain database credentials (including your database password), I consider this as a very serious threat to the security of your database. Thus, I ** STRONGLY ** recommend to keep this file out of the DOCUMENT_ROOT of the web server by overriding this value, or at least to protect this directory with a .htaccess file.

  Yet, you don’t have to worry too much if you only have 1 store configured, as the /path/to/magento_dir/var directory is already protected with a .htacces file.

  Thanks again for that code snippet!

  Cheers!

  November 5, 2009 at 12:04 pmReply

• 

  Fabien Petermann

  

  Whoops! This blog doesn’t seem to like XML code So, here’s what should go in your etc/local.xml file (hopefully, this time it will work.. ) :

  <log>1</log>
  <log_file><![CDATA[../../../var/debug/sql.txt]]></log_file>

  e.g. :

  <default_setup>
  <connection>
  <host><![CDATA[localhost]]></host>
  <username><![CDATA[magento]]></username>
  <password><![CDATA[<database_password>]]></password>
  <dbname><![CDATA[<database_name>]]></dbname>
  <active>1</active>
  <log>1</log>
  <log_file><![CDATA[/path/to/magento/var/debug/sql.txt]]></log_file>
  </connection>
  </default_setup>

  November 5, 2009 at 12:08 pmReply

Say Something!