Heal Your Church WebSite

Month: May 2004

Regluar Expressions Editors and Testing Apps.
Regular expressions are like a chain saw … a gas powered chain saw. At first, you’re deathly afraid of even touching it. When you do fire one up for the first time, it’s only after you’ve dressed yourself like a hockey goalie and have your spouse waiting just outside of your roped-off area with his or her their finger on the 911 speed dial button.

Once you realize that if you practice some common sense, that is you take time to think through what you’re trying to do first, regular expressions become your favorite power tool – just like your chain saw.

For example, now that I’m pondering a move to WordPress, I’ve got to port Scripturizer from Perl to PHP. Scott Yang has already done much of the work, but the regex needs to be expanded to include multiple references, e.g. Romans 10:9-10, 12:1-2 or Matthew 5:6-7, 9-12.

Debugging something like this can become incredibly frustrating using the code and test method. Not so much since I have both Perl and PHP installed on my local PC. Still, having tools to help construct, visualize and debug your regular expressions can help reveal hidden bugs, catch typos that snuck past your bleary gaze, and/or teach you how to use your chain saw – I mean regex – without cutting your foot clean off.

So here are some tools, tutorials and testers I found:

Desktop Tools:
- Kiki – a free environment for regular expression testing (ferret). It allows you to write regexes and test them against a sample text, providing extensive output about the results.
- For those of you who love Linux: the KDE Regular Expression Editor
- Expresso – A tool chest for building and testing regular expressions for Microsoft Windows .NET.
- Like PHP but hate regular expressions? Try the RegExpEditor module with PHPEdit.
- RegEx Coach – don’t let the plain-jane website fool you out of using this Window-based tool.
Online Tutorials:
- A Tao of Regular Expressions – how regex is used by different languages/editors
- Regular Expression Tutorial by Regular-Expressions.info
- An Introduction to Regular Expression with VBScript by 4Guys.
- The Code Project – An Introduction to Regular Expressions
Online Testing Tools:
- Quanetics – Regular Expression Tester – includes useful cheat sheet
- Simple Regular Expression Tester by WebDevTips.
- Bite Size – Online Regular Expression Syntax Checker
- ioctl .org – an online Javascript Regex Tester
- ASPSmith.com – gives you the choice of testing your regex using .NETor Javascript
Leave a comment if you have a favorite not on the lists. Or if you have tried one of the above cool regex tools and tutorials, let us know what you think.
May 26, 2004
Using MySQL with the MT-Blacklist to Auto Ban IP addresses

One of the downsides of increased blog popularity is an increase in comment spam attempts. Fortunately, using a variety of tools already at hand, I’m able to identify and block these persistent buggers automagically while I sleep.

The Problem

Last week, this blog enjoyed some linkage from the technical ‘A-list’ regarding my analysis SixApart’s since modified licensing schedule for MovableType 3.0. With this recognition came a flood of comment spam attacks. Fortunately, most entries were thwarted using Jay Allen’s MT-Blacklist plug-in; however a few employing intentional typos got through.

I had pondered some sort of trap for the spammers … in fact I still am. Until then, I need a means of automatically denying them access regardless of typos without denying read access to legitimate visitors. So I began to pour through my various log files for a solution.

MT-Blacklist Logs to the Rescue

One of the options the MT-Blacklist offers is to log failed entry attempts. I have this turned on so I can quickly peruse my MovableType activity log. In fact, it was this very feature that brought to my attention the stepped-up attacks on my system. It also brought to my attention that the few successful entries shared IP addresses with many of the failed entries.

You see, along with being greedy and lazy, spammers, especially comment spammers, are persistent. I know because it took a set of 5 ips four days to finally sneak some comments onto my system … after 87 failed attempts. It was at this point I realized what I could do the temporarily thwart the spamscum:

Crontab as SQL statement that inserts records into the MovableType mt_ipbanlist table from entries in the mt_log where a denied IP address shows up more than once. I then encapsulated the SQL statement in a Perl program so I could add bells and whistles at a later date.

