caching data locally

Hi,

SFS has tens of millions of data points. I don't think caching their entire database is feasible, if it is even available for download (which I think it is not).

Sorry.

Hi,

Now that this has been mentioned ... I vaguely remember looking into this a long time ago, but didn't implement local data because there was some sort of issue.

If I remember correctly, it's because the files available for download do not contain the entire database.

Looking at their downloads page, they only make the last 365 days of data available ... which is not the full set of data.

One way to do it is to use DNS, such as described at http://en.wikipedia.org/wiki/DNSBL. This is typically used for e-mail spammers, but would be fine for forum spammers. DNS is a very lightweight protocol and the results are cached at local servers, so it's much better for performance than HTTP. You can even download the whole database with a zone transfer.

It looks like the Stop Forum Spam folks have already implemented this. See http://www.stopforumspam.com/forum/t793-Blacklist.

Also, you don't have to cache the whole list.

It would be quite feasible to make a dynamic local cached lookup table. Every time the module fetches results (good or bad), it caches them in a local table. The module could then check the table to see what it finds there and possibly avoid the http request. With proper indexing, this could be quite fast (especially if the IP address is stored as an integer, not a string).

Drupal's cron mechanism could periodically clean up old records. Caching them for just a day would speed things up a lot and also reduce the load on the SFS server. The size of the table could vary greatly, depending on how many unique visitors the site gets.

In fact, as I haven't looked at the code, I wouldn't be surprised if this feature already exists.

I just looked at ZB Block. It uses stopforumspam.com requests. It caches each result it gets in a local flat file, and checks that file before making the http request. Maybe it would be possible to borrow some of that code.

There is an active discussion related to it on SFS forum where I am actively discussing the issue. It will be helpful if some one from Drupal Team or the Developers of this plugin participates in that discussion.
http://www.stopforumspam.com/forum/viewtopic.php?id=5136&p=1

May be some one can come across a solution.

I've researched some solutions.

See http://dnsbl.tornevall.org/index.php?do=usage for instructions on how to look up stopforumspam IP addresses with DNS. Basically, to look up 46.165.208.107, just do a gethostbyname() on 107.208.165.46.dnsbl.tornevall.org. In this case, the result is 127.0.0.67. Since the 64 bit (1 + 2 + 64 = 67) in the last octet is set, it's an abusive host and can be blocked.

Also, it is possible to just download the data as a (compressed) CSV file. The data is broken down into individual files in various ways, including incremental updates. You can see the lists at http://www.stopforumspam.com/downloads/. This module could certainly set up a daily cron job to download the files, parse them, and dump them in the database. Then, instead of doing http lookups, it could just consult the database.

Depending on which file you download, it can be as small as 40K. If you get the full records for 30 days of e-mail addresses, it's 12 MB, but it downloads in a few seconds to a fast site.

If you store the IP addresses in the DB as indexed 32-bit numbers, the lookup can be very fast. Account names and e-mail addresses would naturally be slower...but stopforumspam.com has to do the lookup anyway if you do an http query, so you might as well do it locally.

I've created a patch that uses the Drupal cache API to save individual responses from stopforumspam.com.
You can turn caching on or off and can configure how long the responses are cached for.

Note: It caches the results for the email, username and IP address separately and will not make a remote query if any of these cached results identify the user as a spammer.

i.e.
You make a query with email=foo@abc.com&ip=123.1.1.1 which results in the ip check exceeding your threshold.
You then check email=bar@xyz.com&ip=123.1.1.1.
Checking the cache first will find ip=123.1.1.1 is blacklisted, so it will not make a call to SFS to test email=bar@xyz.com

@w_a_mozart: Sorry, I've been really busy and still am. I'm not sure when I'll get a chance to test out your patch.

Patch need to be updated according to current dev version.

Also, there should be a lot of cache data, so I suggest to disable cache by default.

I'd like to open discussion back up about the caching of the responses from the StopForumSpam API.

Caching of a service response like this is almost a no-brainer. We should implement drupal_static caching as well as utilize a cache bucket for the responses. In addition to this, the API sends up to three distinct values to the API to identify a potential spammer: (handle, email, IP). The caching should try to serve a response for the specific API call if it exists in the cache, or it should try and look through the stored cache results to identify a match on any one of these potential identifiers.

The existing patch seems to tackle the checking of the cache values before each call to spambot_sfs_request(). Is it best practice to do it in that manner or would it make more sense to add the caching directly to that function? I favor the latter as caching is always handled by default at that point.

We ultimately should be caching all responses by default for at least a sane minimum time, say 5m. Positive responses that confirm spam accounts should be cached by default because they are confirm bad behavior and also warrant storing the response for a lengthier amount of time. The responses which don't return a confirmation of a spammer should only last for that minimum lifetime as there could end up being more reports which support the case of a ban.

By adding caching by default, with a minimum time for false responses and a lengthier storage time for positive responses, we can effectively cut down on the number of times the remote API is called. Doing so will also speed up calls during cron and allow for a more responsive feeling function of this module.

I am willing to volunteer my own time to add this functionality to the module and I would really appreciate it if someone from the community would be able to review my plans and potentially my forthcoming patch(es). I'm open to suggestions but would like to get this functionality implemented shortly.

I have attached a patch which handles a large majority of what I described above. I started with some of the work from @w_a_mozart (largely for the settings form) and then implemented the caching checks within the spambot_sfs_request function. I added in my proposed new cache setting for a minimum lifetime (currently set at 5m) for any responses where the username/email doesn't appear.

On second thought, we may not need to implement drupal_static caching as we will have a local cache bin.

Committed to current dev.

Spam user registration blocking appears to fail (i.e. all accounts are always approved even if they match the configured SFS results threshold) when caching is enabled in the current 7.x-1.x-dev version. Possible related issue:
https://www.drupal.org/project/spambot/issues/3022164