Oklahoma Leaks 10,000 Social Security Numbers

DrJokepu writes "Apparently the folks at the Department of Corrections of Oklahoma just forgot to use common sense when they created the state's Sexual and Violent Offender Registry. By putting SQL queries in the URLs, they not only leaked the personal data of tens of thousands of people, but enabled literally anyone with basic SQL knowledge to put his neighbor/boss/enemies on the sexual offender list. Fortunately, after the author of the blog The Daily WTF notified the department about the issue, the site went down for 'routine maintenance' on April 13 2008."

Pleeeese!

[arizwebfoot (1228544)]

Please tell me this is a spoof.

Re:Pleeeese!

[kalidasa (577403)]

READ THE ARTICLE. The same database had all criminal offenders listed - and all employees of the state corrections system. They were using an SQL query in a GET query string! You could pull up anything you wanted from the DB because they didn't lock the permissions correctly. They did a half-assed fix the first time, and only took real action when the whistle-blower pointed out that their own SS#s were accessible.

Oblig.

[Ethanol-fueled (1125189)]

(1)Hack the registry

(2)Put your own name in the registry

(3)Sue the state

(4)Profit!!!


(5) (remember to have your name removed from the registry!)

Re:Oblig.

[cptgrudge (177113)]

(5) (remember to have your name removed from the registry!)

This is government you're dealing with. It will never happen.

"But, but, I sued the state and won! Look, here's my legal documents! I'm not a sexual predator, honest!"

"Yeah, sure.. Time to organize the community to hassle you until you leave. Enjoy being a hermit you sick pervert."

Minor Correction

[geekoid (135745)]

"Yeah, sure.. Time to organize the community to hassle you until you leave. Enjoy being a RICH hermit you sick pervert."

Re:Oblig.

[mauthbaux (652274)]

(5) (remember to have your name removed from the registry!)

Rather, this is a boon to those already on the list. Now they can simply claim that their status as a listee was simply a vengeful prank courtesy of an unnamed drinking buddy.

I do hope they have validated archives somewhere.

Re:Added to list

[by Anonymous Coward]

So I said to my girlfriend, "I am not a pedophile! But that is a pretty big word for a 10 year old."

*facepalm*

[TheSpoom (715771)]

This breaks my brain, even for the normally stereotypically slow, stereotypically technology-shy government (though I will say that a lot of the Government of Canada sites work surprisingly well in my experience).

SQL queries IN THE QUERY STRING. Someone reading their FIRST BOOK on web development would know not to do that! And now God help the people who have been affected by this: try proving to the government that you're not a sexual offender when you're already on their list.

SQL injections. [wikipedia.org] Learn them. Learn how to mitigate them [php.net] (a PHP-specific example, but there are similar mitigation techniques for other languages). And I mean, hell, in a site like this (and especially with programmers apparently this bad), stored procedures [wikipedia.org] might be the thing to implement. Or even better, use a framework like CakePHP [cakephp.org], Rails [rubyonrails.org], or Django [djangoproject.com] with this sort of sanitation built into the queries it generates.

Ugh. I hope someone gets fired for this. I bet, though, that in reality this was programmed by the lowest bidder.

Re:*facepalm*

[samkass (174571)]

ObXKCDComic [xkcd.com]

It's scary how lazy some of the web developers are. For years Yahoo used a system where their login system had the URL to go to once login succeeded urlencoded in the URL. It would have been exceedingly easy to duplicate the login page with a "Username/Password was typed incorrectly. Please try again." Then send people to the authentication page with your page as the follow-on one.

URLs should only be able to contain sanitized field values to search on that the server composes into actual SQL, URLs, etc.

Re:*facepalm*

[MightyMartian (840721)]

