34 ms·

I Went to SQL Injection Court

• tptacek 2y ago
  Kurt posted this to troll me. Just know my audience here was, mostly, non-technical people involved in politics in my local Chicagoland municipality. Permit me a PSA about local politics: engaging in national politics is bleak and dispiriting, like being a gnat bouncing off the glass plate window of a skyscraper. Local politics is, by contrast, extremely responsive. I've gotten things done --- including a law passed --- in my spare time and at practically no expense (drastically unlike national politics). An amazing thing about local politics, at least in a lot of places, is that they revolve around message boards. The boards won't be in places you want to be (in particular: a lot of them are Facebook Groups) and you just have to suck it up. But if you enjoy participating in a community like HN, you can participate in politics, too, and message-board your way towards making things happen.
  • AceJohnny2 2y ago
    > (drastically unlike national politics) Man, I remember your & Maciej's effort to get FIDO keys to the campaign staffers, and how depressing that was
  • copypasterepeat 2y ago
    Would you care to elaborate which law you helped to pass? Also, can you link to some good resources for someone who wants to get off the sidelines and get more involved in Chicago politics, whether the resources are on FB or elsewhere? I've previously tried Googling for some but with very limited success. Thanks.
    • tptacek 2y ago
      We're the first municipality in Illinois to draft and adopt an instance of ACLU's CCOPS model legislation, which requires board approval at a recorded public board meeting before any agency (most especially our police force) can adopt any form of surveillance technology, given a broad (ACLU-supplied) definition of "surveillance". Previous to that, our police force could acquire arbitrary surveillance products so long as they kept under a discretionary budget threshold; they used that latitude to acquire a pilot deployment of Flock ALPR cameras, and CCOPS was a response to that. My real goal is zoning. In Chicago itself, I have less clarity, but am optimistic that somewhere on Facebook is a message board where the staff at your alderman's office reads posts, and the most politically engaged people in your neighborhood argue with each other. That's your starting point (and maybe your ending point). Just go, listen, and chime in with high-effort comments. If you're used to clearing the bar for HN comments, you're way past the threshold of coding like a super-thoughtful person in local politics.
      • pchristensen 2y ago
        My real goal is zoning. God speed to you sir! What is your goal wrt zoning?
        • tptacek 2y ago
          The categorical elimination of single-family zoning along with any building envelope restrictions that would make as-of-right 3-flats uneconomical.
          • pchristensen 2y ago
            That would be an outstanding outcome! Is this just for Oak Park, or beyond?
            • tptacek 2y ago
              You'd hope that Oak Park, Evanston, Wilmette, and then Berwyn and Schaumburg could get this done, and then your next step would be either Chicago (tough because of aldermanic structure) or statewide, the way California did. Either way: you start in one municipality and work from there. It helps that zoning matters more in Oak Park (and Evanston) than almost anywhere else in Chicagoland.
              • pchristensen 2y ago
                Why does zoning matter more in Oak Park and Evanston? High demand from being on the El and close to Chicago?
                • tptacek 2y ago
                  Yep. Historically both of these places basically exist to concentrate the interests of the upper middle class and to reinforce segregation. They're both basically Chicago but with a better funded school system (because lawyers and doctors get to funnel all their property taxes into the school down the street from them), which makes them highly desirable.
  • hinkley 2y ago
    “Never doubt that a small group of thoughtful, committed citizens can change the world: indeed, it's the only thing that ever has.” - Margaret Mead
    • Y_Y 2y ago
      Like a hedge fund? Or are we including those committed to violence?
      • Terr_ 2y ago
        Probably not the intent of the attributed author [0] but literally speaking the statement doesn't specific "ethical" or "peaceful", no. [0] https://quoteinvestigator.com/2017/11/12/change-world/ https://quoteinvestigator.com/2017/11/12/change-world/
      • Muromec 2y ago
        Would would you ever exclude ones committed to violence? Violence consistently works.
        • sfink 2y ago
          That's what you'd think, but actually violence mostly pushes things to the extreme and sinks the effort permanently. Nonviolent movements are often defeated by one side goading the other into losing control and doing something violent, at which point they immediately cede the moral high ground and lose the support of the masses. (The masses are mostly useless in pushing a movement forward, but are critical for a movement to succeed.) Violence can work if you have the biggest club, but it's mostly ineffective at grassroots change.
      • 0x457 2y ago
        It's about that it's a small-dedicated group that brings change and not government or private institution. If it's still hard to grasp, then think about how national movements started.
      • hinkley 2y ago
  • chaps 2y ago
    Aaaaaaa! I need to finish my post! :(
  • zahlman 2y ago
    >The boards won't be in places you want to be (in particular: a lot of them are Facebook Groups) and you just have to suck it up. But if you enjoy participating in a community like HN, you can participate in politics, too, and message-board your way towards making things happen. How do you figure out where to go?
    • tptacek 2y ago
      The way you'd expect: I bumbled through a bunch of different Facebook Groups, starting with the one simply labeled for my neighborhood, and followed cross-posts. Eventually I found the two really important ones in my area (one is an organizing group for local progressives --- I live in a very blue muni, and the other is the main high-signal political group for the area, in which all the village electeds participate).
  • skissane 2y ago
    > Local politics is, by contrast, extremely responsive. I've gotten things done --- including a law passed You live in a country where local governments have the power to make laws… in a lot of other countries they don’t - or, to be more precise, their lawmaking power is extremely limited. Actually, even in the US, that’s often true too - only local governments with “home rule” can enact laws on any topic (provided it doesn’t contradict state or federal law), those without it can only enact laws on specific topics authorised by the state legislature. Some states grant home rule to all counties and municipalities, others none, others to some but not others (e.g. in Texas a municipality can give itself home rule powers, with approval of its voters, but only once it reaches a population of 5000).
    • bobthepanda 2y ago
      Even state legislators are, by their nature, pretty much locally driven given the relatively small size of their constituencies and thus the margin of victory. Voters significantly underestimate their power even up to the House level; AOC’s first campaign was very scrappy and resulted in a bartender unseating the chair of the Congressional Democrat Caucus and likely successor to Nancy Pelosi, and that was the first campaign in which anyone bothered to primary him.
  • deleted 2y ago
    [deleted]
• duxup 2y ago
  Very interesting read. It does seem absurd to think of divulging schema as protected, as described it allows for a magical sort of outcome where: "well it's in a database you can't know anything about, and if you can't tell me how to find it you're sol". Working at a small company with lots of clients I wouldn't want to hand out DB schema outright, but I also go out of my way to search / get the client the data they want ... not reject them.
  • rectang 2y ago
    A private company wouldn't want to divulge their DB schemas because it's advantageous for competitors to see how you're doing things. That doesn't apply to government databases.
    • bornfreddy 2y ago
      Maybe. But now I'm really curious how bad that schema must be for them to hide it so viciously.
      • duxup 2y ago
        Your imagination can't cover how bad you might think it is (and yet it isn't that bad). Or at least I don't want to explain to "20 years later Monday Morning Quarterback".
      • jrochkind1 2y ago
        I think it's just an excuse to avoid making it feasible for the public to get the data.
      • michaelmrose 2y ago
        Used to be relevant data was in a document but much is no stored in specialized web apps whose data in turn is stored in a db.
      • hot_gril 2y ago
        Maybe their schema has triggers and stuff
