Digests extension update

Yes, I have started work on the Digests extension at last. With the Smartfeed extension now in Beta, I felt I understood the 3.1 architecture well enough to tackle Digests, the more complex and popular of my two mods.

And it is moving forward rather predictably so far, thank goodness. I can copy and paste a lot of my code from the Digests mod, but much of it has to be retrofitted requiring lots of global find and replaces. But some code that used to work simply doesn’t anymore, and you discover these when you see error messages or when things don’t work right. So everything has to be carefully retested and given that there is a lot of business logic in the extension, it just takes time. Digests is definitely in the top 10% of extensions for code size and complexity. Anyhow, the screenshot shows the Edit Subscribers page in the Administration Control Panel, now moved to the Extensions tab. All the screens look pretty much the same. I removed a couple options that are now handled by the improved architecture.

Edit Subscribers
Edit Subscribers

(Note: the above image shows robots and search engines as subscribers. I changed their user status to member temporarily for my convenience of testing the pagination logic.)

It all looks so easy you are probably thinking the rest will be a breeze. But in some ways this is the easy part, which is why I started here. The User Control Panel interface has to be created, and this will be new since Smartfeed has no such interface. mail_digests.php will need to be recreated and it will have to be made to work with the new improved cron interface built into phpBB 3.1. However, a real cron job will still be needed to ensure digests go out hourly because if you dig into the internals of phpBB’s updated cron capability, it’s up to you to program a real cron job. Of course the documentation will need a major update as well.

I do expect that the migrator program will ingest all your digests settings from phpBB 3.0 so those will not have to be recreated.

So I am guessing I am 1-2 months from having a first cut of a development version for testing. If consulting picks up delays may get extended. Sorry it takes too long but just remember the price is right: it’s free. My consulting is not and when I have business I will prefer it doing this work for no charge.

Fixing phpBB spam registration problems

(Note: since this post is frequently read, I updated it for phpBB 3.2 on June 28, 2017 and August 1, 2019)

I am frequently sought out to help address issues with spam registrations on phpBB forums. The most typical spam problem I encounter is not spam posts, but spam registrations. The symptoms are lots of bogus usernames in your member list or inactive user list, often strings of random letters and numbers for the username, with a “website” listed in their profile that points to a spam site.

I am happy to fix these for you. You may wish to try some of the solutions below first. The solutions discussed apply principally to phpBB 3.0, 3.1 and 3.2.

Getting rid of existing spam registrations

Most of these “users” don’t bother to complete registration, simply want to leave a spam memberlist link and be gone. Consequently they show up as inactive users.

  • Inactive users can be deleted in the Administration Control Panel. ACP > Users and Groups > Inactive Users. Select the inactive users you believe to be spam registrations and in the little drop down box in the bottom right of the screen select Delete and press Submit. You may have to do this many times for many screens to get rid of them all. (Note the Mark All link at the bottom of the page. This can speed up things.) This is the safest approach.
  • You can also use the Prune Users function: ACP > Users and Groups > Prune users. A user that has not completed registration is not necessarily a spammer. Unfortunately, the Prune User function is not smart enough to be able to examine the profile website field to see if it contains data as this is often the key to filtering out likely spammers. However, if you prune a legitimate inactive user they can always come back to the board and register again. To remove these users it helps to look at your memberlist by date and see if you can figure out when the spam started. In the Prune users utility you can enter this date in the Joined field. Entering 0000-00-00 in the Last Active field essentially is the same as filtering inactive users only. You can select the option to delete posts for these users too but if they haven’t completed registration there should be no posts to delete.
  • You can globally delete these on the backend with SQL but beware: you may also delete legitimate new users that haven’t finished the registration process. Running any raw SQL statement is inherently risky, so backup the database first! Make sure you select the right table prefix (phpbb_ is shown in the examples) for the users table. Your config.php file contains your table prefix, which is usually “phpbb_” as well as the database you are using. If you have more than one application this will distinguish the one containing the data for your forum. You can use a tool (typically phpMyAdmin in your web host control panel) to issue SQL to delete these. Here’s an example of SQL for MySQL that will remove all inactive users with zero posts but with a website URL:
    • For phpBB 3.1 and 3.2:
