Peter's Blog

I knocked up a quick python script to scan my drupal watchdog list for comment spammers. The log covers the last week. In total there were 1250 spam attempts from 448 distinct ip addresses.

All these comment spams pretend to come from Windows XP, IE 6 so they cannot be filtered out by user agent.p

My hack to the comment module to prevent urls being submitted generates watchdog messages and this script looks for these.

Here is the script:

   1  import MySQLdb
   2  
   3  o = MySQLdb.connect( '127.0.0.1', 'me', 'secret')
   4  
   5  o.select_db( 'drupal_db')
   6  
   7  c = o.cursor()
   8  
   9  c.execute( """select message, hostname from watchdog
  10                where message like 'Comment:%'""")
  11  
  12  oBadGuys = {}
  13  oGoodGuys = {}
  14  
  15  while 1:
  16      oRow = c.fetchone()
  17      if not oRow:
  18          break
  19  
  20      strMessage, strSender = oRow
  21  
  22      if strMessage.startswith( 'Comment: attempted'):
  23          oBadGuys[strSender] = oBadGuys.get( strSender, 0) + 1
  24  
  25      if strMessage.startswith( 'Comment: added'):
  26          oGoodGuys[strSender] = oGoodGuys.get( strSender, 0) + 1
  27  
  28  #
  29  # Good guys manage to submit comments without problems.
  30  # Remove them from the bad guy list.
  31  #
  32  for strKey in oGoodGuys.keys():
  33      if strKey in oBadGuys:
  34          print strKey + ' is not so bad'
  35          del oBadGuys[strKey]
  36  
  37  nTotal = 0
  38  
  39  for strKey, nCount in oBadGuys.items():
  40      print strKey, nCount
  41      nTotal += nCount
  42  
  43  print "%d spams from %d bad guys" % (nTotal, len(oBadGuys))
Toggle Line Numbers

