Terror Watchlist "Crippled By Technical Flaws" 324
I Don't Believe in Imaginary Property writes "The database used by the government to generate lists like the No-Fly List is 'crippled by technical flaws,' according to the chairman of a House technology oversight subcommittee. And the upgrade may be worse than the original. Rep. Brad Miller (D-NC) says that 'if actually deployed, [the upgrade] will leave our country more vulnerable than the existing yet flawed system in operation today.' It seems that the current database doesn't have any easy way to do plain-text matching, forcing users to enter SQL queries. That might not sound so bad until you learn that the database contains 463 poorly indexed tables. How long until there's a terrorist named Robert'); DROP DATABASE; —?"
Re:Robert'); DROP DATABASE; â" (Score:3, Informative)
Re:Robert'); DROP DATABASE; (Score:3, Informative)
Size Comparison (Score:5, Informative)
*gasp!* (Score:2, Informative)
it may have been a good idea, but the implementation was horrible, come on....
Re:Number of tables (Score:3, Informative)
> This is not a good measure of how good or bad a database is.
Oh yes it is.
In order for a database to be USEFUL, you need to query it. If you can't
query the database because of the way that it's laid out or indexed then
it is infact broken.
400+ tables for an identity resolution database is absurd. There are MUCH
better ways to account for all sorts of bizzarre types of identifying
information.
This database probably isn't properly normalized either. With 400+ tables
and a simple problem you would think that all keys are primary keys in this
schema.
As far as "this" problem goes: been there & done that (quite literally).
Re:Why Would You Expect Otherwise? (Score:3, Informative)
Believe it or not but there are some good applications out there in the government. I worked on a Naval base for a year as a contractor and was fortunate enough to work on a really kick ass PHP application. I can't tell you what it was, but to this day it was the best web application I've seen as far as security, design, functionality and sophistication goes. I think it was over 130,000 lines of code and was written by 2 guys over 3 years. I learned a lot from working on that application. So there are some gems out there, this so called terrorist database could have had the potential to be better. Maybe it would have been better if there wasn't so much hype around it.
Re:It's _not_ crippled by technical flaws. (Score:4, Informative)
Technically, the Terrorist Watch List Database contains about 400,000 unique persons, of which the remainder represents known aliases. This is the so-called "green light" list, with no restrictions on them whatsoever. The "yellow light" list is much smaller, about 10,000 unique persons, and only subjects these people to desk check-ins and special searches. The *actual* No Fly list (the "red light" list) is itself a small fraction of that, perhaps 1,000 people at the most.
Add that to the fact that Congress is starting to mandate some sanity checks and ways to be removed from the list, I could see this someday being useful... just not today.
Re:That's what happens when.... (Score:5, Informative)
I was intrigued! You gave me the info I wanted to google with - Mass CIA resignations lead me to this [washingtonpost.com]
I had no idea how bad it was. Retrospectively, the bashing the CIA got seems stupid considering the impossibility of what they have to accomplish... not just now, but after pissing off most of the world in the last 8 years.
Re:That's what happens when.... (Score:5, Informative)
Oh, boy, are you in for a shock. They dunk you in Holy Water. If you drown, you're hired on the spot. Otherwise, you're a terrorist, and they shoot you.
Re:is this "obvious news day" again? (Score:5, Informative)
My co-workers 2 year old Daughter was on the list. It took 4 years to get her name removed.
It must have been her evil plot to drop a bomb in her diaper.
Re:Data integrity? (Score:3, Informative)
A table has to have a primary key and foreign/unique columns are both keys and as such, double as indexes. What the GP was talking about was how would actual indexes affect data integrity and the answer is, they don't. Having or not having indexes set up in your database will only help with the speed of queries and has nothing to do with data integrity. For example, if you are calling Name, DOB, and Address anytime you scan for a match, if you do not have an index for that query it would take much longer than without one. It goes for just about any query you call with regularity - you want an index set up to make it quicker. Now having too many indexes you don't use could lead to excess overhead and actually slow things down that way, which is perhaps with TFA meant when it mentioned indexes.
Re:Large Systems are Hard (Score:4, Informative)
Sorry, you're not allowed to create an unnecessary and disruptive large system and then pull the excuse that "large systems are hard!" when it fails badly.
If DHS created a program with a goal of kicking every single American citizen square in the nuts, and that program ended up being fraught with budget overruns, cases of mistaken identity, citizens getting kicked square in the nuts twice, some citizens not getting kicked square in the nuts at all, and people complained about the system, would you stand up and say "don't criticize them too much, large systems are hard"?
A sane person should say that TSA does a pointless job in a worthless manner, and this, not the fact that it's a "large system", is the root of the problem.
Database isn't the only form of the lists..... (Score:1, Informative)
The TSA also distributed the lists as separate searchable Excel spreadsheets. The last time I saw them, (roughly 6 months ago) the selectee and no fly lists were pushing 40MB and 15MB respectively. The airline security department would download a new list from the TSA on a daily basis. The Excel version was used as a backup in case the airline's database driven version rolled over and died (which it did frequently).
If you're wondering, I'm a former airline employee. I'm the one that the ticket counter agents would call to bypass the check-in restricted response when someone was flagged as a selectee or No-Fly. After verifying that they are or aren't a match based on the information provided, I'd make an entry in the computer that allowed the counter agent to check the person in.
The lists included all sorts of interesting data, like Birthdate/Birthplace, SSN, Driver's License Numbers, Passport Numbers, Description of the Person, State of Residence, etc.
I'd love to have downloaded them and posted them for the world to see, but I don't cherish the federal prison time that would have come with it.