Oklahoma Leaks 10,000 Social Security Numbers 245
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! (Score:3, Insightful)
Re: (Score:2, Interesting)
http://www.ticic.state.tn.us/sorsql?sql=sp_SOR_IMAGE+'SO001290'&contenttype=image/jpeg
Re:Pleeeese! (Score:5, Interesting)
Re: (Score:3, Funny)
>>--[joke]--->
__0__ <- your head
|
Re: (Score:2)
ELDORADO, Texas (AP)
Oblig. (Score:5, Funny)
(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. (Score:5, Funny)
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 (Score:4, Insightful)
Re: (Score:2)
Re:Oblig. (Score:4, Interesting)
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: (Score:3, Insightful)
Re: (Score:3, Insightful)
What's worse, of the 75% who are supposed to be there, many of them are not as advertised.
The sex offender lists are pushed as a list of child molesters and rapists. They are on there, but so are guys who got drunk and peed behind a dumpster (OK, not pleasant but hardly worthy of a scarlet letter), forgot to close the curtains, etc. One woman is there because she went topless at a protest. Then there are those who are on there for taking pictures of themselves while under 18 or for having sex with someone
Re: (Score:3, Interesting)
Re: (Score:2)
Re: (Score:2)
At least, that's so in my database. The user running on the web-side of my database can insert, update and select but can't delete (there is no reason to let them, if they want to deactivate an entry, there is a column 'active' for that).
Re:Added to list (Score:4, Funny)
*facepalm* (Score:5, Informative)
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* (Score:4, Funny)
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* (Score:4, Insightful)
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* (Score:5, Insightful)
Re:*facepalm* (Score:5, Insightful)
Re:*facepalm* (Score:5, Insightful)
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* (Score:5, Insightful)
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 (Score:5, Insightful)
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: (Score:2)
Python, in this case, wouldn't have made a difference because it wouldn't have prevented SQL injection attacks. You can concatenate together your query from GET arguments in any language.
Re: (Score:2)
Re: (Score:3, Insightful)
Re: (Score:2)
Security features don't work if you don't know to (or why to) use them.
Re: (Score:2)
Re:*facepalm* (Score:5, Insightful)
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: (Score:2)
For me, at least, the biggest problem with virtually all scripted languages is the weak typing and the automatic casting between types. Even when I was forced to code in VisualB
Re: (Score:2)
Re:*facepalm* (Score:5, Interesting)
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* (Score:5, Interesting)
Re: (Score:2, Interesting)
Require the database be erased, all backups etc.
If they can't keep it secure they can't keep it.
As a side effect (if this was their only database
they are out of a job...).
If this supported tax collections, sorry, no
collections...
Re: (Score:3, Interesting)
Re: (Score:2, Funny)
Re: (Score:2)
Re: (Score:2, Informative)
or, perhaps simply use bind variables instead of trying to generate a query. not only will your application thank you, but your database will as well.
Re: (Score:3, Interesting)
what you're talking about seems to be an Oracle-specific thing
no, not really. in the case of sane databases, it is the norm. heck, even mysql [mysql.com] supports them.
But like I said, since these web development frameworks generate the SQL queries for you based on your usage of their models
except that generating SQL on the fly is extremely inefficient . the database must then parse the query, measure costs and determine the best execution plan before executing the query even
Re: (Score:2)
Just curious though; how would you suggest that a framework using MVC and models use these things, assuming they don't already? Create a stored proc for each query if it doesn't already exist?
Re: (Score:2)
Generally speaking, I'd rather sacrifice database performance for security, but the manager doesn't always agree, does s/he?
Re:*facepalm* (Score:5, Insightful)
Re: (Score:2, Informative)
Thei
Dangerous Illusions, There. (Score:2)
They'll have the best technology (your) money can buy when it is used AGAINST you (e.g. Dept of Homeland Security) [...]
Now why on Earth would you assume that? Do you honestly think that a government agency founded by this administration is any better staffed with competent people and supplied with well-managed infrastructure than FEMA was? (Which was under the DHS umbrella, mind you.)
Didn't DHS get a D grade on the last government security report card (compared to the government's C- average)?
Please. A government that feels that government is part of the problem has no interest in making it run efficiently.
Re:*facepalm* (Score:4, Interesting)
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.
Re: (Score:2)
Re: (Score:2)
I have seen his in every industry. Including very large Financial institutions.
If you look at the number of websites the the 'government' has, nearly all of them run fine.
I can't speak for Canandian industry or government, my security work was done within the US.
Yes, I am a programmer that now works for a government agency, and no, not the one this article is about.
Re: (Score:2)
Re: (Score:3, Interesting)
You would be surprised what you can find grepping for cmd_str, command_string, cmdStr, etc. Please developers, parametrize your variables. This won't prevent all attacks, but there is NEVER an excuse to use command strings, especially when you are
Umm... (Score:2)
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: (Score:2)
(yes, uppercase S. One of their first lame attempts to sanitize it tried to do a case-sensitive replacement on the string "social_security_number", but apparently the uppercase still worked...)
Re: (Score:2)
Re: (Score:2)
Yep, that ought to do it!
Re: (Score:2)
Re:Umm... (Score:4, Interesting)
Author of WTF article made security mistake also (Score:5, Informative)
i dare someone (Score:3, Funny)
Re: (Score:2)
Humor? (Score:4, 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.
Re: (Score:2, Informative)
I agree with parent, please tag !humor if that does anything.
Bad blurring (Score:3, Insightful)
I read the daily WTF, and usually I think it's pretty good, but Alex has made his own WTF here, IMHO.
Simon
obligatory (Score:3, Funny)
injectin sql.
The registry is stupid anyway. (Score:4, Insightful)
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.
Re: (Score:3, Informative)
If this range of classification was limited to people who were actually offenders who were likely to commit their crimes again, then this could almost be understandable. However, and especially i
The system is stupid. (Score:3, Insightful)
Why Would Anyone Care? (Score:3, Insightful)
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? (Score:5, Insightful)
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.
Re: (Score:2)
I think you'd have to try to be that incompetent (Score:2)
Maybe they meant to mess it up on purpose? Sabotage? Maybe it was meant as a back door for later?
Re: (Score:2)
Tell me again... (Score:2)
"Routine" maintenance? (Score:2)
"Routine," as in "we clean up messes similar to this one, all the time?"
Obligatory XKCD reference (Score:4, Funny)
http://xkcd.com/327/ [xkcd.com]
You would be suprised... (Score:3, Interesting)
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.
Re:Get your lawyer ready.... (Score:4, Funny)
Re: (Score:2)
Re: (Score:3, Insightful)
It is just one of the things that comes with being a sex offender.
Please be more careful with your terminology. The correct thing to say here is, "It is just one of the things that comes with being convicted as a sex offender." You can be a sex offender and not be on this list (if you're not caught) and you can be a non-offender and be on this list (if you're wrongfully convicted).
I know it may seem like a small thing but it's important to remember that not all criminals are caught, and not all convicted people are actually criminals.
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
Re:wow (Score:5, Interesting)
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.
Re: (Score:2)
lists should be minimal in size (Score:5, Insightful)
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: (Score:2)
Going to jail is a bit over the top
How so? At the very least we know that for the last three years they granted access to thousands of social security numbers and medical records to anyone with internet access and rudimentary skills in sql. This isn't a situation where they made a small mistake, that on one of their report pages they didn't sanitize the 'sort by' field and they got burned. This is the absolute worst mistake that a programmer can make. The programmer should be charged with facilitating identity theft, and everyone in the sta
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
People learn from their mistakes, and the money spent on damage control and cleanup can be seen as paying for that employee's education, in a way.
I mean, what would you prefer, to fire the person who made a mistake and hire someone with unknown
Re: (Score:2)
Re:wow (Score:4, Insightful)
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: (Score:3, Insightful)
Re: (Score:2)
Woulda made a great April Fools prank...
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
Actually, urination in public will win you a spot there too...
Re: (Score:2)
Re: (Score:2)
Question 1. Who determines what list you get on? If its the same people that are deciding this today, the only list with people on it will be "List 3".
Question 2. In this day and age, do you honestly think that once you get on "List 2" or "List 3" you'll ever be able to drop off? There are people out there right now, compling private databases off these lists for the purposes of ensuring these folk "never aga
Re: (Score:3, Insightful)
Maybe in a hundred years we'll have registries of public keys and we'll all have private SS keys that are never shared with your credit card company, bank, and
Re: (Score:2)