Oklahoma Data Leak

Usually I don’t bother blogging about these, but this one is particularly bad. Anyone with basic SQL knowledge could have registered anyone he wanted as a sex offender.

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.

Tags: , , , ,

Posted on April 18, 2008 at 6:16 AM • 39 Comments

Comments

TheDoctor • April 18, 2008 6:58 AM

Once a colleague came back from a technical job in the US, shaking his head all the time and telling everyone: “It’s impossible that they have been on the moon, absolutly impossible!”

Think why 🙂

Tom Welsh • April 18, 2008 6:59 AM

You’re making a huge, and probably unwarranted, assumption – that the developers ever attended a Computer Science course. Nowadays I hear from most employers that they do not require CS qualifications, and even dislike hiring CS graduates. Something about them knowing lots of academic stuff that’s of no practical use, but not being up-to-date on the latest clever Java frameworks and AJAX tweaks. “We don’t need no steenkin’ computer science!”

Sejanus • April 18, 2008 7:17 AM

Tom,
I believe this is a matter of simple common sense. No computer science is needed.

Scott • April 18, 2008 7:21 AM

After I read this on Tuesday, I tried a pretty simple google query and found hundreds more sites like this.

Les • April 18, 2008 7:46 AM

I wouldn’t just blame the developers, this is a multi-level screwup.
For this kind of thing to make it to the web (especially for a government web site), you need to have mediocrity firmly ingrained at every level, from HR to management.

Anyone can write bad code, but it takes a group effort to hire these developers, not train them, not test the code, not submit their work to any basic security checks, etc, etc.

I imagine that the developer(s) will get fired (they were probably working on contract anyways), but the management system that released this code will remain unchanged, and the same kind of errors will come back.

Clive robinson • April 18, 2008 8:29 AM

Personaly I allways expect the minimum of anything from people paid from the public purse.

They normally adopt a herd mentality which as any one who has had to sit through a meeting organised by these people will no doubt know will start at the lowest imaginable level and by the process of wallpapering the but, buck passing and outsourcing responsadility rapidly developes a spiral decent to the depths of inanity not often seen in the comercial world.

Also “public servants” are usually protected not just by law but each other, so there is little or no real accountability on failier.

Finally as burecrats the have a set of rules set by our “political lords and masters” which if they remain inside they are absolutly untouchable. And we all know how long it takes to change the rule book, it makes the glacial process of repealing bad laws look extreamly spritely.

So all in all I’m no surprised by this.

But what does surprise me is that it appears not to have been exploited in a noticable way in three years.

This realy does surprise me unless the DOC is so incompetent they put any exploits down to other things…

vwm • April 18, 2008 8:39 AM

Did anybody try a “drop database” or something via that leak? Just wondering if they have a working backup strategy at least….

rai • April 18, 2008 8:43 AM

Its oklahoma, the people who send imhofe to congress , what can you expect, they have the evidence that the earth is flat right there. just another one of our states that operates like a third world country. mississippi, kansas, alabama, etc. Lack of education is a republican trait. look at how stupid rumsfeld was and he was one of thier self proclaimed geniuses like gingrich.

John Campbell • April 18, 2008 8:51 AM

@Les:

“I imagine that the developer(s) will get fired (they were probably working on contract anyways), but the management system that released this code will remain unchanged, and the same kind of errors will come back.”

Now we have finally learned where the legend of the “Hydra” comes from– management.

It would appear that bureaucracies have been seen, on and off, for thousands of years.

Scarybug • April 18, 2008 9:02 AM

There’s a band in the Rock Band highscore list named “DROP TABLE BANDS”

At least Harmonix knows to sanitize their database.

Roy • April 18, 2008 9:15 AM

I once worked a place where the general manager insisted on the requirement that anyone could change anything without an audit trail.

He wanted to stay out of jail. Anonymous access would allow him to create different sets of books ahead of the auditor’s visit.

Rich Wilson • April 18, 2008 9:32 AM

“I wouldn’t just blame the developers, this is a multi-level screwup.”

Evidenced by the ‘fix’ they implemented when Alex finally got hold of someone who seemed to under the issue. They did a case sensitive replace of social_security_number in the query string. He changed his query to use Social_security_number, and it worked again.

If you understand the problem well enough to attempt to fix it, then you should understand the problem well enough to know it’s a disaster.

paul • April 18, 2008 9:37 AM

The thing is, if I were coding something like this without regard to security, I would think that putting the query in the URL was a brilliant idea. Think of the regularity and elegance. Think of all the time it saves versus coding up little bits of script that would do exactly the same thing without showing the information to the world. Think of how you can use the same back-end to serve data to the public and to serve all of the folks on the state intranet who might actually need to see or write the whole database. Think of all the data-base-driven sites where the main difference is that altering the query parameters just won’t get you anything dangerous, because it’s not there.

M Welinder • April 18, 2008 9:58 AM

“You know, I don’t think those SS numbers are blurred quite enough to not be able to figure out what they are.”

Would you like to steal one of those identities? Discuss the cons and pros.

Scarybug • April 18, 2008 10:07 AM

“Would you like to steal one of those identities? Discuss the cons and pros.”

Was that a pun?

It doesn’t matter if the people in the database are registered sex offenders, one could still get a credit card in their name.

Joseph • April 18, 2008 10:15 AM

If anyone someone questions the Apollo project one more time, I swear I am going to punch somebody in the face.

