• Registered: 2005-05-30
  • Posts: 10

Topic: Recipe for spam-avoidance?

Dear All,

While I was away on holiday my forum was totally trashed by spam and I have had to disable it.

There is plenty of information here about potential spam-avoidance methods.  In fact, there is rather too much information!  So I was wondering if anyone would like to post a "recipe" for spam avoidance that currently works well for them.  I want to make the minimum of changes necessary to get the desired effect.  I think that what I want is "captcha" verification, but I'd like to hear how well people find that works compared to the alternatives.  My server already has SpamAssassin running for email filtering; is there a way to get it to check forum posts too?

I also need a way to remove the recent spams, and the spam-sending users.  I am happy to do this from the postgresql command prompt as I speak SQL quite fluently (better than PHP anyway).  Is there any documentation about this, or a database schema?

Many thanks in advance for any suggestions.

Cheers,

Phil.

2 Reply by Ree

  • Ree
  • Member
  • Offline
  • From: SD, USA
  • Registered: 2005-09-27
  • Posts: 42

Re: Recipe for spam-avoidance?

My advice:  don't allow guest posting, require verification on all registrations (so anyone registering an account has to supply a valid email address), and run an anti-spam mod.  There are several out there.  I use the Akismet mod, which works pretty well so far.  Registration CAPTCHAs work but also shut out the vision-impaired, so I suggest using them only if nothing else works.

As for cleaning up the spam, try the Forum cleanup plugin.

Ree's Website

  • Registered: 2005-05-30
  • Posts: 10

Re: Recipe for spam-avoidance?

endecotp wrote:

I also need a way to remove the recent spams, and the spam-sending users.  I am happy to do this from the postgresql command prompt as I speak SQL quite fluently (better than PHP anyway).  Is there any documentation about this, or a database schema?

I've had a go at this and have quite quickly removed the spam from the database.

But it looks as if the "page 1,2,3....7,8,9" stuff has been cached somewhere, as has the "last post" information.

Can anyone suggest what I need to do to update this?

Thanks,

Phil.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Recipe for spam-avoidance?

update_forum function in include/functions.php should do the trick

  • Registered: 2005-05-30
  • Posts: 10

Re: Recipe for spam-avoidance?

Smartys wrote:

update_forum function in include/functions.php should do the trick

Thanks.  Please excuse me for knowing next-to-nothing about PHP, but what is the easiest way to run that?  Hopefully there is some way to do it from the command line.

Thanks,

Phil.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Recipe for spam-avoidance?

Easiest way?

define('PUN_ROOT', './');
require PUN_ROOT.'include/common.php';

$result = $db->query('SELECT id FROM '.$db->prefix.'forums') or error('Unable to fetch forum info.', __FILE__, __LINE__, $db->error());

while ($row = $db->fetch_row($result))
    update_forum($row[0]);

Upload that as a file and navigate to the proper URL. Delete it when you're done.

  • Registered: 2005-05-30
  • Posts: 10

Re: Recipe for spam-avoidance?

I tried that, and I just saw the contents of the file.
Guessing, I added a line at the start saying <?php and a line at the end saying ?>
Now I don't see any output when I load the page, but the forum still seems to have incorrect page numbers, "Replies" counts and other things.
Where is this cached data kept?  Is it all in the database, or is it in the filesystem?

Thanks for your help.

Phil.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Recipe for spam-avoidance?

Whoops, I forgot to add the <?php tongue

And aha, I know what you're talking about now. I thought you meant the number of posts etc in forums, not topics tongue
The information is stored in the database

  • Registered: 2005-05-30
  • Posts: 10

Re: Recipe for spam-avoidance?

Smartys wrote:

The information is stored in the database

Any "friendly" way to update it?

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Recipe for spam-avoidance?

endecotp wrote:
Smartys wrote:

The information is stored in the database

Any "friendly" way to update it?

http://punbb.org/download/plugins/AP_Forum_cleanup.zip
I think that should let you

  • Registered: 2005-05-30
  • Posts: 10

Re: Recipe for spam-avoidance?

OK, so it looks as if the PunBB database schema is not normalised.  For example, the topics relatation contains a count of the number of posts to that topic; this needs to be updated each time the topics relation is modified.  This is not something that they encourage in the database design textbooks!  Anyway, I have used the following to "fix" this de-normal data:

update forums set num_topics = (select count(*) from topics where forum_id=forums.id);
update forums set num_posts = (select count(*) from posts p join topics t on (p.topic_id=t.id) where forum_id=forums.id);
create temporary table last_posts as
   select p.topic_id, p.posted as last_post, last_post_id, p.poster as last_poster from
    (select topic_id, max(id) as last_post_id from posts group by topic_id) lp join posts p on(lp.last_post_id=p.id);
update topics set last_post=(select last_post from last_posts where topic_id=topics.id);
update topics set last_post_id=(select last_post_id from last_posts where topic_id=topics.id);
update topics set last_poster=(select last_poster from last_posts where topic_id=topics.id);
update topics set num_replies = (select count(*) from posts where topic_id=topics.id);

Notes:
- I updated forums.last_[post|poster|post_id] manually.
- This may not work properly when a topic has only one post in it.