PHP has got to be one of the worst things that ever happened to web development. In the last year I've ended up with two jobs cleaning up someone else's code, and god but that language invites sloppiness on a level I've only experienced in the past with BASIC. The problem seems to be that it's easy enough to get a PHP-based page up, but the actual ability to coherently develop software isn't there. Anyone can learn to code in PHP, but only a few bother or are capable of actually invoking proper coding practices. The problem is that when these projects come up, rather than contracting out to someone who knows what they're doing, or at least hiring or training somebody who can code, they go to Bob the IT guy, who's okay at keeping the network up, and knows a bit of scripting, and who goes online and reads just enough of the PHP tutorial to be really dangerous.

In these cases, there's little or no commenting. Some things are done as classes, some as functions, there's no particular rhyme or reason, and it became so bloated that the original coders appear to have simply given up. It's terrible spaghetti code, but because it's on the web, no one seems to consider it software development. When you combine this with security, it can create a rather frightening mix of shitty almost undebugable code with an unknown number of potential security holes.

I know I sound elitist here, but goddamn it, PHP and all those lovely little scripting languages have unleashed a disaster on the web. It's bad enough that there's hackers out there, but much worse that there are incompetents being given the keys to the internal networks and data, without any knowledge of sound coding principles and of how to harden sites against injection attacks and the like.

Re:*facepalm*

[lattyware (934246)]

Don't blame the language because the developers are incompetent.

Re:*facepalm*

[QuoteMstr (55051)]

The language makes it easily, or even tantalizing, to do it the wrong way, and very difficult to do it the right way.

Re:*facepalm*

[ivan256 (17499)]

I disagree with "very difficult to do it the right way"... If you know what you're doing it's only moderately more work.

Java, Perl, and Python all make it easier to do it the wrong way than the right way too. Simply because the wrong way is less work than the right way in almost every aspect of these types of problems.

(The above paragraph is also true for performance)

The parent to your post is spot on. Don't blame the tool because the user is an idiot. The incompetent programmer from this article doesn't have any business doing web development in any other language either, regardless of how much "easier" that language makes it.

Re:*facepalm*

[OpenGLFan (56206)]

Yes, and:
Lumber and bricks make it very easy to build something that will fall on you and very hard to make a house.
Steel and wire make it very easy to build something that will snap and kill thousands and very hard to build the Golden Gate Bridge.
The solution is not to build the world out of Nerf. The solution is to keep Nature's fry cooks out of skilled labor jobs.

We need accreditation and liability

[QuoteMstr (55051)]

If I hire a carpenter to build my house and it collapses, the carpenter is liable. Engineers won't cooperate if management wants to cut corners on a bridge: they have a code of ethics and a body that enforces it.

Software, on the other hand, is a free-for-all today. We need an accreditation program and a code of ethics, just like more traditional disciplines of engineering. That's not to say that we'll restrict compilers to professionals; we don't reserve wrenches for professional mechanics.

But for a project that has the potential to cause so much harm to so many, a requirement to use trained and certified software engineers (with all the implications of the second word) would be invaluable.

Re:*facepalm*

[TheSpoom (715771)]

There are those of us out there that know how to code PHP in a sane, clear, and secure way. Unfortunately, I have to admit that there are a lot more that don't. I think one of the things you can do is to look for those that have languages like C++ and Java on their CV as well, and also for those that have a portfolio of code to review when they apply for a job. When you actually see the code, it's easy to separate the fly-by-night guys from the actual educated, experienced programmers out there.

By the way, on a somewhat unrelated note, we're using Django [djangoproject.com] for our new web game, and it's both interesting and easy to code, while still (rigorously) maintaining good coding practices. So I think there's also something to be said for those who work with frameworks like CakePHP, Rails, and Django, as those tend to both be object-oriented and to promote good coding practices.

As I've said before, I think PHP can and should be used well; there are just a lot of ways it can be used poorly.

Re:*facepalm*

[jsebrech (525647)]

PHP has got to be one of the worst things that ever happened to web development.

This particular site was coded in java, so PHP was not at fault here. I don't know what got you onto the topic of PHP.

The problem is that when these projects come up, rather than contracting out to someone who knows what they're doing, or at least hiring or training somebody who can code, they go to Bob the IT guy

