The Soulpass Project

This was originally a rough cut (I wanted to redo the end part) but has graduated to a final cut because I don’t feel like working on it anymore. Traditional gospel.

This is a fusion of two different songs:

1. There’s a Hole in My Bucket - Traditional children’s song
2. My Bucket’s Got A Hole In It - Hank Williams

I was trying to UNION two SELECT queries but for some reason it seemed to be omitting duplicate results (the primary key wasn’t included in the SELECTed columns so some had everything but that in common) as if they were SELECT DISTINCT queries. To work around that I added rand() as the last SELECTed item and it worked. This is probably an ugly hack, but it did what it was supposed to do. One could probably just as easily include the primary key. I still don’t know why it was omitting duplicates and now that it works I probably won’t look into it for a while.

New recording… cover of Tell It To Me by Old Crow Medicine Show.

IMPORTANT NOTE: I do not in any way condone the use of cocaine. I happen to like bluegrass, I happen to be a fan of Old Crow Medicine Show, and I happen to like the song “Tell It To Me.” I happened to record it and it was fun.

I’ve always wanted to be in a barbershop quartet. Unfortunately I don’t know anyone else who does, plus I don’t always hit the right notes. So I tried recording a song with a few of myself, mixed down with Audacity.

DOWNLOAD

It’s an old-timey song, but Weezer does a cool cover.

My Evaline (my Evaline)
Say you’ll be mine (say you’ll be mine)
Won’t you come and let me whisper in your ear
Way down yonder in the old corn field
For you (for you)
I’ve pined
Sweeter than the honey
To the honey bee
I love you
Say you love me
Meet me in the shade
Of the old apple tree
Eva-Iva-Ova-Evaline

The Distance Learning folks wanted a provision to allow users to search for site locations based on the distance from a specified zip code. I did some research and found a few resources. Most of things I found use a single SQL statement, but that didn’t suit our needs so I made it into an Oracle function:

create or replace function gc_dist (lat1 IN number, lon1 IN number, lat2 IN number, lon2 IN number) RETURN number

is

A_lat number;
A_lon number;
B_lat number;
B_lon number;
delta_lat number;
delta_lon number;
pi number;
earth_radius number;
gcdist number;
distance number;

begin

pi := 3.14159265358979323;
earth_radius := 3963.189;

A_lat := (lat1 * pi) / 180;
A_lon := (lon1 * pi) / 180;
B_lat := (lat2 * pi) / 180;
B_lon := (lon2 * pi) / 180;

delta_lat := A_lat - B_lat;
delta_lon := A_lon - B_lon;

-- find great circle distance
gcdist := power(sin(delta_lat / 2), 2) + cos(A_lat) * cos(B_lat) * power(sin(delta_lon / 2), 2);

distance := ROUND(earth_radius * 2 * atan2(sqrt(gcdist),sqrt(1 - gcdist)), 1);

return distance;

end;

I found the “exact” Earth radius on Wikipedia. phpZipLocator was used to get it working with the site. I had to modify the functions a little to suit the specific needs of the application.

Using something like this requires a database of zip codes along with the corresponding latitude and longitude for each. One can be found on the phpZipLocator site, but I don’t think I ended up using that one. I did a lot of research to find the best free one, but unfortunately I don’t remember which one was used (here’s another). As far as commercial solutions, I thought this one looks like one of the better ones. I’ll likely get back into it at some point and look this stuff up again.

Here’s an example usage of the function:

1. Extract the latitude and longitude from the given zipcode:SELECT lat, lon FROM zipcodes WHERE zip = :ZIP“:ZIP” is the specified zipcode and “zipcodes” is the name of the zipcode table.
2. Build the first part of the query sans the WHERE clause. In the WHERE clause we’ll specify the radiusSELECT gc_dist(zipcodes.lat, zipcodes.lon, :LAT, :LON) AS distance FROM zipcodes“:LAT” and “:LON” represent the returned values from the first query
3. Use phpZipLocator’s inradius function to build the WHERE clause that will restrict the results within the specified radius of the specified zipcode. Here is what’s inside the function (I don’t remember what I modified in the function so I’ll just paste the main part):WHERE (POWER((69.1 * (lon - :LON) * COS(:LAT / 57.3)), 2) + POWER((69.1 * (lat - :LAT)), 2)) < (:RADIUS * :RADIUS);“:LON” and “:LAT” were found in the first query, and “:RADIUS” represents the specified radius in miles.
4. Put them together and run the query!

