Over the past few days multiple websites of mine are being targeted by automated SQL injection attempts. While these attacks are significant in numbers, they pose no real threat with proper security.
Unfortunately, these attacks cause something entirely different. They fill up the matomo database with garbage... and I mean... A LOT of garbage. Thousands upon thousands of SQL data that I need to delete manually.
In some cases, the attacker is using the same IP address over and over, which makes things a bit easier. I use the visitor ID with the following SQL command to delete garbage data:
DELETE piwik_log_visit, piwik_log_link_visit_action, piwik_log_conversion FROM piwik_log_visit LEFT JOIN piwik_log_link_visit_action ON piwik_log_visit.idvisit = piwik_log_link_visit_action.idvisit LEFT JOIN piwik_log_action ON piwik_log_action.idaction = piwik_log_link_visit_action.idaction_url LEFT JOIN piwik_log_conversion ON piwik_log_visit.idvisit = piwik_log_conversion.idvisit WHERE lower(conv(hex(piwik_log_visit.idvisitor), 16, 16)) = '1234567890123456';
So what do these SQL injection attempts look like? Here are two examples, where the attacker is attempting to inject URL encoded SQL commands in the campaign parameter utm_source:
Unfortunately these long strings are decoded by matomo and stored as strings in the database, thus they also appear in the user interface.
I'm opening this feature request, to ask for a "better" method to deal with such problems, maybe a new plugin that allows the admin to type certain keywords like the visitor ID, or an IP address, and have everything related deleted from the database.
For visitorId or IP address there is such a tool already. It's shown in "Admin => Privacy=> GDPR tools".
Once you searched for a visitor, you will be able to delete visits: