Install a Magento database audit log

This may save you a lot of future trouble.

Most stores use version control to track changes in their code base, which can be very effective in pinpointing unauthorized additions. However, perpetrators can also add malware to the database, which is usually not logged. Without logs, it will be very hard to do a root cause analysis, should an incident occur.

Therefore, Sansec recommends to install database write logging as a preventive measure. Often, enabling write logging for your database server is not feasible because of performance impact or permissions. We propose an alternative solution here, which uses database triggers to selectively log writes to those tables that can be used for malware injection. This will have no effect on the performance of your store.

The database logging should be added by a developer or system administrator.

Magento 1 & 2 database trigger log

Most frequently, Magento database malware is inserted in the core_config_data table. To create a log entry for every config change, first create a logging table using SQL commands:

CREATE TABLE sansec_log_core_config_data (
  `timestamp` DATETIME DEFAULT NULL,
  `config_id` int(10) unsigned NOT NULL,
  `scope` varchar(8) NOT NULL DEFAULT 'default',
  `scope_id` int(11) NOT NULL DEFAULT '0',
  `path` varchar(255) NOT NULL DEFAULT 'general',
  `user_id` bigint(21) unsigned NOT NULL DEFAULT '0',
  `user` varchar(64) NOT NULL,
  `old_value` text,
  `new_value` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Sansec config logging';

Then, create a trigger so that a copy of every write is saved:

CREATE TRIGGER sansec_log_config_changes
    AFTER UPDATE ON core_config_data
    FOR EACH ROW
    INSERT INTO sansec_log_core_config_data
    SET
        timestamp = NOW(),
        config_id = NEW.config_id,
        scope = NEW.scope,
        scope_id = NEW.scope_id,
        path = NEW.path,
        user_id = connection_id(),
        user = user(),
        old_value = OLD.value,
        new_value = NEW.value;

Test your new trigger

Modify some of your config using the Magento Backend. Then run this command in MySQL to see the results:

SELECT * FROM sansec_log_core_config_data \G

Rollback & cleanup

To clean the logging table, run:

TRUNCATE sansec_log_core_config_data;

To remove the trigger, run:

DROP TRIGGER sansec_log_config_changes;

It is recommended to temporarily deactivate the trigger when installing Magento upgrades.