Software development has always been rife with contracts that go to the lowest bidder based on a spec that mentions only features and deadlines (not security, quality, or other metrics). This is because most IT projects are managed by people who don't understand IT, and who do the IT equivalent of asking their neighborhood carpenter to build the golden gate bridge. You get what you pay for.

I know I sound elitist here, but goddamn it, PHP and all those lovely little scripting languages have unleashed a disaster on the web.

If PHP didn't exist, someone would invent it. PHP fills the niche of an environment that lets you get up and running quickly with web applications, just like visual basic did it for windows apps, and access did it for databases. Yes, it's a bit elitist to say that all programming environments should be so difficult to program in that they weed out all but the most dedicated (and knowledgeable), but it's also unrealistic, because people would invent easier environments if there weren't any. PHP is actually quite good at blending the capability for quality coding with a low barrier to access.

And besides, the problems with web app security have nothing to do with PHP or any other scripting language, they have to do with visibility. Think about how many two-tier desktop apps there are that let anyone with a bit of knowledge bypass the app and do whatever they want in the database. Nobody makes a fuss about this, because the visibility of those apps is lower.

I would say the primary problem with web app development is not any of the tools, but the perception among IT managers that web app development is simpler, and requires less skill, than desktop development, while the inverse is true. I do both desktop and web development, and it is much more difficult to build good web apps, because you have to pay more attention to architecture, security, performance envelopes, and gui design. That management (even my management) still doesn't understand that is disappointing, but understandable given how the web started out as a toy and still hasn't quite shaken that perception.

Re:*facepalm*

[NeutronCowboy (896098)]

Actually, for something on this scale, I'd like to see jail time for criminally negligent programming. The cost of being on a sex offender list by mistake is mindboggling - I'm on a "have a long chat with a customs officer every time I enter the US" because some data entry monkey made a mistake with my passport, and it's not pretty. I can only imagine what being on a sex offender list can do to you...

Re:*facepalm*

[by Anonymous Coward]

They'll have the best technology (your) money can buy when it is used AGAINST you (e.g. Dept of Homeland Security) but when they are doing something FOR you they cut corners and really couldn't care less.

Re:*facepalm*

[grassy_knoll (412409)]

Stored procedures are almost always a good idea, since you can also limit the permissions to SELECT and EXECUTE. Depending on the DB, using stored procedures also forces the use of bind variables so there's a CPU utilization optimization as well ( from the lowered parse rate ).

Not only did they put SQL in the query string, they granted more permissions to the DB user for the web app than it needed. If you're just looking up data, not changing it, why does the app need anything other than SELECT ( or EXECUTE if you're using stored procedures )?

A great example of why "just give the app admin rights so it can work" is one of the dumbest statements a developer can make.

Routine Maintenance

[calebt3 (1098475)]

...the site went down for 'routine maintenance' on April 13 2008.

The Reality Distortion Field is weak with this one.

Umm...

[Oxy the moron (770724)]

Without reading TFA... how do they know it was (just) 10,000 SSNs? Did they just approximate the number of entries already in the offenders list and just use that? Couldn't there potentially be more?

Re:Umm...

[Chris Mattern (191822)]

They knew it was 10,000 SSNs because the web site allowed them to do a COMPLETE DUMP OF THE ENTIRE DATABASE. Lock, stock and barrel.

Get your lawyer ready....

[sam0737 (648914)]

I don't see why those on the list are not suing the government for the damage...

Re:Get your lawyer ready....

[calebt3 (1098475)]

Get your lawyer ready.

He was probably notified along with all the other offenders.

Author of WTF article made security mistake also

[joggle (594025)]

The author should have completely blacked out the SSNs rather than blur them. They are still decipherable to those that are inclined to do so. This article [dheera.net] explains why blurring is a bad idea.

i dare someone

[by Anonymous Coward]

What someone needs to do is register a certain G. Oatse as a sex offender in Oklahoma.

Let me be the first to say...

[milbournosphere (1273186)]

D'oh!