mt-autoban-ip.pl
<br /> #!/usr/bin/perl<br /> # ———————————————————<br /> # by Dean Peters, 19May04<br /> # http://www.healyourchurchwebsite.com/archives/001262.shtml<br /> #<br /> # Requires MovableType 2.6x and Jay Allen’s MT-Blacklist plug-in<br /> #<br /> # use at your own risk, I’d appreciate a hyperlink if you do:<br /> # http://www.healyourchurchwebsite.com/archives/000002.shtml<br /> # ———————————————————<br /> # usage via crontab:<br /> # 0 3 * * * perl /home/healed/ mt-autoip-ban.pl > null</p> <p>use DBI;</p> <p>$username = “USERNAME”;<br /> $password = “PASSWORD”;<br /> $dbname = “DBNAME”;</p> <p>$host = “localhost”;<br /> $dbh = DBI->connect(“DBI:mysql:$dbname:localhost”,$username,$password) or die “Bad login info”;<br /> $sth = $dbh->prepare(“select blog_id from mt_blog where blog_allow_comments_default = 1″);<br /> $sth->execute();<br /> while(@blog = $sth->fetchrow_array()) {<br /> $blog_id = $blog[0];<br /> $ith = $dbh->prepare(getInsertQuery($blog_id));<br /> $ith->execute();<br /> $ith->finish();<br /> }</p> <p>$sth->finish();<br /> $dbh->disconnect();<br /> exit;</p> <p># ———————————————————<br /> # yes, I know you can add dynamic variables to a DBI query<br /> # … I just prefer to work this way for issues of readability<br /> #<br /> sub getInsertQuery {<br /> my $bid = shift;<br /> $query = qq~<br /> INSERT INTO mt_ipbanlist<br /> (ipbanlist_blog_id,<br /> ipbanlist_ip,<br /> ipbanlist_created_on,<br /> ipbanlist_modified_on )<br /> SELECT DISTINCT<br /> $bid AS blog_id,<br /> log_ip,<br /> CONCAT( CURDATE(), ” “, CURTIME() ) ,<br /> NOW()<br /> FROM mt_log<br /> LEFT JOIN mt_ipbanlist ON<br /> INET_ATON( mt_ipbanlist.ipbanlist_ip ) =<br /> INET_ATON( mt_log.log_ip )<br /> WHERE<br /> mt_ipbanlist.ipbanlist_ip IS NULL<br /> AND<br /> mt_log.log_message LIKE “MT-Blacklist comment denial%”<br /> GROUP BY log_ip<br /> HAVING count( log_ip ) > 1~;<br /> return $query;<br /> }<br />

Yes, I realize the LEFT JOIN clause is less efficient than a NOT IN subquery, but unfortunately, I couldn’t get this more efficient syntax to work with the crufty version of MySQL on my server.

The Aforementioned Bells and Whistles

Next step is to expand this program to find the most egregious sinners determine which ones are not based in North America, and deny them using my .htaccess file. Here’s the MySQL statement I’d use to find these bums:

SELECT DISTINCT count( log_ip ), log_ip
FROM mt_log
WHERE mt_log.log_message
LIKE “MT-Blacklist comment denial%”
GROUP BY log_ip
HAVING count( log_ip ) >4
ORDER BY 1 DESC

Perhaps Jay Allen could be so kind as to add an option to the MT-Blacklist to automatically add IP addresses to the banned IP table? Or along the same lines, perhaps there could even create some sort of banned-IP list so I could share it with those using other blogging applications such as WordPress and pMachine?

That said, if you can juice it up the above code, leave a comment. I’d be interested in how you deal with it.

May 19, 2004
Countering comment spam with mod_rewrite
Even though you may have installed Jay Allen’s MT-Blacklist to thwart comment spam, wouldn’t it be nice to deny spambots from consuming your system’s bandwidth and CPU by redirecting them to an ‘error page’ especially designed for our vermonous visitors?

Apache Wunderwerkzeug : mod_rewrite

A quick tip-toe through our archives and you’ll see that in the past we’ve given some detailed attention to the use of mod_rewrite for a variety of tasks, including:
- as a tool to redirect old, well indexed pages and/or domains to new pages;
- to make your URLs search engine friendly;
- to switch from html or shtml to php without causing link rot;
- to tell evil spambots to go to H-E-Double Hockey Sticks!
Comment spam … sucks

Like most legit webmasters, I’ve also given the topic of spam quite a bit of coverage on this blog, such as:
- dealing with comment spam;
- the dreaded Nigerian 419 Scam Spam;
- my Patented Anti Spam Email Obfuscation Tool;
- using mod_rewrite to tell spambots to go to H-E-Double Hockey Sticks!
Unix-Girl to the rescue

So just in time for a weekend project , I found a brilliant bit of mod_rewrite by Kasia Trapszo, a.k.a. Unix-Girl, that concatenates these two topics into an effective way to thwart comment spam.

On her page, Kasia details a very simple snippet that demands anyone filling out a comment form first be referred by an article on the same site. In other words, bots just can’t come in out of the blue and fill out a form without at least first pretending to have read the article.

While I believe she, is running Jay Allen’s MT-Blacklist, this form of .htaccess hackery is a good idea as it gives her publishing system and bandwidth a break by stopping this type of scum at the door.

With a little experimentation, this little code gem could also be used to protect any form on your site. Then again, this is mod_rewrite, so be careful not to shoot your foot off. Oh, and let me know if you come up with something unique based on Kasia’s kool example.
May 7, 2004