Heal Your Church WebSite


Teaching, rebuking, correcting & training in righteous web design.

Turning Spam Pings into a HoneyPot

As the BrownPau reports, the Trackback Ping Spammers have been relentless – expending hours and energy figuring out new ways to waste our bandwidth and to destroy the blogosphere. So pardon me if I offer yet another post and yet another approach in an attempt to encourage these crooks to earn an honest living. This time taking a honeypot approach to any successfully posted trackback ping spam.

The Wikipedia defines a honeypot as:

… a trap set to detect or deflect attempts at unauthorized use of information systems …

The primary value of a honeypot is in the information it provides, which can be used for things such as detection, early warning and prediction, or awareness.

So here is my thinking, even though my .htaccess solutions are turning away hundreds of trackback attempts each day, one or two are sneaking through. That said, I’ve noticed that most of these attempts, successful or otherwise are from a somewhat finite set of anonymous/open proxies. Yes folks I’m talking about IP blocking, but not in the conventional sense.

Herding Cats
Now I know blocking IPs is like using vice-grips to contain Jello but remember, security is about layering counter-measures. So using some IP blocking along with some other techniques I’ve discussed ealier continues to harden this site, hopefully to the point of getting the spammer too go away — or at least go bother someone else.

Similarly, they come in bunches, usually early in the morning, or as in this evenings case, shortly after the start of the SuperBowl. It is for these same reasons, I suspect there will be a spam attack sometime tonight, it being Sunday night.

IP Mining
A few night back, when my site got hammered, I decided to clean my blog by directly manipulating the database — in this case using phpMyAdmin. My first thought was to generate the names of the offending referrers so I could amend my .htaccess file using the following, rather inefficient but gets-the-job-done SQL query:

SELECT DISTINCT x.tbping_blog_name
FROM mt_tbping AS x, mt_tbping AS y
WHERE x.tbping_ip = y.tbping_ip
AND(y.tbping_blog_name LIKE “%texas%” OR
      y.tbping_blog_name LIKE “%poker%”);

But then I grinned and thought, “Hey wait, why not let those one or two out of a lucky hundred spin their wheels when they come back for more?” which was immediatly follwed by “Foo, I don’t want to hand-jam all those addresses from my email to MT.” Then I grinned and after making a backup of my database using MySqlDump, I typed in:

INSERT INTO `mt_ipbanlist`
(`ipbanlist_blog_id`, `ipbanlist_ip`,`ipbanlist_created_on`,`ipbanlist_modified_on`, `ipbanlist_created_by`)
SELECT `tbping_blog_id`, `tbping_ip`, `tbping_created_on`, `tbping_modified_on`, ’99′
FROM `mt_tbping`
WHERE tbping_blog_name
LIKE “%texas%” OR tbping_blog_name
LIKE “%poker%”

Viola, no more automated spam from the spammer’s favorite anonymous proxies. At this point I thought I might want to block these IPs from some other websites I administer, so I generated my own cut-n-paste to my .htaccess list:

Then Chuckled at:
SELECT DISTINCT CONCAT( ‘Deny from ‘, `tbping_ip` )
FROM `mt_tbping`
WHERE tbping_blog_name
LIKE “%texas%” OR tbping_blog_name
LIKE “%poker%”
ORDER BY `tbping_ip`

Once I had exhausted all the utility I could think of, then and only then did I:

DELETE
FROM `mt_tbping`
WHERE tbping_blog_name
LIKE “%texas%” OR tbping_blog_name
LIKE “%poker%”;

Which was followed by rebuilding my blog from the command line using mt-rebuild.

So where’s the Honeypot?
I haven’t build it yet. I had enough time to post the above article, or write the script. So if you feel so compelled to automate the above, then here’s my thinking:

  1. CRONTAB a point in time where you allow your site to get spammed by temporarily renaming the .htaccess file – or at better yet, using an .htaccess file that allows one or two well-defined spammer referrer in (e.g. texas-poker).
  2. CRONTAB a time to turn back on all your protections by putting the .htaccess file back in place and then:
    • run the MySQL scripts to insert IP blocks
    • run the MySQL script to clean-up the spam from MT database
    • use mt-rebuild to rebuild your messages sans comment spam

I think however in the future, I’m going to publish a blog and ask the big hitters to link me up. It will mostly post aggregated news, but it will also publish spam hit lists in text and XML formats for easy consumption by nice-people. But first I need to get some scripts working.

In the meantime, post anything related to the above scripts or ideas. I’m sure there’s some SQL that could be better written, for example, I noticed that run more than once, and you get duplicates … which means after backing up my data AND making a copy of mt_ipbanlist in the database, I needed to run the following:

DELETE mt_ipbanlist
FROM mt_ipbanlist t1, mt_ipbanlist t2
WHERE t1.ipbanlist_ip=t2.ipbanlist_ip
AND t1.ipbanlist_id I’m also sure I’ve overlooked some procedures that could be inserted to make the whole thing work better — or at least figure out how blackjack-123.com (64.234.220.141) plays into all this.

Of course if someone could point me to a poisoned and/or booby-trapped mt-tb.cgi, I’d be much obliged.

2 Comments

  1. Pingback: How Now, Brownpau?

  2. You may be interested in http://www.i-marco.nl/pivot-blacklist/ as you fight the good fight against spammers.