If you’re using the Q2A (Question2Answer) software to run a site, you’ll no doubt have struggled with spam. While there are a number of plugins (scroll down to “Anti-spam tools”) as well as built-in settings one can balance to combat spam occurring in the first place, as this software becomes more popular and supported by the day, there is simply always going to be spam that gets through.
The worst of this spam comes in the form of spam user registrations or fake accounts. More times than not, they won’t even post any spam either because the bot failed or because they’re laying dormant to spam at a later date. No matter the case, you’ll end up with thousands of these fake accounts (even if you’re requiring CAPTCHA entry and email validation, which I am). It’s inevitable.
These accounts are easy to spot, but manually deleting them one by one isn’t a practical solution. Also, there are no current built-in settings or plugins that I’m aware of that will handle bulk deletion. So, of course, you may have guessed it, we’ll need to go directly to the source, your MySql database.
Now, when it comes to running bulk SQL queries, we need a way to differentiate from legitimate users and the spam users in order cull the bad ones. As long as you haven’t disabled the points system we can fairly easily specify the bad accounts. Considering you’ll likely have blocked and deleted all the spam accounts that ever came along and actually posted spam as they came, what you’re left with is thousands of ghost accounts that never did anything and will have 0 points.
Targeting accounts with 0 points will allow us to eliminate 99.9% of spam accounts. The margin of error will be people who created accounts and never got around to asking or answering any questions or even making any comments, which in all honesty, should be accounts we can consider abandoned and want to get rid of anyway. However, one legitimate concern are users that just registered. We can partially deal with this by putting the site into maintenance mode, but there will always be some margin of error.
Step 1.
Navigate to phpMyAdmin in your hosting account.
Step 2.
In another browser window, go to your Q2A admin panel under General and check the box “Take site down for temporary maintenance” and save.
Step 3.
In phpMyAdmin, export a complete backup of your Q2A database (in case you have any issues and need to revert).
Step 4.
Run this SQL query on your db:
DELETE FROM qa_users
WHERE userid IN (
SELECT userid
FROM qa_userpoints
WHERE points=0
)Step 5.
Then run this query:
DELETE FROM qa_userpoints
WHERE points=0Step 6.
Switch back to your Q2A admin and under Stats, click “Recalculate User Points”.
Step 7.
Check your Users page which should now be cleaned of all those spam accounts and if everything is in working order, take the site out of maintenance mode and you’re done. Rinse and repeat every so often when the fake accounts build up again.
While this isn’t the prettiest solution, I’m not aware of any other alternative solution than the one I just created. If anyone needs any help or is aware of a better solution or a way to improve upon my solution, please comment below.