Heal Your Church WebSite

Category: Titus 2:7

  • 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.



    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.

  • Mirroring Websites with wget, curl and/or tar

    From time to time, it is a good thing to back up your entire site onto a different computer/server, even if your entire site is database-driven.

    Take my situation two weeks ago, when my former host provider shut down the RBC site because of a false spam report. Even though no spam had actually been sent from redlandbaptist.org, my former host’s upstream provider demanded action. In response, my former shut down the site with no notification despite their existing terms of use to warn on the first time, suspend on the second, etc… After I threatened legal action, the host provider and I found a middle ground where he agreed to put the site online, and I agreed to move my sites elsewhere over the course of a weekend.

    Fortunately, I was already moving sites off this host I personally found troublesome. The Redland site was the last to go. RBC was last because there are legacy portions of it that are not entirely data-driven. That is, with my other sites, I merely dumped the MySQL database off the old machine, install a fresh version of MovableType on the new site, pipe in the data, hit rebuild and viola!

    While there are several PC based options for mirroring a site, I wanted something that would take files from one Linux server and move them to another. I had three choices. First, there is wget, a nice little GNU tool for offline reading and site mirroring. As Jim Roberts writes in his article “Mirroring Websites with wget“, the syntax is insanely simple:

    wget -mirror -w 3 -p -P c:\sokkit\site\rbc ftp://username:password@ftp.redlandbaptist.org

    My only problem with this approach is that much of the legacy stuff at RBC was image related and/or offline because it is seasonal. And though I employed the “-p” option, not all images made the cut, nor did any of our offline archives for obvious reasons. So another solution, at least where images are concerned, would be to use a Perl program that employs another command line download tool simply known as curl:

    perl curlmirror.pl -p -s 800 -o rbc -t /home/backupsite/rbc http://www.redlandbaptist.org

    Unfortunately, since I have several files with a .php extension, I get the same filename mangling that I would with wget’s offline reader syntax:

    wget –mirror -w 2 -p –html-extension –convert-links -P c:\sokkit\site\rbc http://www.redlandbaptist.org

    The above syntax converts urls like http://www.redlandbaptist.org/index.php?sid=123 into http://www.redlandbaptist.org/index.phpsid_123.html. Great for viewing, not so great as a working backup.

    There was another way, one that insured I got all my files, all the correct paths and all the correct file ownerships and permissions. Unfortunately, this method required shell access, and though my former host provider was kind enough to put the site back online, I seriously doubt he would have honored any request to restore ssh access. So I cheated, I downloaded a ‘modified copy’ of the Gamma Web Shell.

    ‘Modified copy?’ The Gamma Web Shell allows an individual to execute shell commands directly from their browser, so you can imagine the security implications of installing such a program. So on my local PC, I first modified the password to something huge and random, I limited the commands allowed, and then I changed the code to accommodate changing the file name from WebShell.cgi to something hard to guess. Once modified, I ftp’d it to the old Redland site, and then entered the following commands:

    mysqldump -uUSRNAME -pPASSWRD -opt mydatabase > databasedump.sql

    tar -zcvf shebang.tar.gz *

    Please be warned, if you go this route, you are putting your site at great risk. Don’t blame me if you get hacked. You have been warned. In fact, I only did it because my back was against the wall. That said, immediately after executing the above backup commands, I deleted the shell program. Then uploaded a text file full of Lorem Ipsum using the same name to make sure it couldn’t be reconstituted from the trash.

    After these security precations, I FTP’s the backup file to the new host, invoked the command “tar -zxvf” and was back in business almost instantly. I also FTP’d the tar.gz backup file to my home PC and ‘burninated‘ a CD as a ‘suspenders and belt‘ precaution.

    So how about you? What’s your method of mirroring and/or moving sites? Remember what I said this past August, “if you fail to plan, then you’re planning to fail.“

  • You need to explain the difference

    An interesting thing happened to me while sitting poolside here at Aqaba, Jordan. I was talking to an individual who works for one of the larger Christian marketing firms. He wants to develop a lay-ministry website for a neighborhood prayer ministry. Like many people I run into via Heal Your Church Website, he’s brilliant within his chosen vocation, and knows which end of the mouse of the computer to speak into, he’s no geek.

    Its for these reasons he’s registered a domain and has subscribed to the web-development services offered by Register.com.

    I mentioned that perhaps a blog-driven format might suit him better since his site is more current events oriented. Just type the post, no HTML in involved. He shrugged his shoulder and replied “well, I can do that with register.com.”

    I didn’t reply. Instead, I just looked out over the beautiful blue waters of the Red Sea and thought that when I get back, I need to think of a way of describing the differences between a turnkey static website management system such as those offered by Register.com and the services one might get from a dynamic publishing system such as TypePad.

    And more than explain the differences, explain why one would be better than another depending on the user’s context. Oh yes, and be able to explain it in non-techical geek terms.

    How about you, un in to this much? If so, how have you explained it. Leave comments.

  • ‘Pelase’ Proof Read

    They say a picture is worth 1000 words.
    ‘Pelase’ don’t say something like this on your web church’s site
    (the sign is bad enough):

    Don't let worries kill you - Let the church help.

    • Always test your new code.
    • Mistakes like can really detract from your site’s content …
    • I always get the help of a third partly before going live.

    NOTE – Sorry guys, but I’m recycling an old article for new commentary. First, because it was published almost two years ago, second because I’m very tired, and though I have some geek things to say … I’m falling asleep on the keyboard.

  • Hyperlink Bible References using Scripturizer 1.3

    After almost a year of people emailing me and leaving comments, I’ve finally made the time to update my Scripturizer Perl Module. The changes aren’t big, but they provide dramatic relief to a series of annoyances that have encumbered some of you from adding more Scripture references to your blogs, church websites and other electronically distributed information. I apologize for taking so long to get around to such an important issue.

    Addition of Abbreviations:

    I think a comment by Werner Peters (no relation) on Rob Hulson’s blog sums up the need for this addition best when he writes:

    “It gets a little tedious writing out Deuteronomy every time!”

    Yeah, you’ve said a mouth full. So I’ve added various abbreviations to the new and improved version. I’ve done so a more hard-coded format than I like, but the regular expressions are complex enough. I want a novice to be able to look at the existing expressions for Books in the Bible and add or alter their own (expressions) as they need.

    This was a painstaking process I automated to some degree with some Perl to split book names after the first three characters, then again at the fourth or fifth character depending on the length and vowel placement. I then went through each of the expressions and added or subtracted based upon a list of common Bible queries over at the ESV Bible. Many thanks to Stephen Smith for directing my attention to this data, it was incredibly helpful!

    Finally, I took care of a small handful of typos, involving pluralization. I still need to write a “mop-up” option to weed-out typos such as “revalations” and “mathew.”

    Eliminate the addition of a space before non-white space bug.

    This bug is exceptionally annoying to those who like to encapsulate their scripture references between brackets or parenthesis, such as (1 John 1:9). So annoying that back last August, Jason Rust left a comment on my blog with a fix. A fix later tried and tested and nicely documented by Joseph Markey.

    Essentially, the bug was introduced when evaluating the space between a volume reference, e.g. 1 John 1:9, which in turn put a space in front of a non-volume reference such as John 1:9. The bug occurred when the character directly before the non-volume number scripture reference was anything other than a space.

    What the fix does is it pushes the evaluation for the space into a variable, then pushes it in front of the hyperlink. An approach that works much better than a hard-coded space as nothing before the reference is then accurately rendered as nothing just before the hyperlink.

    Eliminate the ‘c’ in the gcex modifier in the regular expression.

    This regular expression mode modifier did nothing in this context, except possibily eat up CPU and sometimes throw errors in the latest version of Perl.

    ESV Bible flip-flop

    The great people at the ESV Bible have been so helpful and responsive in the past, I figured why not say thanks in the form of directing those who select the English Standard Version option to the ESV online Bible website.

    In MovableType, that would be <$MTBodyEntry scripturize=”ESV”$>

    Using Scripturizer as a Perl Module, that would be print scripturize(”Phil. 4:6-7″, “ESV”).

    XHTML compliant hyperlinks

    Ampersands are now represented as &amp;

    Usage Notes

    Well, this is sorta a no-brainer. For those of you using the MovableType Scripturizer plug-in, leave the file /plugins/Scripturizer.pl alone.

    Replace the file /extlib/Sermonizer/Scripture.pm with the referenced code below. That should be it, but first make a backup of the old one just in case.

    Here is where I have it set-up on my PC for general purposes using ActiveState’s Perl Dev Kit: c:\perl\site\lib\Sermonizer\Scripturizer.pm.

    Future Iterations

    That’s pretty much it. If you can think of anything else, let me know and we’ll see about adding it.

    Otherwise, I’d like to work on something else Stephen Smith of ESV sent me, a routine that fetches a snippet via their webservice. His code works but I have some MT centric things I want to tackle when slicing-n-dicing an XML stream of Matthew 5. That and I feel a full-blown ESV.pm module coming on. Well, Stephen has already created one, but I’ve got these ideas … and this compiler … well, you get the picture.

    There is also a much needed death-blow to typos method subroutine that needs to be written. The question is, do I write it into the same pass, or do I run first a clean-up pass filter, then scripturize? Any thoughts would be appreciated.

    Again, thanks to everyone for their input. I couldn’t have done any of this without your valuable input. Even if I could, it wouldn’t have been as much fun.

    Download the PM … Sermonizer::Scripturizer.pm

    view it as text: Sermonizer::Scripturizer.pm

    Find a bug? Have a suggestion? Leave a comment. We’ll do our best to see it gets taken care of … quickly. Get it to me in the next couple of days and I’ll try to add it as an update to this article.

  • Using Cron with LWP::Simple and XML::RSS to retrieve news feeds

    Originally published on March 24, 2003 when the war in Iraq was heating up and I found direct links to popular RSS news feeds were effecting the speed in which pages loaded on a friend’s blog whom I help maintain. I’m re-posting this article for reasons that will become obvious later this week. Until then, enjoy this “Spidering Hack!-)”

    Adding some syndicated news feeds is a nice way of adding some compelling content to your site.

    The problem is that sometimes the news feed gets overrun during heavy news days, go offline and/or suffers a host of other connectivity issues that make YOUR site load slow because the software holds your user hostage while the feed retrieval portion of the application has to wait to timeout. You see this alot with PHPNuke and PostNuke sites.

    A simple way around this problem is to use a program that periodically retrieves the feed, slices-n-dices and effectively caches it into an easy to include file on your host. Doing this achieves five goals:

    1. user page loads are not penalized when feeds go down
    2. failures to connect do not harm the existing include file
    3. multiple attempts to read the feed to not penalize user
    4. feed can be mirrored for local/private use
    5. content can be formatted to taste

    Below is a little program I wrote Thursday to grab news feeds from an AP Wire I found via Scripting.com for inclusion on a the website of a friend who makes his living in the political area.

    Using the following CRONTAB syntax, the program is executed every 30 minutes:
    30 * * * * /home/YOURPATH/getap.pl>/dev/null

    The nice thing about this approach is that this particular feed does “get busy” from time to time and at one point on Friday went offline. My users did not notice because in most cases, I was able to get by the “busy signal” on the 2nd or 3rd attempt out of 10. In the case where the feed site went offline, my users merely viewed and older include file without interruption or delay.

    Anyway, since I haven’t posted anything worthwhile in the past few days, I figured this was a good penance:

    #!/usr/bin/perl -w
