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.
Bear in mind that Matt technically lost this, even with the backing of some of the absolute best civil rights lawyers in the country, Loevy and Loevy, fighting on his behalf. This shows you the absurd difficulty in fighting city hall, especially if you're crazy enough to do it without representation.
The one thing working in our favor is what is proposed in TFA: change the law. Once the state Supreme Court has ruled you're hosed unless you can get an amendment. Illinois has a very strong history of amending its FOIA statute, although a proportion of those changes are to further protect information from disclosure, not always on the side of sunshine.
Another change that needs to happen is strong punishment for bodies who lose these fights. In Illinois this is limited to a "$5000 civil penalty" against the body. What is a civil penalty? It's vaguely defined. They used to throw the money to the plaintiff, but in the later cases I fought they simply awarded the money to the county. As one State's Attorney said to me "I don't care if I lose every case, I just write a check out to myself."
(one final note: be careful what you wish for when you litigate, you can end up with an appellate decision like this that solidifying in law the exact thing you were fighting. It's nobody's fault, but it happens. I ended up with one absurd decision that removed prisoners' rights rather than enhanced them.)
A losing public body is also generally on the hook for attorney's fees, which can be considerable. But the general problem here is that the public bodies are all spending someone else's money, so the real deterrent you have is how much of their time you can credibly threaten to eat up with legal actions.
That's true, as long as you are represented. I knew one lawyer in Illinois who would sit in FOIA court and take all the non-represented persons aside and offer to take their cases and split the attorney fees 50/50. I believe it isn't strictly above-board, but it is a solution to a problem.
People don't like being put under oath, so you can somewhat temper a public body's future refusals by deposing them or sticking as many of them on the stand. Especially with depositions, if you aren't represented then you can't be giving any attorney discipline for asking completely outrageous questions to force the deponent to admit crimes etc under oath.
I went up against my muni over their refusal to release their police General Orders (which seems real dumb in retrospect; we got the General Orders from most of Chicagoland with no protest†). I reached out to Matt Topic, who offered to sue for free, or send a nastygram for a billable hour.
I ended up doing the latter, because I gotta work in this town, but one consequence of fee recovery is that it's much easier to get representation for a FOIA suit.
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?
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.
You can "always" do that? Well I just did that. My database said: no such table: information_schema.columns
And what if my database had disabled this capability entirely?
Also, is there anything implying SQL here at all? Can't other databases with injection "capability" have schemas?
> Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot.
No, you can't say it with such certainty at all. It really depends on what else you're triggering in the process of that SQL injection. You could easily be triggering something (like a password reset, a payment transaction...) where you're severely limited in your attempts.
> 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.
Your reasoning and motivation is reductio ad absurdum.
It does not make sense to base your system security on hiding from the public that your 'Users' table is called 'Users'.
If you are vulnerable to this attack, the guilt rests on your deplorable application code, not whether or not your schema table names are known. If we should follow your logic, we would have to name our Users table U_ZER_CLEVER_S because naming it something people could guess would be a vulnerability.
> You can "always" do that? Well I just did that. My database said: no such table: information_schema.columns
Don't expect attackers to give up after one try. It depends on the database software, not everyone implements this exact ANSI standard for reflection but every database supports reflection. That's why the first step after finding a SQLi is to fingerprint the database software and go from there.
> And what if my database had disabled this capability entirely?
You can't disable it, lots of software, database features,
ORMs and clients rely on reflection. If a client can query a table they also can retrieve metadata about that table.
Absolutely, we have very strict lockdowns on the tables and views available to the users that our application uses. The permissions system in Postgres (for example) are very extensive. We even deny delete and update permissions for most tables so they become append only.
Nevermind you are right its possible, but I still think it breaks so much stuff that at least I've never seen anybody doing it or recommending it. All kinds of ORMs and migration tools would break for example. But I guess it would be a defense-in-depth strategy.
Yeah those tools may break if such a change is introduced suddenly, without testing etc. But that's not how normal reality for most companies look like, such rules are there for 2 decades at least. DBs are very old tech without much change in past 20 years and this is DB security 101.
Not even going into reasonability of ORMs, most of the stuff I've seen or implemented added practically 0 added value, and added hard-to-debug issues down the line as software evolved. Cargo culting at its best, often done on trivial schemas that could handle either direct SQL or some sql-query-to-object mapping easily.
There is one further problem with this entire sub-discussion:
There are two mitigation strategies discussed:
- A: guaranteed SQL-injection-proof (SQL injection impossible.)
- B: Having non-obvious table-names and 'secure-defaults' (e.g. INFORMATIONSCHEMA disabled).
So, the original commenter says, he wants to _hide the schema_, so that B can protect him in case of A.
Well, failure of A is Amateur Hour. If you fail on A, I highly doubt you would have delivered correctly on B.
To write it out in plain text: If you have set up and manage an application with SQL injection errors, I have a hard time seeing you still taking care to disable /enable obscure security defaults, or take care to avoid obvious and trivial table names.
Just to put icing on the cake: As soon as you have an SQL injection attack,
a simple select * from randomTable or DESC randomTable would give you the table COLUMNS, so it utterly makes no sense to want to hide those column names - you have already lost them! (in the case you are arguing you need their protection in).
..Unless you argue that the guy making sql injection applications ALSO has set up a secure default to disallow select *..
In my experience, SQL injection is evidence of work of the sloppiest and immature nature; it was bad in 2003, and presumably still is.
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?
I once did an security assessment for a product such as what you describe. Among other problems with it, the product itself had SQL injection vulnerabilities
If you are mature enough to do that, you're mature enough to net SQL injections in the first place. There shouldn't be that many handwritten queries to review in the first place as most mundane DB access is usually through a framework that handles injection properly...
Zane Lackey (with Dan Kaminsky) gave a talk that discussed doing literally that sort of things, back in 2013. Zane went on to found Signal Sciences (acquired by Fastly), doing this sort of stuff in the 'WAF' space.
I guess the main difference is that a WAF attempts to spot things like injection (unbalanced delimiters, SQL keywords in HTTP payloads where SQL shouldn't exist, etc.) typically without knowledge of the schema, whereas GP is talking about the DBMS spotting queries where queries must exist but disagree with the schema. Might as well do both, I suppose.
That’s not what the talk is about - it’s using dbms query error logs to spot attackers. Stuff like “table doesn’t exist” or “invalid syntax” on your production database can be extremely high signal indications that something is wrong, potentially maliciously so.
In the very early 2000’s I worked at a company building something along those lines. We could analyze SQL and SMB traffic on the fly and spot anomalous access to tables/columns/files, etc. Dynamic firewalling would have been the next progression if the company didn’t have other issues.
So if you deploy code before you run the associated db migration, or misspell a column name, you magnify the impact from whichever code paths (& application tier nodes) are running the broken SQL, to your entire production environment.
Simple variation to a hard shutoff: immediately page "significant risk a successful sql exploit was found", and then slow down attackers:
If an SQL query requests an unknown table, log the error, but have that query time out instead of responding with an error. Or, even better, the offending query appears to succeed, but returns fake table data, turning it into a honeypot built-in to the DB. This could be done at the application layer, or in the DB.
The goal is to buy an hour for defenders to determine how to respond, or if its a red herring. There are a variety of ways of doing this without significant user impact.
Yeah it's definitely something that could do more harm than good to a company long term. But I'm sure there are instances where this tradeoff is worth it. They would invest more heavily in runbooks or maybe even ci that runs migrations on deploy. Deleting columns would need to be done on your deploy + 1. Probably no rollback at all.
A good DBA would restrict the account so that it can't access the information schema. It's easy to imagine an environment with a vigilant DBA and less vigilant web developers.
This makes sense, but the the vast majority of tooling including ORMs, autocomplete SQL IDEs, and even suspect application code relies on table descriptions and listings provided by the information schema
That is why we have development and production environments. The production environment is expected to operate in a potentially hostile space and does not need developer conveniences beyond the ability to generate alerts and produce logs, which should be stored in a safe way, everything else should be locked down as much as possible.
Ah so what you're saying is that we ought to rename our logins table to "duckwords" because nobody will ever guess that? Also we should probably store passwords in plaintext but name the column "entercod3" because nobody will think of that. Oh and we should use printf with %s to build our queries right?
> I don't understand the argument that knowing the column names doesn't help an attacker?
So Kevin Mitnick supposedly did most of his hacking using "social engineering". He'd call up some person, pretend to be in some other department within their organization, and ask them for some specific bit of information he needed to further his attack (or ask them to change some specific thing that would allow him to further his attack).
Would knowing the structure of Illinois governmental organizations help someone perform social engineering attacks against them? Yes, absolutely.
Should Illinois therefore keep the internal structures of their organizations -- the department names and the officials who run them -- secret? No, absolutely not.
First of all, if an attacker doesn't know them, they'll just use other social engineering attacks to figure them out; i.e., hiding the structure doesn't stop social engineering attacks, it just slows them down. Secondly, the value to the public of being able to navigate governmental structures far outweighs the cost of potential attacks.
This seems to me to be a direct analog: The "organizational structure" is the "database schema", and the "willingness to help a random person on the phone who seems to know what they're talking about" is the "SQL injection vulnerability". If an attacker knows the schema, their job is faster; but if they don't know the schema, they'll just use attacks to figure out the schema; so keeping it private doesn't stop an attack, only slow it down. And the benefit to the public of being able to issue FOIA requests far outweighs the cost of potential attacks.
> And I don't think I disagree with the court on schema vs. file layouts either.
I disagree that the law should prohibit disclosing "file layouts" but it's pretty clear that the law does block that, and I fundamentally agree with you that schemas are directly analogous to file layouts and thus restricted.
A SQL schema literally does not indicate the locations of data inside of a file. In fact, the whole reason schemas exist is to decouple the relationships between table rows and the pages and indexes that store that data. We had relational databases before SQL, and there are non-SQL relational (and non-relational) databases today, but you program them, at the query level, with code that is aware of what tables live where.
A schema is the opposite of a file layout. A schema is to a file layout what a Google search is to an IP address.
If you tell me that you have a closet for your jackets and another closet for your shirts, you're telling me how clothes are laid out in your wardrobe. Specifically, you're telling me that you're laying those out separately, and able to deal with them independently, with little interference between the two. It's not the entirety of the layout information, but it sure is some of it.
If you tell me that you have a column for your first names and another column for your last names, you're telling me how names are laid out in your database('s files). Specifically, you're telling me that you're laying those out separately, and able to deal with them independently, with little interference between the two. It's not the entirety of the layout information, but it sure is some of it.
Sure -- in theory, you could be actually throwing everything together into a dumpster, then paying enough people to search it all in parallel when you want to retrieve that red jacket. If you're actually doing that, maybe you could legitimately claim that you haven't divulged anything about your closet's layout by telling me that shirts and jackets are separate. But chances are pretty darn good you're not actually doing that (and I would know this for a fact if I already somehow knew you were actually using closets built by Joe down the street), and thus actually are exposing layout information by telling me that you're storing them separately. One security implication of which is that, the moment that I get a glimpse of your closet and notice that it contains a shirt, I know it's not the one with the jackets, and I can skip it when trying to steal that expensive red jacket.
It's either a file layout or it is not a file layout. If you write an affidavit saying it's "sort of like a file layout", the conclusion will be that it is not one. Now, the Illinois Supreme Court found that it was a file layout (wrongly). But they didn't use any of this kind of message board logic to do it; they pulled up a definition for "file layout" from a technical dictionary (which, ironically, pretty clearly established, even more than this thread does, that schemas aren't file layouts), and then they pulled up a definition of "schema" from Mirriam-Webster, and the definition of "schema" was so abstract it could have matched anything.
If anybody on the Illinois Supreme Court had known what a schema actually was, we'd have won the case. Further, if the definition of "file layout" had been more material to the Chancery case, it would have been in the trial record that it wasn't one.
> Now, the Illinois Supreme Court found that it was a file layout (wrongly). But they didn't use any of this kind of message board logic to do it; they pulled up a definition for "file layout" from a technical dictionary (which, ironically, pretty clearly established, even more than this thread does, that schemas aren't file layouts)
"Wrongly" was exactly what I just spent an hour writing a long comment disputing, with a detailed explanation. Specifically, with a real-world analogy between “a description of the arrangement of the data in a file” and “a description of the arrangement of the clothes in your closet.”
If I understand correctly, you're saying that you expect items in a column to tend to cluster near one another on disk. Notably though that doesn't give you any sort of relative or absolute offset. Neither does it have anything to say about, for example, blocks of different types which might be interleaved. Or compression. Or indexes. Or copy on write related garbage collection. Or journaling. Or any number of other things.
Now if you wanted to argue that a schema serves the same purpose as a file layout, ie that it's how a programmer interfaces with the data, and that it impacts workload performance, that would be fair enough. And given that laws are all about intent perhaps that would be relevant. (Or perhaps not. I didn't read about the case yet.)
But I think it's fairly reasonable to say that in typical usage an SQL schema is decidedly not a file layout in a literal sense.
> If I understand correctly, you're saying that you expect items in a column to tend to cluster near one another on disk.
That's one thing I'm saying would be sufficient to consider this file layout, yes. I'm not saying it's necessary. Databases can obviously be row-oriented too. Knowing that they don't cluster would also be layout information. As could any number of other things.
> Notably though that doesn't give you any sort of relative or absolute offset. Neither does it have anything to say about, for example, blocks of different types which might be interleaved. Or compression. Or indexes. Or copy on write related garbage collection. Or journaling. Or any number of other things.
It doesn't have to include offsets or any of those other things. File layout information could be as simple as "data should be aligned to a page boundary for performance" or "this field must reserve space for up to 16 characters" or even "data from different records should not be stored in an overlapping manner, to allow fast erasure"... I could go on. And notice the wardrobe layout example doesn't have offsets either, but the decision to separate jackets from shirts is absolutely one about layout nonetheless.
> But I think it's fairly reasonable to say that in typical usage an SQL schema is decidedly not a file layout in a literal sense.
It is not complete file layout information. But it certainly can be part of the file layout information.
Imagine you had a table with columns name1 VARCHAR(64) and name2 VARCHAR(64) in that order. Now imagine you modified a couple of bytes on the disk, such that you swap the 1 and the 2. You can imagine a database where that would be sufficient to confuse it into thinking the two columns had swapped contents, right? Could you really claim the schema didn't contain any file layout information in that scenario, when it certainly affected which bytes are interpreted as belonging to which columns?
Note that "some information related to the file layout" or "some information that has an impact on the file layout" is not "the file layout" in a literal sense. Thus it seems to me to follow that the answer to the question "is this a file layout" should be no.
Symbolically it isn't [ schema -> file layout ] it's [ schema, engine version -> file layout ]. Even if you had that additional information, neither item by itself nor even the pair together would be correctly considered a file layout. If I have a function f( foo, bar ) -> baz neither a foo nor a bar is a baz. I can fairly trivially fix a sandwich out of bread, peanut butter, and jam; in no way does that imply that the three ingredients sitting next to each other on the counter are a sandwich.
For that matter, even the [ schema -> file layout ] case isn't technically a file layout any more than a json blob is an xml blob. Being trivially translatable doesn't change the definition.
Compare that with the question (also commonly asked by courts) "is thing equivalent in intent (or use, or ...) to other thing" in which case the answer might feasibly be yes.
> Could you really claim the schema didn't contain any file layout information in that scenario, when it certainly affected which bytes are interpreted as belonging to which columns?
In that example you have made an educated guess about the file layout and then taken advantage of that (guessed) information. "You can imagine a database" tells you everything you need to know here, namely that this is entirely dependent on the implementation. So yes, I would claim that the schema did not on its own contain any file layout information though in conjunction with knowledge of the implementation it could be used to derive such.
> I can fairly trivially fix a sandwich out of bread, peanut butter, and jam; in no way does that imply that the three ingredients sitting next to each other on the counter are a sandwich.
What is "sandwich" in this analogy? Nobody is claiming the schema is a "database", or a "table". I was saying it's one component of the file layout.
Using your own analogy: if you know you put the jam near the peanut butter, you know part of the ingredient layout. You can't say "it's not ingredient layout if you haven't told me where the bread is."
The point about the sandwich was that the inputs to a function are not correctly referred to as its output. Those are distinct things.
If you wanted to further extend the analogy to apply to schemas then I guess the recipe would be the database engine and the final product that you eat would be the file layout. Knowing that the final dish will include jam does not mean that you have the final dish in your possession. The jam sitting on the counter is not the final dish.
Importantly, you don't even know how I'm going to use the jam. I could put it only on one half, or I could arrange it in stripes, or I could even use more than two pieces of bread! I might not even make a sandwich! I could even throw it all in a blender and make a (disgusting) smoothie.
We can successfully interpret the two words “guinea pig” without it pertaining to either pigs or things coming from Guinea, so I’m sure this is also possible.
I'm not sure whether 'file' necessarily has to refer to the 'Unix' view of a 'sequence of bytes'? Or just 'some organisational unit of information'? Ie like the stuff you would put into a filing cabinet?
The 'sequence of bytes' view is just one specific level of abstraction. It's not what's actually on disk because of things like compression, encryption and fragmentation.
Database schemas are a different level of abstraction.
DBs can be files on disk though? Besides they're a bit like easy hand rolling powder mix for filesystems. Filesystem entries has properties like filenames and inode numbers and file contents. Databases has columns like emails and membership IDs and their favorite cookies. I don't think "file layout" is an absurd framing.
It is in literally no sense a layout; the whole point of a schema is that it doesn't tie you down to a layout. SQL schemas make sense even in the absence of files!
You suggest that we interpret "file formats" as exactly this -- no more, no less. This approach is also called "textualism". The other option is to interpret "file formats" in the context of the law that includes these words. Or: what exactly did the lawmakers have in mind when they said that (a) government needs to provide information; (b) except for several cases, of which one is (c) "file formats". What kind of information did they think it was ok for the government not to provide?
I agree with the Court's argument that "the information about how the actual information is stored and connected one piece to another" is what the lawmakers meant in this case.
- If the actual information is stored in the files, the government does not need to disclose how these files are organized ("file formats").
- If the actual information is stored in the database, the government does not need to disclose how the database is organized (database schema).
- If the actual information is stored in the block memory -- with structs and pointers -- the government does not need to disclose the structs and the pointers.
The "textualist" opponent would of course argue, as OP did, that the second and the third example aren't excepted by clause (c) because "when there is no file, there could be no file format". This however is missing the point (in my opinion), as it doesn't see the forest for the trees.
> A SQL schema literally does not indicate the locations of data inside of a file.
That's only true if you apply eg the Unix definition of what a file on a file system is (like a sequence of bytes or whatever).
For all we know, the law might take a broader view. Something like: a 'file' is anything that in the olden days you would have stuck into a filing cabinet.
The 'Unix' definition isn't even particularly natural: it's one specific level of abstraction. On disk, the bytes aren't necessarily laid out one after another. Especially with fragmentation, compression and encryption going on.
An SQL schema tells you how data is laid out in a different layer of abstraction than the Unix view of bytes. But that view isn't the only one that the law can mean by 'file'.
>> And I don't think I disagree with the court on schema vs. file layouts either.
> I disagree that the law should prohibit disclosing "file layouts"
Note, the court wasn't ruling what the law should say, only what the law says. At least that's my understanding of it. I certainly wasn't opining on what the law should say.
Understood. I mention that distinction only because I find many people (not you) who say that "X law doesn't apply because if it did, it would be bad" vs directing your ire at the actual laws, which are poorly written and the legislators who are negligent in fixing those laws.
Courts should decide based on the law, not based on what is "good".
> Without additional context, I would interpret the term “file layout” to mean the file and directory structure of an application.
I would interpret it to mean a description of what the file contains and where. This is information you need if you have a mysterious file and you want to parse it. It's also information you need if you have some data and you want to create a readable file that expresses it. But for the concept to apply to a database schema, (a) the database would have to be a file, and (b) the schema would have to specify where the information in the database is stored. That's difficult to do, since the schema has no knowledge of how much information there is in the database or how it might be written down.
Agree, and, I don't even understand why it's in there in the first place (it should just not be) but that's a job for the legislature to resolve, not the courts.
> 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?
Lots of things are "of value". That's not the bar the statute sets. To the extent something isn't per se exempted by the statute (as the outcome of the case established schemas are), the burden is on the public body to demonstrate that disclosure Would jeopardize the security of the system.
It still seems like a massively gray area: despite the distinction between "would jeopardize" and "could jeopardize" as explained by TFA, the definition of "jeopardize" includes "danger" which means "could lead to harm" not "would lead to harm" at which point it hardly matters whether a thing "could endanger" or "would endanger" the security of the system.
"Would" versus "could" has nothing to do with why your analysis doesn't hold. If something doesn't enable people to attack a system, but is merely one of the valuable things you could get from that system, it does not jeopardize that system under Illinois law. The standard of proof for the jeopardy doesn't enter into it, because no claim of jeopardy has been made.
Again: this part of the case is settled. We didn't lose at the State Supreme Court because the court was worried there was jeopardy, but because they re-read the statute as per se exempting schemas as "file layouts".
How is it that this wording stuff isn't already decided globally? I mean, the concept of dangling modifier has existing for centuries, do the courts really decide this kind of thing on a case-by-case basis by random dice roll?
Whereas math, science, and engineering use language as a vehicle for attaining truth, the legal profession too often regards it as truth.
The greatest legal scholars of the state of Illinois believe there is more decorum in querying Merriam-Webster than there is in reading tea leaves or consulting a Ouija board, but they are wrong. All too often, jurists make decisions based on unconscious accidents of wording by their predecessors, then compound it with their own fallible powers of interpretation and deduction, further cementing their wrongness as "precedent." Instead of addressing this core ambiguity of the FOIA exemption, or attempting to appeal this nonsense interpretation of an undefined term, or introduce better linguistic standards to the legal profession at large, the path of least resistance for victims of litigious violence is to add more complexity in the form of endless amendments. This is what Matt and friends must now pin their hopes on.
Little wonder how one can spend a lifetime specializing in the (martial) art of litigation.
> If something doesn't enable people to attack a system, but is merely one of the valuable things you could get from that system, it does not jeopardize that system under Illinois law.
The problem I have with this is that the schema isn't something an attacker recovers for its own sake. It's something the attacker recovers in order to further their attack. This necessarily means that it does enable people to attack the system. That's the only value an attacker sees in it.
> Again: this part of the case is settled. We didn't lose at the State Supreme Court because the court was worried there was jeopardy
Doesn't matter to the discussion; the court, Supreme or trial, can be wrong as easily as it can be right.
I don't understand your argument. If I have a SQLI, I can, as you acknowledge, fetch the schema. So what does it matter if the schema is published a priori? All that matters is whether I have SQLI.
No, as other comments in the thread have pointed out, you can easily have an SQLI that doesn't send information back to you. You may find value in changing what's in the database even if you can't read from it.
If you do have the ability to retrieve information, then one of the first things you'll do is retrieve the schema.
And the reason you'll retrieve the schema, if you can, is that it facilitates the attacks you actually want to make. It has no value to you other than enabling your attacks. This observation seems sufficient to answer the question "does knowing the schema enable attacks?".
There is a whole sub-field of software security dedicated to retrieving information from SQL injections that don't directly return results. This is not a plausible objection.
Maybe for this case, but it sounds like enough hinges on the details of the system that in another database, a court could uphold that there "would" be jeopardy instead of there "could" be. So you won on the more fragile part of the ruling.
On the other hand, interpreting the law as exempting database schemas is something that can be applied to any computer system, and it presumably sets a binding precedent (I'm not familiar with Illinois jurisprudence, but that's how I'd expect something called the State Supreme Court to work) so losing on that point is worse for future cases.
Losing on what point? Everybody agrees it is bad schemas are per se exempt from FOIA. On the security concerns of releasing schemas, we won in basically every court.
> If it's the product of an attack, but not the end goal, surely it's of value to the attacker?
Well sure, but it doesn't help them attack. That's like arguing that since the bank robber wants dollar bills, dollar bills must be a useful tool for breaking into bank vaults.
If both sides agreed to the analogy of giving the bank robber the blueprints to the vault, I think any lay judge would agree that endangers the bank's security.
I'd say it's more like knowing the layout of the drawers inside the cage. If a robber is inside the cage, they've already won. And if an auditor is checking the bank has what it says it does, they've got legitimate grounds to ask which money is in which drawer, and "no, it's a security risk" is not a good answer.
If you have an injection friendly application then that is the security problem.
Say someone hacks the db, is the problem easy to guess table names? The column should never have be called "passwords"?
Perhaps 30 years ago that would sound good.
Obscurity should hardly ever be a line of defense. If it is the only defense the problem isn't that it wasn't obscure enough.
Edit:
I'll do you one better. If you so much as suggest that obscurity is good security you actually openly invite people to fool around with your applications. The odds holes are to be found are much better than elsewhere.
I probably delete everything and pretend it never happened. It depends ofc on the worse case scenario. What can i do/afford to deal with the greatest risk? I might use it on a machine without internet.
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.
>It's not the file layout, but it's analogous...How do you argue they aren't analogous?
laws don't get to be analogous
foia request: "I'd like the report the committee prepared about the costs for the new bridge"
response: "denied. the report contains costs laid out in tables with headings, which while not being schemas are analogous, with schemas not being files but being analogous"
Yeah, I think it's still useful info for an attacker. But only if the system was actually developed by amateurs who never heard of parameterized queries.
I find it a bit bizarre that the city uses "our system was developed with no consideration for security" as a valid defense.
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.
It also fails if the system was written using parameterized queries. I wouldn't expect a system to be sanitizing anything if fails to take the most basic step for db access. This whole discussion is only relevant for systems developed by amateurs. SQL injection can only work at all if you use string concatenation to create queries, which you should never do.
If you do it wrong, yes. Sure, there is no 100% security, but honestly, it's 2025. We already know the techniques how to prevent SQL injection of any kind. I wrote about this here: https://valentin.willscher.de/posts/sql-api/
> Right but the case that is being imagined here is a site that perfectly sanitises * but somehow still allows SQL injection? I don't think so.
It could literally just reject anything with asterisks.
It doesn't even need to do anything perfectly, it just needs to do it enough to produce hurdles for you. Like blowing through the number of attempts you realistically have remaining.
I think sanitizing is the wrong word in this context. That sounds as if the idea were to take the SQL, try to ensure it's valid and then run it. That approach is of course very risky and I'd never do that.
No, my idea (as you can see from my post) is: parse the SQL, then check the resulting structure (that is basically a whitelisting process) and then turn the structure into SQL again. The last part is crucial, because it means that you have turned a whitelisted structure into SQL. Or in other words: even if some evil person found a bug and was able to convince the sql parser that everything is fine even though it is not, they would not be able to leverage that, because you are not actually running their SQL.
Or to be more concrete: let's say the parser thinks that something is a comment, but the DMBS would actually run it as SQL - then that would be a problem if you jsut sanitize the SQL. But it's not a problem if you turn the SQL structure into SQL again, because your code to do that would just reject anything that it doesn't expect (and that definitely includes comments).
Hurdles mean nothing to a determined penetration tester. Unless you have watched one in action, or tried an exercise yourself against a hard target, it is likely to surprise you how little "hurdles" mean.
Well, I would suppose that you pick an existing parser that is well tested and battle proven. No need to write your own. For most languages those parsers already exist. I would also not write my own json parser, so why would I do that for SQL, which is even more complex?
But in any case, the weird input would just be rejected. In my case I'd get a "parser error" from my library and then wrap it into my own "query not supported" error and return that as a 400.
> Have you had anyone do a penetration test on it?
Actually, yes. The pen-testers were surprised about the technique but did not find any problems with it.
I mean, there are still different reputations for certain techniques. And SQL sanitization has a bad reputation for good reasons. But SQL parsing, whitelisting and then reserialization still has a bad reputation in the eyes of many people, but that's only because they don't understand the difference between the two.
No one would say "parsing json is broken if it's not tested" right? Then for SQL the same would be true.
So if you were to use this technique in a business that doesn't mandate regular pentests, it would not change how well the technique works and how secure/safe it is.
In my case yes, I use an OSS library that is based on Scala's (a bit outdated) parser combinators (https://github.com/scala/scala-parser-combinators). So the parsing part is not actually written by hand, the library only defines the keywords, the precedence etc.
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.
No offense, but how can you be 1) insisting it's safe to give up the information to you and 2) openly planning to use the information obtained for further exploitation, at the same time? You can't have the cake and eat it too, unless the information available in 2) technically do not depend on 1) but doing it this way would only save them massive time or something.
> the only solution is what is discussed at the end: updating the law.
That, and actually penetrating the data system and subsequently "leaking" parts of it. Which is nearly always illegal, but could be considered a form of "Civil Disobedience" especially if done ethically - e.g. removing sensitive data or leaking only aggregates of the data. Either from outside, or by a whistle-blower.
I'm not saying "hack the government!". But I am arguing that the pressure of "getting hacked" is like the pressure of protests, blockades, occupying facilities etc, all of which civil disobedience, and often simply illegal too. All are tools in the belts of civilians to keep a government in check. Extracting information that a government is not willing to give but that would benefit the governed, should IMO often be considered such a tool as well.
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.
If they lose in court they have to pay court-determined attorneys' fees. That might be sufficient to get them to appeal automatically.
This is a tension you sometimes see discussed in the context of wrongful imprisonment, where one faction says that if you get tossed in jail for 30 years over something there was never any evidence that you did, the state should have to pay a penalty, and another faction says that if you penalize the state for randomly imprisoning innocent people, those people will never be allowed out of jail.
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.
The other answers here are great, but let’s say you’re right.
If you release a whole DB of data you’re going to have a hard time covering something you removed up in such a way that it’s not noticeable. Gaps in keys, suspiciously missing data for certain queries, etc.
Even if you do that perfectly, there are other data sources to compare to. If the city said it issued 2500 parking tickets and made $7500 in January on some financial report and the DB disagrees you have proof something is going on.
Or you could crowd source people’s parking tickets to compare to the DB to see if everything matches. What happens if one doesn’t? If one’s missing but the person had the proof they paid it?
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.
The statute says they're not required to. For a couple years, the statute did say that they had to, as we won multiple cases in lower courts, but Chicago appealed to the Illinois Supreme Court, and the outcome was that now the statute exempts schemas.
By that logic there's no point investigating any crime or doing any kind of audit. You increase the costs of covering up, and put them in a dilemma - remember this is exactly what brought down Nixon.
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.
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".
That’s what I meant. For example, people who have a suspicious number of tickets dismissed. Or perhaps certain employees that dismiss a suspicious number.
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
Very interesting case! Just one question: to what extent do changes in database schemata fall under FOIA in Illinois? That is, if they should change the database schema to conceal whatever it is they're fighting tooth and nail to hide, are they compelled to retain detailed information about that change? Or can they later present you (should the legislation pass) with a cleaned-up, nothing-to-see-here updated version?
I don't want to take away any steam from your sails but giving bad information in regards to case law shouldn't be taken lightly. Your "expert witness" did you a disservice.
Schema is very much a critical field in terms of AuthZ privileges. Just knowing the structure is not far off from knowing the max entropy a password may hold. In regards to InfoSec, table structure is the recon phase which limits effort and minimizes time. Someone with that much time in security knows DBs will be hacked, not if but when. Time is an incredibly important tool which is why we have expirations on so many authN and authZ windows of attack.
I'm glad that you are challenging them but I believe a credible engineer would have made mince meat of your expert and hurt the rest of us who want to see you successful.
It's possible rewriting certain statutes can help us but there is no company worth its salt that would share DB schema.
> Just knowing the structure is not far off from knowing the max entropy a password may hold
Not if the password is hashed, as it should be. Unless the schema somehow indicates that it uses a hash algorithm such as bcrypt that has a maximum password length. And even then, if they pre-hash the password, the password itself could have more entropy than that. And if there is a maximum password length, then you can probably figure that out via other means, like it being listed in the requirements when you set your password. It does tell you the size of the hash of the password, but if the maximum entropy is sufficiently high, as it should be, then it doesn't really matter; it would still be impractical to brute force.
> there is no company worth its salt that would share DB schema
So you are saying that every company with a self-hosted or open source product that uses a database isn't worth their salt? If your DB is running on a customer's infrastructure, that customer will by necessity have access to the schema. And likewise if the source code for a product is publicly available it is trivial to determine the schema.
I'm not arguing the complexity of hacking a password, I'm familiar. So instead of responding with rainbow tables or how knowing the schema informs you the location of salts for the salted hash (which is the actual proper way), I'll just point to an example.
Look at how RSA is implemented. Look at the intentional obscurity of S tables and lack of detailed information.
There is a reason information is withheld. DB schema is just that, information that increases increases the threat.
And running a DB on someone's infrastructure doesn't necessarily give you access. You need to read up on AuthN and AuthZ.
If you listed an open source example I'd take the time to poke holes in your strawman argument but you honestly just need to take a step back and think about what you are really arguing.
Do you really think not having the schema is as inconsequential as having the schema when attacking something? I mean what is the first step most folks do in reverse engineering? I honestly can't believe I'm having to say this.
As mentioned in the post FOIA tends to only include existing records/information, it doesn't extend to producing new work. So producing a new report would be considered too much work. (But fighting a lawsuit to not reveal the schema is fine )
> 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!
While you're waiting, check out this older post: https://mchap.io/that-time-the-city-of-seattle-accidentally-...