5

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.

hakre
  • 193,403
  • 52
  • 435
  • 836
jjwdesign
  • 3,272
  • 8
  • 41
  • 66
  • 1
    If you have access to the server, you can try to use MySQL's built-in [query logging](http://dev.mysql.com/doc/refman/5.1/en/query-log.html). – gen_Eric Oct 25 '11 at 18:31
  • Rocket, good idea. But, we might switch to MS SQL or Oracle in the future. I'm looking for a PHP based solution. – jjwdesign Oct 25 '11 at 18:35

3 Answers3

5

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.

Ben Swinburne
  • 25,669
  • 10
  • 69
  • 108
  • 1
    I haven't used the Profiling library yet. I need to Audit (record) the queries as they are happening (not at the end) and save each query as an entry in a database table. I'm not sure the Profiler would be able to help me with this. – jjwdesign Oct 25 '11 at 18:42
  • Updated my answer explaining how to overwrite the database driver class without touching the core. – Ben Swinburne Oct 25 '11 at 19:00
  • Thanks Ben, that might be the best approach. Thanks to everyone that commented on this question. Much appreciated, as usual. – jjwdesign Oct 25 '11 at 22:02
  • No problem- let us know if you get it working using this for future readers.Good luck with it :) – Ben Swinburne Oct 26 '11 at 07:57
3

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.

evan
  • 12,307
  • 7
  • 37
  • 51
  • 3
    +1 - Overwriting the core is not a great idea, but since you can't extend the db class, this might be the best solution without rewriting code you already have. – swatkins Oct 25 '11 at 18:35
  • 1
    In my opinion, codeigniter is good but not great. There's no reason not to bend its code to your will. That said, an alternative is to use a custom model class that all others extend. They all call a parent method that calls `$this->db->query()`. When that's done, you can call `$this->db->last_query()` and log it. OR you can have the SQL layer log queries. – evan Oct 25 '11 at 18:50
  • A custom model might be the way to go. I could even record the queries prior to actually running them, if necessary. A database_model.php which all models would need to use. – jjwdesign Oct 25 '11 at 18:55
  • You can only record queries prior to running them if nobody is using the db methods to help construct them. If you're going to create a custom model, you can do it easily by putting `MY_Model.php` in `application/core/` – evan Oct 25 '11 at 18:59
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.

Community
  • 1
  • 1
swatkins
  • 13,530
  • 4
  • 46
  • 78