Remove Spam Content and Inactive Members from Your Database

While diagnosing possible causes for a Pligg site running slower than I would like, I came up with some SQL queries to perform some spring cleaning on a Pligg database. The following 3 queries will remove stories and comments flagged as spam, then remove inactive members who haven’t interacted with the site by submitting content or casting votes. I don’t suggest that everyone use these methods to clean up their database. Pligg doesn’t discard spammer data from your database by default so that the spammers can’t just register again on your site. We maintain their user information (email, username, IP address) so that we can use that information to block them.I have had requests in the past for a way to completely remove these users from the database for the purpose of shrinking the database size. The first query we are going to run will remove all stories flagged as spam. Start by navigating to phpMyAdmin’s SQL tab and then submit the following code:
DELETE FROM pligg_links
WHERE link_status = 'spam'
Next we will remove comments marked as spam from the database. We are running this and the previous query before the third one so that spammers will show up as inactive users in the third step. Once again, run this code from the SQL tab in phpMyAdmin.
DELETE FROM pligg_comments
WHERE comment_status = 'spam'
Finally, we will remove all inactive users from the website. An inactive member is one who does not have any stories, comments, or votes registered in the system. Sometimes we will refer to these members as “zombie” users.
DELETE FROM pligg_users
WHERE pligg_users.user_id NOT
IN (
SELECT link_author
FROM pligg_links
)
AND pligg_users.user_id NOT
IN (
SELECT comment_user_id
FROM pligg_comments
)
AND pligg_users.user_id NOT
IN (
SELECT vote_user_id
FROM pligg_votes
)
AND pligg_users.user_level <>'admin' 
AND pligg_users.user_level <>'god' 
AND pligg_users.user_level <>'moderator' 

8 thoughts on “Remove Spam Content and Inactive Members from Your Database

  1. I was actually wondering how else besides captcha, Akismet, and the math spam question I could possibly put a clamp down on these doggone spammers. They are making me sick! Thank you so much for providing this helpful & useful information on how to counter-attack the spams a’ ronis!

  2. It is amazing how the spammers load my site up. I’m thinking they have to be getting in through a back door somehow. Each day I wake up to 25 spam stories to moderate. Crazy!

  3. “Oh my goodness, that felt good! Thanks for sharing you spring cleaning tips.”

    - I totally agree with you StampNerd!

    A BIG THANK-YOU!! to whoever wrote this article. I just reduced our database by over 75% just by removing the spammy links & discarded links from the DB.

    BTW, if anyone wants to know how to delete the discarded links via phpmyadmin, it’s the same as the syntax for removing spammy links, but just change the “spam” to “discard” – our in-built function isn’t working at the moment, so this was a fantastic solution in the meantime – thanks again. Karen

  4. This is really useful.

    This should really become a part of the admin panel, preferably with an option to run it regularly in an automated fashion. For the time being, I have added all three scripts to a cron job that I run regularly.

    But like I previously mentioned already on the forums, there are a number of things that could be easily improved to make spamming much harder, even automated spamming.

    I have a number of additional ideas and suggestions regarding anti-spam measures, I think I am going to write them down and post them to the forum.

    Thanks

  5. Some good queries, i upgraded your queries and added some of my own.

    My goal is not to delete the users Themselves, is to mark them as spammers and delete everything else the related to them.

    DELETE FROM pligg_links WHERE link_status = ‘queued’;
    DELETE FROM pligg_links WHERE link_status = ‘discard’;
    DELETE FROM pligg_links WHERE link_status = ‘spam’;
    DELETE FROM pligg_comments WHERE comment_status = ‘spam’;

    UPDATE
    pligg_users
    SET
    user_level = ‘Spammer’,
    user_enabled = ’0′
    WHERE
    pligg_users.user_id NOT IN ( SELECT link_author FROM pligg_links )
    AND pligg_users.user_id NOT IN ( SELECT comment_user_id FROM pligg_comments )
    #AND pligg_users.user_id NOT IN ( SELECT vote_user_id FROM pligg_votes )
    AND pligg_users.user_level ‘god’
    AND pligg_users.user_level ‘admin’;

    DELETE pligg_votes.* FROM pligg_votes INNER JOIN pligg_users ON pligg_users.user_id = pligg_votes.vote_user_id
    WHERE pligg_users.user_level = ‘Spammer’;

Comments are closed.