Comment Spam, Pinging, Spamblocklog, and database questions

[johncanary]

Hi,

I get a lot of spam lately. SPAM PROTECTOR does a good job.
However I looked at the database to empty the spamblocklog and,
... have a couple of questions, whether I understand the database
structure correctly.

Feedback from knowledgeable folks would be great.
Please, let me know, if statements of mine are true, correct it,
and answer things that I didn't "get".

I am sure your answers will be valuable to others than me, also.

Thanks in advance.
Yours John


*Database Table*

* comments
Holds comments that get either status approved or moderate, when
received.
Auto Rejected comments are not stored. Comments that are deleted
(e.g. during moderation) are deleted from this table.

* spamblocklog
Holds the log for comments or trackbacks that get either
rejected immediately or thrown into moderation.

* suppress
?? Not clear what those entries are coming from and are good
for?? What??


* references
Stores the links used in Blog entries.
Is this the table that determines, if a trackbeck has already
been sent to another blog ????
If I delete rows in that tables for a specific entry_id and
edit/save the entry with the Admin tool again, will those
trackbacks be sent again (sometimes there are conncection
timeouts - that way I could make sure the trackbacks are sent,
if I just send them again)?

If not, where are the trackbacks that have been sent stored??


* referrers
Stores the $_SERVER['HTTP_REFERER'] variable from people that
are browsing the blog, basically storing where they are coming
from to the blog.


Spam Protector Plugin
When moderating comments (with Spam Protector Plugin active) the
lists for the following get appended.

Wordfilter for URLs
Wordfilter for author names
Wordfilter for comment body

Where are the values stored?


Announce Plugin:
Where is stored which sites already have been pinged?



That's it. I hope it's not too much at once, but it would help me to
understand things better.

[judebert]

Whoo, that's a lot. I've been working with trackbacks and Spam Protector lately, I'll answer what I can and mark the rest for others.

I get a lot of spam lately.

Me too. I once rejected all trackbacks in my .htaccess, but I've re-enabled it because I'm becoming more active, and would like to network with other blogs. Forty spam trackbacks the same day. I have some very strong words for spammers. I won't post them here; I don't swear, but the resulting content is unsafe for the general public nonetheless. I'm very interested in eradicating spam forever.

Database tables:

* comments
Holds comments that get either status approved or moderate, when
received.
Auto Rejected comments are not stored. Comments that are deleted
(e.g. during moderation) are deleted from this table.

Exactly correct.

* spamblocklog
Holds the log for comments or trackbacks that get either
rejected immediately or thrown into moderation.

Also correct. However, the spamblocklog also holds information for Akismet-approved comments.

* suppress
?? Not clear what those entries are coming from and are good
for?? What??

This is an inaptly-named table for keeping track of referrers. There is one entry for each HTTP_REFERER used in each unique link to your blog. Approximately once every 100 visits, this table is cleaned up to remove bookmarked and manually-entered visits.

It does not actually suppress anything. I can find code where it's set, but not anything that actually uses it, even in a plugin.

* references
Stores the links used in Blog entries.
Is this the table that determines, if a trackbeck has already
been sent to another blog ????

Yes, this is where trackbacks are stored, as well as other links used in your entries. Yes, if you delete an row, then save the entry, that link will be trackbacked again. There is some magic in there, though; it stores the previous set of references in a configuration item, and may retry them, even if they've been deleted from the entry.

In fact, I can find links in this table that aren't actually in my entries any more.

* referrers
Stores the $_SERVER['HTTP_REFERER'] variable from people that
are browsing the blog, basically storing where they are coming
from to the blog.

Yes, along with "suppress". I don't know why we need two tables. suppress is just a list of the referrers, and it gets cleaned out periodically (anything with only 1 referrer is removed). referrers keeps a count, so you can see how many times a particular article was visited from any particular page. This allows us to make a list of "top referrers".

Spam Protector:
The wordfilter values are stored in the "config" database table, along with all the other plugin configuration values.

Announce Plugin:
Where is stored which sites already have been pinged?

This one I don't know. But, if you want to re-ping, just save the entry as draft and save it again as publish. Uncheck any sites you don't want pinged again.

I checked the plugin, and it doesn't appear that it's saved anywhere. Instead, the backend_publish hook is checked. This is only called on the initial press of the "Save" button; any Save while the entry is already published calls backend_update instead. Changing from draft to publish as described above does call backend_publish, and so the ping is performed again.

Well, I think that covers anything. Now we just wait for the corrections to roll in.

[johncanary]

Judebert

Thanks for the detailed answers. I don't think that much needs to be
corrected, though.

Seems that you dugg pretty deep into the code. The S9Y code is
very good structured, isn't it. One can learn much from that
style of programming.

Spam protector works just fine for me. No need so far to kick in
Askimet. So I didn't know that it writes 'approved' to spamblocklog.

I just changed my functions_comments.inc.php and
main language file to have the actual text of comments in
the email for people, who subscribe to an entry.

Thanks for your answers.

[judebert]

Happy to help out.

I just found out that the Spamblock plugin writes AKISMET_APPROVED to the log when a comment fails, and AKISMET_SPAM when it succeeds. Exactly backwards. Since the log is seldom used, it's not a huge problem, but I intend to fix it anyway.

I'm also investigating how to work with Akismet. I hope to allow Serendipity to fully participate with Akismet, sending false positive and false negative data back to the server so the filtering can be improved.

It sounds like you may be interested in doing this stuff, though. If so, please do! I've got more than enough work on my plate right now. Just let me know so I can move on to other things.

[johncanary]

Thanks for the clearification.

It sounds like you may be interested in doing this stuff, though. If so, please do! I've got more than enough work on my plate right now. Just let me know so I can move on to other things.

Everything works very fine for me. I don't do much more than little template tweaks for myself and can't afford to spend time on technical things at all at this time.
Sorry. I am fully engaged in commercial, non-technical projects.

Besides that my priorities are:

1. Finally upgrade my S9Y blogs to the latest 1.3 build to have full pingback functionality.
   Most new WordPress installations/updates have turned off trackbacks, which sucks big time, when you are doing Blog Carnivals (which I recommed for publicity!)
2. Optional: Enhance the core functionality that I can have custom meta description and meta keywords for each post. The plugin I know sets one for the entire Blog, which is kind of obsolete, becasue I can write those directly in the smarty template and save some overhead.
3. Optional: Work on the Blogroll Plugin to show an X number of excerpts of the most recent post of any Blog in the blogroll in the sidebar. (There is a WP plugin that I'd like to copy functionality-wise.)

If I ever get to it I'd love to share my results with the community here of course.

Stop. I just see there is finally a 1.3 Beta version out!

Since I need to setup a new Blog anyway. I just give that a try, even though it means that I ave to install it manually, becasue my host only offers releases.

Thanks again for your feedback.