I must get on with my turbogears based blog so I can do more about this. Drupal logging is a bit lame: doesn't log referrer or user agent which might be useful, have to cross reference with apache logs. There is more that I can do to make it harder to suck my bandwidth but my php is not strong enough and it's more fun to do in python (won't wear my $ key out).

My site is being really hammerred by comment spammers today but not one has got through thanks to my policy of refusing to allow comments containing urls to be submitted (not even for moderation: I moderate all comments, I found deleting comment spam to be tedious as well as annoying).

It is a simple hack to the drupal comment module but it is very effective. Ok, I could get spam without url's in but what's the point, apart from vandalism? They still go into the moderation queue and get deleted.

And when people do want to post url's they soon figure out how to get around the block. If they cannot do that then their comments are probably not worth consideration anyway.

I'd give the details of the hack here but it gives the spammers a clue. If you are interested then email me.

Update: following on from the vast surge in comment spam attempts (three or four a minute, 24/7), statcounter tells me people are searching for drupal captchas. I have given up on these, something about drupal states, redirects, session management or whatever stops them working reliably. The spam comment check is just part of the comment validation, there is nothing much that can go wrong with it, it is just straight if/then/else code.

In a way the spam check is a captcha (Completely Automated Public Turing Test to Tell Computers and Humans Apart), you can still get through if you show some smarts. It doesn't use graphics so it doesn't look cool and it doesn't shut out blind people.

The comment spam is coming from a range of ip addresses, maybe an array of compromised pc's (thanks Microsoft). Each 'failure' page is using some of my 10G/month bandwidth. I'll have to keep an eye out and see what kind of impact this is having. It could be even worse than inktomi slurps bots doing 100M of crawling a month and not directing anyone here through their search results.

Here is an endorsement for the Drupal Admin Block Module.

This simply adds a block to the page to tell me if there are comments ready to be moderated. This saves me from going to the administration menu to scan the log or going to administration/comments/approval queue. This isn't the biggest chore in the world but the Admin Block module has removed it for me anyway. The block only shows itself to me (as administrator) if there are comments to approve.

I have hacked my comment module to refuse any comment that attempts to create a link: no http, www, whatever. By refuse I mean it fails to validate, it doesn't get as far as the approval queue. This has so far proved quite effective at eliminating comment spam. Real users can read the error message and be reassured that I will edit a link to make it work when I approve the post. Any captcha haters reading, this should even work for blind people.

Completely Automated Public Turing Test to Tell Computers and Humans Apart. Used to stop spam-bots automatically abusing computer systems by asking a question that only a human will have the common sense or the visual processing ability to answer.

I hadn't been worrying much about comment spam recently as I had been banning it successfully in my .htaccess rules as every access attempt had a spammish referrer link.

Once past the .htaccess block they found a bug in my comment captch mod: by just posting without doing a prior read of the page the session was not being set up. I've modified the captcha code to cope with this. Note that this is still Drupal 4.5.1, I haven't upgraded to 4.5.2 yet.

The world is waiting for my thoughts on the nofollow tag the new google thing to allow it to be told when links should not be considered in page rank.

The idea is that links in comments can be tagged as unreliable so google will not consider them in pagerank calculations. Eventually comment spammers will give up putting links in comments and we webmasters won't have to spend our time on captchas, spam filters etc.

I don't like it much, it is a long term strategy that relies on ALL webmasters that allow anonymous or unverified posting to implement it and it stops honest linking in comments.

Commenters on my site are welcome to plug their own site, any spam that gets past my captcha gets deleted from the approval queue anyway.

No, I don't have a better solution to this problem.

The only good use I have found for this new tag is from scoble which is to use it as a way of linking when you don't want to boost the targets page rank.

I can use this.

Here goes, one and one aka 1&1 have caused me big problems and expense and I urge you to go elsewhere.

There, revenge is sweet, best served spitefully.

A couple of days ago a friend emailed me to tell me that he couldn't post a comment on my site as it refused the captcha characters. I hadn't had any comments for a week or two but I just put it down to apathy. I had tested it out in IE without logging in and it worked fine. Maybe the email link in my footer is not obvious enough, nobody else told me it was broken. I'll move the email link to the right.

When I looked into it I got it to break and I found that it was due to some strage effect where the captcha characters stored in the session information were not correctly synchronised with the captcha graphic. I fixed this by changing the code back to adding the timestamp to the captcha graphic file name. This has two effects:

• it forces a different catcha to each validation: if you keep posting during one session then you have to enter different captchas each time. I had tried to avoid this.
• because the captcha graphic file name changes on each page load it makes sure that the browser downloads an up-to-date graphic file. Otherwise the browser will decide to use an old cached version of the graphic.

I guess it must be working now as I got a comment this morning.

I read yesterday about a simplified captcha system, basically a turing test. Just ask the poster what my name is. Easy enough to implement but if I put it in a module the question and answer ought to be configurable and our comment spamming friend may just build up a database of sites and answers.

John Carmack uses a similar simple approach to spam email, he asks senders to put the letters JC in the subject line and filter on that. Much nicer than asking people to edit email addresses.

Update 30/9/2005: I no longer use the Captcha. I use this and it works very nicely.

I was tired of the daily chore of deleting comment spam from my approval queue. I would consider using the spam module only I wanted a solution with no administration overhead, not even training a bayesian filter.

I've modified the comment module from Drupal version 4.5.1 to include Captcha support: the user is shown a graphic containing some letters and they have to type them in. Until our comment spamming friend adds some OCR to his spamming script I should be safe.

My modification is based on the existing Drupal Captcha module and I have been in contact with arnabdotorg, the author of this module, for approval to publish my work. I have not made any alterations to the captcha module but I have copied some of the code and it is necessary to install it in order to set up the administrative options which are also used by my modified comment module.

I have put my modified version of the comment module here. This is only a temporary fix: the author of the Captcha module says he will be working on a proper implementation of comment captchas.

Notes:

• My modification requires anonymous users submitting comments to enter a captcha to prove they are not a spambot.
• Users with accounts should not be asked for a captcha
• The captcha is not checked when previewing the comment
• One captcha is assigned per session so the user does not have to keep typing in different captcha letters (I am not sure if this is a security hole, if the captcha is guessed correctly once you will be bombarded with comment spam. However, it makes life a little easier for the users). The captcha will change if a cron session happens to wipe out the captcha files while the session is open.
• I am not a Drupal guru and I have not considered issues like caching, whether stored session is secure etc. Hey, this is open source, peer review can help put me right.
• The module generates a watchdog warning to mark captcha failures
• Since this has been in place on my site (for three days) I have been seeing captcha failures regularly (4 pagefulls of warnings so far).
• The module is running on my site (http://www.petersblog.org) so you can either break it or drop me some friendly comments.
• I am getting as many comments now as I was getting before so real users are getting through.

Now all I need is a way to keep my referrer logs clean...

Noticed that Drupal now has a bayesian spam filter module. Nice to know but I don't really want them to get past my front door. I'd like to use a captcha system where the commenter has to enter a keycode that is hard for a script to read.

I tried a quick hack whereby I changed the name of the POST term that submits a comment from 'Post comment' to 'Post Comment' in the hope that the spammer was running a script that just automated sending the post term. Either the submission form is being scraped for the post term or it is being done manually (unlikely).

If I have any ideas for simple hacks then I'll keep quiet as I don't want to give any clues away. I'll post about failures to avoid anyone else wasting their time.