So ODU recently purchased a shiny new Google Search Appliance. We’re talking $10,000 worth of web-searching power. It came in a box with the Google logo on the site. Most rack server machines look the same — plain black rectagles in a shape that could probably be used as a food tray. If you look through the racks in the ODU server room you won’t see much variance in the mounted machines.

In comes the Google Mini. Needless to say, its shade of cerulean blue really sticks out in the racks. When you first fire it up it takes about 10 minutes to boot. Once it’s booted you interface it with a laptop using a crossover cable (it comes with some cables). We used my work laptop to set it up. I got to type in the configuration settings for it, but unfortunately I had to pass the computer to David when it came time to set the password
The Google Appliance will soon replace the current Google Custom Search the site is using. Having an in-house search appliance will exponentially increase the flexibility, alowing us to tightly integrate current databases such as those for faculty directories, courses and calenders. The possibilities are endless. The operating system internals of the Google Mini are locked down pretty tightly; they still don’t want people finding out how their actual search algorithms work or what kind of database system they are using.

I took some pictures of the installation process and will probably post them at some point.

It came with a Google T-Shirt! At the next Web Group meeting they’re going to announce who gets it. *crosses fingers*

A major concern when developing database-driven websites is SQL Injection. Poorly written code could result in a malicious user running custom queries from his or her browser! Obviously the effects of that sort of security hole could be devasting for a web application.

Since I deal mostly with PHP/MySQL/Oracle, I’ll just be talking about PHP/MySQL/Oracle.

PHP has a built-in MySQL function called mysql_real_escape_string(). If that’s the injection-prevent method you go with, any time user-provided data from a URL query string is going into a database query it should be passed through that function.

If you’re using Oracle, the best and most elegant bet is to take advantage of the query binding features.

An example from the Wikipedia article on SQL injection (modified to fit the Oracle syntax):

$query = $sql->prepare("select * from users where name = :NAME");
$query->execute($user_name);

The execute member function here takes the value of $user_name and “binds” it to “:NAME” in the query. In other words it replaces the data stored in $user_name with the bind variable “:NAME.” However, before dumping it into the query it makes sure that the data in $user_name is not SQL. Regardless of the contents it treats it as query-safe data. When using native Oracle binds there is no need to use other functions such as mysql_real_escape_string().

This function serves as an extended version of htmlentities() and htmlspecialchars(). It converts all characters from ASCII 126 to ASCII 255 into their respective HTML entity codes.

function char_entities($data) {
	for ($i = 126; $i < = 255; $i++) {
		if ($i != 160) {
			$badchars[$i]  = chr($i);
			$goodchars[$i] = "&#$i;";
		}
	}

	// Normalize quotes and em dashes (e.g. - MS Word crap)
	$goodchars[145] = '\\'';
	$goodchars[146] = '\\'';
	$goodchars[147] = '"';
	$goodchars[148] = '"';
	$goodchars[151] = '-';
	// We don't want to convert spaces!
	unset($badchars[160]);
	unset($goodchars[160]);

	return str_replace($badchars, $goodchars, $data);
}

As a side note, it seems that WordPress throws a “503 Service Temporarily Unavailable” error when submitting posts that contain the following string (possibly any use of chr()):

chr($i);

I had to escape it in order to get it to work:

chr($i)

Google to me is practically synonymous with innovation. It’s as if they ignore what other companies are doing and just constantly try to roll out new ideas. Give it about a few months later and other sites like Yahoo! and MSN will be trying to keep up. Point in case: Google Maps comes out (followed by a sudden exitement about AJAX techonology). Now have a look at Yahoo!Maps and Microsoft’s Live.com Maps. It’s kind of upsetting that rather than doing much innovating these companies are just copying Google’s ideas. Microsoft went so far as to use a very similar AJAX system! Yahoo!’s uses flash (which I don’t think is much fun or as impressive as Google’s using AJAX).

Anyway, the point of this post is Google Base. I guess the aforementioned things will eventually be related since it’s only a matter of time before the concept is copied by another company.

It’s a simple database that anyone can utilize via the web! With Google’s philosophy and goal of cataloging knowledge and data, it’s no surprise that the user-provided data can in some cases be searched via certain Google searches. Users can enter any kind of data about things. First you choose a category, then you add items and choose your own attributes! It’s just like creating a regular database but it’s set up in a way where the average user can easily figure it out. If a predefined category is chosen then attributes are automatically added which makes it even easier. You can add images and AdWords as well.

I posted a “Swingline Stapler” as a test!

ALL YOUR GOOGLE BASE ARE BELONG TO US!