In all seriousness, though, this just goes to show that it always helps to slow down in order to avoid this sort of disaster. One hope s that the genius responsible for this is held accountable. 10,000 social security numbers is a lot of personal data to be throwing around like that.

Humor?

[Wilson_6500 (896824)]

Who would tag this "humor"? Given the deeply-ingrained social stigma attached to being put on one of these lists, I don't really see how it's funny that one was so horribly misimplemented. Even when something is _obviously_ wrong, as in this case, it can be hard to iron out the impression that actual people get from reading these lists. What if the problem weren't as obvious as this one supposedly is? Would it still be funny?

Generally, no retraction is ever as effective as the original statement. That's probably one of the reasons why libel is such a big deal for some people--just saying "sorry, we were wrong" may not be good enough.

SSNs

[visible.frylock (965768)]

Can't read the dailywtf article, but from the summary, I'm thinking one of the biggest problems is that SSNs are on a public facing server when they don't need to be. Working in gov based IT myself, I know that Least Access is many times not followed.

Bad blurring

[Space cowboy (13680)]

Whereas the names and addresses of these people is a matter of public knowledge, is their email address and SSN also open ? If not, despite what you may think of their actions (public urination ? Really ?), it's not fair of the site to "blur" the relevant details so poorly.

I read the daily WTF, and usually I think it's pretty good, but Alex has made his own WTF here, IMHO.

Simon

obligatory

[by Anonymous Coward]

im in ur sex offender database,
injectin sql.

Oh the Chaos!

[SeeSp0tRun (1270464)]

Imagine how many people said:
"OMFG It was only one piss on a tree!!"

And they others saying:
"I remember something about being convicted for that" *shrug* "Out of sight, out of mind!"

Not Entirely Unexpected

[HadouKen24 (989446)]

I've lived in Oklahoma all my life, and it really doesn't surprise me that something like this has occurred. While Oklahoma City and Tulsa actually have some competent officials--Oklahoma City's recent prosperity can be chalked up in large part to a few good decisions--our ability, as a whole, on the technical front is pretty low. Really, I've just been waiting for something like this to come out. Corrupt state officials can only keep this kind of thing hush-hush for so long. I anticipate even more scandals of this kind for my state in the next few years. Especially as we move toward putting more and more information online.

The registry is stupid anyway.

[Dog-Cow (21281)]

The whole idea of having the registry is sheer stupidity, but on a scale designed to ruin innocent people.

Let's assume that a given person on the list was really a rapist (and not just convicted of it). If he's served his time and has repented, he won't do it again. So why do we punish him for the rest of his life with the registry? And if you think he will do it again, why is he not in jail?

You may as well just shoot him and be done with it.

Why Would Anyone Care?

[Bob9113 (14996)]

but enabled literally anyone with basic SQL knowledge to put his neighbor/boss/enemies on the sexual offender list.

Why would anyone care if they were put on this list?

This issue has gone to the Supreme Court and they have ruled [wikipedia.org] that these lists are not punishment, and hence does not run afoul of restrictions against ex post facto punishment [wikipedia.org] or due process [wikipedia.org]. So if it is not punishment, why would anyone care if they are on the list?

Re:Why Would Anyone Care?

[Damvan (824570)]

You are kidding, right?

In California, we have this thing called Jessica's Law. That law prohibits registered sex offenders from living a certain distance (usually 1000 ft) away from places children might congregate, such as schools, churches, playgrounds, parks, and in some cases, shopping centers.

So, if you are on the list, there are alot of places you CANNOT live. There are many cities in California where you can't live at all, simply because there is no place that is at least 1000 ft from the prohibited locations. If I was put on this list, I would be forced to sell my house and move as I live 1000 ft from a church. In fact, I would have to move out of the City I live in entirely as there is no residential areas outside of the prohibited locations.

missed opportunity

[kris.montpetit (1265946)]

they not only leaked the personal data of tens of thousands of people, but enabled literally anyone with basic SQL knowledge to put his neighbor/boss/enemies on the sexual offender list

DAMN! I'll guess just have to find another way to mess with my old bosses..

