June 6, 2012

PHP MySQL “instant” Audit Trail

I was approached by a client recently who had developed their own bespoke Customer Relationship Management software (CRM).  It wasn’t really a planned system – more a necessity as the business grew.  The developer was a member of staff who happened to know a little PHP and the software was developed without design or future planning.

As such, it became bloat-ware, bits tacked on here and there over a long period of time, no real database design to speak of, no structure to the code.  The client engaged FlatCoder Ltd. with the ultimate aim of a new system.  Obviously there is a lead time, and the client requested if “meanwhile” it was possible to build auditing/audit logs into their existing application.  Currently, there was no view of who edited what in the system and thus no history (audit trail) against any account or similar.

PHP / MySQL app requires an Audit Trail



A Shortcut?

One of my early thoughts was to simply add triggers to the MySQL database tables to catch updates, note the differences and record them in a separate audit table.  In principle, it would have been a neat solution.  Sadly, none of the existing tables captured “who”/last change made by, making the critical part of the task impossible – recording who changed what.  The client had also requested IP logging, again, not possible via triggers without passing extra data in (which would mean massive code modification).  If I was going to have to modify the code anyway, there was little value in the idea of triggers – it was no longer a neat shortcut.

A Shortcut Rev. 2

When I said “massive code modification” – I was not joking.  There were literally hundreds of PHP files, many not even used as far as I could tell, many making no sense with no comments or relation to each other.  After a few hours with the code I concluded it would take days to fully understand and then days upon days to modify for auditing.  What I had gleamed from the code, however, was that no Object Request Model (ORM) or similar was being used, no PDO, no nothing, just the regular MySQL library as provided by PHP.  All of the queries being sent to the database were raw SQL passing through the “mysql_query” method of PHP.

“So I pondered the notion of a simple replacement to mysql_query – a wrapper if you like, that would record auditing information and then call PHP mysql_query as normal.”

Then, all I would need to do, would be a search and replace on the code base – replace “mysql_query” with “my_new_mysql_query”.  In theory, job done!

Implementing the PHP MySQL Auditor

Here is how I wrote it.

First, it was clear I needed to parse the SQL.  It had to be understood if I were to have any chance of recording anything meaningful in the audit logs.  I found a PHP MySQL Parser that looked promising:

Sadly, it was not without bugs.  I ended up having to fix it and after the bug hunting I partly wish I’d just written my own parser.  I digress.  The problem with the parser was in the Parser.php file it included.  A function, “parseCondition”, does some string comparisons.  It was using the “==” PHP syntax which is not type safe.  I simply replaced the “==” comparisons with the type safe variant “===” for the function parseCondition in Parser.php.

The bug I had seen (zero conditions of numerical values being seen as strings) went away.

Next, a database table to store the audits.  Noting the clients requirements from previously, it turned out as:

