Best practice for cleaning fake customers in Magento 1

Question

What if a Magento 1 shop had a nasty bot attack. Thousands of fake customer account were created and subscribed to the newsletter. Some of the account are quite easy to find (fake domains for example), others have another pattern (Front name and last name are exactly the same) and some follow no pattern at all.

Now we are faced with the task of cleaning out these fake customers / subscribers, and we I am wondering what would be best practice? Write an SQL script that filters customers based on certain patterns? Or something else?

Answer 1

There is no perfect way to solve this issues as far i think.

But we can try to solve them one by one.

If your website authenticates new customer email ids, then its easy to figure out those customers whose email not verified.

If your store doesn't authenticate or send Verify link than you can delete the recent customer but there are chances that new real users have no order as well, in this case, you can send them an email verification link to verify the email.

OR

have a look at their I.P address if there is any common thing or all are generated from the same country you can take that hard decision to delete them. BUT before deleting those based on I.P please first check is there any order placed from the same I.P.

How to protect from now onwards?

1. Everyone should start using simple tools such as CAPTCHA and enable them.
2. Keep patch updated.
3. We can use third party Bot Blocker extension to protect our store

How to enable default Magento 1.9 CAPTCHA?

Enable CAPTCHA for frontend
Magento has built-in captcha for Frontend, you can enable it by navigating to System > Configuration > Customers > Customer Configuration

REQUEST:` Whatever you tried please spend few minutes to update your question so that it helps others as well and keep the community easy to find the right solution.

Update: Magento have website scan functionality which scans our websites and provide brief details about the health and issues of our Magento stores it is available for all version and easy to integrate.

To activate just visit Unable scan and follow easy steps.

Update 2 Recently one of the website on which i am working flodded with spam emails like below

1. [email protected]

Answer 2

you'll sometimes find that when people create bots to create accounts for your website, they will use email addresses based in their country. For example, My web store does not ship to russia, but we once had a large influx of .ru email addresses being registered.

This may be a good way for you to detect if the accounts are legitmate or not.

Answer 3

You can execute this sql request. Simply identify the customers created by bot and delete them and their relationships in the database.

To select:

SELECT *
FROM customer_entity
-- [Α-Ωα-ωА-Яа-я] > cyrillic character
-- ^(..)*(E[4-9]) > chinese character
WHERE customer_entity.firstname REGEXP '[Α-Ωα-ωА-Яа-я]|^(..)*(E[4-9])'
-- all email with *.qq.com and *.ru
OR customer_entity.email REGEXP '.*@qq\.com$|.*\.ru$';

This request is complete but very long to execute because of the multiple joints.

DELETE customer_entity, customer_address_entity, customer_log, customer_grid_flat, quote, quote_address
FROM customer_entity
LEFT JOIN customer_address_entity 
    ON customer_address_entity.entity_id = customer_entity.entity_id
LEFT JOIN customer_log 
    ON customer_log.customer_id = customer_entity.entity_id
LEFT JOIN customer_grid_flat
    ON customer_grid_flat.entity_id = customer_entity.entity_id
LEFT JOIN quote
    ON quote.entity_id = customer_entity.entity_id
LEFT JOIN quote_address
    ON quote_address.customer_id = customer_entity.entity_id
WHERE customer_entity.firstname REGEXP '[Α-Ωα-ωА-Яа-я]|^(..)*(E[4-9])'
OR customer_entity.email REGEXP '.*@qq\.com$|.*\.ru$';

I suggest executing the requests on each table. This considerably increases the speed of execution.

DELETE FROM quote_address 
WHERE customer_id in (
    SELECT 
        entity_id
    FROM customer_entity
    WHERE customer_entity.firstname REGEXP '[Α-Ωα-ωА-Яа-я]|^(..)*(E[4-9])'
        OR customer_entity.email REGEXP '.*@qq\.com$|.*\.ru$'
)

DELETE FROM quote 
WHERE entity_id in (
    SELECT 
        entity_id
    FROM customer_entity
    WHERE customer_entity.firstname REGEXP '[Α-Ωα-ωА-Яа-я]|^(..)*(E[4-9])'
        OR customer_entity.email REGEXP '.*@qq\.com$|.*\.ru$'
)

DELETE FROM customer_grid_flat 
WHERE entity_id in (
    SELECT 
        entity_id
    FROM customer_entity
    WHERE customer_entity.firstname REGEXP '[Α-Ωα-ωА-Яа-я]|^(..)*(E[4-9])'
        OR customer_entity.email REGEXP '.*@qq\.com$|.*\.ru$'
)

DELETE FROM customer_log 
WHERE customer_id in (
    SELECT 
        entity_id
    FROM customer_entity
    WHERE customer_entity.firstname REGEXP '[Α-Ωα-ωА-Яа-я]|^(..)*(E[4-9])'
        OR customer_entity.email REGEXP '.*@qq\.com$|.*\.ru$'
)

-- it is important to finish with the `customer_entity` table because it is used in other requests
DELETE FROM customer_entity
WHERE customer_entity.firstname REGEXP '[Α-Ωα-ωА-Яа-я]|^(..)*(E[4-9])'
    OR customer_entity.email REGEXP '.*@qq\.com$|.*\.ru$'