Month: January 2013

Originally posted February 7, 2005, pushed up a bit because it’s a fun and nerdy way to take on spam pings.

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 earlier 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 even broader after making a backup of my database using MySqlDump, and typing 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