nop3.00 - SQL Script to delete bot created customers permanently.

Hello,

I'm trying to create a script that may be helpful to other store admins.  Here's the scenario:

What I do
Created a store without using reCaptcha on registration.

Consequences
6,000 bot created fake customers were created.

How you know they're bots?
Fake users have all the following identical properties.
- Phone Number: 123456
- "Create on" date and time = "Last activity"
- Names are almost all in this format: textone textone(1 or 2 cap letter) [example: MomeGuavasova MomeGuavasovaHP]
- Address 1 = Address2 = City

What I do to fix this mess
1. Add reCaptcha for registration page.
2. Permanently delete fake customers.

Script Help Needed
I understand that if I dlete a customer through nopcommerce admin, it simply does a soft delete.  However, since these are all fake accounts and they haven't been accessed, completely deleting them seems a better option in this scenario.

How can I write a SQL script that follows these criteria:

IF PhoneNumber = '123456'
Problem 1:
Even though they all show their phone number as '123456' I do not know where this is stored in DB since the [dbo].[Customer] for [BillingAddress_Id] and [ShippingAddress_Id] are both set to Null...

Assuming I can do the "if" above (if phone number = '123456).. Continuing with the script below

AND IF
From [dbo].[Customer] > [BillingAddress_Id] AND [ShippingAddress_Id] is null
AND IF
From [dbo].[Customer] > [CreatedOnUtc] = [LastActivityDateUtc]
AND IF
From [dbo].[Customer] > [CreatedOnUtc] < 2014-02-01 11:43:49.863 // Just to ensure new customer registering get a chance to sign up

THEN Take their ID
From [dbo].[Customer] > [id]

Then DELETE
[dbo].[Customer_CustomerRole_Mapping] where [Customer_Id] = [dbo].[Customer] > [id]
AND DELETE
From [dbo].[Customer] > [id]

Anyone can help turn this into a viable SQL script?

Thank you

This should get you started  (I don't know that you should be  checking for " [CreatedOnUtc] = [LastActivityDateUtc]", since the times could be off by fractions of seconds)

/*
You don't need separate delete for role mapping (or other related tables), because they have:
  FOREIGN KEY...
  ON DELETE CASCADE
I.e. deleting the Customer will also delete the related records in other tables.
The exception is the [GenericAttribute] table, which is also where the Phone is stored
*/

SELECT Id INTO #Temp
  FROM [Customer] c
 WHERE Id IN
   (
   select [EntityId]
     from [GenericAttribute] g 
    where g.[EntityId] = c.Id
      and g.[KeyGroup] = 'Customer'
      and g.[Key] = 'Phone'
      and g.[Value] = '123456'
   )
  AND [BillingAddress_Id] is null
  AND [ShippingAddress_Id] is null 
   
--SELECT * FROM Customer  WHERE Id in (SELECT Id from #Temp)

DELETE [dbo].[Customer]
 WHERE Id in (SELECT Id from #Temp)

DELETE [GenericAttribute]
 WHERE [EntityId] IN (SELECT Id from #Temp)
   AND [KeyGroup] = 'Customer'


DROP TABLE #Temp

(caveat emptor :)

Hi New York,

Thank you for this so much, it worked really great in deleting 48,000+ entries in the database. (just under 6,000 customers)

I did add the
AND [CreatedOnUtc] = [LastActivityDateUtc]

Simply because in my case, what I checked via management studio, they were the same down to the second.

Hope this helps others in cleaning up their DB.


~closed/solved/delivered~

Thank you New York!
Love the fact someone actually posted a solution rather than unuseful comments.

Worked perfectly on version 4.4 too!

You're welcome.  Here are some more

-- really long names are typically spammy, so be sure they really are before using this (or adjust the "30")
DELETE [Customer] 
 WHERE [Id] IN (select [EntityId] from genericattribute where [Key] = 'FirstName' and len(value) > 30)

-- 'google' often appears in Company 
DELETE [Customer] 
 WHERE [Id] IN (select [EntityId] from GenericAttribute g where [Key] = 'Company' and g.Value = 'google')


--...  do other deletes here, and then finally DELETE [GenericAttribute]

--[GenericAttribute] table - Delete if related to Customer and the customer no longer exists.
DELETE [GenericAttribute]
 WHERE [KeyGroup] = 'Customer'
   AND not exists (select 1 from Customer c where c.Id = EntityId)

See this too
https://www.nopcommerce.com/en/boards/topic/87269/spam-on-register-page-and-recaptcha-disappear#273909