How To Stop Forum Spam

How To Stop Forum Spam and Take Back Control of Your Forum

If one thing sucks the enthusiasm out of site Administrators, it’s the never ending battle against spammers. I say battle as effect of forum spam are truly unwarranted and often create long lasting problem like email blacklisting, spam posts and can even slow the speed of your forum.

It’s important to understand the techniques spammers use and know a bit of why spam exists to be able to effectively prevent it. A forum can be attacked with both automated scripts (aka “Bots”) and/or Human “Bots” as well. Yes, a “Bot” could be human but more times than not they simply bypass registration security and go on something like cruise control, for lack of a better way to describe it. What every site owner must realize is just as forum security evolves to combat spam, spammers are also evolving in their methods in order to bypass new security measures. It’s a constant battle.

Why are there “Spambots”? Well that’s simple. For the most part it is to promote their content or links on other sites, with high traffic and page rank being the most valued targets. So if your forum is large , you’d better believe there’s a target on it.

What we’re trying to do here is give you, the site owner needed information and proven methods to help battle the onslaught of spam on their site. Hopefully you’ll find this interesting and informative and we hope to hear back on your spam stopping techniques.
____________________

*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. We’ll try to identify conflicts in advance and mark them w/ an  *asterix indicating that you should not use two w/ a * at the same time.

Legend:
Blue *’s indicates mods that are similar in functionality and should not be used in conjunction with each other.

Anti-Spam Modifications:

Recently,  many forum owners have seen an increase in spam registrations and in some rare cases the registration page is being hit so hard it’s almost like a denial of service attack. If this sounds familiar try this mod from BOP5:

Be creative in your prevention methods! Here’s a list of methods, utilities and modifications you could possibly use in helping cleanup if a spammer does hits:

* 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 that’s proven quite effective is banning spammers using the hourly cleanup cron job or cleanup.php by adding in code similar to the below. PLEASE NOTE this method is based on custom profile fields and requires you to manually monitor spam to determine patterns and repeated entries that make no sense and plainly labels the users as spammers. This does not remove posts; 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

Using data entered by spambot in the default fields can be used against them however it’s not as effective as utilizing 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 to go 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 that you need to study new registrations and develop your own ways to identify the spammers using the default and custom profile fields before this will work effectively.

*Please Note: This can affect your users in such a way it might be a complete disaster. If you are not comfortable with how this works (sql queries in particular), setup a test site and test your changes first. You can clone your live site and place it in a /testvb folder on the server (htaccess protect it per the license agreement) this will keep you from slipping and causing issues 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 (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
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 tells 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 it seems a common mistake bot scripts to put Man or Woman data in the Biography field.

Now for the != parts near the bottom. This is required when you close out your comparing two fields using a like and if they were both empty it could cause issues as without the != if both are empty and the query moves them, possibly identifiying a normal user as a spammer. Not a good thing, so be careful when creating your own query!

If your are banning the user, go ahead and update their user title as well. This query compliments the first 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 to add these codes? Makes sure scheduled tasks are enabled then edit the file cleanup.php You’ll find it in the /includes/cron/ folder. 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 != ”
“);

Session Table Issues?
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 (OR the same IP address for countless users) 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’
“);

To delete the users session based on IP use:

PHP Code:

$vbulletin->db->query_write(”
DELETE FROM ” . TABLE_PREFIX . “session
WHERE host = ‘127.0.0.1’
“);

^ Replace the 127.0.0.1 with the problematic users IP address.

*Yes you can truncate the session table HOWEVER that is not advised, if you do truncate then it simply kicks EVERYONE off the site all at once and they must relogin and also navigate back to the page they were on prior.

**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 vBulletin community members:

  • Lynne
  • Last Superman

default
Post Written by
Greg Middlesworth is the owner of URLjet.

2 Comments

  1. Collette says:

    Heya i’m for the first time here. I found this board and I find It truly useful
    & it helped me out a lot. I hope to give something back and aid others like you helped me.

Leave A Reply


CommentLuv badge

%d bloggers like this: