Announcement

Collapse
No announcement yet.

2 Security / Spam Questions

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 2 Security / Spam Questions

    I've had to resort to moderating members at this point, and using htaccess blocking. But I have 2 questions to "cleanup" the user mess that has been created...

    1. Is there a more advanced prune/move users feature (I can asked as vbulletin.org or if someone knows point me there please). I want to try and delete the obvious spam accounts, no point in them in my database at all, but prune/move is the only one with a checkbox option to select multiple users at once, instead of 1 by 1.

    2. Is there a way to remove the "URL" or "Homepage" feature on registration and profile?

    Thanks

  • #2
    I have a Spam Check mod that allows me and moderators to view the username, signature (if any), number of posts, join date, last activity, and homepage (if any), all on one page. It really helps in catching spammers who sign up with a spam sig or homepage, even if they make no posts. If you see a spammer, there is an infract button that will send them to a spammer usergroup (which you need to set up first). Let me know if you want me to send it to you.

    Also, to clean exisiting spammers out of the database, I have a few queries that work great.

    Use this to look for spam sigs for members who have less than 2 posts and they joined and have no activity after 1 day (most spammers). You can change the join date to whatever suits you, I like to start with the current year, and work backwords.

    SELECT U1.userid, U1.username, U1.homepage, U1.usergroupid, S1.signature
    FROM user AS U1, usertextfield AS S1
    WHERE U1.userid = S1.userid
    AND U1.joindate >1293840000
    AND ( U1.lastactivity - U1.joindate ) < 86400
    AND U1.posts < 2
    AND S1. signature NOT LIKE ''
    AND S1. signature IS NOT NULL
    ORDER BY U1.username

    This works to check homepages:
    SELECT userid, username, usergroupid, homepage, pmtotal, posts, joindate, lastactivity
    FROM user
    WHERE joindate >1293840000
    AND posts < 2
    AND homepage NOT LIKE ''


    Once you are satisfied that the query returns mostly or only spammers, you can move them to the spammer group with this Peter Bradley gem:

    DROP TEMPORARY TABLE IF EXISTS temp;
    CREATE TEMPORARY TABLE temp
    SELECT userid
    FROM (
    SELECT U1.userid, U1.username, U1.usergroupid, U1.homepage
    FROM user AS U1
    WHERE U1.joindate >1293840000
    AND U1.lastactivity - U1.joindate < 86400
    AND U1.posts < 2
    AND U1.homepage LIKE '%http%'
    ) a
    UNION
    SELECT userid
    FROM (
    SELECT U1.userid, U1.username, U1.usergroupid, S1.signature
    FROM user AS U1
    JOIN usertextfield AS S1 ON U1.userid = S1.userid
    WHERE U1.joindate >1293840000
    AND U1.lastactivity - U1.joindate < 86400
    AND U1.posts < 2
    AND S1. signature NOT LIKE ''
    AND S1. signature IS NOT NULL
    ) b;
    UPDATE user
    JOIN temp USING(userID)
    SET usergroupID=450
    WHERE usergroupID=2;


    Be sure to change the SET usergroupID=450 to whatever your spammer usergroup is.
    Once you have them in that user group, you can go to the ACP>Prune/Move users, and delete all users in the spammer group.

    Always back up your db before playing working with it, and I suggest doing this cleanup in increments. So if you get a six month batch of 600 spammers, that's enough for each Prune session. Don't try to do the whole db in one clip, or you could choke your server.

    Comment


    • #3
      Thanks for the info, is the "spam check" mod you reference actually called Spam check or something else? Do you have a link to the mod?

      Comment


      • #4
        Actually, there's no link to it yet, I paid one of the vb.org modders (one of the better ones, too!) a tidy sum to make it for me. I plan to ask him to release it as a mod when we finish the final version. It's simple really, it's just a variation of the New Members mod, but it lists the user signature (if they have one) and homepage (if they have one) on the member roster, making it super easy to detect spammers with spam sigs or homepages. There is also an option to "Infract" them into a spammer usergroup, allowing you to Move/Prune them off the db.
        Attached Files
        Last edited by Panzer Max; Mon 13th Jun '11, 8:51am. Reason: added screenshot

        Comment

        widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
        Working...
        X