While you're waiting, check out this older post: https://mchap.io/that-time-the-city-of-seattle-accidentally-...
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.)
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 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.
> so the real deterrent you
> have is how much of their
> time you can credibly threaten
> to eat up with legal actions.
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.
account, accounts, password, passwords, profile, profiles, credential, credentials, auth, auths, authentication, authentications, authentication_info, authentication_infos, authorization, authorizations, passwd, passwds, user_info, user_infos, login_info, login_infos, account_info, account_infos... should I keep going?
And these are just the logins/passwords; what if the information of interest was something else, like parking tickets?
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.
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.
- 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.
1: you can set secure defaults at one place globally, but your code must be correct all the time to be free of SQLi
2: it's usually not the same persons who configure the DB and who write the code.
Security is an onion, not a coconut.
For another example of what defenders are up against, see https://users.ece.cmu.edu/~adrian/731-sp04/readings/Ptacek-N.... This paper all but caused an upheaval in the WAF industry.
https://youtu.be/jQblKuMuS0Y?t=866 (timestamp is when Zane starts talking about it)
Not the real solution, IMO, but WAFs are useful for more than SQLi, and is the kind of tech you can ask money for.
A sort of “you shouldn’t be in here, even if we left the door unlocked.”
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.
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.
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 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.
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.
"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.”
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.
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?
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.
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."
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.
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.
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.
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'.
> 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.
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.
Such an application could potentially store data as plain files, the names of those files may contain personal or sensitive information.
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?
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".
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.
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.
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?".
Some may use their newfound education for good while others use it for evil, as with any education.
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.
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.
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.
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.
If I'm looking at a database, I like knowing column names, but I like knowing table names more.
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"
I find it a bit bizarre that the city uses "our system was developed with no consideration for security" as a valid defense.
1=1
1=0
There are MANY other tricks that don't involve ''.
Besides, consider the number of valid queries done by the application that involve '*'. You are not going to turn that off.
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.
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).
Have you had anyone do a penetration test on it?
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.
That is good. I have long believed that "if it is not tested, it is broken"
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.
LIMIT 1 limits row count. The issue here was columns. Like a giant blob someone might've stored in there.
> `SELECT .. FROM information_schema...`
no such table: information_schema.columns
> etc.
Oh I guess I will try the other very small number of options that it could be.
> LIMIT 1 limits row count. The issue here was columns. Like a giant blob someone might've stored in there.
Come on, this is pure nonsense.
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.
"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.
As such, they could claim all FOIAs that require redactions shouldn't be fulfilled because a redacted record is a new record.
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.
What do you think are the next steps?
But after that, getting a reasonable law passed to fix this now-broken nonsense.
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.
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.
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?
It could still prove useful.
And often times, the denials eventually lead to significant reorg once judges and Congress can revise laws to fix the ambiguities.
And boy they’re fighting suspiciously hard.
Good luck.
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.
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.
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.
[0] https://sockpuppet.org/blog/2025/02/09/fixing-illinois-foia/ [1] https://mchap.io/that-time-the-city-of-seattle-accidentally-...
Welcome to Seattle :-)
-Guybrush, from The Secret of Monkey Island