Nov 12 2010
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 firstname.lastname@example.org and email@example.com. 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.