Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

A way to clean the database from malicious attack #15415

Closed
ghost opened this issue Jan 18, 2020 · 1 comment
Closed

A way to clean the database from malicious attack #15415

ghost opened this issue Jan 18, 2020 · 1 comment
Labels
answered For when a question was asked and we referred to forum or answered it.

Comments

@ghost
Copy link

ghost commented Jan 18, 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
Copy link
Member

tsteur commented Jan 20, 2020

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 #3385

@tsteur tsteur closed this as completed Jan 20, 2020
@tsteur tsteur added the answered For when a question was asked and we referred to forum or answered it. label Jan 20, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
answered For when a question was asked and we referred to forum or answered it.
Projects
None yet
Development

No branches or pull requests

1 participant