delete from phpbb_users
 where user_id in
 (select user_id from (select u.user_id from phpbb_users u, phpbb_profile_fields_data p
 where u.user_id = p.user_id
 and user_posts = 0
 and user_type = 1
 and pf_phpbb_website <> '') as u)
    • For phpBB 3.0:
delete from phpbb_users
 where user_id in
 (select user_id from (select user_id from phpbb_users
 where user_posts = 0
 and user_type = 1
 and user_website <> '') as u)

Reducing spam registrations

Since most spam comes in the form of registrations and not spam posts, generally tightening up the registration process can reduce or eliminate these registrations. Many admins require administrator approval for each registration, but this becomes labor intensive. Most CAPTCHAs have been thoroughly hacked and are to be avoided. Here are some better alternatives:

  • Tighten up your spambot countermeasure to something more likely to work, like the question and answer one.
    • ACP > General > Board configuration > Spambot countermeasures
    • Under Available plug ins, select Q&A and press Configure
      • Create a question that is unique to the focus of your forum and won’t be guessed with a Google search. Create as many questions as you want. All must be successfully answered to complete registration.
      • Go back to Spambot countermeasures, select Q&A again and press Submit. This will change the countermeasure.
    • If this stops the spam, it’s probably safe to change it so the admin doesn’t have to approve every registration. An email verification is a good approach. ACP > Board configuration > User registration settings > Account activation > By user (email verification)
    • As a best practice or whenever you start to notice spam registrations again or quarterly change the registration question(s)
  • phpBB 3.2 supports Google’s new version of reCaptcha as a spambot countermeasure.
    • First go to the reCaptcha site and generate the public and private keys you will need for your domain.
    • ACP > General > Board configuration > Spambot countermeasures
    • Under Available plug ins, reCaptcha and press Configure
    • Enter the public and private keys from the reCaptcha site into the fields and press Submit.
    • Go back to Spambot countermeasures, select reCaptcha again and press Submit. This will change the countermeasure.

Some sites get hammered and even these steps are not enough. If this happens to you try these options:

  • phpBB 3.2 has an Akismet Anti-spam extension. Using it is not necessary free, but usually is for personal or club forums. The extension doesn’t cost anything but the Akismet service requests money depending on your usage. The extension also checks posts for spam.
  • If you don’t mind paying $8USD a year, my customers report 100% success with Cleantalk. You will have to download the modification or extension, create an account on Cleantalk and enter your registration key in phpBB. It’s worth the time and expense.
  • phpBB 3.1 and 3.2: Install the Stop Forum Spam extension. This checks the IP and some other information of the user registering or the guest poster against popular blacklists, and if there is a match they cannot post. It’s not 100% perfect so some legitimate people may not get through and it’s also possible some spam will get through.
  • phpBB 3.1: Install and use the Sortable Captcha extension
  • phpBB 3.0: Install the Advanced Block Mod. Warning: this is a very complex mod to install and configure correctly. You will need to point it to more updated blacklists. You may want to have me install it professionally.

I will cover how to remove spam posts in a future blog post.

Monthly work summaries

I removed the Recent Work page I created showing my monthly work summaries. Instead, I created posts after the fact for each month to show work for that month. As this information is more of a journal, using posts is  more appropriate. I’ll be posting these work summaries for the previous month near the start of the month.

All client information is removed to ensure their confidentiality.


Smartfeed extension version 3.0.1 (Beta) released, digest extension work to start

Smartfeed 3.0.1 User Interface

Business slowed a bit allowing me to update the Smartfeed extension under development. I’m labeling this version 3.0.1 as a Beta version as it seems to be stable and features seem to work as advertised. You can download it from my Smartfeed page. There are no new features but lots of code improvements have been made and some bugs found and addressed.

I feel I understand this well enough now to begin work on the Digests extension, which is good news for you suffering digests users.

October 2015 work summary

