Saturday, April 16, 2011

why does my phpBB3 application database table phpbb_search_wordmatch keeps growing in size.

Believe it or not, phpBB is one in among the rest of the open source applications which is being hacked/ spammed oftenly when it is not properly configured or updated frequently to the latest stable version. It's common to know that the posts, topics, comments section of the forum/ board is being attacked by the spammers or hackers to post their contents and advertisements. So do you think is there any other table that is being largely abused ? , yes, there is! and it's the search tables 'phpbb_search_wordmatch' and 'phpbb_search_wordlist' (Note: The table prefix phbb_ may change accordinly as per your prefix used during the installation).

The tables 'phpbb_search_wordmatch' and 'phpbb_search_wordlist' grows larger and larger in size and this will not only increase the size of the table but also abuses the server by generating slow queries and affects in the performance of thier servers for many of the databases hosted on the same database servers. So many a times the customer contact us saying that how did it happen?, how and who is increasing the size of the table phpbb_search_wordmatch by adding too many entries into these tables?, the answer for this question would be simple to say i.e "spam bots", yes again the "spam bots" . There are many spam bots which are also the web crawlers. The spam bots are nothing but the robots or a script which is defined with a set of instructions to *do* when the bots finds its target.

Forum spam-bots surf the web, looking for guest books, wikis, blogs, forums and other types of web forms which it can then use to submit bogus content. These often use OCR technology to bypass CAPTCHAs. Some spam messages are targeted towards readers and can involve techniques of target marketing or even phishing, making it hard to tell real posts from the bot generated ones. Other spam message are not meant to be read by humans, but are instead posted to increase the number of hyperlinks to a particular web site, to boost its search engine ranking (extracted from the wiki page).

By default the phpBB3 has the search functionality enabled for the guest users. If this search functionality is not configured properly then it will be abused by many of the spam bots as discussed above. Spam bots are created in the thought that there are many users who keep this functionality as default with or without the knowledege. So the spam bots are programmed in a such a way that it simply does the search as guest users in a loop.  It contains pairs of data of which posts include which words from the search_wordlist table. So this is the main reason for the tables 'phpbb_search_wordmatch' and 'phpbb_search_wordlist' grow in thier size generating slow queries which results the server performance to degrade.

Following are steps involved to prevent it from happening:

1. By increasing the user search flood intervals and the Common word threshold values from the phpBB administrative Panel->General->Search Settings.

2. The next step would be to disable the search functionality for the guest users by going to the phpBB3 administrative panel ->Users & Groups -> Groups permissions.

3. Go to phpBB Administrative panel -> Users & Groups-> Groups Permissions, choose 'Guests' from the drop down menu and hit submit button.

4. Click on the Advanced permissions link and then open the Misc tab. Set Never for the can 'search board option' and dont for get to apply the changes.

So you can delete all the affected rows from these tables and then apply the above said settings to prevent it happening in future again. Also note that you have taken the backup of your database or the desire tables. Also then do not forget to contact your hosting support and tell them the changes that your are done to happening again in future and your hosting company should be happy hearing about the same.