CREATE TABLE `auditor` (
  username varchar(55) NOT NULL,
  ipaddr varchar(55),
  change_type varchar(20),
  table_name varchar(255),
  table_row varchar(255),
  changes text,
CREATE INDEX idx_ftp_files_command_and_dir ON auditor(username);

The above SQL was executed on the target database (in a test environment initially) and the audit table ready to be populated.  Next came the actual auditor.  The code is given below.  I’ll admit it’s not the most elegant code ever and I’ll blame that on a combination of pressure to deliver and the knowledge this system was going in the next few months anyway.  It’s very difficult to write an elegant addition to a terrible code base.  I’ve added a few notes in the code comments.

The most important part to take note of is that we ONLY record actual changes to our audit table. That is, values being passed in via a MySQL UPDATE are first compared against OLD values. Again, the system being what is, sends an UPDATE detailing every column in a row when the user hits “Save” as opposed to only sending values that have actually changed.

<!--?php // FILE: auditor.php // Bring in our "fixed" parser require_once('Parser.php'); // Note the use of "static" so we never need an instance. class tamsAuditor { // This does all the magic. It makes heavy use of // the SQL Parser library (see link above and consult // its documentation).  static function query($query) { // We're not interested in SELECT, there are likely // millions and they serve no audit purpose. Pass // them through!  $pos = stripos(ltrim($query),"SELECT"); if ($pos === 0) { return mysql_query($query); } // You'll need to modify this for your web app. // Alternatively, pass user name in as an argument? $username = $_SESSION["username"]; // Setup our parser  $ip_addr = $_SERVER['REMOTE_ADDR']; $parser = new SQL_Parser("",'MySQL'); $tree = $parser-&gt;parse($query); // Some variables to record our audit data         $tables = "";         $type = "";         $changes = "";         $rows = "";     $change_list = Array();         // print("$query");         // print_r("$tree");         foreach ($tree as $branch) {             // Parse a MySQL UPDATE // Note - it is VITAL we check OLD values. The // system in question often sends UPDATES that // only change one column yet details them all!             if ($branch[command] == "update") {                 $type = "UPDATE";                 foreach ($branch[tables] as $table) {                     $tables .= $table[table]." ";                 }                 foreach ($branch[sets] as $set) {                     $changes .= $set[column][column]."=";                     // print_r($set[column][column]);                     foreach ($set[condition][args] as $value) {                         $changes .= $value[value]." ";                         $change_list[$set[column][column]] = $value[value];                     }                 }                 foreach ($branch[where_clause] as $condition) {                     // print_r($condition);                     $col = "";                     $val = "";                     foreach ($condition as $pair) {                         if ($col == "") {                             $col = $pair[column];                         } else {                             $val = $pair[value];                         }                         if ($col != "" &amp;amp;&amp;amp; $val != "") {                             $rows .= $col."=".$val." ";                             $col = "";                             $val = "";                         }                     }                 }             } // If you WERE handling audits on INSERT/DELETE or even SELECT // You'd insert your new code block here         } // Now we check against the OLD values         if ($changes != "") {             if ($type != "") {             $old_row = @mysql_query("SELECT * FROM ".$tables." WHERE ".$rows);             // compare and contrast against change_list             if ($old_row) {                 if(mysql_num_rows($old_row) == 1) {                     $finals = "";                     $row = mysql_fetch_assoc($old_row);                     // print_r($row);                     foreach ($change_list as $kk =&gt; $vv) {                         // print("&lt;/p&gt; &lt;p&gt;KEY: $kk, VAL: $vv&lt;/p&gt; &lt;p&gt;");                         if ($row[$kk] == $vv || ($row[$kk] == "" &amp;amp;&amp;amp; $vv == "NULL") || ($row[$kk] == "0000-00-00" &amp;amp;&amp;amp; $vv == "NULL")) {                             // print("&lt;/p&gt; &lt;p&gt;No Change&lt;/p&gt; &lt;p&gt;");                         } else {                             if ($finals != "") { $finals .= ", "; }                             $finals .= "$kk from \"".$row[$kk]."\" to \"$vv\"";                             // print("&lt;/p&gt; &lt;p&gt;Was ".$row[$kk]." will become ".$vv."&lt;/p&gt; &lt;p&gt;");                         }                     }                     // RECORD the AUDIT                     if ($finals != "") {                         $finals = $finals . ".";                         $auditQry = "INSERT into auditor (username,ipaddr,change_type,table_name,table_row,changes) VALUES ('$username','$ip_addr','$type','$tables','$rows','$finals')";                         @mysql_query($auditQry);                     }                 } /* else {                     print("Confused, more than one row.  Cannot handle bulk update yet!");                 } */             }          }      }         return mysql_query($query);     } }; ?-->

Using the New Auditor

Auditor all written, using it was simple!  Search the code base for calls to mysql_query and replace them.  For example, a typical call might look like this:

$result = mysql_query($query);

And it would be replaced with:

require_once('auditor.php');          // brings in the auditor
$result = tamsAuditor::query($query); // replacement call

Of course, you can just make the “require_once” call once in sensible place in the code base (which is what I ended up doing, I added it to a “common.php” that seemed to be pulled in everywhere).  It won’t fail if you use require_once everywhere as we’re specifically using “require_once” and not “require” (which would fail!).  I guess there’s probably a small performance gain to be had by requiring only once.  Regardless, Job done!  Some sample rows from the audit table are shown below:

Sample Data

Click to Enlarge

Adding a Web Viewer

It was the obvious next request.  To integrate the audit log into the application so that a change log could be shown against each account (or “contract” in this clients case).  Obviously, this part will vary wildly depending on the web application you are integrating with.  In this case, the record ID could be gleamed from the URL and was easily stored (using $_GET).  From that, an IFRAME was created (it’s how the other “panels” in the web application currently worked…. YuK!), and a simple table.  For visual ease, the table was by default hidden and a “toggle” button provided for visibility:

echo "<strong>Changelog:</strong> [<a href="#changeframe"> toggle view</a>]
<div id="changelog" style="display: none;"><iframe id="" src="viewaudit.php?table=contracts&amp;key=ID&amp;value= &quot;.$_GET[" width="100%" height="250"></iframe></div>

The associated Javascript for toggling the form:

<script language="javascript">  
function toggleForm(theid) {
        var ele = document.getElementById(theid);
        if(ele.style.display == "block") {
                ele.style.display = "none";
        else {
                ele.style.display = "block";

And the associated viewaudit.php file:

<!--?php // common file from the web app that handles DB connection: include 'include/configopen.php'; // TODO: Fix for production, the obvious SQLi below. $table = $_REQUEST['table']; $pk = $_REQUEST['key']; $value = $_REQUEST['value']; $sql = "SELECT * FROM auditor WHERE table_name = '$table' AND table_row = '$pk=$value'"; $result = mysql_query($sql); echo "&lt;/p&gt; &lt;table width='100%'&gt;"; echo "&lt;/p&gt; &lt;tr&gt; &lt;td width='20%'&gt;&lt;strong&gt;Date and Time&lt;/strong&gt; &lt;/td&gt; &lt;td width='12%'&gt;&lt;strong&gt;User&lt;/strong&gt;&lt;/td&gt; &lt;td width='15%'&gt; &lt;strong&gt;IP&lt;/strong&gt;&lt;/td&gt; &lt;td&gt;&lt;strong&gt;Changes&lt;/strong&gt;&lt;/td&gt; &lt;p&gt; &lt;/tr&gt; &lt;p&gt;"; if ($result) {     while ($row = mysql_fetch_assoc($result)) {         echo "&lt;/p&gt; &lt;tr&gt; &lt;td&gt;";             echo $row['change_date'];         echo "&lt;/td&gt; &lt;td&gt;";         echo $row['username'];         echo "&lt;/td&gt; &lt;td&gt;";         echo $row['ipaddr'];         echo "&lt;/td&gt; &lt;td&gt;";         echo $row['changes'];         echo "&lt;/td&gt; &lt;/tr&gt; &lt;p&gt;";     } } echo "&lt;/table&gt; &lt;p&gt;"; mysql_free_result($result); ?-->

A Late Addition, auditing record Views

Impressed, the client made one last request before we switched to looking at a new system.  That was, “can we also audit views of accounts?”.  The answer, “yes”.  All I did was add a new static method to our auditor class as per below:

    static function record_view($table,$row) {
        $username = $_SESSION["username"];
        $ip_addr  = $_SERVER['REMOTE_ADDR'];
        $auditQry = "INSERT into auditor 
                     (username,ipaddr,change_type,table_name,table_row,changes) VALUES
                     ('$username','$ip_addr','VIEW','$table','$row','VIEWED ONLY')";

I then tracked down the “view” code for an account viewing (which was easy, PHP file in the URL and so was the needed ID info as a parameter) and simply added:

    if (isset($_GET["ID"])) { $queryID = $_GET["ID"];} 
    else {$queryID=$_SESSION ["ContractID"];}    

End of Line.