Query log in codeigniter using hook

Question

I need to create a query log in my project. So I created a post_controller hook. It saves all the executed queries in both a text file and a database. But it works only for SELECT queries. I know it is repeated question, but after a lot of search, I couldn't find solution.

Here is my code:

config/hooks.php:

$hook['post_controller'] = array(
    'class' => 'LogQueryHook',
    'function' => 'log_queries',
    'filename' => 'log_queries.php',
    'filepath' => 'hooks'
);

hooks/log_queries.php

class LogQueryHook {

function log_queries() {   
    $CI =& get_instance();             
    $times = $CI->db->query_times;
    //$dbs    = array();
    $output = NULL;     
    $queries = $CI->db->queries;
    //print_r($queries);
    if (count($queries) == 0){
        $output .= "no queries\n";
    }else{
        foreach ($queries as $key=>$query){
            $took = round(doubleval($times[$key]), 3);
            $CI->db->query('INSERT INTO queryLog_tbl(`query`, `executedTime`, `timeTaken`, `executedBy`) VALUES ("'.$query.'", "'.date('Y-m-d h:i:s').'", "'.$took.'","'.$CI->session->userdata('UserID').'")');
            $output .= $query . "\n";                
            $output .= "===[took:{$took}]\n\n";
        }

    }

    $CI->load->helper('file');
    if ( ! write_file(APPPATH  . "/logs/queries.log.txt", $output, 'a+')){
         log_message('debug','Unable to write query the file');
    }   
}
}

and hooks enabled in my config.php : $config['enable_hooks'] = TRUE;

Answer 1

My code skipping all queries other than SELECT because of internal redirection. So I created a library for this. I am attaching my code here. It may help someone else

application/libraries/Querylog.php

class Querylog {
    protected $CI;

    public function __construct() {        
        $this->CI =& get_instance();
    }

    function save_query_in_db() {
        $query = $this->CI->db->last_query();
        $times = $this->CI->db->query_times; 
        $time = round(doubleval($times[2]), 5);
        $this->CI->db->query('INSERT INTO queryLog_tbl(`query`, `executedTime`, `timeTaken`, `executedBy`) '
            . 'VALUES ("'.$query.'", "'.date('Y-m-d h:i:s').'", "'.$time.'","'.$this->CI->session->userdata('UserID').'")');
    }
}

load this library in your controller or autoload.php

and call save_query_in_db() where ever you want

eg: in model :

$this->db->set('status', 1);
$this->db->where('UserID', $this->session->userdata('UserID'));
$this->db->update('user_tbl');
$this->querylog->save_query_in_db();

Answer 2

You need to check your internal redirection after any modification query(Insert, Update or delete query) executed. If you put any redirect statement after modification query then it will overtake hook execution.

You can do it by overwriting the query() method in system/database/DB_driver.php

Or

Create library and call it from relevant controllers.