How to fix list of registered customers, and subscribed users created by bot?

[CoyoteKG]

Hello,

I have two problems and I have not idea how to fix it. Problem is because these months some bot registered thousands of accounts, and subcribed to newsletter.

We did not noticed that because we did not used Newsletter function, and at this moment checkout on this site is disabled.

1. Registered customers. I can recognize what is bot job, because of Chinese letters. Something like this. domain is qq.com

Problem is because when I select more then 50 of those records and delete it, I'm getting Timeout error. And if i do that 20 by 20, it will take too many time for 4-5000 Customers .

I tried to find how to make SQL query for deleting it with condition where e-mail is *@qq.com, but I did not find solution.

Can you help me with advise how to delete all customers?

2. Problem is about Subscribers.

There is very difficult to recognize bot users.

Is there any option for unsubscribe them all and send them again subscribe mail?

Of course, my plan is to enable mail confirmation for users registration, and mail confirmation about subscription.

Also I will enable captcha

But how to fix this things now?

[info128]

Hello 

I read your post and I a have the same problem. Did Magento ever gave you a solution?

Please lt me know.

KInd regards Hans

[Ketika M]

Hi @CoyoteKG,

First of all for sending the confirmation & unsubscribe email of Newsletter, you need to configure it through admin side. Sharing the steps:

System-> Configuration ->Customers ->Newsletter [Here you can find the subscription options and you can set the configuration for newsletter]

Now for unsubscribing the all users at once, follow these steps:

Go to: Newsletter ->Newsletter Queue [Here you can see the list of all subscribed users]

Select all users from here and click on action. From here you can unsubscribe all users at once. Attaching screen shot for your reference:

Now, for deleting these type of customers. As you are able to find out those from email ID. So in admin side, when you go to the customer listing then you can search from the email ID.  So in email search field, just put this string "@qq.com" and search for it. 

Now select all users and from action [in right hand side of customer listing], you can delete all customers at once.

if issue solved,Click Kudos & Accept as Solution

[CoyoteKG]

Hi @Ketika M ,

not sure if you missed from my first post

"Problem is because when I select more then 50 of those records and delete it, I'm getting Timeout error. And if i do that 20 by 20, it will take too many time for 4-5000 Customers"

So that solution was not possible, even it was obvious. Unfortunately we deleted id 20 by 20, and that took a days...

I was hoping that someone would tell about some sql query which can do that...