# ———————————————————————–
# copyright Dean Peters © 2003 – all rights reserved
# http://www.HealYourChurchWebSite.com
# ———————————————————————–
#
# getap.pl is free software. You can redistribute and modify it
# freely without any consent of the developer, Dean Peters, if and
# only if the following conditions are met:
#
# (a) The copyright info and links in the headers remains intact.
# (b) The purpose of distribution or modification is non-commercial.
#
# Commercial distribution of this product without a written
# permission from Dean Peters is strictly prohibited.
# This script is provided on an as-is basis, without any warranty.
# The author does not take any responsibility for any damage or
# loss of data that may occur from use of this script.
#
# You may refer to our general terms &amp; conditions for clarification:
# http://www.healyourchurchwebsite.com/archives/000002.shtml
#
# For more info. about this code, please refer to the following article:
# http://www.healyourchurchwebsite.com/archives/000760.shtml
#
# combine this code with crontab for best results, e.g.:
# 30 * * * * /home/YOURPATH/getap.pl&gt;/dev/null
#
# ———————————————————————–
use XML::RSS;
use LWP::Simple;
# get content from feed — using 10 attempts
  my $content = getFeed("http://www.goupstate.com/apps/pbcs.dll/section?Category=RSS04&amp;mime=xml", 10);

