Deleting Spam user accounts from site with WooCommerce

  • Resolved Marshall Annis

    (@mannis82)


    9 years, 11 months ago

    On a site using WooCommerce I have a large number of user accounts that have been created, I am assuming through some sort of script/bot. I’m not really sure how they are being created, as the “Anyone can register” option is unchecked, and am investigating that further. In the mean time however I’d like to delete these spam accounts, without removing the real customer accounts.

    Looking at the usermeta table I’ve noticed that real customer accounts have a number of additional fields added by WooCommerce, such as shipping_first_name, that are not present on these spam accounts. So I can use that to differentiate the real customer accounts from the spam accounts.

    I’ve gotten as far as writing a SELECT query that (I think) is getting me all the records I want to delete:
    SELECT * FROM wp_usermeta JOIN wp_users ON (wp_usermeta.user_id = wp_users.ID) WHERE user_id NOT IN (SELECT um1.user_id FROM wp_usermeta um1 WHERE um1.meta_key = 'shipping_first_name')

    However I am not a SQL expert, and could use some help with a query to delete these records from the user and usermeta table. Trying a DELETE FROM wp_usermeta ... is throwing a SQL error, I believe because the query contains a sub-query.

    Can anyone offer any help on how to construct a query to remove the spam accounts, or let me know if I am going about this the wrong way? Also any insight into how these accounts are being created, and how to stop it would be great too. Thanks!

Viewing 1 replies (of 1 total)
  • Thread Starter Marshall Annis

    (@mannis82)

    I was able to find a solution by first copying the records I needed to delete to a temporary table and referencing that in my DELETE statement. The following seems to have accomplished what I needed:

    CREATE TABLE wp_usermeta_temp (umeta_id bigint(20), user_id bigint(20), meta_key varchar(255), meta_value longtext);

INSERT INTO wp_usermeta_temp
SELECT * FROM wp_usermeta WHERE user_id NOT IN ( SELECT um1.user_id FROM wp_usermeta um1 WHERE um1.meta_key = 'shipping_first_name');

DELETE FROM wp_usermeta WHERE wp_usermeta.umeta_id IN (SELECT umeta_id FROM wp_usermeta_temp);

DELETE FROM wp_users WHERE wp_users.ID IN (SELECT DISTINCT user_id FROM wp_usermeta_temp);

    Then deleting the temp table when this was done.

Viewing 1 replies (of 1 total)
  • The topic ‘Deleting Spam user accounts from site with WooCommerce’ is closed to new replies.

Tags