And we are ...

[ArIck (203)]

hearing it now when the site is down for 'routine maintenance'.... I wanted to add my boss to the list!

The Real WTF

[Freeside1 (1140901)]

TRWTF is that OK's DoC did nothing (nothing effective) to stop using SQL statements in the URL's querystring, UNTIL the author showed how manipulating the vulnerability not only put the criminals' personal info at risk, but also the employees' info.

I think you'd have to try to be that incompetent

[Bobb Sledd (307434)]

I don't even know how you could test and debug such a system without inadvertently figuring out a design flaw like that.

Maybe they meant to mess it up on purpose? Sabotage? Maybe it was meant as a back door for later?

Tell me again...

[SCHecklerX (229973)]

Why offshoring to the cheapest labor monkey is a good idea?

Being a resident...

[AioKits (1235070)]

...let me be the first to say Welcome to Oklahoma!

Now, would one of you be so kind as to get me the fuck out of here?

"Routine" maintenance?

[Sloppy (14984)]

"Routine," as in "we clean up messes similar to this one, all the time?"

Obligatory XKCD reference

[gizmonic (302697)]

Wow, an on topic post for my all time favorite XKCD! :)

http://xkcd.com/327/ [xkcd.com]

uh oh

[blakecraw (1235302)]

"Why doesn't Texas float away into the Gulf of Mexico? Because Oklahoma SUCKS!"

Now, my fellow Texans, you may be worried that a leak in the hose could cause them to lose their powerful vacuum, but in fact it's only served to multiply the sucktion! We're safer than ever!

I can just feel my karma evaporating

You would be suprised...

[jbsooter (1222994)]

I used to work (3 years ago) at a background checking company that would pull/harvest databases off the internet if the county or state wouldn't sell it to us in bulk. You'd be suprised how many county websites have stuff like this.

There is one county in Florida that will return more results by walking thru their ID numbers than by searching for everyone in thier site. That means people who for whatever reason aren't supposed to show up on the website get harvested by companies like the one I worked at.

A county in Texas tried to stop the harvesting by making people sign in and limiting searches but managed to introduce a sql injection hole that lets you do whatever you'd like to the Users tables. I didn't try messing w/ the offenders tables but wouldn't be suprised if it was possible.

These are just two examples that I recalls. There were quite a few more.

Not the only site

[reybrujo (177253)]

A quick Google search [google.com] reveals many sites with similar problems. I like this [kupferjewelry.com] one, though. You get not only the full query, but the path to the database and the database name =) Not the same, but you find idiots everywhere =)

Re:wow

[Silver Sloth (770927)]

Putting aside natural feelings of outrage and injustice exactly what offense with an associate jail term have they committed? I'm not sure about the US, I'm a Brit, but over here offenses under the Data Protection act don't carry jail terms.

Re:wow

[BlowHole666 (1152399)]

Why? In most cases you are protected from liability and your employer is the one to blame. You may get fired from your job, but you will not get sued. For example my wife works at a school and someone told her they were going to sue her. She notified the principal and the school district took care of it. So no this person should not go for jail they should be fired and the employer should give a bad referral.

Re:wow

[jmichaelg (148257)]

Going to jail is a bit over the top. Losing their job is what is called for.

However, if Oklahoma has problems similar to California, then they're faced with a Hobson's choice. They can fire the guy/gal but given the low pay scales, they could well end up with someone just as bad.

lists should be minimal in size

[davidwr (791652)]

I know you are being sarcastic, but the bigger these lists are the more useless they become.

If every public urinator and teenager in love gets put on these lists, it's that much harder to spot the really bad guys. The same goes for the really bad people who are now harmless 89-year-old men dying in a nursing home. Get these people off the list ASAP.

If you aren't "level 3" or whatever "really really dangerous" is in your state, only the cops and those who have a proven need to know should have access to your information.

Re:wow

[yuna49 (905461)]

This is an official government list of alleged "sex offenders," not a list of people with parking tickets. Developers tasked with providing public access to such sensitive information, and the people who employ and direct them, should be adhering to the best practices, not the worst practices as in this case.

