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.

Answer 2

If you just want to do this directly...

The query function is in system/database/DB_driver.php line 250 (or so depending on your version).

There's a comment that says "Run the query" at about line 289.

Output $sql in any way you like at that point.

Answer 3

It seems that you could extend the database class and overwrite the query method, but according to Phil, it can't be done.

One solution is to create a helper method that you can replace all of your $this->db->query calls with that will log your queries, and then call the query method.

UPDATE

You could create a library that copies the _compile_queries method of the Profiler library (line 168 of https://github.com/EllisLab/CodeIgniter/blob/develop/system/libraries/Profiler.php). Normally, this method outputs all queries to the browser, but you could implement it in a way that logs all queries. then, not only would you get queries generated with $this->db->query, but all queries run.