Logging sql bind params

How do I log bind params to an phsql? I cand profile the sql queries, but not the binded params?



81.1k

Try this:

$di->set('db', function() {

    $connection = new Mysql(array(
        "host" => 'localhost',
        "username" => 'root',
        "password" => 'pass',
        "dbname" => 'test'
    ));

    $eventsManager = new Phalcon\Events\Manager();

    $logger = new \Phalcon\Logger\Adapter\File(APP_PATH . "/app/logs/db.log");

    //Listen all the database events
    $eventsManager->attach('db', function($event, $connection) use ($logger) {
       if ($event->getType() == 'beforeQuery') {
            $sqlVariables = $connection->getSQLVariables();
            if (count($sqlVariables)) {
                $logger->log($connection->getSQLStatement() . ' ' . join(', ', $sqlVariables), Logger::INFO);
            } else {
                $logger->log($connection->getSQLStatement(), Logger::INFO);
            }
        }
    });

    //Assign the eventsManager to the db adapter instance
    $connection->setEventsManager($eventsManager);

    return $connection;
});


4.8k

Because I wanted to use the Firebug table format, and have timestamp, sql and params on a different column I ended up extending the default Profiler, but the solution is not very nice, because I can't use my own Profiler Item. So I came up with this:

namespace Kyo\Db;

class Profiler extends \Phalcon\Db\Profiler
{
    protected $_allProfilesWithBind;

    public function startProfileWithBind( $sqlStatement, $bindParams )
    {
        $this->_allProfilesWithBind[] = $bindParams;
        return parent::startProfile($sqlStatement);
    }
    public function getAllProfilesWithBind()
    {
        return $this->_allProfilesWithBind;
    }
}

and in the base controller in the afterExecuteRoute method i put this:

        $profiler = $this->getDI()->getDbProfiler();

        if( $profiler->getProfiles() ) {

            $table   = [];
            $table[] = ['Time', 'SQL Statement', 'Params'];

            $totalQueries = $totalDuration = 0;

            $params = $profiler->getAllProfilesWithBind();
            foreach ( $profiler->getProfiles() as $key => $profile ) {
                $totalDuration += $profile->getTotalElapsedSeconds();

                $bindParams = isset($params[$key]) ? $params[$key] : [];
                $table[] = array( $profile->getTotalElapsedSeconds(), $profile->getSQLStatement(), $bindParams );
            }
            if( $profiler->getNumberTotalStatements() > 0 ) {
                $this->getLogger()->table('SQL Debug ( '.$profiler->getNumberTotalStatements().' @ '.$totalDuration.' sec )', $table );
            }
        }

It would be nice to be able to use your own \Phalcon\Db\Profiler\Item and add these properties there.