I Went to SQL Injection Court
sockpuppet.org
sockpuppet.org
While you're waiting, check out this older post: https://mchap.io/that-time-the-city-of-seattle-accidentally-...
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.
And boy they’re fighting suspiciously hard.
Good luck.
And often times, the denials eventually lead to significant reorg once judges and Congress can revise laws to fix the ambiguities.
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.
What do you think are the next steps?
But after that, getting a reasonable law passed to fix this now-broken nonsense.
[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
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.
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?
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.
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.
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.
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.
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.
https://youtu.be/jQblKuMuS0Y?t=866 (timestamp is when Zane starts talking about it)
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.
Not the real solution, IMO, but WAFs are useful for more than SQLi, and is the kind of tech you can ask money for.
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.
> 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?
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.
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".
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.
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.
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.
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.
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.
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.
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.
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'.
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.
> 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".
I find it a bit bizarre that the city uses "our system was developed with no consideration for security" as a valid defense.
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.
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.
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"
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.
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.
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.
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.
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.
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.
It's hard to imagine a case where you can't even get info based on timing. But it requires more effort and knowledge to exploit this.
In other words, advance knowledge of the schema may make it easier to act maliciously.
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
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.
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.
What you see there is a protobuf, serialized as JSON. If a protobuf definition isn’t a schema, I don’t know what is.
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.
Edit: More NULL, or maybe lack thereof cause they use the string "NULL" instead? https://news.ycombinator.com/item?id=20676904
It wouldn't. I'm just assuming that the thrust of the hypothetical negligence accusation was "The schema is useless unless you have SQL injection holes. So give us the schema or admit you are negligent!" But you're correct that there are other justifications one could make to keep the schema secret.
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!
Blind SQL injection is a type where no error is produced, but some subtle signal can indicate success or failure. The most interesting one that I know about is where the presence of a successful injection was a normal looking response that was one byte longer than an unsuccessful injection. This was used to not only figure out the schema, but to fully exfiltrate the entire database.
There is nothing in the log on the server that indicates an error.
Most of the relatively introductory SQL injection exercises that I taught proceed without any knowledge of the schema.
This is why SQL injection is so insidious.
Where if you join another table (by e.g. requesting extra info in a graphql query) the response goes from ms to s or even m. Indicating the size of the joined table.
Or where I could change a "?sort[updated_at]=desc" to a "?sort[password_hash]" through trial-and-error and suddenly see the response time drop from ms to seconds (in this case finding columns that exist but aren't indexed).
Even if the response content is exactly the same, we know things exist, are big, not indexed, or simply present, by timing the attack.
A famous one is obviously the timing trick to find out that an email is in the system because "user = user.find(email) && user.password_matches(password)" short cirquits if the email does not exist but spends significant time on hashing the password for matching it. A big lot of backends and apps make this mistake.
Knowing the name of the service helps the attacker, knowing the name of government officials working at city hall helps attackers, knowing the legal description of what a parking ticket is helps attackers. If you are sued and decide you want to hack the government knowing the details of the suit against you helps you in your attack.
The barrier is not “any helpful information must be censored” the barrier is “don’t disclose passwords or code that would divulge backdoors” a schema cannot be that.
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.
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.
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.
It helps that zoning matters more in Oak Park (and Evanston) than almost anywhere else in Chicagoland.
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.
Of course the a vocal minority is fuming about higher density.
The thing that really kills density in most cases is the height restrictions. A lot of the upzoning in my area has resulted in ugly, wall-to-wall low-single-digit floor count buildings with near zero setback. It's better than single family but it isn't particularly dense and it's a huge step backwards aesthetically.
Violence can work if you have the biggest club, but it's mostly ineffective at grassroots change.
How do you figure out where to go?
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).
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.
Man, I remember your & Maciej's effort to get FIDO keys to the campaign staffers, and how depressing that was
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?
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?
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).
There is also the concept of a "canon of construction", which exists specifically to handle these kinds of reoccurring grammatical issues. I'm surprised there isn't one for dangling modifiers.
Isn't this exactly what happened? A court of computer laypeople reached for Merriam-Webster in order to disambiguate a sample of programmer argot that was written into law by another group of computer laypeople. The legal profession isn't just dirty, it seems doomed to defeat itself in even its most rigorous practice.
Other countries have legal specialists for different areas and update their laws continuously based on expert opinion, common law gets expert testimony but is based on generalists to make the final determination
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.
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
> 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.
One of the things that this also reminds me of is the impression that "learn to code" meant "everyone should be a software developer" when your experiences demonstrate a "learn to code" was part of "becoming a better legal professional".
Do you have other examples of how learning to code improved your abilities as a legal professional?
Alternatives like codified law exist and are practiced, just not in the US or Canada.
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.
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.
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.
Copyright is time limited author’s death and 70 years for individuals and 95 years for corporations .
While there are arguments to be made for lesser duration , better preservation requirements etc the balancing of public good to private value is the basis of all copyright laws since statute of Anne 1709.
In a court case you can get access to all types of information as part of discovery, if you are harmed or believed to have been, there are other avenues available for you . If you have standing to sue and the discovery requests are made by a competent lawyer you can get access to internal communications to trade secrets to any other document supporting your claim . you or your lawyer can not use such information for economic benefit or disclose it, they are still protected .
Given that you have options legally to get this data , there is no public need that trumps private property rights because of real or potential harm that justifies blanket access by default
PS: note software is not just copyrighted , it is also covered by patents (20 years) and trade secrets (no expiry ). Also while the law provides protection it does not require disclosure on expiry .
Patents aren't relevant here since they are disclosed upon granting and cover the design rather than the implementation, for trade secrets the situation is more complicated ( https://www.americanbar.org/groups/litigation/resources/news... ).
Though rulings like this might have a chilling effect.
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.
Or at least I don't want to explain to "20 years later Monday Morning Quarterback".
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.
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.
Private companies don't divulge schema because it's valuable IP.
Public entities IP belongs to the public, so there is nothing to protect
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...
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.
Great project by the way!
For example, https://www.cityofflint.com/wp-content/uploads/2023/06/Annua...
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"
Alas, that part should be illegal under FOIA.
Source code should be open source and verifiable. Being exempt from FOIA circumvents public confidence in the government's use of software.
I'd be curious to learn if/where courts have decided such things already.
There's probably also some actual business logic that government orgs want to and are legally permitted to keep secret. In the OP's case of a parking ticket database, maybe there's software talking to that database, whose source code includes the logic of picking when / where parking inspectors should conduct a "random" blitz of issuing fines.
Oh yes, and that "random" blitz of issuing fines definitely doesn't have any racist part to its algorithm. Just trust the government on that one. The government and the "business" what wrote the code in the first place. Yup, makes sense.
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...
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?
Ditto for the org-chart.
> 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.
In many was sql is all about divorcing the schema from the files.
(Kinda a file format inside a file format inside a file format.)
Edit: If you're talking about the byte representation only, I don't think section headings indicate the placement of the body's bytes.
I am a human, you know I have a kidney, but I am not a kidney.
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.
But your comment illustrates just how difficult it is to nail these things down, based on inherently imprecise language.
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.
Huh? Depends on the DMBS, but each InnoDB table is a file.
And the schema determines the file structure.
A table isn't a schema, it is a component of a schema, and most databases don't use InnoDB.
So if you have the schema, you have the tables.
"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.
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.
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.
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.
Now if you want to go by Illinois definitions, SQL schemas are file layouts, that's why the plaintiff lost.
You also said SQL schemas are declarative, not imperative. Those are types of programming languages, so software.
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.
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.
¹ 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...
It won't be the whole database schema, but it would be a start.
This (spoiler) visualization's going into my eventual post about the lawsuit: https://observablehq.com/d/026992341cc47ff0
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 :)
This is really bad. Words have different meanings in different domains. You can't just point to a dictionary definition for the wrong domain. This is absolute madness and should be grounds for termination as a judge. Imagine how angry that judge would be if you did that for some random legal jargon that is very different from the common definition of a word!
I helped him process and visualize the original batch of parking ticket data waaaay back in 2016.
I can't believe he's still on this in 2025. We need more junkyard dogs like him fighting for what's right.
I can confidently say it does not stop at message boards for many people, self included
I sure hope the impact of this is not that government entities switch to schema less databases!
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.
If the answer is "the ability of the request data from a specific table/column", I would say that this should possible to do by asking for the relevant data directly (instead of asking for "the timestamps of each ticket" ask for the "time-related data of each ticket" for example) ?
And yes, having your db schema out in the wild can be a vector of attack, if only because it allows targeting the sql injections (the blog author himself argues this in court).
The court was right to reject this. Maybe the exact word of the law doesn't ask for it, but the spirit certainly does.
And with the ruling that the condition only applies to "other information" (which to me seems like a very strange reading, and probably not the intent of the law), regardless of if a SQL schema is considered a "file layout", creates a massive loophole, where the government can just use some obtuse custom file layout to avoid FOIA requests.
How plausible or likely does that jeopardy need to be? Very
Does a database Schemas constitute “source code”? Yes
Is a SQL schema a “file format”? No & yes. In that order.
And, finally, does the “would jeopardize” language apply to everything in the exemption, or just to the nearest noun “any other information”? Yes
Was this ever attempted?
SELECT * FROM `information_schema`.`tables`; 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'
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.
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.
In my experience working in government, including on state-equivalent-of-FOIA requests, almost everyone working on those kinds of requests is “involved in” budgeting and resources, and more to the point anyone in a position to sign off on a decision of whether something should or should not be denied as exempt is a manager, for whom (that is, for any manager, down to the line level, over any function in any government agency, but FOIA-type requests, eepecially if there are going to be assertions of exemptions in total or in part, generally involve coordination and signofffs between multiple managers, e.g., from the most relevant line unit, the public information unit, and legal) managing budgeted resources and doing the work of justifying requests for additional resources that is the root of the agency-initiated budget change request process, and then participating in drills and internal analyses and responses as those proposals work through the budget process is a central part of their job.
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?
"I want your data"
"What data?"
"What do you have?"
"Ha ha. No. Tell me what you want"
"Your data that is the metadata of your data"
"Well actually..."
...
Leave the "Clerk" bit of this out and just imagine you're requesting straight from the IT department. What you can do: get anything not otherwise exempt that they know how to retrieve (it usually helps to provide example commands in the requests). What you cannot do: ask them to go look around and see what they have. That's research. Research is your job, not theirs, under Illinois FOIA.
Again: this is why pulling schemas is so valuable.
Once the information is released, can anyone can make FOIA requests using the schema?
It's not legally protected. An employee could leak it. A public body can voluntarily reveal documents that are exempt from FOIA (absent some other Illinois law prohibiting disclosure). A public body can disclose source code, for instance, despite it being explicitly exempt in the statute. "This data is exempt" is an affirmative defense that the public body has to raise.
Besides, when the law is ambiguous, it's very often because the legislature themselves weren't sure what they intended, and/or because the legislature had deeply divided views and arrived at ambiguous wording as a compromise, and/or because the legislature used their "somebody else's problem" prerogative i.e. they said "let's leave that for the courts to decide". Ambiguously worded laws isn't a bug, it's a feature!
Second, interpreting the law is a full-time job, and our legislators are already not doing the jobs they have. If we asked them to not do a second job on top of the one they're already in dereliction of, nothing would get done. Well, twice as much nothing would get done, if that makes sense. So, you need someone with the full-time job of interpreting the law, and it might as well be that judicial branch.
Third, legislation is often a compromise. If you go back and try to interpret what it was that people said during the debate which produced the language at issue, you'd have to decide whose voices carried the most weight, which seems very, very tricky. It's why legislation is written down in the first place: a single source of truth. Even if ambiguous, it's still less ambiguous than a transcript of extemporaneous debate.
I think law and lawmaking would be vastly improved if only lawyers learned the miracle of parentheses.
Specifically, would a request for “data field labels” (i.e. a column list without any table structure info) likely circumvent the exemption?
> 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)
/s
(3) I imagine Chicago greatly regrets towing Matt Chapman "over a facially bogus ticket".
Can you request the desired information using natural language, based on your guesses of what information they store?
Can you ask for the database record from dispatching that inspection visit to Mel's Diner on 11/11/2024, even if you don't know the exact database column names and relations?
If you can ask for that one dispatch database record, without knowing the schema, can you ask for the database records for all inspection visits to all locations in Smallville in 2024? (Or does the complexity of that database query constitute "research"?)
You can suggest that they retrieve the inspection report from their database. This can be useful if staff wouldn't know where to find the document you're looking for. The FOIA clerk will hand the request off to IT, and if it's sensible, they'll probably try it.
You probably (these are all humans, so no definites) can't literally use public body staff as a proxy to a database shell; for instance, they're not going to let you do a lot of interactive stuff. You're either going to produce the data that you're looking for --- which you'll need to describe in prose --- or get nothing.
So if you ask for documents in a way that sounds more complex than "every inspection report ever done on Mel's Diner", which is something they might be able to satisfy reasonably using a paper filing system or by eyeballing rows on a screen, then the request could be denied as "research"?
So then is what the petitioner in this case looking for was a database schema that would let them say, "respectfully, I think it's not research; I think it can be satisfied with the following exact SQL query"?
The public body could then say "no, we're not going to run that query or dig into that database". But then you can take them to court, and you'll probably win if the query is reasonable and simple enough.
Data engineering: doing a lot of fancy work to make a very simple product
Now a nerdy question. As someone who investigates SQL injections, why are you running a server based on nginx 1.4.6? Do you know something I don't? :-)
Since there is no contact method on the website, figured I'd mention it in a comment; hope this helps
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.
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.
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, marginal attacker benefit, and other things in that exemption, and I'd say that again as an expert witness.