CodeIgniter: SQL Audit of all $this->db->query() method calls?

Question

I'm using CodeIgniter 2+ and would like to Audit all $this->db->query($sql); calls.

All of our database calls are thru the query() method; no active record usage. I need to record the $sql queries and enter them into an custom table for audit recording purposes. Does any know of a way of extended the core system database library to audit queries?

It seems like this should be easy, but I can't seem to find a simple solution. The CI forum has a couple of failure posts about old versions.

Answer 1

It depends how you want to audit them. If you are looking for a per page basis then enabling the profiler will be fine. This shows all queries run on that page load as well as the time taken to execute them. See the link below on the profiler.

http://codeigniter.com/user_guide/general/profiling.html

If you are looking to log all of the queries as they happen and then read the log file later, you will have to extend the database class. If this is the case, comment and I'll update/extend my answer further.

Extending to overwrite query()

Extend MY_Loader.php in /application/core/ and insert this function

function database($params = '', $return = FALSE, $active_record = NULL)
    {
        // Grab the super object
        $CI =& get_instance();

        // Do we even need to load the database class?
        if (class_exists('CI_DB') AND $return == FALSE AND $active_record == NULL AND isset($CI->db) AND is_object($CI->db)) {
            return FALSE;
        }

        require_once(BASEPATH.'database/DB'.EXT);

        // Load the DB class
        $db =& DB($params, $active_record);

        $my_driver = config_item('subclass_prefix').'DB_'.$db->dbdriver.'_driver';
        $my_driver_file = APPPATH.'core/'.$my_driver.EXT;

        if (file_exists($my_driver_file)) {
            require_once($my_driver_file);
            $db = new $my_driver(get_object_vars($db));
        }

        if ($return === TRUE) {
            return $db;
        }

        // Initialize the db variable.  Needed to prevent
        // reference errors with some configurations
        $CI->db = '';
        $CI->db = $db;
    }

Then create /application/core/MY_DB_mysql_driver.php

Then inside that you can overwrite query()

function query($sql, $binds = FALSE, $return_object = TRUE) {
    // Do your stuff
    return parent::query( $sql, $binds, $return_object );
}

Obviously replace mysql in the filename to whatever database driver you're using/trying to extend.

This will also work with Active Record as all of the get() methods call upon query() from the driver to run their queries.