Matt forwarded me this sweet story today about dumping databases when faced with an unsuitable user interface.

The authors call it “Finding a lost dog’s owner with Perl and WWW::Mechanize” because that’s the meat of their story.

It starts with a lost dog and a programmer. The programmer finds an online database of dog registrations. Unfortunately the programmer isn’t sure how to find the license number, which is needed to look up the dog owner. So he proceeds to write a perl script to automate queries and dump the entire database.

I ran wireshark captures of both a manual post in Chrome and my test script. Comparison of the captures revealed that the submit button name/value was not being sent by my script. Looking at the WWW::Mechanize docs, I found the button parameter to the submit_form() method for simulating a submit button click. It worked. I finished the script, looping over all 24996 records and soon I was pulling down all the Warren County dog registration records for 2011.

Now that he has all the information on his local system, he searches for the parameters that are more obvious to him.

After that, it was some simple calls to grep to filter the results:

cat warren_county_dogs.txt | \
grep -i springboro | \
grep -i lab | \
grep -i brown | \
grep -i female \
> brown_labs.txt

This narrowed down the 25,000 records to 39. That made it easily to visually scan the list and find the addresses that were closest to where the dog was found. That narrowed it down to three. Adam Googled the phone numbers, found that one was a cell, and texted it.

The programmer could have built a way to narrow down a license number query using the existing interface. Or he could have built an interface to query the site on his filter parameters (e.g. color, sex). Both would have left the data on the server. Instead he pounded the database to dump all its information for 2011; lucky for him (and unlucky for 24,999 other dog owners) they did not notice or block his automated queries. The only thing more questionable would have been if this story tried to justify a SQL injection to dump databases and rescue a lost dog.

This reminds me of a joke I heard many years ago. Two guys are angry that they can’t tell their horses apart. So one notches his horse’s tail. Then the other horse gets its tail caught in a fence and the notches are the same. So the other one clips his horses ear. Then the other horse gets into a patch of woods and clips its ear in the same spot. Finally the two men realize one horse might be slightly larger than the other. They measure and discover the white horse is a whole hand taller than the black horse.

If only the dog owner database had allowed a more useful query to begin with…