Joseph • April 18, 2008 10:16 AM

That was a joke. Kindof. Please don’t turn me in for threatening bodily harm. If you do, I’ll punch you in the face.

Anonymous • April 18, 2008 10:24 AM

The people in the database are not necessarily registered sex offenders. You could query all the offenders in the prison. You could also display the information of the guards (which is why it got fixed, before that was revealed they were doing half-assed things like changing the capitalization of a letter).

Cocotoni • April 18, 2008 10:56 AM

“It doesn’t matter if the people in the database are registered sex offenders, one could still get a credit card in their name.”

The “obvious” solution is to prevent sex offenders from getting the credit cards. In fact, burocracy would find that solution not only obvious but logical.

Carlo Graziani • April 18, 2008 11:06 AM

If you take the standard Google query for locating GET/sql servers (see http://www.memestreams.net/users/acidus/blogid10326823/) and further restrict it to .gov domains, several somewhat sensitive websites from the District of Columbia government show up — including “Alcoholic Beverage Regulation Administration — Suspended and Revoked Licenses”, and “Department of Health — Food Establishment Closures”.

tim • April 18, 2008 12:55 PM

People seem to forget that state governments do not necessarily attract top talent. Hell I live in a state in which salaries are pegged to that of the governor.

Ed Hurst • April 18, 2008 2:02 PM

As a born and raised Okie, let me assure you this is our state’s standard level of incompetence. Could you stand to read our state constitution, you would realize that huge fat book of bylaws (it does not qualify as a genuine “constitution”) is the perfect breeding ground for corruption. In a microcosm, everything wrong with the US electoral system characterizes the depth of idiocy here. Were it not for their monumental incompetence in government officials and crony bigshots also enforcing all their idiot ideas, we’d move to another place. However, they tend to leave most of us normal residents along, preferring to bicker more with each other.

Mark • April 19, 2008 12:56 AM

“Did anybody try a “drop database” or something via that leak? Just wondering if they have a working backup strategy at least….”

There’s much more fun to be had than that. This is the Department of Corrections and their sex offender registry. A little social engineering to gather the right details followed by a few judicious ‘updates’ might even have them arresting each other.

Clive Robinson • April 19, 2008 7:41 AM

@ averros,

“Government Is Incompetent.

News at 11.”

True for the day only but with a slight modification,

“Government Is Incompetent at the 11th hour.”

That holds for all occasions 8)

TAO • April 19, 2008 11:30 PM

Oh, so very surprising this is. I thought I knew it all about why our modern sex offender laws and management techniques are horribly flawed and are a giant backfire waiting to happen…but this is just freakin’ icing on the cake. So basically, if you want to create a S.O. hit list, just SELECT all your desired victim(s) and dump the output to /dev/lp0 (that’s a parallel printer under Linux for you Windows people out there).

Just one more cute little reason to not trust everything you see online. I’m also curious as to whether or not this could have been used to, say, delete or corrupt the database for the entire SOR in one spiffy little batch operation. (I wouldn’t try it because these people DO log EVERYTHING, they just couldn’t secure a stapler to a desk if their lives depended on it, much less a critical public records website.)

If you’ll excuse me, I have to spend the rest of the evening praying to my deity of choice, giving thanks that I’m not as BIG OF A F*($ING IDIOT AS THESE PEOPLE ARE. Ugh.

TAO • April 19, 2008 11:30 PM

Oh, so very surprising this is. I thought I knew it all about why our modern sex offender laws and management techniques are horribly flawed and are a giant backfire waiting to happen…but this is just freakin’ icing on the cake. So basically, if you want to create a S.O. hit list, just SELECT all your desired victim(s) and dump the output to /dev/lp0 (that’s a parallel printer under Linux for you Windows people out there).

Just one more cute little reason to not trust everything you see online. I’m also curious as to whether or not this could have been used to, say, delete or corrupt the database for the entire SOR in one spiffy little batch operation. (I wouldn’t try it because these people DO log EVERYTHING, they just couldn’t secure a stapler to a desk if their lives depended on it, much less a critical public records website.)

If you’ll excuse me, I have to spend the rest of the evening praying to my deity of choice, giving thanks that I’m not as BIG OF A F*($ING IDIOT AS THESE PEOPLE ARE. Ugh.

TheDoctor • April 21, 2008 5:02 AM

@ Joseph: Peace my friend, I do know they have been on the moon [and if not, I would use the Mount Paranal VLT to look for evidence :-)]

It’s just hard to believe if you take a look into US factories.

bob • April 21, 2008 7:15 AM

It scares the shit out of me how many public records are being handed over to computers. It was OK back when there was a real-world document someplace at the core which could be accessed (eventually) to find the original information, but just think what our lives will be like (a la Sandra Bulllock in “the Net”) when a simple mis-click (or malicious) typo will switch you from a law-abiding state taxpayer to a cop-killer prison escapee who is to be shot on sight without warning; yet no one will notice the odd fact that you are still living at the address listed in the record instead of “in hiding”.

Leave a comment

Login

Allowed HTML <a href="URL"> • <em> <cite> <i> • <strong> <b> • <sub> <sup> • <ul> <ol> <li> • <blockquote> <pre> Markdown Extra syntax via https://michelf.ca/projects/php-markdown/extra/

Sidebar photo of Bruce Schneier by Joe MacInnis.