Is is possible to see what data was changed by a query?

Question

I have a database application that needs change-auditing. I am hoping to implement this at the database level so that I don't have to parse queries to see what fields are being changed or add in logging routines to existing code. Instead, I would like to add in the necessary auditing code at the DB class level.

I would like to be able to issue an UPDATE query, and then, following that, issue another query to see what data was changed.

If the query UPDATE customers SET cus_tel = '45678', cus_name = 'Mary', cus_city = 'Cape Town' WHERE cus_id = 123; is run, the change-detection query would return something like this:

------------------------------------------
| Field    | PK  | Old Value | New Value |
==========================================
| cus_tel  | 123 | 12345     | 45678     |
| cus_name | 123 | John      | Mary      |
------------------------------------------

In this case, I'm assuming that the cus_city field was already Cape Town and so did not need to be updated. The PK field is useful in case a query updates multiple rows at once.

Using this data, I could then log the changes into an auditing table as required.

I am using PHP and MySQL/PDO.

EDIT

I found this SO question which addresses the issue of a trigger to log the changes to a table - almost exactly as I require:

DELIMITER $$

DROP TRIGGER `update_data `$$

CREATE TRIGGER `update_data` AFTER UPDATE on `data_table`
FOR EACH ROW
BEGIN
    IF (NEW.field1 != OLD.field1) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field1", OLD.field1, NEW.field1, NOW());
    END IF;
    IF (NEW.field2 != OLD.field2) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field2", OLD.field2, NEW.field2, NOW());
    END IF;
    IF (NEW.field3 != OLD.field3) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field3", OLD.field3, NEW.field3, NOW());
    END IF;
END$$

DELIMITER ;

It is clear, though, that this logs from only a single table with defined fields. Is there a way to "generalise" this trigger so that it could be applied to any arbitrary table with any arbitrary fields with no (or minimal) modification?

You could create a stored procedure that gets the fields before the update. Performs the update, then gets the fields after the update. Inner join the two result sets, and you're done. Not sure why this is tagged with PHP, as that is irrelevant if you want this to happen solely on the DB layer. — crush, Mar 14 '14 at 14:36
Are you wanting this as some sort of diagnostic feature that you can call with any update query? — crush, Mar 14 '14 at 14:39
trigger the event into a history table and use that to find an audit trail. be wary of cascading updates and deletes based on other possible triggers. — Randy, Mar 14 '14 at 14:42
Thanks for comments so far: @crush - yes - I didn't make it clear that this should audit changes from any table (or at least a predefined list of tables) that could be changed from any number of queries. — Philip, Mar 14 '14 at 14:43
If you use some sort of database class or doctrine I would imagine you could store every query ever made to your system. — MonkeyZeus, Mar 14 '14 at 14:45
@MonkeyZeus - yes, that is the case and technically possible. But for auditing I need to be able to show that "Joe" updated "phone number" to "123" on a specific date and time. — Philip, Mar 14 '14 at 14:46
MySQL has a Commercial Edition product called Enterprise Audit, which offers this feature (see: https://www.mysql.com/products/enterprise/audit.html) — udog, Mar 14 '14 at 18:22

score 0 · Accepted Answer · answered Mar 15 '14 at 07:28

OK. My solution is a combination of PHP and MySQL to get this working as "transparently" as possible.

These methods exist in a Data wrapper class which uses PDO and prepared statements.

Some explanation of other methods used:

Data::prepareAndExecute ($query, $tokens); is a shortcut method that prepares a query, executes it and, if there are results, returns an associative array of those results.
Data::isSafeDatabaseEntity ($table) simply checks that the table name fits a preg_match ("/^([a-zA-Z0-9_]+)$/", $check); command to prevent SQL injection. This is since I can't use prepared statements for field and table names.
Data::tableInfo ($table); returns an associative array of the columns in a table based on information received from PDOStatement::getColumnMeta ();.
Data::getTablePrimaryKey ($table); uses the results of a SHOW INDEX FROM... query. It should be said that this is designed to work with single-field PKs only.

Extracts from my Data class:

public static function addTableLogging ($table, $ignorecolumns = array ())
{
    if (Data::isSafeDatabaseEntity ($table))
    {
        $update_trigger = "CREATE TRIGGER `{$table}_after_update` AFTER UPDATE ON `{$table}` FOR EACH ROW BEGIN\n";
        $insert_trigger = "CREATE TRIGGER `{$table}_after_insert` AFTER INSERT ON `{$table}` FOR EACH ROW BEGIN\n";
        $columns = Data::tableInfo ($table);
        $pk = Data::getTablePrimaryKey ($table);
        foreach ($columns as $column)
        {
            if (!in_array ($column ['name'], $ignorecolumns))
            {
                $update_trigger .= "   IF (NEW.{$column ['name']} != OLD.{$column ['name']}) THEN
     CALL changelog_store ('{$table}', OLD.{$pk}, '{$column ['name']}', OLD.{$column ['name']}, NEW.{$column ['name']});
  END IF;\n";
                $insert_trigger .= "   CALL changelog_store ('{$table}', NEW.{$pk}, '{$column ['name']}', '', NEW.{$column ['name']});\n";
            }
        }
        $update_trigger .= "END";
        $insert_trigger .= "END";

        self::removeTableLogging ($table);
        self::prepareAndExecute ($update_trigger);
        self::prepareAndExecute ($insert_trigger);
    }
}

public static function removeTableLogging ($table)
{
    if (self::isSafeDatabaseEntity ($table))
    {
        Data::prepareAndExecute ("DROP TRIGGER IF EXISTS `{$table}_after_update`;");
        Data::prepareAndExecute ("DROP TRIGGER IF EXISTS `{$table}_after_insert`;");
    }
}

public static function refreshLoggingProcedure ()
{
    /* -- for logging into MySQL Table:
      CREATE TABLE `changelog` (
        `change_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `change_table` VARCHAR(50) NULL DEFAULT NULL,
        `change_table_id` VARCHAR(25) NULL DEFAULT NULL,
        `change_field` VARCHAR(50) NULL DEFAULT NULL,
        `change_old` VARCHAR(255) NULL DEFAULT NULL,
        `change_new` VARCHAR(255) NULL DEFAULT NULL,
        `change_user` INT(10) UNSIGNED NOT NULL DEFAULT '0',
        `change_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
        PRIMARY KEY (`change_id`),
        INDEX `change_table_id` (`change_table_id`),
        INDEX `change_table` (`change_table`, `change_field`)
      );
    */
    $logquery = "CREATE PROCEDURE `changelog_store`(IN `tab` VARCHAR(50), IN `pkval` INT, IN `fieldn` VARCHAR(50), IN `oldv` TEXT, IN `newv` TEXT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    IF ISNULL(@STAFFID) THEN
        SET @STAFFID = 0;
    END IF;
    INSERT INTO `changelog` (change_table, change_table_id, change_field, change_old, change_new, change_date, change_user)
        VALUES (tab, pkval, fieldn, oldv, newv, NOW(), @STAFFID);
END";
    Data::prepareAndExecute ("DROP PROCEDURE IF EXISTS `changelog_store`;");
    Data::prepareAndExecute ($logquery);
}

using `$mysqli -> affected_rows` we check if USER change data, then we save `"LAST_UPDATE" = NOW()` that is so speed. But fore more details about WHAT is updated: https://stackoverflow.com/questions/26820063/how-to-find-out-which-rows-were-affected-after-update-statement-inside-mysql-pro — Stackoverflow, Jun 07 '21 at 17:23

Is is possible to see what data was changed by a query?

1 Answers1

Linked