Garden Of Blog

Something for security geeks: battle of the anti-virus products.

I just took the Perl Survey. You should, too, if you use Perl.

SQL geeks only…

So, you have an IP address.

You also have a MySQL table of subnets, defined by columns called “ip_range_start” and “ip_range_end”.

You want to find which subnet the IP address falls within.

Perl gives you an easy way to split (using the appropriately named builtin “split”) the IP adrress into its 4 octets:

my @octets = split(/./, $ip);
my $octet3 = $octets[2];
my $octet4 = $octets[3];

(As UNH is on the 132.177* IP block, I only care about octets 3 and 4).

That’s all fine and dandy, but how the heck do you deal with those octets in SQL? No “split” to use. This is what I ended up with:

SELECT arpwatch, snmp
FROM machine_tracker.subnet_snapshots
WHERE
(
	(
		/* match the 3rd octet of the ip to the ip range */
		SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', 1) +0
		BETWEEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', 1) +0
		AND
		SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', 1) +0
	)
	AND
	(
		IF ( /* if this ip range is all on the 3rd octet, this is easy */
			SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', 1) +0
			=
			SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', 1) +0
		,    /* easy 4th octet match  */
			SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', -1) +0
			BETWEEN
			SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', -1) +0
			AND
			SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', -1) +0
		,  /* if this subnet spans 3rd octet values, mpt so easy */
			IF (  /* if we are at start or end of range, handling is tricky */
				SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', 1) +0
				=
				SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', 1) +0
				OR
				SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', 1) +0
				=
				SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', 1) +0
			,  /* are we at the start of the range? */
				IF (
					SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', 1) +0
					=
					SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', 1) +0
				,  /* the ip is at the start of the range */
					SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', -1) +0
					BETWEEN
					SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', -1) +0
					AND
					255
				,  /* the ip is at the end of the range */
					SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', -1) +0
					BETWEEN
					0
					AND
					SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', -1) +0
				) /* end IF */
			,  /* no problem, this is in the middle of the range */
				SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', -1) +0
				BETWEEN 0 AND 255
			) /* end IF */
		) /* end IF */
	)  /* end AND */
) /* end WHERE */

Not tested yet, but I think it works.

Any SQL gurus out there with a better or more efficient way to do this?

I work, and once graduated from, the University of New Hampshire, where the Dimond Library played a rather important role in my life. While a student, I had a work study job in the library for awhile, as an assistant to a couple of research librarians (one, then the other). It was there that I was first introduced to making simple web pages, and the use of a digital scanner to put images on the web. Man, that web thing was cool, and I even got to have my own space at pubpages.unh.edu.

This was in 1996. Even then it seemed clear where all this digital stuff was going. The musty old books I still had to truck from one building to another never smelled mustier as I imagined what this web thing was going to do to libraries. Now, more than a decade later, there is pretty clear evidence that the traditional library is slowly being replaced by the internet.

Now that I work at Computing and Information Services (hmmm… even the latter half of our department name is moving in on the library’s territory), I can see more evidence of this shift. One of our initiatives this year is the establishment of the “Dimond Academic Commons” complete with an IT Support Center. The motto for the “DAC” was voted on and turned out to be “Integrating Learning and Technology”. Of course, the word technology is defined as “the application of scientific knowledge for practical purposes”, so this motto could just as easily be “Integrating Learning and Doing” or even “Learn To Do Something, Jack”… but I didn’t get to vote. Probably a good thing.

So little by little, silicon will replace wood pulp over at Dimond Library and every other library in the world. Sure, there will be books for years to come, but those interested in preserving a legacy in writing these days will do so in the digital domain. Books can burn, rot, and can’t be copied very easily. They are “bound” (I kill me) to be obsolete sooner or later.

Still, I plan to keep the “Book Learnin’” section here in the Garden of Blog alive and well, even if, in my later years, the word “book” becomes an arcane symbol for the act of reading.

Here I am in Houston at my first Perl conference. It’s a cross between Woodstock and MIT (though our host is the University of Houston).

Larry Wall, the creator of Perl, just finished his keynote talk. Larry really is an evangelist of free thought, a metaphor which can be seen extending all the way from Perl’s language design through his cultural musings. The son and grandson of preachers, Larry has founded his own new cult, one that has adopted a distinctly anti-dogmatic motto: TMTOWTDI (There’s More Than One Way To Do It). At one point early in their marriage, Larry and his wife were training to be missionaries, and he joked that newlyweds in this occupation couldn’t resist joking about the missionary position. TMTOWTDI, even for evangelists.

