Install a Magento database audit log
by Team Sansec
Published in Guides
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.
In this article
Easy CSP for your store?
Try Sansec Watch! Free, simple and fully integrated. Get PCI compliant alerting with minimal effort.
Sansec Watch