I have a client who’s Drupal 7 site has been targeted by spam bots for a couple of years. They did not want confirmation emails or captchas, so this had to be managed the old-fashioned way – by hand. When the site started out, I installed and configured a honeypot module. Together with a fairly long time threshold for form submissions, this kept bot registrations at a minimum for a few months, but eventually the bots took over.
I have been looking for a module off and on for over a year that could help me get rid of thousands of bot accounts, with little success.
The bot accounts all share 2 characteristics:
- The first name and the last name contain the same values
- Once they found out that there is nothing to post or comment, they left and never returned.
The modules that I found solved one problem or another, but not mine. Some ended up sending out large numbers of emails to real users without asking me, like the user-expire module. I can’t warn you enough of this one. The advuser module looked promising, too, but would not let me combine two fields in a filter, only apply constant values against individual fields.
In the end, I opted to writing my own query. The problem with this approach is that we should not delete (user) nodes in a Drupal database because of the risk of missing ancillary data. For example, profile fields would remain orphaned if only a user row is deleted. So this required a hybrid approach. I have a query that I can run in a Pantheon MySQL shell from my development server.
select u.mail from users u inner join field_revision_field_first_name f on u.uid = f.entity_id inner join field_revision_field_last_name l on u.uid = l.entity_id where f.field_first_name_value = l.field_last_name_value
I then take the results of this query, format them in a text editor, and paste the list of email addresses into the userdelete module’s to-be-deleted field. This works safely and quickly for thousands of emails.
