The Friday Fragment

It's Friday, and time again for a new Fragment: my weekly programming-related puzzle.

This Week's Fragment

This week's puzzle is a new type of challenge: SQL coding. And since taxes and health care reform are all the buzz this April 16, that will be our backdrop.

Owen Moore, fearless programmer for the IRS, would like to help small businesses cope with certain health care reform provisions. In particular, he'll provide free reports to companies showing who to fire to get below the 50-employee limit by 2014. Fortunately, he has an Employee database table populated from recent tax filings. It has, among other fields, EmployeeID (SSN, char(9)), CompanyID (EIN, char(9)), and HireDate (a date). Since last-in-first-out seems reasonable, his "pink slip pick list" will show all but the 49 most senior employees in each company. That is,

select companyid, employeeid, hiredate from employee e where ... order by companyid, hiredate

Can you provide the missing fragment (fill in the "...") to complete Owen's SQL?

If you want to “play along”, post the SQL as a comment or send it via email. To avoid “spoilers”, simply don’t expand comments for this post. Owen will be owing you a big favor.

Last Week's Fragment - Solution

Last week's puzzle required helping Mark Duke recover his forgotten Linux password. We had his /etc/shadow entry and knew that he was a "roller": someone whose password is simply his user ID followed by a number (two digits in this case).

The /etc/shadow password format is well documented; even Wikipedia covers it. The "$6" at the beginning indicates that it uses SHA-512 hashing with the salt following. The unix crypt(3) function makes easy work of this, especially since there are wrappers in just about every language imaginable. I chose to code it in PHP:

for ($i=1; $i<99; $i++) {
   $attempt = sprintf("%s%02d", $userid, $i);
   if (crypt($attempt, $salt) == $password) {
      echo "Password is: " . $attempt;
      break;
   }
}

The password is markdupe42. 42: what else?

My son, Spencer, coded in Python, and posted his solution (see comments in last week's post) shortly after he read the fragment. Now you see what I'm up against: this guy can dump your LM hashes and dictionary-crack your Windows passwords in no time.

I commend readers for not just posting "sudo apt-get install john". Yes, there are tons of programs like John the Ripper for quickly cracking passwords, with no coding required. This reinforces the need to choose strong passwords (likely not in any password dictionary) and use different passwords for different sites. Frequently changing passwords is really no help, as we learned this week.