The real issues are that

(a) No one in the OK government probably cared much about the privacy of these "sex offenders" because, well, they're "sex offenders."

(b) Government agencies are constantly tasked by executives and legislatures to implement programs they're ill-equipped to handle and often receive no additional funding to carry out these mandates. Do you think the OK agency involved had tens of thousands of dollars to hire outside contractors with solid coding skills to undertake this task? Probably they handed it to someone in house who knew how to write SQL queries and a little PHP.

I'd fire the lot of them, including the department heads, and start over with people who have at least some clue about good IT practices. If this fiasco was actually the product of an outside consulting shop, I'd ban them from working for my state government for a very long time.

If we don't have substantial and public penalties for poor management like this, we're just going to be repeating our mistakes.

Re:SQL

[Skater (41976)]

Can't you just be happy that they used "literally" correctly?

Re:wow

[jamstar7 (694492)]

Along with Cheney, Rumsfeld, Wolfowitz, Rice, and the rest of the 'Usual Suspects'.

Woulda made a great April Fools prank...

Re:Sex Offender Lists

[Tmack (593755)]

... they lump rapists in with flashers ...

Actually, urination in public will win you a spot there too...

If it were eldorado Texas, just one

[davidwr (791652)]

Jeffs.

The purpose of the SO list

[davidwr (791652)]

The purpose of the SO lists is to identify those likely to re-offend.

Great in theory miserable in practice.

If you want to do an offender registry right, evaluate every ex-con and create lists of people likely to commit new serious crimes.

I'd like to see likely-offender lists for:
* violent crimes including forcible sex crimes, murder, assault, etc.
* crimes involving con games/trickery of people who have no reason to know better
* financial crimes not relying on con games, e.g. bank fraud, felony burglary, etc.
* crimes against children, the elderly, and other easily-victimized groups

For each category, have a "level 1, level 2, level 3" system where level 1 means private registration, level 2 means those who ask and need to know get to see your info, and level 3 means public registration.

If a person is the reincarnation of Adolf Hitler but he's not in a position to commit new crimes, he doesn't get on the list. If a person has a single felony on his record but is deemed likely to commit one of those types of crimes in the near future, he's on the relevant list.

People change, so re-evaluate the list every year.

Re:Indecent Exposure = Sex Offender

[QuoteMstr (55051)]

Also, public urination counts as a sex offense in some areas. This is a modern-day witch hunt. That said, you sound like intelligent, reasonable people; why not move to a more liberal (small 'l') state and let your selection encourage the creation of reasonable laws?

Re:Sex Offender Lists

[MightyMartian (840721)]

Whether or not any particular set of crimes is worse than another set is ultimately up to the wider society. At the moment, sex crimes are right up there. In Louisiana they want to make child rape a capital offense. In general, I'm against capital punishment, so I think this is wrong (and there's also concerns that testimony from young children is notoriously inaccurate, and that it's one thing to lock up a pedophile for long periods of time, where at least you can let him out if it's discovered he didn't do it, but once you kill him, there's no going back).

I'm not convinced such published lists are all that effective. I know of no actual research that demonstrates their effectiveness, and it appears to be simply a way to further punish an offender after their release.

My other big problem with this category is that in many places it's an extremely broad brush. Yes, you get the pedophiles and rapists, but you also get gropers, peepers and flashers that, while clearly people committing some kind of crime, cannot under any reasonable standard be considered in the same league as a child molester or rapist.

The problem with a lot of these laws is that they're extremely kneejerk. Some serial rapist terrifies a neighborhood, the outcry grows, and politicians bring in harsher laws that are really not well thought out at all, but rather seemed to be designed to mollify the mob.

If you want to bring in harsh crimes to keep serious sex offenders behind bars, then sign me up. But if you want every mentally ill flasher forced into the same category as a rapist, then no, I think you're being unreasonable. It's like treating every shoplifter to the same punishments that a bank robber gets.