@robo2bobo opened this Issue on January 18th 2020

Hello everyone,

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:

Example 1

?utm_source=%28SELECT%20%28CASE%20WHEN%20%282967%3D1288%29%20THEN%202967%20ELSE%202967%2A%28SELECT%202967%20FROM%20INFORMATION_SCHEMA.CHARACTER_SETS%29%20END%29%29

Example 2

?utm_source=online%29%20UNION%20ALL%20SELECT%20NULL%2CNULL%2CNULL%2CNULL%2CNULL%2CNULL%2CNULL%2CNULL%2CNULL%2CNULL%23

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.

Thank you.

@tsteur commented on January 20th 2020 Member

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:

image

Refs https://github.com/matomo-org/matomo/issues/3385

This Issue was closed on January 20th 2020
Powered by GitHub Issue Mirror