# save off feed to a file — make sure you have write access to file or directory
saveFeed($content, "newsfeed.xml");

# create customized output
my $output = createOutput($content, 8);

# save it
saveFeed($output, "newsfeed.inc.php");
sub getFeed {
     my ($url, $attempts) = @_;
     my $lc = 0;		# loop count
     my $content;
     while($lc $outfile") || die("Cannot Open File $outfile");
         print OUT $content;
     close(OUT);
}
sub createOutput {
     my ($content, $feedcount) = @_;

     # create new instance of XML::RSS
     my $rss = new XML::RSS;

     # parse the RSS content into an output string to be saved at end of parsing
     $rss-&gt;parse($content);
     my $title = $rss-&gt;{'channel'}-&gt;{'title'};
     my $output = "GoUpstate/AP NewsWire\n";
     my $i = 0;
     foreach my $item (@{$rss-&gt;{'items'}}) {
         next unless defined($item-&gt;{'title'}) &amp;&amp; defined($item-&gt;{'link'});
         $i += 1;
         next if $i &gt; $feedcount;
         $output .= "<a>{'link'}\"&gt;$item-&gt;{'title'}</a>\n";
     }

    # if a copyright &amp; link exists then post it
    my $copyright = $rss-&gt;{'channel'}-&gt;{'copyright'};
    my $link = $rss-&gt;{'channel'}-&gt;{'link'};
    my $description = $rss-&gt;{'channel'}-&gt;{'description'};
    $output .= "  <a>$copyright</a>\n"	if($copyright &amp;&amp; $link);
    $output .= "";
    return $output;
}

    Of course, now I need to go ahead and practice what I preach and do the same here!

  • Comments and Code – Obfuscator 2

    As part of my ongoing online response to some great questions, I offer the following request by Colin Kuskie, webmaster for the Sunset Presbyterian Church:

    Hi Dean,

    My name is Colin Kuskie, and I’m Sunset Presbyterian Church’s
    volunteer webmaster/hostmaster. I’m planning on moving our website,
    www.sunsetpres.org, to a CMS, and I’d like to help secure staff
    and member email addresses against spam by using your obfuscator
    as a macro, but I couldn’t find anywhere to download the code from
    your site.

    Is it publicly available, and if so, could you please give me a
    pointer as to how to find it?

    Thank you,
    Colin

    p.s. Thank you very much for your online minstry. It’s one of
    the things that convinced me to go to a CMS, and then to teach
    myself CSS in order to implement it. I pray that God will you
    your website to enable other churches to reach out with God’s
    message to the world.

    How can I say no to a request like that? So I emailed Colin back with the response “… Colin, stop it, my ego is already ‘well maximized’ enough!” … Actually, that’s not what I said, but I thought it might be fun to share a little opera singer humor with you. Go ahead, ask your minister of music about their well-optimized ego … but I digress …

    Actually, my first response to Colin was “Macro? For what system?.” This wasn’t out of any suspicion, but more out of curiosity, and more out of concern that my original obfuscator hack wouldn’t integrate all that well as it was built into an online form generated using CGI.PM. Colin responded that he intends to integrate the code behind the Mean Dean Anti-Spam E-Mail Obfuscator(MDA-SE-MO) into a macro for a Perl-based content manglement system (CMS) named WebGUI. A robust system I’ve played with once or twice before, but don’t let my ease-of-use fool you. This is one CMS that requires you know Perl, paths and your site map before you go shooting your foot off of with it … but I digress …

    CMS sorties aside, Colin’s question reminded me of this sage advice she’d offer whenever I’d leave the house: “make sure you have on clean underwear.” Not that it would help if the last thing I ever saw was an oncoming cement truck … but I digress …

    So, if I’m going to put my code ‘out there,’ I had better tidy it up a bit, and put it into a easy to deploy, use and maintain Perl module. This way, he could use it for WebGUI, and I could use it for an upcoming MovableType plug-in … unless one of you kind souls comes up with it first … but I digress …

    I also figured if I as going to put it ‘out there,’ I might as well add an option that might really, truly drive spammer’s nutzo … that is to render the hyperlink as inline javascript. Yes, I know 12% of all browsers have Javascript disabled, but then again, how many people using wGet or Curl are actually interested in sending me email? That said, it is for such reasons that I have in the past advocated including a form-based email solution on your church or charity website. All the more so if you’re going to use the inline javascript option on Obfuscator … but perhaps this tangent is better left as a topic for later discussion?

    Anyway, below is the code for the new perl module behind the slightly improved online demonstration of the MDA-SE-MO:


    Yes, this obfuscator isn’t nearly as dastardly as the very cool and very effective online offering from HiveWare (hat tip to Mark Pilgrim) … perhaps by putting the Obfuscator.pm code ‘out there’ … someone will come up with an elegant “<noscript>” solution. I’ve already got one person who’s keen on optimizing the regular expressions … Colin, thank you.

  • Robot Exclusion Tutorial

    I received a very nice email from Carolyn at the Faith Evangelical Church in Melrose, MA. She writes:

    My husband sent me this URL to improve the church website, but I feel over my head. In [point #] 7 you say. “What robot exclusion standard?” What is that?

    Carolyn, as I like to say my 11th grade Sunday school class “there are no dumb questions, so thank you for asking … in doing so you help the rest of us learn something new.” Or put another way … ask and you shall receive!

    Here is how it works. When Google or some other (legitimate/well-behaved) search engine visits your site, they first look for a file in your root/home directory named robots.txt. This little file contains simple, line-at-a-time instructions on what directories and files you would or would not like indexed. Here is an example of mine:

    User-agent: *
    Disallow: /cgi-bin
    Disallow: /images

    The first line says “User-agents” for ALL user agents, allow/disallow the following directories. The next two lines specify that I don’t want search engines to index anything in my /cgi-bin or /images subdirectory, mostly because it saves me bandwidth. Now I know what you’re thinking … “what the heck is a user agent?”

    A user-agent is how we identify what type of software is visiting our site. What us geeks sometimes refer to as a “client application.” For example, many of my visitors are identified as using Microsoft Internet Explorer version 6, though today, because yesterday’s article found its way to Linux.org, the majority of my visitors are using the Mozilla browser. Google identifies itself as “googlebot.”

    If I wanted, I could make an entry specific to Google that says I also don’t want it to visit nor index a file on my site named “googleme.html” by adding the following entry in my robots.txt file:

    User-agent: googlebot
    Disallow: googleme.html

    Of course, there are those who abuse robots.txt. So the general rule of thumb is, if none of your web pages has a link to a private directory, then don’t list it in robots.txt. Looking at it the other way around, you should only allow/disallow subdirectories are linked on any of your web pages. For that, may I suggest reading my March 01’03 post entitled “How to block spambots, ban spybots, and tell unwanted robots to go to ….”

    I could go on, but there is a MUCH better tutorial over at SearchEngineWorld. Not only does it go into greater detail on with useful real-world examples, but it also accompanied by their Robots.txt Validator … an online program that lets you check to see if your robots.txt file is kosher.

    And if this doesn’t help, feel free to email me or leave a comment. I’ll just keep explaining it until everyone in the class gets it.