Announcement

Collapse
No announcement yet.

Anti-Spam Methods and Resources

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

  • Anti-Spam Methods and Resources

    Spam can at times for many site Administrators become a never ending battle for months on end unless you implement ways to counter-act such... well perhaps we should call them "attacks" on your forum because they are truly unwarranted by everyone else other than the one responsible whether it be a human spammer or a spambot.

    Now some of you are thinking well what are we up against? Over the years I've noticed that sites can have both automated script derived "Bots" and/or Human "Bots" as well. Some may be confused by the fact a "Bot" could be human but more times than most they simply bypass the registration if any semi-decent security exist and then place it on "auto-pilot" if I had to describe it. What every site owner should realize is just like security on websites evolving because of spam, spam in-turn also evolves because we have both sides striving to beat the previous version of the other in a sense. Why are there "Spambots" well that's simple, there's pretty much various reasons for them but overall it's to promote and "spam" their content or links and high traffic sites are the primary targets so if your forum is large you can bet your bottom dollar they are trying to get in or have already!

    The focus of this article will be to provide some very useful information and methods to help you counteract the constant issue countless forum owners encounter with spam on their site. I would like to encourage everyone to post their methods and ideas on how to fight spam and I'll add each one to the original post just below thereby helping many make the changes required to give them a break for once!

    ____________________

    *Please note that not all of these modifications or methods listed will work for you, some will work better than others, and some in combination will work best instead of just one being utilized. I'll also do my best to ensure that any combinations resulting in a conflict are identified in advance and marked w/ a *asterix therefor you should not implement two w/ a * at any given time.

    Legend:
    Blue *'s indicates these mods are similar in functionality and should not be used in conjunction with each other only one at a time.


    Anti-Spam Modifications:




    Be creative in your prevention methods! Here's a list of methods, utilities and modifications you could possibly benefit from with some helping cleanup if a spammer hits:



    *Please note that Paul M recently added in a new modification prefix "Anti-Spam Options" so as new modifications are released, if the author chooses the prefix correctly you will start to notice more listed here for example: http://www.vbulletin.org/forum/forum...i-Spam+Options



    Methods:



    Some vBulletin 3.x mods will work on vBulletin 4.x, here's a article by BirdofPrey5 that list quite a few compatible modifications.

    Another Method I have come up with has proven to be quite effective for myself and a few others I work for. Here is a example of how to ban spammers using the hourly cleanup cron job or cleanup.php by adding in code similar to the below HOWEVER please note this method is based on custom profile fields and requires you to manually monitor spam to determine it's patterns and repeated entries that make no sense plainly labeling the users as spammers. This method does not remove posts or anything of that nature it only bans the spammer to prevent further activity.

    The default profile fields in vBulletin are:
    1. Biography
    2. Location
    3. Interests
    4. Occupation



    You have the ability to create new profile fields via AdminCP > User Profile Fields > User Profile Field Manager
    Example: http://www.yoursite.com/forum/adminc....php?do=modify

    Using what a spambot enters into the few default fields can be used against them however it's not nearly as effective as utilizing more custom fields to help determine a pattern and fight against it! Here are descriptions of the default and other (custom) fields in the query shown so you can see how I'm going about this:
    • user.username = The members username on the forum.
    • userfield.field1 = Biography
    • userfield.field2 = Location
    • userfield.field3 = Interest
    • userfield.field4 = Occupation
    • userfield.field5 = A Custom field where you required them to enter in let's use "Youtube Profile URL" as our example here and you used it in some creative way to show a image/link to their Youtube profile below their avatars in posts and profile or other etc etc.
    • userfield.field6 = A Custom field where you required them to enter in let's use "Facebook Profile URL" as our example here and you used it in some creative way to show a image/link to their Facebook profile below their avatars in posts and profile or other etc etc.
    • userfield.field7 = A Custom field where you required them to enter in let's use "Twitter Profile URL" as our example here and you used it in some creative way to show a image/link to their Twitter profile below their avatars in posts and profile or other etc etc.
    • userfield.field8 = A Custom field where you required them to enter in let's use "LinkedIn Profile URL" as our example here and you used it in some creative way to show a image/link to their LinkedIn profile below their avatars in posts and profile or other etc etc.



    The key thing to note here is the fact you need to study new registrations and develop your own ways to identify the spammers using default and custom profile fields before a method like this will work effectively.

    *Please Note: This can affect your users in such a way it might be a complete disaster to sort it all therefor I recommend that novice forum owners not familiar with how this all works (sql queries in particular) to setup a test site and use that to test your changes on, you can easily clone your existing site and place it in a /testvb folder on your server (You need to .htaccess protect it per the license agreement) this way you don't slip up and cause a issue on your LIVE/Production site.

    PHP Code:
    // Spam Management - Move all spammers based on profile fields to usergroup 8 i.e. Banned Users
    $vbulletin->db->query_write("UPDATE " TABLE_PREFIX "user AS user
    LEFT JOIN " 
    TABLE_PREFIX "userfield AS userfield
    ON user.userid = userfield.userid
    SET usergroupid = '8'
    WHERE userfield.field7 LIKE user.username
    AND userfield.field8 LIKE '%12345%'
    AND userfield.field3 != ''
    AND userfield.field5 != ''
    AND userfield.field7 != ''
    AND userfield.field3 LIKE userfield.field5
    AND userfield.field5 LIKE userfield.field7
    "
    ); 

    Now we interpret the above query as:

    Update the user table and join the user table with the userfield table (these tables are separate, we join so we can compare and execute what we want to do) and set the users usergroupid to 8 (which is the Banned usergroup by default) where their Twitter Profile URL matches their Username
    AND (where) their LinkedIn Profile URL contains something like 12345 in any part of the field (why we use the %'s on each end so it reads the field and if for example they entered in 123456 it catches that because it contains 12345 - Also you may want to note that no one's LinkedIn profile is going to be 123456 i.e. http://www.linkedin.com/in/123456 well minus the one silly man who did just that "Erik Hammerquist" )
    AND (where) their Interests is not empty
    AND (where) their Youtube Profile URL is not empty
    AND (where) their Twitter Profile URL is not empty
    AND (where) their Interests is like thier Youtube Profile URL
    AND (where) their Youtube Profile URL is like their Twitter Profile URL

    Logic and common sense tell you that, if you made the custom profile fields and use them in certain ways... that http://www.linkedin.com/in/123456 or http://www.facebook.com/123456 (even though that will redirect to http://www.facebook.com/ohmycarling the user in question would never enter in /123456 he/she would enter in /ohmycarling see my point?) and when you notice the word Man or Woman in the code shown below, well did you know the most common mistake a bot script makes is to fill in "Man" or "Woman" into the Biography field? I see it all the time and not even language differences cause someone to enter that in - Think about it!

    Now when we get into the != parts near the bottom this is required because when you close it out your comparing two fields using a like and if they were both empty it could cause issues because without the != if both are empty it will move them and possibly identify a normal user as a spammer and we certainly don't want to ban a innocent now do we? Certainly not so in other words be careful when creating your own query!

    If we are banning the user, let's go ahead and update their user title as well, this query compliments the one above:

    PHP Code:
    $vbulletin->db->query_write("UPDATE " TABLE_PREFIX "user AS user
    LEFT JOIN " 
    TABLE_PREFIX "userfield AS userfield
    ON user.userid = userfield.userid
    SET usertitle = 'Banned'
    WHERE userfield.field7 LIKE user.username
    AND userfield.field8 LIKE '%12345%'
    AND userfield.field3 != ''
    AND userfield.field5 != ''
    AND userfield.field7 != ''
    AND userfield.field3 LIKE userfield.field5
    AND userfield.field5 LIKE userfield.field7
    "
    ); 
    Where can you add these codes? Ensure your scheduled tasks are enabled then edit the file cleanup.php which is located in the /includes/cron/ folder and add your version of the query just below:

    PHP Code:
    // ########################################################################
    // ######################### START MAIN SCRIPT ############################
    // ######################################################################## 
    Save the file and upload, when the scheduled task next runs if any accounts match your custom query they will be moved into the banned usergroup.

    **To Test a query beforehand you can use SELECT for example here is a query to show you the results BEFORE actually running a query:

    Code:
    SELECT *
    FROM vb_user AS user
    LEFT JOIN vb_userfield AS userfield
    ON user.userid = userfield.userid
    SET usergroupid = '8'
    WHERE userfield.field7 LIKE user.username
    AND userfield.field8 LIKE '%12345%'
    AND userfield.field3 != ''
    AND userfield.field5 != ''
    AND userfield.field7 != ''
    AND userfield.field3 LIKE userfield.field5
    AND userfield.field5 LIKE userfield.field7;
    *Prefix added, remove if you are not using one

    WARNING: TEST ALL QUERIES BEFORE YOU EXECUTE THEM! Do not perform any query on your database without doing a database backup and without knowing how to use it if required!

    Here are some links provided by Lynne to help you perform a backup:




    Now let's see another example...
    You try to see how I used the logic here:

    PHP Code:
    $vbulletin->db->query_write("UPDATE " TABLE_PREFIX "user AS user
    LEFT JOIN " 
    TABLE_PREFIX "userfield AS userfield
    ON user.userid = userfield.userid
    SET usergroupid = '8'
    WHERE userfield.field1 = 'Man' OR userfield.field1 = 'Woman' 
    AND userfield.field6 = userfield.field7
    AND userfield.field3 = userfield.field7
    AND userfield.field8 = '123456'
    AND userfield.field3 != ''
    AND userfield.field6 != ''
    AND userfield.field7 != ''
    "
    ); 

    The query for usertitle update:

    PHP Code:
    $vbulletin->db->query_write("UPDATE " TABLE_PREFIX "user AS user
    LEFT JOIN " 
    TABLE_PREFIX "userfield AS userfield
    ON user.userid = userfield.userid
    SET usertitle = 'Banned'
    WHERE userfield.field1 = 'Man' OR userfield.field1 = 'Woman' 
    AND userfield.field6 = userfield.field7
    AND userfield.field3 = userfield.field7
    AND userfield.field8 = '123456'
    AND userfield.field3 != ''
    AND userfield.field6 != ''
    AND userfield.field7 != ''
    "
    ); 
    Here's a quick method if you happen to notice a sudden increase of the number online when nothing has warranted such behavior. Monitor your session table to see possible spammers online however a query like this is only effective if the bot logs in multiple times in which you will see different ip addresses (possibly hundreds) all as the same logged in user and accessing different locations as well (some even show as logging in again). With it added to your cleanup.php cron job it will interrupt their actions on the fly and upon refreshing your session table you'll notice quite a few less entries.

    PHP Code:
    $vbulletin->db->query_write("
    DELETE FROM " 
    TABLE_PREFIX "session 
    WHERE userid = '15065'
    "
    ); 
    **If you have any issues using queries that you develop it may be due to using a table prefix in your database (recommended actually) so the perfect example of what to add in is right above us i.e. " . TABLE_PREFIX . " should be pasted before any Table names in the queries otherwise you will receive a error and here is a example:

    PHP Code:
    $vbulletin->db->query_write("UPDATE " TABLE_PREFIX "user 
    As you can note, the code " . TABLE_PREFIX . " goes directly in front of the table name without spaces which then converts into the prefix you have listed in the config.php file!

    In the other queries shown that you can run via phpmyadmin directly you will need to modify them to add in the prefix so for example if you have a table prefix vb_ then it would resemble this:

    PHP Code:
    UPDATE vb_user AS user
    LEFT JOIN vb_userfield 
    AS userfield
    ON user
    .userid userfield.userid
    SET usergroupid 
    '8'
    WHERE userfield.field7 LIKE user.username
    AND userfield.field8 LIKE '%12345%'
    AND userfield.field3 != ''
    AND userfield.field5 != ''
    AND userfield.field7 != ''
    AND userfield.field3 LIKE userfield.field5
    AND userfield.field5 LIKE userfield.field7
    _________________________________________________

    Contributions to this article from these valued community members:
    • Lynne
    • Your name here - Make a contribution!



    Click image for larger version

Name:	ProfileFieldQuery.jpg
Views:	1
Size:	94.8 KB
ID:	3722543Click image for larger version

Name:	ProfileFieldAnti-SpamUserfields.jpg
Views:	1
Size:	97.8 KB
ID:	3722544Click image for larger version

Name:	ProfileFieldAnti-SpamUser.jpg
Views:	1
Size:	96.2 KB
ID:	3722545
    Last edited by TheLastSuperman; Fri 11 May '12, 9:20pm. Reason: Another update per vb.org article


    Former vBulletin Support Staff
    Hacked recently? See my blog post "Recovering a Hacked vBulletin Site".
    Thinking outside the box? Need modification support? Visit www.vBulletin.org and have at it!

  • #2
    I'd really appreciate it if staff here give this a good look over and if you have anything to add please post, I'll add it in and give you full credit - thanks!!


    Former vBulletin Support Staff
    Hacked recently? See my blog post "Recovering a Hacked vBulletin Site".
    Thinking outside the box? Need modification support? Visit www.vBulletin.org and have at it!

    Comment


    • #3
      Added info on how to test query beforehand using SELECT * and rephrased Warning message and made it stand out more.


      Former vBulletin Support Staff
      Hacked recently? See my blog post "Recovering a Hacked vBulletin Site".
      Thinking outside the box? Need modification support? Visit www.vBulletin.org and have at it!

      Comment


      • #4
        Hmm did this fall through the cracks?


        Former vBulletin Support Staff
        Hacked recently? See my blog post "Recovering a Hacked vBulletin Site".
        Thinking outside the box? Need modification support? Visit www.vBulletin.org and have at it!

        Comment

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