Do this now, and your future self will thank you.
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:
To remove the trigger, run:
DROP TRIGGER sansec_log_config_changes;
It is recommended to temporarily deactivate the trigger when installing Magento upgrades.
This page was last updated at Mar 8th, 2021