Hi everyone, I'm the plaintiff in this lawsuit. I'm still working on my companion post for tptacek's post! I'll have it ready Soon TM, but feel free to me any questions in the meantime here.
While you're waiting, check out this older post: https://mchap.io/that-time-the-city-of-seattle-accidentally-...
I don't understand the argument that knowing the column names doesn't help an attacker? Especially in a database that doesn't allow wildcards, doesn't it make things much easier if you know you can do '); SELECT col FROM logins, as opposed to having to guess the column name?
And I don't think I disagree with the court on schema vs. file layouts either. It's not the file layout, but it's analogous: it tells you how the "files" (records) are laid out on the "file system" (database tables). For example, denormalization is very analogous to inlining of data in a file record. The notion that filesystems are effectively databases itself is a well known one too. How do you argue they aren't analogous?
You can always `SELECT table_name, column_name, data_type FROM information_schema.columns`, which is part of the SQL standard. https://www.postgresql.org/docs/current/infoschema-columns.h...
Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot. And there's only so many combinations of `SELECT {id,userid,user_id,uid} FROM {user,users,login,logins,customer,customer}` before you find something useful.
That's a good point, has anyone hardened a database by locking out users who select columns that don't exist? Or run other dubious queries? This would obviously interrupt production but if someone is running queries on your db it's probably worth it?
On the surface that’s a very attractive idea.
A sort of “you shouldn’t be in here, even if we left the door unlocked.”
And this part seems self-defeating:
> Attackers like me use SQL injection attacks to recover SQL schemas. The schema is the product of an attack, not one of its predicates”.
If it's the product of an attack, but not the end goal, surely it's of value to the attacker?
It seems clear to me that the statute does, as worded, in principle allow the city not to disclose the database schema - it would compromise the security of the system, or at the very least, it would for some systems, so each request needs to be litigated individually.
The proposed amendment sounds like a good way to fix this - is it likely that will pass?
The Department of Justice disagrees and voluntarily releases column and table names: https://www.justice.gov/afp/media/1186431/dl?inline=
I agree with you. Knowing the exact column names can speed up an attack and, in some cases, make it more feasible.
Why don’t they just request disclosure of what’s actually stored and allow renaming of the columns? It seems odd that knowing the exact column names would be necessary if the goal is simply to understand what data is being stored and its intended purpose.
I wonder if that would be considered a "new report", which they don't have to provide.
They can either have their cake or eat it. If they don't want to obfuscate the column names, they have to provide the data with the original ones.
'); SELECT * FROM logins --
This fails if either the UI sanitizes wildcards, or if the database prohibits them, or if it produces so much data that you can't ingest it in time, etc.
`Especially in a database that doesn't allow wildcards`
Out of curiosity, could you ask for something like "one row of data from every table in the CANVAS database"?
This is a technical solution to a people problem. My reading is that the city doesn’t want to give up this information. If that’s the case, a technical solution wouldn’t work, no matter how easy it is. And given that this has already gone to the Illinois Supreme Court (and lost), the only solution is what is discussed at the end: updating the law.
I agree this is something of a technical solution, but the court wasn't interpreting whether you could ask for rows from a database, but whether you could ask for the schema directly. I don't think the court had the option of saying "you can't ask for the schema, but asking for a sample row is ok".
The short answer is yes, you can do this. I've seen this work for emails, where the request is basically, "Give me the most recent email of blah@gov.com".
And yeah, the plan was to eventually submit a batch of requests using the table names, similar to `SELECT * FROM {table_name_from_schema_request} LIMIT 1`, but one FOIA request per-table.
I have once wrote a script that translated sql requests into proper Ukrainian legalize invoking the equivalent of FOI to quite citizenship statistics from the agency. It worked, but they were not very happy when I had to get to them on the phone.
Seems like you could asked for a verbally masked description? Like an enigma coda specific to the FOIA.
"Describe to me the columns, in simple non-programmatic english, and what the purpose of the table is for, for each table related to parking tickets"
Essentially a human to schema DSL That is only technically decipherable by the admin of the database. Then you're not having actual code and only the admin could decipher.
But yah, as you said, if the humans don't want to disclose their foibles, how the request is filled is technically meaningless.
I wish it were that easy easy. I'll go more into this specific question in my post, but the short answer is that FOIA does not statutorily require the creation of new records in response to a request. The gov agency creating a description of the data in response to the FOIA request would be creating new records. It's silly.
Yeah I can see that, seems like masking isn't creating a new record, but obviously that's not how it's interpreted, because you're using the human filling out the form to interpret then return the data. FOIA typically allow for redactions and that seemingly creates new records because they have to redact things and knowing what to redact is providing masked information and that's a new record.
As such, they could claim all FOIAs that require redactions shouldn't be fulfilled because a redacted record is a new record.
They don't do describe, as it creates the new document, which is a blind spot of FOI
Kudos to you for enduring through this fight! We can only achieve transparency when people choose not to be complacent. Thank you.
What do you think are the next steps?
My first step is to actually finish my post :)
But after that, getting a reasonable law passed to fix this now-broken nonsense.
Have you tried looking for information from the developer about CANVAS? With any luck the developer has support documentation online that describes CANVAS and maybe you'll be able to narrow down your FOIA request.
I think the point of the lawsuit is less about CANVAS schema itself and more about the ability of the government to hide this kind of information from FOIA requests.
Damn, this is impressive. I've been fighting with a state agency since December for 17,000 emails. I don't think I've ever tried to request emails and received zero push-back, but a $33 million estimate just, chef's kiss
They can produce a report using english language labels instead of the db column names. Their argument isn't fact it's vexatious obstenance.
> Normally, a flustered public records officer would just reject a giant request for being for “unduly burdensome”… but this sort of estimate is practically unheard of. So much so that other FOIA nerds have told me that this is the second biggest request they've ever seen. The passive aggression is thick. Needless to say, it's not something I'm willing to pay for!
Welcome to Seattle :-)
> that's the second biggest FOIA request I've ever seen!
-Guybrush, from The Secret of Monkey Island
This older post was such a fantastic read, thanks for sharing your story!
It's dated from ~2 weeks ago... is there other date information I am missing?
ah no, I just said "older" since OP said it was older and I wanted to distinguish from the SQL post that this post is about
What are the administrators of CANVAS hiding?
Hard to say. One of my personal drivers for this lawsuit is a tip I received that said that Chicago has a list of vendors whose tickets are dropped in the back-end. When I requested that info, the city said they had no such list. I trust my source, so having schema information could help figure out the extent and if they were lying.
Considering how much they fought to not release the schema, there's probably a column named "exempt_from_penalty" or something equally obvious.
Earnest question: If you suspect them of lying on the issue, why would you trust them to release the full schema in response to the FOIA request, and not just omit any possibly incriminating columns?
It's always a possibility that some low level official not in on the scam sees the FOIA request before management tells them not to work on it. The more you ask for, the less filtering there is going to be, simply because of how people work.
If you're running the scam, you don't want to tell low level employees about it, because they have no incentive not to blow the whistle.
How is this different from literally any other FOIA transaction, computer-y or otherwise?
What is the theory then for why they do not want to release this schema? Don’t misunderstand me I appreciate how important it is that people push the boundaries of FOIA.
What is the theory then for why they do not want to release this schema!
Because this is not how government works. Most of the time it's not a heavily entranched conspiracy. Once the request is approved to go through by the legal department, some technician will happily give you everything you want and it won't be censored or tampered with in process.
Many times the people answering the requests aren't part of the conspiracy to commit random acts of malice. Sometimes they're roped into it under threat of termination.
And often times, the denials eventually lead to significant reorg once judges and Congress can revise laws to fix the ambiguities.
Well that certainly sounds suspicious. But it could also provide more damming evidence of targeting groups, people skimming the till, bribes to make tickets go away, all sort of fun shenanigans.
And boy they’re fighting suspiciously hard.
Good luck.
Bribes are most certainly not logged in the system under the "bribes" column or codified in any way. The data discovered through foi could show some patterns which are suggestive of bribes, but the actual thing is negotiated "off chain".
'ethnicity' header, 'net_income' header... wouldn't doubt chicago could be cave man enough to do this
Thanks for fighting the good fight for us all!
"Retrieve the data of every parking ticket issued to ‘Bob O’ and also all the rest of the information in the database including everyone’s passwords."
This is the example of SQL Injection written in plain English, yet "everyone's" is problematic here in that it's an orphaned single quote. If "Bob O'Conner" is bad, so is "everyone's"
While I believe that the city should share the schema, and that the city is effectively argues for security through obscurity, I disagree with the main premise of the article: that knowing SQL schema doesn't help the attacker.
If I understand the argument of the author here:
> Attackers like me use SQL injection attacks to recover SQL schemas. The schema is the product of an attack, not one of its predicates
The author appears to imply that once the vulnerability is found, the schema can be recovered anyway. It is not always the case. It is perfectly viable to find a SQL injection that would allow to fetch some data from the table that is being queried, but not from any other table, including `information_schema` or similar. If all the signal you get from the vunlerability is also "query failed" or "query succeeded, here's the data", knowing the schema makes it much easier to exploit.
> the problem is that every computer system connected to the Internet is being attacked every minute of every day
If you specifically log failed DB queries, than for all the possible injections that such 24/7 attacks would find you have already patched them. The log would then be not deafening until someone stumbles on the actual injection (that, for example, only exists for logged in users, and thus is not found by bots), in which case you have time to see it and patch before the attacker finds a way to actually utilize it.
Knowing schema both expedites their ability to take advantage of the vulnerability, but also increases their chances of probing the injection without triggering the query failure to begin with.
I'm not an attacker, just a boring old software dev. If there's an SQL Injection I'd say all bets are off re: schema.
That said I've definitely worked on applications where knowing the schema could help you exfill data in the absence of a full injection. The most obvious being a query that's constructed based on url parameters, where the parameters aren't whitelisted.
So I actually do agree that the schema could potentially be of marginal benefit to the attacker.
Wouldn't admitting this in court pin you with some sort of negligence? (if you knew having a schema revealed would compromise your app in some way).
"Defense in depth" is an easy argument to make. I sure hope I don't have any SQL injection holes, but I can't prove it with 100% certainty.
Reminds me that the recently discovered “leak emails using YouTube” exploit kicked off from reading what is essentially, a schema.
> kicked off from reading what is essentially, a schema.
I wouldn't call json a schema.
In the HN discussion tptacek replied that "$10,000 feels extraordinarily high for a server-side web bug": https://news.ycombinator.com/item?id=43025038
However his comment assumes monetisation is selling the bug; (tptacek deeply understands the market for bugs). However I would have thought monetisation could be by scanning as many YouTube users as possible for their email addresses: and then selling that limited database to a threat actor. You'd start the scan with estimated high value anonymous users. Only Google can guess how many emails would have been captured before some telemetry kicked off a successful security audit. The value of that list could possibly well exceed $10000. Kinda depends on who is doxxed and who wants to pay for the dox.
It's hard to know what the reputational cost to Google would be for doxxing popular anonymous accounts. I'm guessing video is not so often anonymous so influencers are generally not unknown?
I'm guessing trying to blackmail Google wouldn't work (once you show Google an account that is doxxed, they would look at telemetry logs or perhaps increase telemetry). I wonder if you could introduce enough noise and time delay to avoid Google reverse-engineering the vulnerability? Or how long before a security audit of code would find the vulnerability?
Certainly I can see some governments paying good money to dox anonymous videos that those governments dislike. The Saudis have money! You could likely get different government security departments to bid against each other... Thousands seems doable per dox? The value would likely decrease as you dox more.
> I wouldn't call json a schema.
What you see there is a protobuf, serialized as JSON. If a protobuf definition isn’t a schema, I don’t know what is.
If you specifically log failed database queries, where "failure" means "indicative of SQL injection", then nothing you can do with the schema is going to reduce the signal in that feed --- even a single SQL syntax error would be worth following up on. No, I don't think your logic holds.
I don't understand your logic. Knowledge of the schema can give an attacker an edge because they now know the exact column names to probe. Whether these probes get logged is irrelevant; even if it makes the system more vulnerable for an instant, it's still more vulnerable.
Even if logging failed queries is your metric, then knowledge of column names would make it more likely for an attacker to craft correct queries, which would not get logged, thus making your logs less useful than if the attacker had to guess at column names and, in so doing, incur failed queries.
To probe for what? How does knowledge of a column name make it easier for me to discern whether a SQL injection vulnerability exists? I've spent a lot of time in my career probing for SQL injection, and I can't remember an instance where my stimulus/response setup involved the table names.
SQL injection is a property of a SQL query, not of the schema itself. To have a meaningful chance of blind-one-shotting a query, getting a TRUE/FALSE answer about susceptibility without ever generating a SQL syntax error, I would need to see the queries themselves.
Knowledge of the column names doesn't give you insight into whether a vulnerability exists. It gives you insight into what you can do with a vulnerability, should it exist. For example, if you want to set your account balance to $1 million, you'd need to know the column name in order to generate a valid query. Without advance knowledge of the column name, your job becomes harder.
> How does knowledge of a column name make it easier for me to discern whether a SQL injection vulnerability exists?
It doesn't. It just means that as soon as you find one, you can immediately begin crafting valid queries instead of randomly guessing table names and columns, therefore not setting off the "DB query failed" alert.
EDIT: I guess this is the part I missed:
> To have a meaningful chance of blind-one-shotting a query, getting a TRUE/FALSE answer about susceptibility without ever generating a SQL syntax error, I would need to see the queries themselves.
Really? I guess I have to take your word for it because I've never attempted it, but I would have thought that in some (horribly broken) systems `bobby tables' or 1=1 --` would have a very reasonable chance of detecting SQL injection without alerting anyone.
You can craft valid queries that don't reference any table or column name.
Right, and that's what you use to find the vulnerability. But imagine you've found the vulnerability and now you want to use it to update all of your parking tickets as paid. Without the schema, this is going to be quite tricky and will generate a lot of failed SQL. With the schema, you might be able to do it on your first try.
Which is why in the ordinary course of a pentest you'd use the SQL injection vulnerability to recover the information in the schema.
Is there not any SQLi vulnerability in practice that doesn't allow such an information recovery? That is, is the schema-recovery step so foolproof that it can always be performed on any target form? GP is suggesting that this may be difficult, depending on the kind of signal that gets returned from the form.
In my entire experience as a software security practitioner, which at the time of my testimony encompassed some hundreds of assessments of SQL-backed websites, the availability of a schema has never impacted my ability to exploit a SQL injection. It's not my job as an expert witness, nor Matt's job as a plaintiff, to invent improbable scenarios where security could hinge on schema availability. The court (all of them, in fact) found that testimony dispositive, so I'm happy to leave the issue there.
Maybe I'm ignorant, but if the account the app is using doesn't have access to the information_schema how do you do this?
Not just that, but perhaps the app is smart enough to lock you out the second it detects an attempt to gather the schema, e.g. by logging and automatically responding to a query that displays the schema. Then you have to look for other ways in (another IP, etc.). But if you know the schema in advance, you have a better chance of a one-shot injection that accomplishes your malicious goal.
In other words, advance knowledge of the schema may make it easier to act maliciously.
> nothing you can do with the schema is going to reduce the signal in that feed --- even a single SQL syntax error would be worth following up on
Syntax errors coming from your web application mean there is a page somewhere with a bugged feature, or perhaps the whole page is broken. Of course that's worth following up on?
Edit: maybe I should add a concrete example. I semi-regularly look at the apache error logs for some of my hobby projects (mainly I check when I'm working on it anyway and notice another preexisting bug). I've found broken pages based on that and either fixed them or at least silenced the issue if it was an outdated script or page anyway. Professionals might handle this more professionally, or less because it's about money and not just making good software, idk
> Syntax errors coming from your web application mean there is a page somewhere with a bugged feature, or perhaps the whole page is broken. Of course that's worth following up on?
This is a government system, with apps probably built by lowest-bid contractors.
I imagine most of us would be horrified by the volume of everyday failed queries from deployed apps.
That's where the court's technical distinction between the words: "could" and "would", is important. It appears they have reduced the distinction to a risk assessment which is more objective than opining wildly!
For example: I've just re-wired a three gang light switch. I verified power on with my multimeter (test the meter), cut the power and then retested all the circuits to make sure I had got it right.
It turns out that switch three is on a separate ring main. Cool I didn't get to test my body's ability to take a whopper of a shock. In the UK it is common to have upstairs and downstairs rings for light circuits. Our kitchen has quite a few lights in it so it got a separate ring as well. Anyway there are quite a lot of wires in there because all of them are two way switches. Oh and I am allowed to work on them because of the switch location - not kitchen and not bathroom, ie a low risk location
I noted down the connections, and took them all out. I put Wagos over the flying ends to make them safe, turned the power back on and got on with the job in hand.
I then cut the power (both circuits) checked again with my Fluke. Oh bollocks ... enable power, test the Fluke and then cut power again and recheck the circuits.
Now I re-terminated all the connections. There was plenty of additional wire so I decided to cut and re-strip the conductors, to make sure that I avoided potential failures due to "work hardening" from the inevitable pushing and pulling and "gentle" forcing into position. Once all the conductors were screwed down I pulled on them fairly forcefully to make sure they wont fall out.
I screwed down the switch face plate and restored power. Its a brushed metal finish switch so I did test it was not live, because I'm careful. I tested the functionality ie all three switch circuits (three) from all the switches (six).
So, given that description is it possible that the connectors might fall out in the future and short on say, the metal back box. Of course it is possible. It could happen but would it happen?
You could postulate all sorts of scenarios. Perhaps I may be careful but I might be cack handed and forgetful and got something wrong anyway and a wire might still drop out. Now we are at the point of whataboutery! and that wont wash.
The would/could distinction is a powerful one and it is analogous to how we do risk assessments.
I'm certainly not saying you are wrong in your assessment but I think you are fiddling with details to conjure up a "could" and not a "would". I agree that knowing the schema would assist a hacking attempt but would it make a successful crack more likely - no I don't think so. It is a classic case of obscurity despite security but a rather more complicated one than putting the ssh daemon on port 2222.
Cripes - I need to get out more!
Kurt posted this to troll me. Just know my audience here was, mostly, non-technical people involved in politics in my local Chicagoland municipality.
Permit me a PSA about local politics: engaging in national politics is bleak and dispiriting, like being a gnat bouncing off the glass plate window of a skyscraper. Local politics is, by contrast, extremely responsive. I've gotten things done --- including a law passed --- in my spare time and at practically no expense (drastically unlike national politics).
An amazing thing about local politics, at least in a lot of places, is that they revolve around message boards. The boards won't be in places you want to be (in particular: a lot of them are Facebook Groups) and you just have to suck it up. But if you enjoy participating in a community like HN, you can participate in politics, too, and message-board your way towards making things happen.
> Local politics is, by contrast, extremely responsive. I've gotten things done --- including a law passed
You live in a country where local governments have the power to make laws… in a lot of other countries they don’t - or, to be more precise, their lawmaking power is extremely limited.
Actually, even in the US, that’s often true too - only local governments with “home rule” can enact laws on any topic (provided it doesn’t contradict state or federal law), those without it can only enact laws on specific topics authorised by the state legislature. Some states grant home rule to all counties and municipalities, others none, others to some but not others (e.g. in Texas a municipality can give itself home rule powers, with approval of its voters, but only once it reaches a population of 5000).
Even state legislators are, by their nature, pretty much locally driven given the relatively small size of their constituencies and thus the margin of victory.
Voters significantly underestimate their power even up to the House level; AOC’s first campaign was very scrappy and resulted in a bartender unseating the chair of the Congressional Democrat Caucus and likely successor to Nancy Pelosi, and that was the first campaign in which anyone bothered to primary him.
Would you care to elaborate which law you helped to pass?
Also, can you link to some good resources for someone who wants to get off the sidelines and get more involved in Chicago politics, whether the resources are on FB or elsewhere? I've previously tried Googling for some but with very limited success.
Thanks.
We're the first municipality in Illinois to draft and adopt an instance of ACLU's CCOPS model legislation, which requires board approval at a recorded public board meeting before any agency (most especially our police force) can adopt any form of surveillance technology, given a broad (ACLU-supplied) definition of "surveillance". Previous to that, our police force could acquire arbitrary surveillance products so long as they kept under a discretionary budget threshold; they used that latitude to acquire a pilot deployment of Flock ALPR cameras, and CCOPS was a response to that.
My real goal is zoning.
In Chicago itself, I have less clarity, but am optimistic that somewhere on Facebook is a message board where the staff at your alderman's office reads posts, and the most politically engaged people in your neighborhood argue with each other. That's your starting point (and maybe your ending point). Just go, listen, and chime in with high-effort comments. If you're used to clearing the bar for HN comments, you're way past the threshold of coding like a super-thoughtful person in local politics.
My real goal is zoning.
The categorical elimination of single-family zoning along with any building envelope restrictions that would make as-of-right 3-flats uneconomical.
A step in the right direction last week for the largest upzoning effort in the city! https://archive.is/QuOcJ
Of course the a vocal minority is fuming about higher density.
That would be an outstanding outcome! Is this just for Oak Park, or beyond?
You'd hope that Oak Park, Evanston, Wilmette, and then Berwyn and Schaumburg could get this done, and then your next step would be either Chicago (tough because of aldermanic structure) or statewide, the way California did. Either way: you start in one municipality and work from there.
It helps that zoning matters more in Oak Park (and Evanston) than almost anywhere else in Chicagoland.
Why does zoning matter more in Oak Park and Evanston? High demand from being on the El and close to Chicago?
Yep. Historically both of these places basically exist to concentrate the interests of the upper middle class and to reinforce segregation. They're both basically Chicago but with a better funded school system (because lawyers and doctors get to funnel all their property taxes into the school down the street from them), which makes them highly desirable.
It's might actually be easier to win the economics battle by chipping away at restrictions on taller buildings. The builders in my area are copy/pasting a 3-flat design all over the place but it requires bargain-basement land prices (literally building on former toxic waste dumps) or money from the township because 3-flats make you have to build wide.
The muni I live in is very constrained (we're just 4 square miles, right on the border of the west side of Chicago) and our land is overwhelmingly SFZ, so most of the ballgame is getting SFZ lots opened up. The emerging consensus is towards "missing middle" housing, which is 2-40 units (but really, a medium term sweet spot in the teens), where you're talking about buildings spanning multiple lots.
That very little can economically be built on existing SFZ lots even with relaxed zoning is actually a feature, not a bug, for getting this done. People want change to be slow. At least to begin with, it's better strategically if it takes a couple years and gradual tweaking to make lots of building happen.
Kam Buckner is trying to get something passed at the state level (but wouldn't apply to Oak Park. https://ilga.gov/legislation/BillStatus.asp?DocNum=3288&GAID... )
“Never doubt that a small group of thoughtful, committed citizens can change the world: indeed, it's the only thing that ever has.” - Margaret Mead
Like a hedge fund? Or are we including those committed to violence?
Probably not the intent of the attributed author [0] but literally speaking the statement doesn't specific "ethical" or "peaceful", no.
It's about that it's a small-dedicated group that brings change and not government or private institution. If it's still hard to grasp, then think about how national movements started.
Would would you ever exclude ones committed to violence? Violence consistently works.
>The boards won't be in places you want to be (in particular: a lot of them are Facebook Groups) and you just have to suck it up. But if you enjoy participating in a community like HN, you can participate in politics, too, and message-board your way towards making things happen.
How do you figure out where to go?
The way you'd expect: I bumbled through a bunch of different Facebook Groups, starting with the one simply labeled for my neighborhood, and followed cross-posts. Eventually I found the two really important ones in my area (one is an organizing group for local progressives --- I live in a very blue muni, and the other is the main high-signal political group for the area, in which all the village electeds participate).
Aaaaaaa! I need to finish my post! :(
Is it not absurd that the supreme and appeal courts disagreed on a syntactical matter? Never mind that this isn't uncommon, or that (IMHO) it would be ridiculous to interpret it as "any file layouts at all, and other stuff too, but only bad other stuff". It's crazy to me that were happy for laws to sit on the books being utterly ambiguous.
I know this suits the courts who benefit from the leeway, and that (despite valiant efforts) we're not going to get "formal formal" language into statutes. I know that the law is an ass. I know that the laws are written by fallible and naive humans.
Even after all that, if the basic sentence structure of what's in the law isn't clear to the courts, hasn't the whole system fallen at the first hurdle?
I am not a lawyer, but my understanding is that's just how the justice system works. Reasonable people can disagree about what exactly a complicated statement says, since language is full of ambiguities. People have been discussing what the U.S. Constitution says exactly from the day it was written and there are still a lot of disagreements.
The standard response to this is that laws should be written in ways that are non-ambiguous but that's easier said than done. Not to mention that sometimes the lawmakers can't fully agree themselves so they leave some statements intentionally ambiguous so that they can be interpreted by the courts.
I’ve often thought we’d get more sensible results in court cases on computer-related issues if we had specialised courts where the judges were required to have a relevant degree (computer science, software engineering, computer engineering, information systems, etc). But I doubt it is going to happen any time soon.
It happens from time to time. https://www.theverge.com/2017/10/19/16503076/oracle-vs-googl... ( https://news.ycombinator.com/item?id=15834800 42 comments)
> These days, he often looks for some kind of STEM background for the IP desk. It’s not necessary, but it helps. Bill Toth, the IP clerk during Oracle v. Google, didn’t have a STEM background, but he told me that the judge had specifically asked him to take a computer science course in preparation for his clerkship. When I asked Alsup about it, he laughed a little — he had no recollection of “making” Toth take any classes — but he did acknowledge that sometimes he gives clerks a heads up about what kind of cases are coming their way, and what kind of classes might be useful ahead of time.
Note that it's not necessarily the judge that's important as an individual knowing the material, but that the clerks who work for the judge are.
Civil code law uses that way of thinking, where there are specialised courts for different areas: administrative, civil, labor, family, commercial and so on. I actually am not so sure it is great as these courts increase the depths of the bureaucracy to the point of being self serving. They also serve to segment expertise.
> Civil code law uses that way of thinking, where there are specialised courts for different areas: administrative, civil, labor, family, commercial and so on.
This happens in common law countries too. For example, the US has specialised courts (at the federal level) for bankruptcy, federal government contract disputes (US Court of Federal Claims), taxation (US Tax Court), among others. It also has a nationwide appellate court (Federal Circuit) with jurisdiction limited to certain topics (patents, trademarks, federal government contracts, among others), and another (DC Circuit) which despite being technically geographic in practice also has topical jurisdiction (many-but not all-lawsuits against federal agencies). Many states have specialised courts for various areas of law
It is very common in common law countries to have specialised courts/tribunals (or divisions thereof-there isn’t a big difference between a specialist court and a specialist division of a generalist court) to deal with certain types of cases, especially bankruptcy, family law, probate, child welfare, juvenile crime, patents, taxation, administrative law, military law, immigration, small claims - the exact set varies, but specialised courts/tribunals/divisions are very common.
But I’ve never heard of a specialised court/tribunal/division for computer cases
Nobody reasonably expects all laws to be written completely unambiguously. But since laws (and indeed all manner of legal documents) are filled with lists and modifiers, I don't think it's unreasonable to require that they be written to a certain standard which defines how these lists and modifiers should be interpreted, similar to RFC 2119 https://microformats.org/wiki/rfc-2119.
To me it feels like the kind of dispute that is exactly why we have multiple levels of appeals court. The "file format" thing is super dumb, and they got it wrong, but the "that if disclosed" statutory interpretation is a thing that seems important to get a final, consistent determination on.
Of course I can't disagree that it's good that it's now settled. Still I can't help but imagine a world where the meaning, at least in terms of which words apply to which others (rather than qualifiers like "reasonable"), should be settled before the law is debated, voted on, and passed.
Even (some) programmers have learnt the dangers of parsing at run time (e.g. "eval is evil"). How can we decide it's the law we want if we don't know what it means yet?
> How can we decide it's the law we want if we don't know what it means yet?
FWIW, judicial interpretation of legislation is generally seen as an exercise in figuring out what the legislature meant. Courts start by looking at the "plain meaning" of the words used, but where that doesn't yield an unambiguous answer they will often look at the overall scheme or purpose of the legislation to try and figure out which interpretation is most consistent with that.
It's far from perfect of course, but it's not like legislation just consists of a bunch of random symbols that are later imbued with meaning by a court operating in a vacuum. The meaning of most legislation is clear most of the time. I'm sure the authors of the bill thought it was sufficiently clear, for any scenario they could contemplate (or, at least, the ones they cared about). But it's hard to see every potential corner case (and if every potential corner case did have to be identified and settled before the bill could even be debated, it's likely Illinois wouldn't have a FOIA today).
Am I the only one slightly perplexed/worried by the point-blank source code exemption?
It's easy to imagine a scenario where the city decides to develop a specific software in-house and hide the "biases" in the source code, or any other thing one might not find desirable.
Hell, they don't even need to make everything from scratch! Could just patch and use a permissively licensed 3rd-party component.
In my opinion, the proposed amendment does not go far enough.
It shouldn't be surprising ?
It is the same problem people trying to open sourcing closed projects experience, there is all sorts of locked-in proprietary code which the developer and the customer only have the license to use but not share the source.
Even projects which from day one are staunchly open and built without direct commercial interests like government contractors need also suffer from this. The Linux kernel challenges for supporting ZFS or binary blob drivers in kernel/user space and so on are well known[1]
Paradoxically on one hand information wants to be free, and economics dictate that open source software will crowd out closed competitors over time, it is also expensive to open source a project and sometimes prohibitively so and that deters many managers and companies open sourcing their older tools etc, even if they would like to do so, involving legal and trying to find even the rights holder for each component can deter most managers.
If a government put requirements in contracts that the vendor should only use open source components in their entire dependency tree, it could drive the costs very high because a lot of those dependencies may not have equivalent open source ones or those lack features of the closed ones so would need budgets to flesh them out. In the short term and no legislature will accept that kind of additional expense, while in long term public will benefit.
---
[1] yes kernel problems are largely a function of GPL, more permissive licenses like Apache 2 /MIT would not have, BSD variants after all had no challenges in supporting ZFS.
However a principled stance on public applications being open source by government would be closer to GPL than MIT in terms of licensing. Otherwise a vendor can just import the actual important parts as binary blobs "vendored" code and have some meaningless scaffolding in the open source component to comply.
In theory the decision to put those biases in the code should be public information. You can ask for the criteria the software was made to, just not the software itself.
Though rulings like this might have a chilling effect.
That's why it's important to push for "public money - open source" initiatives like some countries in the EU are trying to implement.
Off the top of my head, I think the last (now failed) German coalition had this in their programme but didn't deliver. Maybe the new government will.
Very interesting read.
It does seem absurd to think of divulging schema as protected, as described it allows for a magical sort of outcome where: "well it's in a database you can't know anything about, and if you can't tell me how to find it you're sol".
Working at a small company with lots of clients I wouldn't want to hand out DB schema outright, but I also go out of my way to search / get the client the data they want ... not reject them.
A private company wouldn't want to divulge their DB schemas because it's advantageous for competitors to see how you're doing things. That doesn't apply to government databases.
Not quite, and the details get hairier the closer you look. The database in-question here is an IBM system. The database itself is used for government functions, making it FOIA'able, despite it being managed by a third party company. IBM even tried to argue that the schema was trade secret, but the statute isn't straight forward. Here's my (successful) response when they tried:
You mentioned on Thursday over the phone that IBM is not too keen on having its database schema released, and, between IBM and Chicago, is seeking an exemption under 5 ILCS 140/7(1)(g) - an exemption that is only valid if the release of records would cause competitive harm. This email preemptively seeks to address that exemption within the context of this request in the hopes of a speedier release of records. It is FOI's belief that there is little room for the case for the valid use of 5 ILCS 140/7(1)(g) when considering the insignificance of the records in conjunction with the release of past documents:
1. Chicago released CANVAS's technical specification [1] seven years ago. To the extent that the specification's continued publication does not cause competitive harm, it is very unlikely that the release of CANVAS's database schema would cause any harm. 2. The claim that the release of a database schema would cause competitive harm is not unlike suggesting that the release of filing cabinets' labels can cause competitive harm.
Furthermore, in your response, please be mindful that the burden of proving competitive harm rests on the public body [2].
[1] https://www.cityofchicago.org/content/dam/city/depts/dps/Con... [2] http://foia.ilattorneygeneral.net/pdf/opinions/2018/18-004.p...
The schema on the last project I worked on was probably our most important IP. Specifically, the ways in which we solved certain circular dependency issues.
I wouldn't take the ability to design a schema for granted. I don't think many people are any good at it. Do not underestimate the value of your work products.
Part of the reason I’m so… enthusiastic… about tech debt is that I’ve worked a few times where we had a competitor whose lunch we were stealing or who was stealing ours and the ability or inability to copy features cheaply was substantially the difference between us.
That quad graph of value versus difficulty that everyone loves? It’s not quadrants it’s a gradient and the difficulty dimension depends quite a bit on context. What’s a 4 difficulty for me might be a 6 for someone else. Accidental versus intrinsic complexity plus similarity to or distinctions from things we have already done.
Maybe. But now I'm really curious how bad that schema must be for them to hide it so viciously.
I think it's just an excuse to avoid making it feasible for the public to get the data.
Your imagination can't cover how bad you might think it is (and yet it isn't that bad).
Or at least I don't want to explain to "20 years later Monday Morning Quarterback".
Maybe their schema has triggers and stuff
Used to be relevant data was in a document but much is no stored in specialized web apps whose data in turn is stored in a db.
Given the Illinois Supremes decision, seems like an opportunistic time to say "Everything is a file".
I FOIA'ed >1M pages of docs for my project cleartap.com, a DB of water quality of the USA.
Most states would charge a small amount to gather the documents.
Michigan wanted $50K to for the FOIA request. I think because of the Flint lead crisis. They wanted me to go away.
I noticed that you do have data for Flint. Did you have to pay it, or is there some appeals process if you're quoted an unreasonable amount?
Great project by the way!
Ended up finding the majority of Michigan through scraping.
For example, https://www.cityofflint.com/wp-content/uploads/2023/06/Annua...
There is no fredom of information if the public is not allowed to know what data the government has.
I suppose I need to change all my column names to random 16-character strings so I don't leave my database insecure!
> Each spreadsheet has a header row, labeling the columns, like “price” and “quantity” and “name”. A database schema is simply the names of all the tabs, and each of those header rows.
This is also how I explain it to my relatives, I'm kind of surprised this analogy (one so direct that it's almost literal) didn't fly with the judges.
If database column names cannot be revealed, then shouldn't that mean the state is also able to redact the headers of all their spreadsheets?
Knowing a spreadsheet header doesn't help an attacker gain access to that spreadsheet in any way. Knowing SQL column names may give an attacker an advantage in accessing a database.
Per the post, this also wouldn't fly.
> Believe it or not, there’s case law on “would” versus “could” with respect to safety. “Could” means you could imagine something happening. But the legal standard for “would” is “clear evidence of harm leaving no reasonable doubt to the judge”. The statute set the bar for me very low and I managed to clear it.
Compare: "Knowing the writing style of current employees may give an attacker an advantage while phishing, therefore, we cannot turn over any memos or emails whatsoever."
Ditto for the org-chart.
It's a reverse vlookup
In the new language proposed in SB0226 (as linked, didnt search for authoritative sources, can't tell how durable that link will be for posterity, arrgh archiving the web is hard etc), doesn't that language leave open a hole for excessive complexity to be a reservoir for FOIA resistance?
Feels like there is an important theme here that SB0226 is dancing around --could government be legible in addition to being "plain-text" transparent?
"plain-text description" of "each field of each database of the public body" and "specific database queries" may not do what you mean.
Not sure how to fix it though.
I could see gratuitous ORMs and database-of-databases patterns winning tax dollars with taunt-them-with-the-schema listed as a feature.
Random thought: someone should drive to Chicago, get a parking ticket, and then make a FOIA request for all of their information contained in that database.
It won't be the whole database schema, but it would be a start.
Short answer -- already been done.
This (spoiler) visualization's going into my eventual post about the lawsuit: https://observablehq.com/d/026992341cc47ff0
Great read. Frustrating that the court ruled that a schema was a file layout, since I don't think it is, but at the same time if it didn't fall under that exception, there is a strong arguments that would be considered "documentation pertaining to all logical ... design of computerized systems". A schema is literally, the logical design of the database, and the database is a part of the computerized system. Once it was ruled that those examples are "per se" exempt it was a long shot to argue that schema wasn't covered by any of the examples.
I completely agree with you that (unlike/despite the Supreme Court ruling), database table/column schema design (and other system designs) should fall under the Illinois statute as "documentation pertaining to all logical and physical design of computerized systems". It's interesting that the law did pick up on that distinction between logical and physical design but none of the parties described in this article did. Logical/physical designs are not just about servers and integrations, they are also about data.
I'm not sure why that wasn't argued by the state and the state argued the database schema was a "file format". Per my reasoning, the state still would have won, but for different reasons.
I disagree with you slightly however and would say that the schema table/column names should be considered not logical but "physical design" while the business naming/meaning of tables would be a "logical design" (or conceptual design). See Wikipedia: https://en.wikipedia.org/wiki/Logical_schema
SQL injection is really about physical schema designs, not logical ones (I do get that every bit of information including business naming of tables/columns helps in an attack, but it does change the degree of threat and thus the balancing tests of the risk which are relevant per the definitions and case law described in the original article.)
So in terms of what the law /SHOULD/ be, the law should not include logical design as a security exception, only physical design. It /SHOULD/ be possible for citizens to do FOIA requests and get a logical understanding of all the database fields without giving them the SQL names that can accelerate SQL injection attacks. In that way citizens could ask for the data by a logical/business-named handle rather than a physical one.
And the state should create logical models or provide data dictionaries with business (not technical terms) on request as part of their FOIAable obligations to their citizens for the data they are maintaining.
My 2 cents as someone designing database schemas for 25+ years.
Schema is definitely software, a operating protocol, source code, and file layout. Maybe also documentation.
A schema isn't software in the sense imagined by the ILGA. If it was, every Excel spreadsheet would be too, and Excel spreadsheets are the basic currency of FOIA.
An "operating protocol" is a step-by-step list of things to accomplish some action. It's a finite state machine for humans. Obviously, a schema isn't that; a schema is declarative, and an operating protocol is imperative.
The court definitively established that SQL schemas aren't source code in the sense imagined by the ILGA. SQL queries can be. Schemas are not.
See downthread for why a schema isn't a file format. In fact, a schema is almost the opposite of a file format.
A court will look at the term "documentation" in the ordinary sense of the word; as in, "a prose description and set of instructions".
"Associated with automated data processing operations" isn't an element in the statute; it's a description of all of the elements.
If the Excel spreadsheet has formulas in it, it's software. If you're just talking about the data in the sheet, i.e. what you'd get exporting it as a CSV, then it's not.
Col types, unique/FK/PK constraints, default values, and computed cols define the steps for handling row inserts/updates/deletes. Even adding a uniqueness constraint to an already-unique col will change how the code interacts with it, specifically how it deals with concurrency/locking. If they said it has to be an imperative programming language, then it's not that.
If they said the schema isn't source code then ok, but I still think it is.
I assure you that Excel spreadsheets with formulas in them are FOIA-able in Illinois. Since we can take that as axiomatic, I think we can put "schemas are software" to bed.
SQL schemas aren't Excel spreadsheets.
That's fascinating, but you just claimed Excel spreadsheets were "software" in the sense of the Illinois FOIA statute definition, and they are not. QED.
You said that SQL schemas aren't software, and that's what this lawsuit was about. If they explicitly say that Excel docs (even w/ formulas) aren't software, I think they're wrong, but that doesn't matter because Excel docs aren't SQL schema.
Now if you want to go by Illinois definitions, SQL schemas are file layouts, that's why the plaintiff lost.
Again: the post explains why the court determined schemas to be file layouts, and none of it involves any of the logic you've supplied here. Even Chicago didn't try to claim that a schema was a "software".
They didn't need to. In the first appeal, it didn't matter because it didn't jeopardize security. In the second appeal, they said it's a file layout.
You also said SQL schemas are declarative, not imperative. Those are types of programming languages, so software.
An Excel formula should be considerd a kind of software, because you cab do code golf in it.
I think a schema will definitely be part of the source listing, either in the main programming language source code or in a some other file used to define or initialize the database. But I don't think it is software, any more than a protocol is software. Software does something.
One tricky aspect of this is that even if the schema itself as a higher level concept doesn't fit into any of those definitions, all existing instances of the schema are likely considered either source listings or documentation. So the instances are barred from release per se, and you can't ask the government to create new documents.
How is a database schema not a file layout?
The article describes why. 2 different db engines (or even instances) can use different file layouts for the same schema.
In many was sql is all about divorcing the schema from the files.
But on the other hand, in all database systems the schema is used to determine how the files are laid out. Although I suppose the same thing could be argued for any data that is stored in a file, excepting that a schema is metadata that determines the organisation of data so it's a bit of a special case.
In a Microsoft Word document, the section headings also tell Word how to lay out the Word document file.
Do you mean that section headings aren't a file layout? That's their entire purpose.
Edit: If you're talking about the byte representation only, I don't think section headings indicate the placement of the body's bytes.
You have found an argument that proves too much.
There's a solid chance that the schema gives away what DBMS is being used. But even if it didn't, I'd still call it a file layout in this context.
The DBMS is almost definitely going to be mentioned in RFP or specification documentation. As it was in this lawsuit.
So?
So if you have the schema and the DBMS, you probably know how data is arranged in the files ("files" in the filesystem sense).
Is your argument that government agencies should also withhold the names of filing cabinet manufacturers? :)
Just that it's a file layout. Or even if you strictly define a file layout as say an ext4, NTFS, or FAT file tree, that revealing the schema is revealing the file layout.
I don't know why they don't want to reveal file layouts, but for whatever reason, they decided it was "per se" exempt regardless of the security implications.
It's obviously not a file format. The same SQL schema can generate N different files, with N different layouts, for N different databases. By the logic you're using ("schema" + "database vendor" = "file format"), a Word document outline is also a file format.
The parent asks "how is it not a file layout" not "can you guess the file layout?" given it.
I am a human, you know I have a kidney, but I am not a kidney.
If you send a copy of the code, is that sending the code? If it is, what about sending a copy of the code with a Caesar Shift?
Another way to think about it is that if a SQL schema is a file, so is an Excel spreadsheet template.
It's interesting that the opening analogy in the post uses an Excel spreadsheet as a great way to explain a database. It's such an easy next step to say the way an xls/ods file is saved is a file format but the column layout in the tabs/tables are the schemas. The court (and the city) playing these games is so scary since it is so biased toward all modern government data being covered by FOIA exemptions.
File or file layout? Cause both of these are probably stored as files, .sql and .xltx respectively.
An Excel spreadsheet template is an arrangement of rows/columns/cells which is encoded in a XML document which is encoded in a ZIP file archive.
I don't follow your point.
It literally does not describe a file, and does not literally describe the data layout of anything on disk (though with enough knowledge, you may be able to infer facts about probable layouts).
> does not literally describe the data layout of anything on disk
Huh? Depends on the DMBS, but each InnoDB table is a file.
And the schema determines the file structure.
Schema is an abstraction over the file structure. Different RDBMSes will use different file layouts for a given schema. The same RDBMS may even have different engines that use different file layouts, or may change file layout between major versions.
"Determines" is too weak: it must be "is". If "schema is file layout" is true, then sure, a schema is a file layout. But if it is merely "schema determines file layout", then no, a schema is not a file layout.
Abstractions are notoriously leaky in DBMSes. First off, they don't even use the same SQL spec. Give me a schema that uses anything Postgres-specific, and I can tell you what the bytes on disk look like for a given row or index.
I think it's a moot point anyway because the language is broader than just files in the filesystem sense, which is basically what the court said too.
> but each InnoDB table is a file.
A table isn't a schema, it is a component of a schema, and most databases don't use InnoDB.
> it is a component of a schema
So if you have the schema, you have the tables.
The schema describes the database layout. The file layout (if you were going to call it that) in a modern RDBMS would describe how the RDBMS implemented a particular database layout as described by the schema.
Because it doesn't describe how data is laid out on disk.
Neither does a file layout. FS will decide that... even then, not physically.
We're talking about "file layout" at the application level, not the filesystem level.
But your comment illustrates just how difficult it is to nail these things down, based on inherently imprecise language.
So you mean the filetree and file contents, as seen by userspace program?
It's meant to be imprecise, because they didn't want some "gotcha." If they say we won't reveal the disk layout, technically you can't tell that from the filetree. If they won't reveal the filetree, but this is SQLite, it's always a single file. If it's file tree + contents, well the CPU byte endianness might matter for some DBMSes, even though you could just try both.
We can't FOIA details about how xls file laid out internally, despite that xls file being FOIA'ble itself. That's the file-format we're talking about.
Got to see this happen day by day on the Midwest Venture Partners Slack. There was another lawsuit Chappman and Tom did for laser based speed detection in Chicago.
> [Public bodies] shall provide a sufficient description of the structures of all databases under the control of the public body to allow a requester to request the public body to perform specific database queries.
I sure hope the impact of this is not that government entities switch to schema less databases!
"Schemaless" is like "serverless" in that there's always a schema, even if it's not enforced by the database and instead applied dynamically by the application layer.
>just self-important message-board hedging
I can confidently say it does not stop at message boards for many people, self included
It's a real issue when writing an affidavit or testifying. Lots of ingrained bad habits.
Interesting takeaways from me:
All that pompous sounding legalese can still be ambiguous! I feel less bad for not understanding contracts that have 100 word compound sentences.
Legal people can't keep up with our tech jargon but they have their own jargon including "predicate" lol. So same logical thinking, different jargon framework.
Question: why do they want the schema not the data?
Because once you have the schema you can issue FOIA requests that include queries for them to run.
What if you guess common table names? Wonder if they send back the error message.
Juxtapose this legal process with DOGE hoovering (in more ways than one) data willy-nilly from everywhere. The dissonance between THIS uninteresting DB schema being so rigorously protected while massive amounts of sensitive data is completely misappropriated is painful.
> where the only way to get at the underlying data is to FOIA a database query
Was this ever attempted?
SELECT * FROM `information_schema`.`tables`;Yep, that was done in the FOIA request related to this lawsuit:
select utc.column_name as colname, uo.object_name as tablename, utc.data_type as type
from user_objects uo
join user_tab_columns utc on uo.object_name = utc.table_name
where uo.object_type = 'TABLE'
Yeah, it's obvious the double standard here, then. Curious indeed why they are so adamant to keep the schema/data secret.
Because they know that eventually the data contained in that table is going to be used to support some sort of lawsuit that their parking enforcement activity is biased, and is targeting people of color.
It's already ridiculous that they spent several years blocking this request while it went through court. If the plaintiffs spoke to pretty much anyone involved in maintaining the system, or with any of their internal infosec people, they would know that there's no real security risk to releasing this information.
They've already spent orders of magnitude more time and money litigating the issue than it would take to just release the information in the first place, so this is clearly not a cost or resourcing issue.
They don't want to release it because they'd prefer it's secret, because secrecy makes it harder for the public to hold them accountable. That's all.
There is an explanation for the fight that doesn't involve something nefarious with CANVAS (though I think CANVAS is dodgy from talking with Matt).
The precedent set here will let data journalists (like Matt) setup effectively automated FOIA workflows on _any_ database they can get the name of for a FOIA request. So even if _this_ db isn't dodgy it enables any of them that are to be found quickly.
Or even less cynically, its just going to cost a ton of resources to respond to all those automated FOIA requests.
I said in another comment but I suspect the column names themselves are incriminating (basically saying this person doesn't get a ticket because they are in a special club, that's probably not technically legal)
is_cop bool not null default false
Anyone with a legal background willing to opine about potential workarounds to this ruling?
Specifically, would a request for “data field labels” (i.e. a column list without any table structure info) likely circumvent the exemption?
I think that would run afoul of
> The one big limitation of Illinois FOIA (with FOIA laws everywhere, really) is that you can’t use them to compel public bodies to create new records.
Unless for some reason they already had a list of columns without table structure.
(Not that I claim to have a legal background)
I had that thought too, but my naive rebuttal would be that the column data already exists by default in any standard RDBMS as information_schema.columns. No new record creation required.
Yes, but that requires someone to execute a query on a database and package it as a report?
Yes but what if we come up with a directive that every FOIA request must be logged into a DB. Therefore every request is automatically invalid as it requires we create a record!
/s
Not a lawyer, but why not use opensource as an example? Many successful public e-commerce websites have public schemas and aren't all hacked.
This is part of what discouraged me from going to law school. So much of litigation is Kabuki theater, grant rhetoric not in any way intended at achieving a just or logical outcomes, but designed only to the person in power an excuse to decide however they had already wanted to decide before the case was tried.
> So much of litigation is Kabuki theater, grant rhetoric not in any way intended at achieving a just or logical outcome
Agreed, that is what this sounds like. What stood out to me is the remark »“only marginal value” is just self-important message-board hedging«: it's also simply correct, but the author concluded that they shouldn't have said it because "marginal" plus a bunch of explanation didn't have the rhetorical value that "no" would have had
Someone could legitimately configure a WAF-like system to scan for various ways of querying the database schema coming in as HTTP requests (keywords like "information_schema", encodings thereof, etc.), which will always be hacking attempts and can be blocked. If you already have the schema, you can craft a query without needing to bypass that restriction first. Is this likely to be a serious barrier at all? No. Is it anything to do with self-importance? I don't see how that's the case, either. It seems simply correct that this is marginal (situated in the margins, not the point, not important to discuss), but by saying nothing but the truth, now the other side blows that up to something much bigger and tries to get the court to agree that, "see, their own expert says it has value!" And so this expert concludes that they shouldn't have said it, that they should have just said "no value" which I would say is wrong, but so marginally wrong that it's hard to prove for the opposing side that it is not fully correct, and thus being less correct helps you in (this) court... so it's about rhetoric as much as being an expert...
What stands out to me about this article is the time between court appearances. Seems like if you want to accomplish anything in court you need to be prepared to spend years of your life on it.
Can confirm this is the case everywhere. Even before taking anything to trial, one can spend months on trying to come up with a mutually agreeable solution, in my case getting seemingly one step further each time¹. I'm not sure I'd not just give up and move on with my life if this dragged on for years and wasn't about something that majorly impacts my life or that of a loved one
¹ Details: it was a warranty case, so first they agreed to repair it, then they didn't do that (but maintained that they were going to, whenever I asked about the status), then they agreed to refund, then they didn't do that, then I set a deadline, they iirc agreed, then they didn't pay, then I included specifics of what my next steps would be (lots of research here, seeing what even my options are and what I can truthfully claim that won't get shot down by a judge later) if they didn't pay before some other deadline (so I showed I was serious now), then the deadline crept up and they finally refunded the day before it would expire and I was frankly disappointed because, by now, I was prepared and ready, and all I got was the original sum that I had paid them. I checked the legal interest rate and changing my demand to include that simply wasn't worth wasting more time on this, and I didn't find any sort of precedent that I could bill any time I provably spent, not even to the value of minimum wage, so any time you invest is just lost free time (which I didn't have much of during that particular year). Protip: scroll down the reviews before buying something worth more than a few tenners from a small store. I wasn't the first person who had to threaten litigation...
And of course, people and entities (private or as in this case public) who have a lot of resources take advantage of that, a state of affairs which often serves to perpetuate injustice indefinitely.
I thought the same thing. Sure it's async but still you have to keep this in your mind for a very long time.
Do stored procedures count as part of the schema? I've recently found a SQL injection vulnerability in a client's SP that was using concat (very badly)
Wowzers, that was a lot of words to express something that's very simple.
A database schema is just an empty form. By looking at an empty form, you know what fields have be filled in, what type of information they'll contain, etc.
Of course people making data requests need to know what forms are being used to collect and store information.
As for security - not letting people do anything because 'it might be dangerous' is bonkers. The way to secure databases has been known for decades. Let's start living in the 21st century :)
The whole back half of the post is about why the analysis is not as simple as you suppose it is. We had no trouble establishing at Chancery Court that schemas don't endanger security. That's not why the case failed at the Illinois Supreme Court. The IL Supremes did not decide spontaneously that schemas actually are dangerous.
This was fine, legally, but I'd be pretty irritated if someone I knew wasted everyone's time on this. The schema clearly is (marginally) useful for hacking, but who cares; it clearly is a file layout also, but who cares; those matter legally but not morally. Morally, this is just dumb: it's not something they really needed, and they're just irritating people and wasting resources for the fun of it. Shameful.
Just because the article gets into fine details doesn't mean it's silly. They're working with what they have.
But after reading more, I agree. The point of FOIA in the first place was "access by all persons to public records promotes the transparency and accountability of public bodies at all levels of government." Not "pushing FOIA statutes to their limits, sniffing out buried data and bulk-extracting it with clever requests."
If he's just asking for his own parking ticket records, ok. This isn't in the spirit of that. Separately, I agree that the SQL schema is software, a type of file layout, and other things in that exemption, and I'd say that again as an expert witness.
No. I'm involved in local government, and on the citizens commission where we keep track of our our municipality (adjacent to Chicago) stores and manages information. I'm acutely familiar with how people are spending their time in these organizations, and what is and isn't a big lift for them.
Increasingly, year over year, more and more information that would previously have been stored in filing cabinets or shared drives is moving into turnkey applications that municipalities buy and enroll all their data in. Those applications are opaque. But almost all of them are front-ends to SQL databases.
Being able to recover schemas from publicly operated databases is vital to keeping public records and data public, rather than de-facto hidden from inquiry.
Matt's suit was anything but a waste of people's time. Hopefully, it'll result in a change to our state law.
See here: https://news.ycombinator.com/item?id=43176625
FOIA requester responded in comments saying they received a tip indicating illegal practices, and noted in his article that he had previously uncovered evidence of over-policing in black neighborhoods.
I think a file layout describes the exact arrangement of bytes in a file. A schema is higher level. It describes what is stored, not how it is stored. A database could be one file, or a file per table, or a file per column. Data could be stored across multiple drives.
am I the only disappointed there's no mention of little Bobby Tables?
I got to about 1/3rd of the way before I noticed my eyes were kinda struggling to read the article. Toggling different CSS rules, it's the #333 gray color. Turning that off is instantly better. The custom font is much thinner than the default, but that by itself doesn't seem to be the issue if the color is (closer to) black. (There is also a font-weight rule, but toggling it makes no visual difference in Firefox. Maybe the text is intended to look different?)
Since there is no contact method on the website, figured I'd mention it in a comment; hope this helps