He also pointed out that the job of a missionary is not to erect a little church with a white steeple in the midst of a completely different culture, but to deliver only the core ideals to the community. TMTOWTDI.

I can’t do his whole keynote justice here, other than to pass on that he announced Perl 6 would be released this Christmas.

Update: as it turns out, “release by X-Mas” is a running Perl 6 joke. Yeah, they’ll probably get me next year, too.

Well, our little experiment in marital blogging has failed due to neglect. We did try, but we just didn’t have enough time to maintain our own blogs in addition to the couples’ blog. BlogNchain.com now redirects back to the Zucchini Patch where the curious can generally get a feel for the state of our relationship at any given time.

I still think BlogNchain.com is a great domain name, so please, make us an offer. We can be bought. Jessica was the one who came up with it originally.

I’ve finally found the time to teach myself a little bit of Rails. It’s really quite a nice framework and there’s plenty to learn. I’m going to use it for our private archive of family videos, with a CRUD interface for me and videos to browse through for my family.

Being that RoR is an instant immersion in Rails methods, it doesn’t feel like I’m learning much Ruby at all. But I suppose I am.

It’s finally starting to get fun. I’ve spent many hours watching screencasts and reading articles, tentatively poking at my new Rails install. I’m ready to poke harder. This is the same old shit, just perhaps, better organized and conceived? The RHTML feels a lot like PHP and the object data model is similar to Perl’s CGI::Application (and probably 100 others). It sort of has some of the best features of a lot of frameworks rolled into one. I’ll have to report back later if I notice any particular advantages or limitations to RoR that haven’t made themselves obvious yet. So far, it seems to be living up to the hype.

Here is where I am keeping a collection of Ruby on Rails resources.

This is very cool. Flickr photos, as they are posted, worldwide.

Thanks to Ed for pointing out that comments were broken after the server move I just did.

Should be fixed now!

Reading some of my previous posts, I come to realize what an obnoxious person I really am. Apologies to those obliged to deal with me.

Geek posts serve us better. If you’re looking for a free email/calendaring solution with portable data and good client software, try Mozilla’s Thunderbird in combination with the Lightning and Google Provider plugins. Your Google Calendar data, from one or multiple calendars, is now integrated with your email in T-Bird. I think Google is going to win with this approach, making the data you share with Google open and free to toss around in other applications. You can still use the Google Calendar web interface, of course, which is what Jessica is still doing on the calendar that we keep for the family.

What’s nice about Lightning is, you could also add calendars from other data sources, or a local data source, and mashup your entire temporal reality. Or something like that.

Carnegie Mellon University has figured out how to attract more women to its Computer Science department: by “moving emphasis away from programming proficiency”. That brought them from 8% female enrollment in CS to 40%. Bravo.

Because, you know, a degree in computer science doesn’t have to be weighed down by all that exposure to code.

In fact, studies show, remove the code from a computer, and it will cease crashing and exhibit no bugs whatsoever. It will also do absolutely nothing.

Top-shelf schools of medicine will soon follow suit in order to attract more prospective women doctors by shifting emphasis away from the study of anatomy, which is seen as complex and boring.
———

Seriously, it’s OKAY for men and women to be interested in different things, in differing percentages. Enrollment speaks to interest, not aptitude. Did they think of coloring computers pink, or making them wear corsets? All we need is better marketing, not an easier course of study. Computers right now are marketed as tools for GETTING THINGS DONE, clearly a male endeavour. If they focused instead on computers as tools for MAKING MEN GET THINGS DONE, I am sure more women would become interested in majoring in CS.

C’mon, let’s give it a try.

Actually, I was thinking of entering a Women’s Studies program but gee, it just doesn’t interest me that much. Could you change the program? Maybe remove some of the emphasis on women, who I consider complex and boring?

Just kidding, ladies. All I’m trying to do is get a reaction. Imus makes one little comment about nappy-headed ho’s and it’s worldwide news. I can’t get arrested. ISN’T ANYONE INTERESTED IN MY TOTALLY INAPPROPRIATE COMMENTS? AM I NOT TRYING HARD ENOUGH???

Having recently installed an application called Comic Life on my Mac, I’ve been thinking about what sort of story I could write for the comic book medium.

It ain’t like the olden days, folks.  I used to get so many ideas, so many.

Finally something struck me.  My ‘96 Toyota Camry is dying.  Several automobile doctors have made this pronouncement.  So I am looking around for a replacement.  Received the advice of many.

No new car yet, but I think I do have an idea for the comic book.  It would be the tragicomedy of a man car shopping.  A man who desperately needs a ride, but doesn’t want one.

Don’t give away my ending, Dave.