• bobsmooth 2y ago
  What stands out to me about this article is the time between court appearances. Seems like if you want to accomplish anything in court you need to be prepared to spend years of your life on it.
  • rectang 2y ago
    And of course, people and entities (private or as in this case public) who have a lot of resources take advantage of that, a state of affairs which often serves to perpetuate injustice indefinitely.
    • esotericimpl 2y ago
      [dead]
  • barbazoo 2y ago
    I thought the same thing. Sure it's async but still you have to keep this in your mind for a very long time.
  • lucb1e 2y ago
    Can confirm this is the case everywhere. Even before taking anything to trial, one can spend months on trying to come up with a mutually agreeable solution, in my case getting seemingly one step further each time¹. I'm not sure I'd not just give up and move on with my life if this dragged on for years and wasn't about something that majorly impacts my life or that of a loved one ¹ Details: it was a warranty case, so first they agreed to repair it, then they didn't do that (but maintained that they were going to, whenever I asked about the status), then they agreed to refund, then they didn't do that, then I set a deadline, they iirc agreed, then they didn't pay, then I included specifics of what my next steps would be (lots of research here, seeing what even my options are and what I can truthfully claim that won't get shot down by a judge later) if they didn't pay before some other deadline (so I showed I was serious now), then the deadline crept up and they finally refunded the day before it would expire and I was frankly disappointed because, by now, I was prepared and ready, and all I got was the original sum that I had paid them. I checked the legal interest rate and changing my demand to include that simply wasn't worth wasting more time on this, and I didn't find any sort of precedent that I could bill any time I provably spent, not even to the value of minimum wage, so any time you invest is just lost free time (which I didn't have much of during that particular year). Protip: scroll down the reviews before buying something worth more than a few tenners from a small store. I wasn't the first person who had to threaten litigation...
• SunlitCat 2y ago
  [flagged]
• wswope 2y ago
  Anyone with a legal background willing to opine about potential workarounds to this ruling? Specifically, would a request for “data field labels” (i.e. a column list without any table structure info) likely circumvent the exemption?
  • gpm 2y ago
    I think that would run afoul of > The one big limitation of Illinois FOIA (with FOIA laws everywhere, really) is that you can’t use them to compel public bodies to create new records. Unless for some reason they already had a list of columns without table structure. (Not that I claim to have a legal background)
    • duxup 2y ago
      Yes but what if we come up with a directive that every FOIA request must be logged into a DB. Therefore every request is automatically invalid as it requires we create a record! /s
      • deleted 2y ago
        [deleted]
    • wswope 2y ago
      I had that thought too, but my naive rebuttal would be that the column data already exists by default in any standard RDBMS as information_schema.columns. No new record creation required.
      • 0x457 2y ago
        Yes, but that requires someone to execute a query on a database and package it as a report?
  • Andys 2y ago
    Not a lawyer, but why not use opensource as an example? Many successful public e-commerce websites have public schemas and aren't all hacked.
• pavon 2y ago
  Great read. Frustrating that the court ruled that a schema was a file layout, since I don't think it is, but at the same time if it didn't fall under that exception, there is a strong arguments that would be considered "documentation pertaining to all logical ... design of computerized systems". A schema is literally, the logical design of the database, and the database is a part of the computerized system. Once it was ruled that those examples are "per se" exempt it was a long shot to argue that schema wasn't covered by any of the examples.
  • paulddraper 2y ago
    How is a database schema not a file layout?
    • kasey_junk 2y ago
      The article describes why. 2 different db engines (or even instances) can use different file layouts for the same schema. In many was sql is all about divorcing the schema from the files.
      • tptacek 2y ago
        Another way to think about it is that if a SQL schema is a file, so is an Excel spreadsheet template.
        • hot_gril 2y ago
          File or file layout? Cause both of these are probably stored as files, .sql and .xltx respectively.
        • paulddraper 2y ago
          An Excel spreadsheet template is an arrangement of rows/columns/cells which is encoded in a XML document which is encoded in a ZIP file archive.
          • 2y ago
• hnthrow90348765 2y ago
  >just self-important message-board hedging I can confidently say it does not stop at message boards for many people, self included
  • tptacek 2y ago
    It's a real issue when writing an affidavit or testifying. Lots of ingrained bad habits.
• gowld 2y ago
  This is part of what discouraged me from going to law school. So much of litigation is Kabuki theater, grant rhetoric not in any way intended at achieving a just or logical outcomes, but designed only to the person in power an excuse to decide however they had already wanted to decide before the case was tried.
  • lucb1e 2y ago
    > So much of litigation is Kabuki theater, grant rhetoric not in any way intended at achieving a just or logical outcome Agreed, that is what this sounds like. What stood out to me is the remark »“only marginal value” is just self-important message-board hedging«: it's also simply correct, but the author concluded that they shouldn't have said it because "marginal" plus a bunch of explanation didn't have the rhetorical value that "no" would have had Someone could legitimately configure a WAF-like system to scan for various ways of querying the database schema coming in as HTTP requests (keywords like "information_schema", encodings thereof, etc.), which will always be hacking attempts and can be blocked. If you already have the schema, you can craft a query without needing to bypass that restriction first. Is this likely to be a serious barrier at all? No. Is it anything to do with self-importance? I don't see how that's the case, either. It seems simply correct that this is marginal (situated in the margins, not the point, not important to discuss), but by saying nothing but the truth, now the other side blows that up to something much bigger and tries to get the court to agree that, "see, their own expert says it has value!" And so this expert concludes that they shouldn't have said it, that they should have just said "no value" which I would say is wrong, but so marginally wrong that it's hard to prove for the opposing side that it is not fully correct, and thus being less correct helps you in (this) court... so it's about rhetoric as much as being an expert...
• deleted 2y ago
  [deleted]
• paulddraper 2y ago
  [flagged]
  • kelnos 2y ago
    I think you have an unrealistically high bar for who is suitable to be an expert witness. People who are not even remotely experts are often trotted up as "expert witnesses". OP is very easily an expert in his field; the only issue is that his communication style is not quite tuned properly for legal matters. Which shouldn't be surprising; that's the case for pretty much anyone who isn't in the legal profession, doing this sort of thing day in and day out. And I think this is the correct state of affairs. The kind of person who does have their communication style tuned for legal matters probably engages in so much legal work that they aren't doing enough work in their field to truly be considered an "expert".
    • pessimizer 2y ago
      The only problem seemed to be that he was unable to rule anything out, no matter how unlikely, because he is honest and an expert. He lacked the dishonesty and false confidence that we demand from an expert witness within an adversarial justice system.
      • tptacek 2y ago
        No he didn't. The grandparent comment here was just a snarky put-down. No part of my testimony was impacted by a casual write-up I did about it 4 years after the fact.
    • paulddraper 2y ago
      If you say "Even I don’t know what I meant by that" ... that's not really communication "tuning" now is it? I don't expect someone -- even an expert -- to have perfect phrasing. But if they can't even tell you what they meant to say? How is that unrealistic expectations?
  • hot_gril 2y ago
    The author already said he messed up. What are you adding by saying this?
• chaps 2y ago
  Hi everyone, I'm the plaintiff in this lawsuit. I'm still working on my companion post for tptacek's post! I'll have it ready Soon TM, but feel free to me any questions in the meantime here. While you're waiting, check out this older post: https://mchap.io/that-time-the-city-of-seattle-accidentally-gave-me-32m-emails-for-40-dollars4997.html https://mchap.io/that-time-the-city-of-seattle-accidentally-...
  • doctorpangloss 2y ago
    What are the administrators of CANVAS hiding?
    • chaps 2y ago
      Hard to say. One of my personal drivers for this lawsuit is a tip I received that said that Chicago has a list of vendors whose tickets are dropped in the back-end. When I requested that info, the city said they had no such list. I trust my source, so having schema information could help figure out the extent and if they were lying.
      • noboostforyou 2y ago
        Considering how much they fought to not release the schema, there's probably a column named "exempt_from_penalty" or something equally obvious.
        • thaumasiotes 2y ago
          If they lose in court they have to pay court-determined attorneys' fees. That might be sufficient to get them to appeal automatically. This is a tension you sometimes see discussed in the context of wrongful imprisonment, where one faction says that if you get tossed in jail for 30 years over something there was never any evidence that you did, the state should have to pay a penalty, and another faction says that if you penalize the state for randomly imprisoning innocent people, those people will never be allowed out of jail.
      • 2y ago
• probably_wrong 2y ago
  Random thought: someone should drive to Chicago, get a parking ticket, and then make a FOIA request for all of their information contained in that database. It won't be the whole database schema, but it would be a start.
  • chaps 2y ago
    Short answer -- already been done. This (spoiler) visualization's going into my eventual post about the lawsuit: https://observablehq.com/d/026992341cc47ff0 https://observablehq.com/d/026992341cc47ff0
• lcnPylGDnU4H9OF 2y ago
  > where the only way to get at the underlying data is to FOIA a database query Was this ever attempted? SELECT * FROM `information_schema`.`tables`;
  • chaps 2y ago
    Yep, that was done in the FOIA request related to this lawsuit: select utc.column_name as colname, uo.object_name as tablename, utc.data_type as type from user_objects uo join user_tab_columns utc on uo.object_name = utc.table_name where uo.object_type = 'TABLE' https://www.muckrock.com/foi/chicago-169/canvas-database-schema-60175/ https://www.muckrock.com/foi/chicago-169/canvas-database-sch...
    • lcnPylGDnU4H9OF 2y ago
      Yeah, it's obvious the double standard here, then. Curious indeed why they are so adamant to keep the schema/data secret.
      • noboostforyou 2y ago
        I said in another comment but I suspect the column names themselves are incriminating (basically saying this person doesn't get a ticket because they are in a special club, that's probably not technically legal)
        • hot_gril 2y ago
          is_cop bool not null default false
      • kelnos 2y ago
        Because they know that eventually the data contained in that table is going to be used to support some sort of lawsuit that their parking enforcement activity is biased, and is targeting people of color. It's already ridiculous that they spent several years blocking this request while it went through court. If the plaintiffs spoke to pretty much anyone involved in maintaining the system, or with any of their internal infosec people, they would know that there's no real security risk to releasing this information. They've already spent orders of magnitude more time and money litigating the issue than it would take to just release the information in the first place, so this is clearly not a cost or resourcing issue. They don't want to release it because they'd prefer it's secret, because secrecy makes it harder for the public to hold them accountable. That's all.
• Y_Y 2y ago
  Is it not absurd that the supreme and appeal courts disagreed on a syntactical matter? Never mind that this isn't uncommon, or that (IMHO) it would be ridiculous to interpret it as "any file layouts at all, and other stuff too, but only bad other stuff". It's crazy to me that were happy for laws to sit on the books being utterly ambiguous. I know this suits the courts who benefit from the leeway, and that (despite valiant efforts) we're not going to get "formal formal" language into statutes. I know that the law is an ass. I know that the laws are written by fallible and naive humans. Even after all that, if the basic sentence structure of what's in the law isn't clear to the courts, hasn't the whole system fallen at the first hurdle?
  • tptacek 2y ago
    To me it feels like the kind of dispute that is exactly why we have multiple levels of appeals court. The "file format" thing is super dumb, and they got it wrong, but the "that if disclosed" statutory interpretation is a thing that seems important to get a final, consistent determination on.
    • Y_Y 2y ago
      Of course I can't disagree that it's good that it's now settled. Still I can't help but imagine a world where the meaning, at least in terms of which words apply to which others (rather than qualifiers like "reasonable"), should be settled before the law is debated, voted on, and passed. Even (some) programmers have learnt the dangers of parsing at run time (e.g. "eval is evil"). How can we decide it's the law we want if we don't know what it means yet?
      • NoboruWataya 2y ago
        > 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).
• koolba 2y ago
  > [Public bodies] shall provide a sufficient description of the structures of all databases under the control of the public body to allow a requester to request the public body to perform specific database queries. I sure hope the impact of this is not that government entities switch to schema less databases!
  • CharlesW 2y ago
    "Schemaless" is like "serverless" in that there's always a schema, even if it's not enforced by the database and instead applied dynamically by the application layer.
• SJC_Hacker 2y ago
  [flagged]
  • deleted 2y ago
    [deleted]
  • crazygringo 2y ago
    Why not? Conceptually, that's literally what it is. Rows of values labeled by columns.
    • SJC_Hacker 2y ago
      Spreadsheets are poorly structured. Different entries in the same column can have different data types. There is no concept of a superkey, so duplicates are allowed. There is a concept of ordering by row/column number which does not necessarily exist in a DBMS. Querying facilities are generally poor. Now you can kinda fix this by restricing the type of column, etc. but most people don't bother They are good at what they do - quick manipulation of relatively small datasets. WYSIWYG printouts with decent formatting and charts. But they are only a "database" in the same way that say, a bunch of random data is.
      • arp242 2y ago
        For a quick 30-second explanation of what a "databases" and "schemas" even are in the first place for non-technical people, it's more than "good enough", and spreadsheets are the most common example that people are generally already familiar with. Unique keys, typing, etc. really isn't relevant here, especially not in the context of what the court case is about. The important bit to get across is that it's a 2D table with rows and columns, and that's all there is to it (that is: it doesn't include the source code to query it).
      • hot_gril 2y ago
        Excel sheets are databases. That's their purpose. They store rows/cols like an RDBMS. They allow joins and constraints, including uniqueness. There are even backends that use a spreadsheet as a DB. What else do you want?
• SkidanovAlex 2y ago
  While I believe that the city should share the schema, and that the city is effectively argues for security through obscurity, I disagree with the main premise of the article: that knowing SQL schema doesn't help the attacker. If I understand the argument of the author here: > Attackers like me use SQL injection attacks to recover SQL schemas. The schema is the product of an attack, not one of its predicates The author appears to imply that once the vulnerability is found, the schema can be recovered anyway. It is not always the case. It is perfectly viable to find a SQL injection that would allow to fetch some data from the table that is being queried, but not from any other table, including `information_schema` or similar. If all the signal you get from the vunlerability is also "query failed" or "query succeeded, here's the data", knowing the schema makes it much easier to exploit. > the problem is that every computer system connected to the Internet is being attacked every minute of every day If you specifically log failed DB queries, than for all the possible injections that such 24/7 attacks would find you have already patched them. The log would then be not deafening until someone stumbles on the actual injection (that, for example, only exists for logged in users, and thus is not found by bots), in which case you have time to see it and patch before the attacker finds a way to actually utilize it. Knowing schema both expedites their ability to take advantage of the vulnerability, but also increases their chances of probing the injection without triggering the query failure to begin with.
  • tptacek 2y ago
    If you specifically log failed database queries, where "failure" means "indicative of SQL injection", then nothing you can do with the schema is going to reduce the signal in that feed --- even a single SQL syntax error would be worth following up on. No, I don't think your logic holds.
    • kmoser 2y ago
      I don't understand your logic. Knowledge of the schema can give an attacker an edge because they now know the exact column names to probe. Whether these probes get logged is irrelevant; even if it makes the system more vulnerable for an instant, it's still more vulnerable. Even if logging failed queries is your metric, then knowledge of column names would make it more likely for an attacker to craft correct queries, which would not get logged, thus making your logs less useful than if the attacker had to guess at column names and, in so doing, incur failed queries.
• jaxgeller 2y ago
  I FOIA'ed >1M pages of docs for my project cleartap.com, a DB of water quality of the USA. Most states would charge a small amount to gather the documents. Michigan wanted $50K to for the FOIA request. I think because of the Flint lead crisis. They wanted me to go away.
  • davethedevguy 2y ago
    I noticed that you do have data for Flint. Did you have to pay it, or is there some appeals process if you're quoted an unreasonable amount? Great project by the way!
    • jaxgeller 2y ago
      Ended up finding the majority of Michigan through scraping. For example, https://www.cityofflint.com/wp-content/uploads/2023/06/Annual-Water-Quality-Report-2022.pdf https://www.cityofflint.com/wp-content/uploads/2023/06/Annua...
• aqueueaqueue 2y ago
  Interesting takeaways from me: All that pompous sounding legalese can still be ambiguous! I feel less bad for not understanding contracts that have 100 word compound sentences. Legal people can't keep up with our tech jargon but they have their own jargon including "predicate" lol. So same logical thinking, different jargon framework. Question: why do they want the schema not the data?
  • tptacek 2y ago
    Because once you have the schema you can issue FOIA requests that include queries for them to run.
    • hot_gril 2y ago
      What if you guess common table names? Wonder if they send back the error message.
    • aqueueaqueue 2y ago
      Oh wow! If that is necessary, that is so kafkaesque! "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..." ...
      • tptacek 2y ago
        You can't ask public bodies to do research for you. That's the public policy balance in our FOIA laws: you can get almost anything (and: talk to Matt, you really can get a lot of stuff), but you have to be specific about what you're asking for, and it has to be "at hand" for the staff responding to the request.
        • hunter2_ 2y ago
          Clerks fielding FOIA requests have SQL consoles "at hand"?
          • 2y ago
• pudding12345 2y ago
  Do stored procedures count as part of the schema? I've recently found a SQL injection vulnerability in a client's SP that was using concat (very badly)
• EMIRELADERO 2y ago
  Am I the only one slightly perplexed/worried by the point-blank source code exemption? It's easy to imagine a scenario where the city decides to develop a specific software in-house and hide the "biases" in the source code, or any other thing one might not find desirable. Hell, they don't even need to make everything from scratch! Could just patch and use a permissively licensed 3rd-party component. In my opinion, the proposed amendment does not go far enough.
  • dotdi 2y ago
    That's why it's important to push for "public money - open source" initiatives like some countries in the EU are trying to implement. Off the top of my head, I think the last (now failed) German coalition had this in their programme but didn't deliver. Maybe the new government will.
  • manquer 2y ago
    It shouldn't be surprising ? It is the same problem people trying to open sourcing closed projects experience, there is all sorts of locked-in proprietary code which the developer and the customer only have the license to use but not share the source. Even projects which from day one are staunchly open and built without direct commercial interests like government contractors need also suffer from this. The Linux kernel challenges for supporting ZFS or binary blob drivers in kernel/user space and so on are well known[1] Paradoxically on one hand information wants to be free, and economics dictate that open source software will crowd out closed competitors over time, it is also expensive to open source a project and sometimes prohibitively so and that deters many managers and companies open sourcing their older tools etc, even if they would like to do so, involving legal and trying to find even the rights holder for each component can deter most managers. If a government put requirements in contracts that the vendor should only use open source components in their entire dependency tree, it could drive the costs very high because a lot of those dependencies may not have equivalent open source ones or those lack features of the closed ones so would need budgets to flesh them out. In the short term and no legislature will accept that kind of additional expense, while in long term public will benefit. --- [1] yes kernel problems are largely a function of GPL, more permissive licenses like Apache 2 /MIT would not have, BSD variants after all had no challenges in supporting ZFS. However a principled stance on public applications being open source by government would be closer to GPL than MIT in terms of licensing. Otherwise a vendor can just import the actual important parts as binary blobs "vendored" code and have some meaningless scaffolding in the open source component to comply.
• lucb1e 2y ago
  I got to about 1/3rd of the way before I noticed my eyes were kinda struggling to read the article. Toggling different CSS rules, it's the #333 gray color. Turning that off is instantly better. The custom font is much thinner than the default, but that by itself doesn't seem to be the issue if the color is (closer to) black. (There is also a font-weight rule, but toggling it makes no visual difference in Firefox. Maybe the text is intended to look different?) Since there is no contact method on the website, figured I'd mention it in a comment; hope this helps
  • deleted 2y ago
    [deleted]
• lubujackson 2y ago
  Juxtapose this legal process with DOGE hoovering (in more ways than one) data willy-nilly from everywhere. The dissonance between THIS uninteresting DB schema being so rigorously protected while massive amounts of sensitive data is completely misappropriated is painful.
• deleted 2y ago
  [deleted]
• alexashka 2y ago
  Wowzers, that was a lot of words to express something that's very simple. A database schema is just an empty form. By looking at an empty form, you know what fields have be filled in, what type of information they'll contain, etc. Of course people making data requests need to know what forms are being used to collect and store information. As for security - not letting people do anything because 'it might be dangerous' is bonkers. The way to secure databases has been known for decades. Let's start living in the 21st century :)
  • tptacek 2y ago
    The whole back half of the post is about why the analysis is not as simple as you suppose it is. We had no trouble establishing at Chancery Court that schemas don't endanger security. That's not why the case failed at the Illinois Supreme Court. The IL Supremes did not decide spontaneously that schemas actually are dangerous.
    • deleted 2y ago
      [deleted]
• deleted 2y ago
  [deleted]
• abfan1127 2y ago
  am I the only disappointed there's no mention of little Bobby Tables?
• ajkjk 2y ago
  This was fine, legally, but I'd be pretty irritated if someone I knew wasted everyone's time on this. The schema clearly is (marginally) useful for hacking, but who cares; it clearly is a file layout also, but who cares; those matter legally but not morally. Morally, this is just dumb: it's not something they really needed, and they're just irritating people and wasting resources for the fun of it. Shameful.
  • jbritton 2y ago
    I think a file layout describes the exact arrangement of bytes in a file. A schema is higher level. It describes what is stored, not how it is stored. A database could be one file, or a file per table, or a file per column. Data could be stored across multiple drives.
  • deleted 2y ago
    [deleted]
  • tptacek 2y ago
    No. I'm involved in local government, and on the citizens commission where we keep track of our our municipality (adjacent to Chicago) stores and manages information. I'm acutely familiar with how people are spending their time in these organizations, and what is and isn't a big lift for them. Increasingly, year over year, more and more information that would previously have been stored in filing cabinets or shared drives is moving into turnkey applications that municipalities buy and enroll all their data in. Those applications are opaque. But almost all of them are front-ends to SQL databases. Being able to recover schemas from publicly operated databases is vital to keeping public records and data public, rather than de-facto hidden from inquiry. Matt's suit was anything but a waste of people's time. Hopefully, it'll result in a change to our state law.
  • zonkerdonker 2y ago
    See here: https://news.ycombinator.com/item?id=43176625 https://news.ycombinator.com/item?id=43176625 FOIA requester responded in comments saying they received a tip indicating illegal practices, and noted in his article that he had previously uncovered evidence of over-policing in black neighborhoods.
• Terr_ 2y ago
  > Each spreadsheet has a header row, labeling the columns, like “price” and “quantity” and “name”. A database schema is simply the names of all the tabs, and each of those header rows. This is also how I explain it to my relatives, I'm kind of surprised this analogy (one so direct that it's almost literal) didn't fly with the judges. If database column names cannot be revealed, then shouldn't that mean the state is also able to redact the headers of all their spreadsheets?
  • kmoser 2y ago
    Knowing a spreadsheet header doesn't help an attacker gain access to that spreadsheet in any way. Knowing SQL column names may give an attacker an advantage in accessing a database.
    • Terr_ 2y ago
      Compare: "Knowing the writing style of current employees may give an attacker an advantage while phishing, therefore, we cannot turn over any memos or emails whatsoever." Ditto for the org-chart.
    • flutas 2y ago
      Per the post, this also wouldn't fly. > Believe it or not, there’s case law on “would” versus “could” with respect to safety. “Could” means you could imagine something happening. But the legal standard for “would” is “clear evidence of harm leaving no reasonable doubt to the judge”. The statute set the bar for me very low and I managed to clear it.
      • Terr_ 2y ago
        Reminds me of Shall versus May in RFCs. (Though those are, of course, statements of obligation rather than natural consequence.)
  • butlike 2y ago
    It's a reverse vlookup
• lq9AJ8yrfs 2y ago
  In the new language proposed in SB0226 (as linked, didnt search for authoritative sources, can't tell how durable that link will be for posterity, arrgh archiving the web is hard etc), doesn't that language leave open a hole for excessive complexity to be a reservoir for FOIA resistance? Feels like there is an important theme here that SB0226 is dancing around --could government be legible in addition to being "plain-text" transparent? "plain-text description" of "each field of each database of the public body" and "specific database queries" may not do what you mean. Not sure how to fix it though. I could see gratuitous ORMs and database-of-databases patterns winning tax dollars with taunt-them-with-the-schema listed as a feature.
• fatih-erikli-cg 2y ago
  [dead]
• indymike 2y ago
  There is no fredom of information if the public is not allowed to know what data the government has.
• b8 2y ago
  Got to see this happen day by day on the Midwest Venture Partners Slack. There was another lawsuit Chappman and Tom did for laser based speed detection in Chicago.
• djeastm 2y ago
  I suppose I need to change all my column names to random 16-character strings so I don't leave my database insecure!
• kingforaday 2y ago
  Given the Illinois Supremes decision, seems like an opportunistic time to say "Everything is a file". 1. https://en.m.wikipedia.org/wiki/Everything_is_a_file https://en.m.wikipedia.org/wiki/Everything_is_a_file
• deleted 2y ago
  [deleted]
• dylan604 2y ago
  "Retrieve the data of every parking ticket issued to ‘Bob O’ and also all the rest of the information in the database including everyone’s passwords." This is the example of SQL Injection written in plain English, yet "everyone's" is problematic here in that it's an orphaned single quote. If "Bob O'Conner" is bad, so is "everyone's"
• irrational 2y ago
  > I’ll conclude this long piece by saying (1) obviously the bill should pass, and (2) it should be called “The Chapman Act”. (3) I imagine Chicago greatly regrets towing Matt Chapman "over a facially bogus ticket".
• DangitBobby 2y ago
  When a law is ambiguous by wording, why do they never ask the people who drafted the law what was intended?
  • tptacek 2y ago
    The current sitting ILGA is not the ILGA that passed the statute.
    • DangitBobby 2y ago
      They are probably still alive, shouldn't be that hard to find. They have no problem giving subpoenas to other witnesses or soliciting expert testimony.
  • jaza 2y ago
    That would be against the separation of powers doctrine inherent in all Western democracies. The job of the legislature is to write the law. The job of the judiciary is to interpret the law. 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!
    • DangitBobby 2y ago
      I don't see how it could break separation of powers, especially if a legislator could provide minutes and/or a paper trail of discussions and revisions pointing the intent in a certain direction. You know, like evidence. The legislature surely has intent while writing the law, otherwise what would be the point in trying to interpret it, and the whole thing being litigated is the authors intent. I don't think the separation of powers doctrine presupposes that the legislature has no idea what their goals are while writing laws, that would be quite an insane assumption to bake into our system, and broken by design. And in this case, I very much doubt it was left intentionally ambiguous, since FOIA was clearly intended to help people get information from obstinate government agencies. What would even be the point in writing the law if obstinate government agencies are supposed to be able to weasel around the ambiguity behind a comma? Regardless, if we are able to ask the people who spent time drafting it, we could ask. There might even be a paper trail!
• inetknght 2y ago
  > You also generally can't FOIA the source code of programs they run. 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.
  • jaza 2y ago
    I assume that - even though there's a strong public interest argument for it - government orgs are prone to blanket banning the release of source code, for the same primary reason that businesses are prone to doing so. That is, too high a chance of sensitive data (passwords, tokens, IP addresses, etc) being hard-coded in all-too-often non-12-factor-aspiring code; and too much security / liability headache if said sensitive data gets out. 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.
    • inetknght 2y ago
      > 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.
• gervwyk 2y ago
  Should have used mongodb in the first place.
  • qbxk 2y ago
    lol'd so hard at this
• gunian 2y ago
  sql injection court seems more fun than slave court where they tell you spending anything above 5 is a crime lmaooooo
• neilv 2y ago
  > [...] where the only way to get at the underlying data is to FOIA a database query. Can you request the desired information using natural language, based on your guesses of what information they store?
  • tptacek 2y ago
    Probably not, because then you'd be asking them to go do research. You FOIA for specific documents and records.
    • neilv 2y ago
      So you can ask for the document that is the inspection report from Mel's Diner on date 11/11/2024? 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"?)
      • tptacek 2y ago
        You can ask for "the inspection report from Mel's Diner on date 11/11/2024". You can also ask for "every inspection report ever done on Mel's Diner". 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.
        • neilv 2y ago
          Thanks. I might be misinterpreting, but maybe the standard for what's "research" involves not presuming that a given request can be satisfied by a database query? 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"?
• rafram 2y ago
  How were you able to stand as an expert witness when you have a personal relationship with the plaintiff? I don’t know the specifics of the law in Illinois, but my understanding is that that would generally be a disqualifying conflict of interest.
  • hondo77 2y ago
    I have this cousin, Vinny, who's a lawyer, and he was able to use his girlfriend as an expert witness. Both sides agreed she really knows her stuff because that's what really matters.
• Jean-Papoulos 2y ago
  I understand freedom of information, but what exactly does the public gain by Matt getting the database schema ? 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.
  • tptacek 2y ago
    The blog author argued no such thing, because that is not true.
  • gizmo 2y ago
    Municipalities obstinately refuse reasonable requests because they resent that the Freedom of Information Act allows regular civilians to get all up in their business. The excuses they make for noncompliance (it's burdensome! it violates privacy! sql injection!) are not serious. They don't want to comply because they don't like accountability. That's it.
• makach 2y ago
  Does disclosure of a database schema really jeopardize the security of the system? Yes 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
• cytocync 2y ago
  [dead]
• scotty79 2y ago
  > Does the “would jeopardize” language in the statute apply to everything in the exemption, or just to the nearest noun “any other information”? I think law and lawmaking would be vastly improved if only lawyers learned the miracle of parentheses.
  • Ylpertnodi 2y ago
    Comma's can be expensive, too.
• ngriffiths 2y ago
  > Congratulations! You now understand databases. Data engineering: doing a lot of fancy work to make a very simple product
• rubymancer 2y ago
  It's Matt Champan! https://mchap.io/ https://mchap.io/ 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.
• thayne 2y ago
  I'm confused why file layout is included in the list of exceptions in the first place. If an adversary knowing your file format is a security problem, then you are doing something very wrong! 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.
• gavin_gee 2y ago
  https://x.com/JackRhysider/status/1885732851779285184 https://x.com/JackRhysider/status/1885732851779285184
• el_snark 2y ago
  Enjoyed the read. Good luck with the future developments. 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? :-)
• boxed 2y ago
  > Unfortunately, the Illinois Supreme Court had at their disposal a second dictionary. In the Merriam-Webster Online Dictionary, a “schema” is defined as “a structured framework or plan: outline”. “This is a difference in name only”, said the court. Argh. Schemas are now file layouts. We lose. 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!
• hackburg 2y ago
  [dead]