Less work than usual this month, in part because I was settling into a new house and that took precedence. Fortunately, work slowed down to give me time to do things I had to do anyhow, so no stress!

  • Painful lesson learned in incomplete backups. See this blog post. Geeky details: I tried to upgrade a forum with 1.4M posts from 3.0.11 to 3.1.6. Upon running database_update.php I encountered an error: Fatal error: Class ‘Symfony\Component\HttpKernel\DependencyInjection\Extension’ not found in /home/mattash/public_html/forum/phpbb/di/extension/core.php on line 25. I figured it was likely due to bad or inconsistent data in the client’s 3.0 database. So I installed the phpBB support toolkit and fixed issue with certain forums not appearing by running the left ID/right ID utility. I also removed some empty topics. I changed phpbb_users table to change language from “english” to “en” — it’s unclear how this got so nonstandard. I slowly upgraded from one micro version of 3.0 to another, from 3.0.11 to 3.0.14, hoping to mitigate risk. I tried again with a fresh install of 3.1.6 but same error occurred. I sought help on topic on phpbb.com but none of their suggestions worked. I tried a third time to do the conversion with a clean copy of 3.1.6 but same result. Could not restore database after 3.1 upgrade attempt. This was because the downloaded backups were not complete. I asked client to see if web host had a backup and to restore that. Eventually I recovered the database by patching together pieces of other databases and writing a program to create the missing topics. With a complete archive, for cost reasons the user decided to leave it as an archive and create a new 3.1 forum. Once it was created, I installed a bunch of extensions and moved his new forum into the web root. I installed the Board3 Portal extension on the new site and it is set to be the default for the site.
  • Ad hoc work for an existing client during October. Patched db.php to use a SQLServer CAST SQL statement because a SQL Server error occurred when trying to update a config_value for a row in the phpbb_config table due to a too small integer for the value that needed to be written. I made another patch to add a CAST statement when another function trigger a similar error. Enabling a new extension group caused an integer overflow again so I yet again added more code to use a CAST statement for SQLServer. I attended a Skype conference call with client and answered questions from his employee and on the forum. I documented a MCP screen overlay issue with a phpBB bug report. I put in an enhancement request on phpBB’s bug tracker to use the <video> tag for .mp4 files so plug ins would not be needed for these files. I fixed another SQLServer issue with integer overflow on Manage attachment link.
  • First I addressed an issue where phpBB could not connect to the database. The password in the config.php file apparently was no longer valid for the database and database username. So I created a new password in cPanel for database user and changed config.php to reflect the new password. Then I upgraded forum from 3.0.11 to 3.1.6, reinstalling the logo and using the ported prosilver_se style.
  • Upgraded a forum from 3.1.2 to 3.1.6.
  • Installed a beta version of the Advertising Management extension for a client. While I was in there I upgraded phpBB from 3.1.5 to 3.1.6. I later placed four ads in the beta Advertising Management extension since the user needed some help with HTML for the ads and added code to track clicks.
  • I ghost wrote an technical article.
  • I was paid for an hour of miscellaneous work over last few months plus placed a new ad in the top spot for new advertiser for the client.
  • Client was having a terrible spam problem. I first removed some spam users with zero posts and a listed website URL in profile, 444 users altogether using SQL. I later removed 6193 bogus topics by guest on the same forum, 976 bogus posts by guest and removed 33 orphaned posts, all with SQL. Everything is clean and shiny again, and spambot countermeasures and permissions were improved to reduce the likelihood of this happening again.
  • I upgraded plain prosilver forum from 3.0.x to 3.1.6. The database upgrade took a long time because of 300,000 users, most of them bogus. I also removed these inactive users from the database.
  • I upgraded my digests mod from 2.2.25 to 2.2.26 for a client to fix an error where an inactive user who had unsubscribed was getting a digest.
  • On a phpBB 2 forum, the unread and new private message counts were incorrect in the users table, triggering an error when trying to store a negative value for the number of saved messages when the column type did not allow it in MySQL. I did a number of queries, exports and imports to fix these values. I also added a real name statement next to username on the registration screen.