Rogue Customer Accounts

Over the last year, osCommerce store owners have been hit with an increase in bots creating accounts.  Why do they do this?  They are trying to access site functions, such as wishlists, which may have vulnerabilities, that are only available to logged in customers. Before you worry it happens to all eCommerce solutions not just osCommerce.

You might tighten up your create_account.php  function by adding some form of human detection such as Google Recaptcha or Honeypot but how to clean up the accounts that have already been created?

If you have access to phpmyadmin via your hosting control panel you can run three simple scripts to remove all customers who have never placed an order. 

Customer data is held in three tables: customers,  customers_info and address_book.  First you delete the customers and once deleted delete their info. Once you have done that you can delete their addresses.

Delete Customer

Paste the following into the phpMyadmin sql tab:

delete from customers where customers_id not in (select customers_id from orders)

You can then simulate the query to see how many will get deleted.  If you are happy with what you see, then run it again and press Go.

Delete Customer Info

Paste the following into the phpMyadmin sql tab:

delete from customers_info where customers_info_id not in (select customers_id from customers)

Again,you can then simulate the query to see how many will get deleted.  It should be the same number as your customers. If it matches then run it again and press Go.

Delete Address

Paste the following into the phpMyadmin sql tab:

delete from address_book where customers_id not in (select customers_id from customers)

This time you should get a number greater or equal to the number of customers (given each customer has at least one address).  If you are happy with what you see, run it again and press Go.

Now all those accounts that never placed an order are gone!

Need help?  Contact us.