Daily Archives: April 16, 2010

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.

Authenticate This

I had a good lunch today with a friend who wanted to quickly set up simple (yet strong) authentication on a Tomcat web server using his own login page.  Since forms authentication is built into all J2EE web servers (and ASP .NET servers, for that matter), it’s quite easy.

In summary, the steps for Tomcat are:

  1. Add security-constraints to WEB-INF/web.xml to specify protected resources / folders.   Also include auth-constraints and security-roles for access.
  2. Create user and user role tables and configure the JDBC realm in server.xml.  Or, simply start with defining users directly in tomcat-users.xml; you can always add the database later.
  3. Create the login and login error JSPs, pointing to them from the login-config section of web.xml.  Remember to include the required form element names in the JSP (j_security_check, j_username, j_password, etc.).  Also note that these pages can’t use style sheets and other external files, so you have to (redundantly) embed style information directly into the JSP.

By default, all traffic (including login passwords) isn’t encrypted, so this should only be used with SSL/TLS encryption in place.  That means installing a digital certificate, which is also fairly easy.  That is:

  1. Purchase an SSL certificate.  For initial testing, you can create a self-signed cert using keytool, included with JSSE.
  2. Edit server.xml and enable the SSL (port 8443) connector.  The commands are already present, just un-comment them.
  3. If the server is local, re-start Tomcat, open your browser, and access your site using the https://localhost:8443 URL.  Look for the browser cues for a secure site: padlock icon, green or yellow address bar, etc.

You may eventually switch to more sophisticated methods, like integrating with external security systems for single sign on (e.g., using SAML).  But the simple steps above will get you going quickly with basic, unbreakable authentication.