Spam is still a serious problem for web sites, mainly due to the fact that spam marketing is highly effective. A small percentage of sales of millions of e-mails sent out can generate substantial revenues. Content management systems require users to typically create an account before posting comments or participating in their forums. This is where spammers go first before posting their ad on your site.
The problem here is that you can have thousands of registered users that are actually spamming robots. If your web site is very active, it can be a time consuming process to manually delete the bad accounts. In Joomla, you can use SQL commands to quickly delete large numbers of users. I strong urge you to back up your database before running any of the following SQL commands. A mistake in typing can delete more than you had intended and you may damage your database. The SQL commands can be run using phpMyAdmin which most web hosting packages offer for users to administer their MySQL databases. You can always install phpMyAdmin in your hosting account if it wasn’t already installed there.
If you have never run a SQL command before, I recommend that you learn some basic SQL before attempting the procedures in this article. A good place to learn this is the SQL Tutorial at W3Schools.
Delete All Inactive Users
A user who has registered and never visited your site is probably a spammer. In the jos_users table, this is indicated by the value in the lastvisitDate field as 0000-00-00 00:00:00. You can delete all of the records with no last visit with following SQL command:
delete from jos_users where lastvisitDate=’0000-00-00 00:00:00′
Before you execute this command, you can run the following SQL command to see what records will be affected by the delete statement:
select * from jos_users where lastvisitDate=’0000-00-00 00:00:00′
Deleting Users Based on E-mail
Its highly unlikely that an English web site will have readers from Russia, where many spammers send their mail from. Their e-mail has a .ru extension. You can delete all the Russian accounts with the following command:
delete from jos_users where email like ‘%.ru’
The % is a wildcard character which includes all characters. The ‘%.ru’ will delete every email that has a .ru extension, such as bob@abc.ru and bill@def.ru. You can change the .ru to any other country that has excessive accounts on your site and is obviously not a user.
Again, if you want to preview what will be deleted, use the select command:
select * from jos_users where email like ‘%.ru’
Deleting All Users with One Word Names
Real people who register on your web site usually enter their first and last name which is saved in the name field in jos_users. The one word names here are probably spammers. You can delete them with the following command:
delete from jos_users where not (instr(name, ‘ ‘))
This command deletes all users that don’t have a space in their name, i.e. first name and last name. You need to be careful here because this may affect the admin account where the default name is Administrator. This is the first account in the jos_users table (ID = 62). If the name is Administrator, temporary assign two names here while you are running the SQL command to delete users.
Run the following command to view which users will be affected by the delete command:
select * from jos_users where not (instr(name, ‘ ‘))
The Never Ending War With Spam
These steps should eliminate most of the spam accounts in your database. You can apply the same techniques to other applications that you’ve installed in Joomla, i.e. in Ajacoom you would be deleting accounts in jos_acajoom_subscribers. After the spam accounts have been eliminated, view your user list regularly to maintain legitimate users on your web site.
thanks for these commands very useful. Don’t suppose you have any that:
a) lists all the user accounts with the same first and last name
b) deletes all the user accounts with the same first and last name
i’m looking for the same solution david. that method seems like it would be the most effective
I also go this error when trying to run your command:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’00:00:00” at line 1
Tried to run you commands in phpMYAdmin 3.4.11 with no joy on all commands.
Looked high and low to find a SQL command that works with no joy.
DARN!
I think your single-quotes got converted to smart quotes in a few of the SQL commands.
in new SQL you must use like this
DELETE FROM `z2rm8_users` WHERE `lastvisitDate` like ‘0000-00-00 00:00:00’
Very nice to find this. I’ve tweaked the lastVisitDate to give possible real users a 14 day window to activate their account by adding the following to the delete clause:
and dateDiff(registerDate, now()) < -14
You can also try the Quick User Cleaner Plugin for Joomla 3 and Joomla 4. It will delete not activated accounts as well as not visited and idle accounts.