[HN Gopher] I Went to SQL Injection Court
___________________________________________________________________
I Went to SQL Injection Court
Author : mrkurt
Score : 485 points
Date : 2025-02-25 18:39 UTC (4 hours ago)
(HTM) web link (sockpuppet.org)
(TXT) w3m dump (sockpuppet.org)
| tptacek wrote:
| 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.
| copypasterepeat wrote:
| 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 wrote:
| 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 wrote:
| My real goal is zoning.
|
| God speed to you sir! What is your goal wrt zoning?
| tptacek wrote:
| The categorical elimination of single-family zoning along
| with any building envelope restrictions that would make
| as-of-right 3-flats uneconomical.
| pchristensen wrote:
| That would be an outstanding outcome! Is this just for
| Oak Park, or beyond?
| tptacek wrote:
| 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 wrote:
| Why does zoning matter more in Oak Park and Evanston?
| High demand from being on the El and close to Chicago?
| tptacek wrote:
| 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.
| Spivak wrote:
| It's might actually be easier to win the economics battle
| by chipping away at restrictions on taller buildings. The
| builders in my area are copy/pasting a 3-flat design all
| over the place but it requires bargain-basement land
| prices (literally building on former toxic waste dumps)
| or money from the township because 3-flats make you have
| to build wide.
| tptacek wrote:
| The muni I live in is very constrained (we're just 4
| square miles, right on the border of the west side of
| Chicago) and our land is overwhelmingly SFZ, so most of
| the ballgame is getting SFZ lots opened up. The emerging
| consensus is towards "missing middle" housing, which is
| 2-40 units (but really, a medium term sweet spot in the
| teens), where you're talking about buildings spanning
| multiple lots.
|
| That very little can economically be built on existing
| SFZ lots even with relaxed zoning is actually a feature,
| not a bug, for getting this done. People want change to
| be slow. At least to begin with, it's better
| strategically if it takes a couple years and gradual
| tweaking to make lots of building happen.
| cozzyd wrote:
| Kam Buckner is trying to get something passed at the
| state level (but wouldn't apply to Oak Park. https://ilga
| .gov/legislation/BillStatus.asp?DocNum=3288&GAID... )
| btucker wrote:
| A step in the right direction last week for the largest
| upzoning effort in the city! https://archive.is/QuOcJ
|
| Of course the a vocal minority is fuming about higher
| density.
| hinkley wrote:
| "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 wrote:
| Like a hedge fund? Or are we including those committed to
| violence?
| Terr_ wrote:
| 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/
| chaps wrote:
| Aaaaaaa! I need to finish my post! :(
| zahlman wrote:
| >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 wrote:
| 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 wrote:
| > 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 wrote:
| 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.
| duxup wrote:
| 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 wrote:
| 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 wrote:
| Maybe. But now I'm _really_ curious how bad that schema must
| be for them to hide it so viciously.
| jrochkind1 wrote:
| I think it's just an excuse to avoid making it feasible for
| the public to get the data.
| duxup wrote:
| 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".
| michaelmrose wrote:
| 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 wrote:
| Maybe their schema has triggers and stuff
| hinkley wrote:
| Part of the reason I'm so... enthusiastic... about tech debt
| is that I've worked a few times where we had a competitor
| whose lunch we were stealing or who was stealing ours and the
| ability or inability to copy features cheaply was
| substantially the difference between us.
|
| That quad graph of value versus difficulty that everyone
| loves? It's not quadrants it's a gradient and the difficulty
| dimension depends quite a bit on context. What's a 4
| difficulty for me might be a 6 for someone else. Accidental
| versus intrinsic complexity plus similarity to or
| distinctions from things we have already done.
| bob1029 wrote:
| The schema on the last project I worked on was probably our
| most important IP. Specifically, the ways in which we solved
| certain circular dependency issues.
|
| I wouldn't take the ability to design a schema for granted. I
| don't think many people are any good at it. Do not
| underestimate the value of your work products.
| chaps wrote:
| Not quite, and the details get hairier the closer you look.
| The database in-question here is an IBM system. The database
| itself is used for government functions, making it FOIA'able,
| despite it being managed by a third party company. IBM even
| tried to argue that the schema was trade secret, but the
| statute isn't straight forward. Here's my (successful)
| response when they tried:
|
| You mentioned on Thursday over the phone that IBM is not too
| keen on having its database schema released, and, between IBM
| and Chicago, is seeking an exemption under 5 ILCS 140/7(1)(g)
| - an exemption that is only valid if the release of records
| would cause competitive harm. This email preemptively seeks
| to address that exemption within the context of this request
| in the hopes of a speedier release of records. It is FOI's
| belief that there is little room for the case for the valid
| use of 5 ILCS 140/7(1)(g) when considering the insignificance
| of the records in conjunction with the release of past
| documents:
|
| 1. Chicago released CANVAS's technical specification [1]
| seven years ago. To the extent that the specification's
| continued publication does not cause competitive harm, it is
| very unlikely that the release of CANVAS's database schema
| would cause any harm. 2. The claim that the release of a
| database schema would cause competitive harm is not unlike
| suggesting that the release of filing cabinets' labels can
| cause competitive harm.
|
| Furthermore, in your response, please be mindful that the
| burden of proving competitive harm rests on the public body
| [2].
|
| [1] https://www.cityofchicago.org/content/dam/city/depts/dps/
| Con... [2] http://foia.ilattorneygeneral.net/pdf/opinions/201
| 8/18-004.p...
| bobsmooth wrote:
| 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 wrote:
| 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.
| barbazoo wrote:
| 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 wrote:
| 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 time1. 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
|
| 1 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...
| wswope wrote:
| 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 wrote:
| 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 wrote:
| 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
| wswope wrote:
| 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.
| Andys wrote:
| 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 wrote:
| 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 wrote:
| How is a database schema not a file layout?
| kasey_junk wrote:
| 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 wrote:
| Another way to think about it is that if a SQL schema is a
| file, so is an Excel spreadsheet template.
| hot_gril wrote:
| File or file layout? Cause both of these are probably
| stored as files, .sql and .xltx respectively.
| paulddraper wrote:
| 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.
| tptacek wrote:
| I don't follow your point.
| atkulp wrote:
| It's interesting that the opening analogy in the post
| uses an Excel spreadsheet as a great way to explain a
| database. It's such an easy next step to say the way an
| xls/ods file is saved is a file format but the column
| layout in the tabs/tables are the schemas. The court (and
| the city) playing these games is so scary since it is so
| biased toward all modern government data being covered by
| FOIA exemptions.
| ludston wrote:
| But on the other hand, in all database systems the schema
| is used to determine how the files are laid out. Although I
| suppose the same thing could be argued for any data that is
| stored in a file, excepting that a schema is metadata that
| determines the organisation of data so it's a bit of a
| special case.
| tptacek wrote:
| In a Microsoft Word document, the section headings also
| tell Word how to lay out the Word document file.
| hot_gril wrote:
| Do you mean that section headings aren't a file layout?
| That's their entire purpose.
|
| Edit: If you're talking about the byte representation
| only, I don't think section headings indicate the
| placement of the body's bytes.
| tptacek wrote:
| You have found an argument that proves too much.
| hot_gril wrote:
| There's a solid chance that the schema gives away what DBMS
| is being used. But even if it didn't, I'd still call it a
| file layout in this context.
| tptacek wrote:
| So?
| hot_gril wrote:
| So if you have the schema and the DBMS, you probably know
| how data is arranged in the files ("files" in the
| filesystem sense).
| hyperpape wrote:
| The parent asks "how is it not a file layout" not "can
| you guess the file layout?" given it.
|
| I am a human, you know I have a kidney, but I am not a
| kidney.
| hot_gril wrote:
| If you send a copy of the code, is that sending the code?
| If it is, what about sending a copy of the code with a
| Caesar Shift?
| chaps wrote:
| Is your argument that government agencies should also
| withhold the names of filing cabinet manufacturers? :)
| hot_gril wrote:
| Just that it's a file layout. Or even if you strictly
| define a file layout as say an ext4, NTFS, or FAT file
| tree, that revealing the schema is revealing the file
| layout.
|
| I don't know why they don't want to reveal file layouts,
| but for whatever reason, they decided it was "per se"
| exempt regardless of the security implications.
| tptacek wrote:
| It's obviously not a file format. The same SQL schema can
| generate N different files, with N different layouts, for
| N different databases. By the logic you're using
| ("schema" + "database vendor" = "file format"), a Word
| document outline is also a file format.
| chaps wrote:
| The DBMS is almost definitely going to be mentioned in
| RFP or specification documentation. As it was in this
| lawsuit.
| michaelmrose wrote:
| Because it doesn't describe how data is laid out on disk.
| hot_gril wrote:
| Neither does a file layout. FS will decide that... even
| then, not physically.
| kelnos wrote:
| We're talking about "file layout" at the application
| level, not the filesystem level.
|
| But your comment illustrates just how difficult it is to
| nail these things down, based on inherently imprecise
| language.
| hot_gril wrote:
| So you mean the filetree and file contents, as seen by
| userspace program?
|
| It's meant to be imprecise, because they didn't want some
| "gotcha." If they say we won't reveal the disk layout,
| technically you can't tell that from the filetree. If
| they won't reveal the filetree, but this is SQLite, it's
| always a single file. If it's file tree + contents, well
| the CPU byte endianness might matter for some DBMSes,
| even though you could just try both.
| dools wrote:
| The schema describes the database layout. The file layout (if
| you were going to call it that) in a modern RDBMS would
| describe how the RDBMS implemented a particular database
| layout as described by the schema.
| hyperpape wrote:
| It literally does not describe a file, and does not literally
| describe the data layout of anything on disk (though with
| enough knowledge, you may be able to infer facts about
| probable layouts).
| paulddraper wrote:
| > does not literally describe the data layout of anything
| on disk
|
| Huh? Depends on the DMBS, but each InnoDB table is a file.
|
| And the schema determines the file structure.
| hyperpape wrote:
| > but each InnoDB table is a file.
|
| A table isn't a schema, it is a component of a schema,
| and most databases don't use InnoDB.
| paulddraper wrote:
| > it is a component of a schema
|
| So if you have the schema, you have the tables.
| kelnos wrote:
| Schema is an abstraction over the file structure.
| Different RDBMSes will use different file layouts for a
| given schema. The same RDBMS may even have different
| engines that use different file layouts, or may change
| file layout between major versions.
|
| "Determines" is too weak: it must be "is". If "schema is
| file layout" is true, then sure, a schema is a file
| layout. But if it is merely "schema determines file
| layout", then no, a schema is not a file layout.
| hot_gril wrote:
| Abstractions are notoriously leaky in DBMSes. First off,
| they don't even use the same SQL spec. Give me a schema
| that uses anything Postgres-specific, and I can tell you
| what the bytes on disk look like for a given row or
| index.
|
| I think it's a moot point anyway because the language is
| broader than just files in the filesystem sense, which is
| basically what the court said too.
| hot_gril wrote:
| Schema is definitely software, a operating protocol, source
| code, and file layout. Maybe also documentation.
| tptacek wrote:
| A schema isn't software in the sense imagined by the ILGA. If
| it was, every Excel spreadsheet would be too, and Excel
| spreadsheets are the basic currency of FOIA.
|
| An "operating protocol" is a step-by-step list of things to
| accomplish some action. It's a finite state machine for
| humans. Obviously, a schema isn't that; a schema is
| declarative, and an operating protocol is imperative.
|
| The court definitively established that SQL schemas aren't
| source code in the sense imagined by the ILGA. SQL queries
| can be. Schemas are not.
|
| See downthread for why a schema isn't a file format. In fact,
| a schema is almost the opposite of a file format.
|
| A court will look at the term "documentation" in the ordinary
| sense of the word; as in, "a prose description and set of
| instructions".
|
| "Associated with automated data processing operations" isn't
| an element in the statute; it's a description of all of the
| elements.
| hot_gril wrote:
| If the Excel spreadsheet has formulas in it, it's software.
| If you're just talking about the data in the sheet, i.e.
| what you'd get exporting it as a CSV, then it's not.
|
| Col types, unique/FK/PK constraints, default values, and
| computed cols define the steps for handling row
| inserts/updates/deletes. Even adding a uniqueness
| constraint to an already-unique col will change how the
| code interacts with it, specifically how it deals with
| concurrency/locking. If they said it has to be an
| imperative programming language, then it's not that.
|
| If they said the schema isn't source code then ok, but I
| still think it is.
| tptacek wrote:
| I assure you that Excel spreadsheets with formulas in
| them are FOIA-able in Illinois. Since we can take that as
| axiomatic, I think we can put "schemas are software" to
| bed.
| hot_gril wrote:
| SQL schemas aren't Excel spreadsheets.
| tptacek wrote:
| That's fascinating, but you just claimed Excel
| spreadsheets were "software" in the sense of the Illinois
| FOIA statute definition, and they are not. QED.
| hot_gril wrote:
| You said that SQL schemas aren't software, and that's
| what this lawsuit was about. If they explicitly say that
| Excel docs (even w/ formulas) aren't software, I think
| they're wrong, but that doesn't matter because Excel docs
| aren't SQL schema.
|
| Now if you want to go by Illinois definitions, SQL
| schemas are file layouts, that's why the plaintiff lost.
| tptacek wrote:
| Again: the post explains why the court determined schemas
| to be file layouts, and none of it involves any of the
| logic you've supplied here. Even Chicago didn't try to
| claim that a schema was a "software".
| hot_gril wrote:
| They didn't need to. In the first appeal, it didn't
| matter because it didn't jeopardize security. In the
| second appeal, they said it's a file layout.
|
| You also said SQL schemas are declarative. As in
| declarative programming, so software.
| n_plus_1_acc wrote:
| An Excel formula should be considerd a kind of software,
| because you cab do code golf in it.
| pavon wrote:
| I think a schema will definitely be part of the source
| listing, either in the main programming language source code
| or in a some other file used to define or initialize the
| database. But I don't think it _is_ software, any more than a
| protocol is software. Software does something.
|
| One tricky aspect of this is that even if the schema itself
| as a higher level concept doesn't fit into any of those
| definitions, all existing _instances_ of the schema are
| likely considered either source listings or documentation. So
| the instances are barred from release per se, and you can 't
| ask the government to create new documents.
| gregw2 wrote:
| I completely agree with you that (unlike/despite the Supreme
| Court ruling), database table/column schema design (and other
| system designs) should fall under the Illinois statute as
| "documentation pertaining to all logical and physical design of
| computerized systems". It's interesting that the law did pick
| up on that distinction between logical and physical design but
| none of the parties described in this article did.
| Logical/physical designs are not just about servers and
| integrations, they are also about data.
|
| I'm not sure why that wasn't argued by the state and the state
| argued the database schema was a "file format". Per my
| reasoning, the state still would have won, but for different
| reasons.
|
| I disagree with you slightly however and would say that the
| schema table/column names should be considered not logical but
| "physical design" while the business naming/meaning of tables
| would be a "logical design" (or conceptual design). See
| Wikipedia: https://en.wikipedia.org/wiki/Logical_schema
|
| SQL injection is really about physical schema designs, not
| logical ones (I do get that every bit of information including
| business naming of tables/columns helps in an attack, but it
| does change the degree of threat and thus the balancing tests
| of the risk which are relevant per the definitions and case law
| described in the original article.)
|
| So in terms of what the law /SHOULD/ be, the law should _not_
| include logical design as a security exception, only physical
| design. It /SHOULD/ be possible for citizens to do FOIA
| requests and get a logical understanding of all the database
| fields without giving them the SQL names that can accelerate
| SQL injection attacks. In that way citizens could ask for the
| data by a logical/business-named handle rather than a physical
| one.
|
| And the state should create logical models or provide data
| dictionaries with business (not technical terms) on request as
| part of their FOIAable obligations to their citizens for the
| data they are maintaining.
|
| My 2 cents as someone designing database schemas for 25+ years.
| hnthrow90348765 wrote:
| >just self-important message-board hedging
|
| I can confidently say it does not stop at message boards for many
| people, self included
| tptacek wrote:
| It's a real issue when writing an affidavit or testifying. Lots
| of ingrained bad habits.
| gowld wrote:
| 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 wrote:
| > 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...
| chaps wrote:
| 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-...
| doctorpangloss wrote:
| What are the administrators of CANVAS hiding?
| chaps wrote:
| 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 wrote:
| Considering how much they fought to not release the schema,
| there's probably a column named "exempt_from_penalty" or
| something equally obvious.
| MBCook wrote:
| Well that certainly sounds suspicious. But it could also
| provide more damming evidence of targeting groups, people
| skimming the till, bribes to make tickets go away, all sort
| of fun shenanigans.
|
| And boy they're fighting suspiciously hard.
|
| Good luck.
| 9dev wrote:
| Earnest question: If you suspect them of lying on the
| issue, why would you trust them to release the full schema
| in response to the FOIA request, and not just omit any
| possibly incriminating columns?
| cyanydeez wrote:
| Many times the people answering the requests aren't part
| of the conspiracy to commit random acts of malice.
| Sometimes they're roped into it under threat of
| termination.
|
| And often times, the denials eventually lead to
| significant reorg once judges and Congress can revise
| laws to fix the ambiguities.
| jrockway wrote:
| It's always a possibility that some low level official
| not in on the scam sees the FOIA request before
| management tells them not to work on it. The more you ask
| for, the less filtering there is going to be, simply
| because of how people work.
|
| If you're running the scam, you don't want to tell low
| level employees about it, because they have no incentive
| not to blow the whistle.
| butlike wrote:
| 'ethnicity' header, 'net_income' header... wouldn't doubt
| chicago could be cave man enough to do this
| hathawsh wrote:
| Kudos to you for enduring through this fight! We can only
| achieve transparency when people choose not to be complacent.
| Thank you.
|
| What do you think are the next steps?
| chaps wrote:
| My first step is to actually finish my post :)
|
| But after that, getting a reasonable law passed to fix this
| now-broken nonsense.
| mmaunder wrote:
| Thanks for fighting the good fight for us all!
| hn_user82179 wrote:
| This older post was such a fantastic read, thanks for sharing
| your story!
| layoric wrote:
| It's dated from ~2 weeks ago... is there other date
| information I am missing?
| hn_user82179 wrote:
| ah no, I just said "older" since OP said it was older and I
| wanted to distinguish from the SQL post that this post is
| about
| notjulianjaynes wrote:
| Damn, this is impressive. I've been fighting with a state
| agency since December for 17,000 emails. I don't think I've
| ever tried to request emails and received zero push-back, but a
| $33 million estimate just, _chef 's kiss_
| maCDzP wrote:
| Have you tried looking for information from the developer about
| CANVAS? With any luck the developer has support documentation
| online that describes CANVAS and maybe you'll be able to narrow
| down your FOIA request.
| manquer wrote:
| I think the point of the lawsuit is less about CANVAS schema
| itself and more about the ability of the government to hide
| this kind of information from FOIA requests.
| foota wrote:
| > Normally, a flustered public records officer would just
| reject a giant request for being for "unduly burdensome"... but
| this sort of estimate is practically unheard of. So much so
| that other FOIA nerds have told me that this is the second
| biggest request they've ever seen. _The passive aggression is
| thick_. Needless to say, it 's not something I'm willing to pay
| for!
|
| Welcome to Seattle :-)
| foota wrote:
| Out of curiosity, could you ask for something like "one row of
| data from every table in the CANVAS database"?
| mbreese wrote:
| This is a technical solution to a people problem. My reading
| is that the city doesn't want to give up this information. If
| that's the case, a technical solution wouldn't work, no
| matter how easy it is. And given that this has already gone
| to the Illinois Supreme Court (and lost), the only solution
| is what is discussed at the end: updating the law.
| foota wrote:
| I agree this is something of a technical solution, but the
| court wasn't interpreting whether you could ask for rows
| from a database, but whether you could ask for the schema
| directly. I don't think the court had the option of saying
| "you can't ask for the schema, but asking for a sample row
| is ok".
| chaps wrote:
| The short answer is yes, you can do this. I've seen this
| work for emails, where the request is basically, "Give me
| the most recent email of blah@gov.com".
|
| And yeah, the plan was to eventually submit a batch of
| requests using the table names, similar to `SELECT * FROM
| {table_name_from_schema_request} LIMIT 1`, but one FOIA
| request per-table.
| cyanydeez wrote:
| Seems like you could asked for a verbally masked
| description? Like an enigma coda specific to the FOIA.
|
| "Describe to me the columns, in simple non-programmatic
| english, and what the purpose of the table is for, for
| each table related to parking tickets"
|
| Essentially a human to schema DSL That is only
| technically decipherable by the admin of the database.
| Then you're not having actual code and only the admin
| could decipher.
|
| But yah, as you said, if the humans don't want to
| disclose their foibles, how the request is filled is
| technically meaningless.
| probably_wrong wrote:
| 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 wrote:
| Short answer -- already been done.
|
| This (spoiler) visualization's going into my eventual post
| about the lawsuit: https://observablehq.com/d/026992341cc47ff0
| lcnPylGDnU4H9OF wrote:
| > 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 wrote:
| 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-sch...
| lcnPylGDnU4H9OF wrote:
| Yeah, it's obvious the double standard here, then. Curious
| indeed why they are so adamant to keep the schema/data
| secret.
| noboostforyou wrote:
| 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 wrote:
| is_cop bool not null default false
| kelnos wrote:
| 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.
| kasey_junk wrote:
| There is an explanation for the fight that doesn't
| involve something nefarious with CANVAS (though I think
| CANVAS is dodgy from talking with Matt).
|
| The precedent set here will let data journalists (like
| Matt) setup effectively automated FOIA workflows on _any_
| database they can get the name of for a FOIA request. So
| even if _this_ db isn't dodgy it enables any of them that
| are to be found quickly.
|
| Or even less cynically, its just going to cost a ton of
| resources to respond to all those automated FOIA
| requests.
| Y_Y wrote:
| 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 wrote:
| 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 wrote:
| 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?
| copypasterepeat wrote:
| I am not a lawyer, but my understanding is that's just how the
| justice system works. Reasonable people can disagree about what
| exactly a complicated statement says, since language is full of
| ambiguities. People have been discussing what the U.S.
| Constitution says exactly from the day it was written and there
| are still a lot of disagreements.
|
| The standard response to this is that laws should be written in
| ways that are non-ambiguous but that's easier said than done.
| Not to mention that sometimes the lawmakers can't fully agree
| themselves so they leave some statements intentionally
| ambiguous so that they can be interpreted by the courts.
| skissane wrote:
| I've often thought we'd get more sensible results in court
| cases on computer-related issues if we had specialised courts
| where the judges were required to have a relevant degree
| (computer science, software engineering, computer
| engineering, information systems, etc). But I doubt it is
| going to happen any time soon.
| ptsneves wrote:
| Civil code law uses that way of thinking, where there are
| specialised courts for different areas: administrative,
| civil, labor, family, commercial and so on. I actually am
| not so sure it is great as these courts increase the depths
| of the bureaucracy to the point of being self serving. They
| also serve to segment expertise.
| kmoser wrote:
| Nobody reasonably expects all laws to be written completely
| unambiguously. But since laws (and indeed all manner of legal
| documents) are filled with lists and modifiers, I don't think
| it's unreasonable to require that they be written to a
| certain standard which defines how these lists and modifiers
| should be interpreted, similar to RFC 2119
| https://microformats.org/wiki/rfc-2119.
| koolba wrote:
| > [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 wrote:
| "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.
| SkidanovAlex wrote:
| 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 wrote:
| 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 wrote:
| 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.
| tptacek wrote:
| To probe for what? How does knowledge of a column name make
| it easier for me to discern whether a SQL injection
| vulnerability exists? I've spent a lot of time in my career
| probing for SQL injection, and I can't remember an instance
| where my stimulus/response setup involved the table names.
|
| SQL injection is a property of _a SQL query_ , not of the
| schema itself. To have a meaningful chance of blind-one-
| shotting a query, getting a TRUE/FALSE answer about
| susceptibility without ever generating a SQL syntax error,
| I would need to see the queries themselves.
| default-kramer wrote:
| > How does knowledge of a column name make it easier for
| me to discern whether a SQL injection vulnerability
| exists?
|
| It doesn't. It just means that as soon as you find one,
| you can immediately begin crafting valid queries instead
| of randomly guessing table names and columns, therefore
| not setting off the "DB query failed" alert.
|
| EDIT: I guess this is the part I missed:
|
| > To have a meaningful chance of blind-one-shotting a
| query, getting a TRUE/FALSE answer about susceptibility
| without ever generating a SQL syntax error, I would need
| to see the queries themselves.
|
| Really? I guess I have to take your word for it because
| I've never attempted it, but I would have thought that in
| some (horribly broken) systems `bobby tables' or 1=1 --`
| would have a very reasonable chance of detecting SQL
| injection without alerting anyone.
| jstanley wrote:
| You can craft valid queries that don't reference any
| table or column name.
| lucb1e wrote:
| > nothing you can do with the schema is going to reduce the
| signal in that feed --- even a single SQL syntax error would
| be worth following up on
|
| Syntax errors coming from your web application mean there is
| a page somewhere with a bugged feature, or perhaps the whole
| page is broken. Of course that's worth following up on?
|
| Edit: maybe I should add a concrete example. I semi-regularly
| look at the apache error logs for some of my hobby projects
| (mainly I check when I'm working on it anyway and notice
| another preexisting bug). I've found broken pages based on
| that and either fixed them or at least silenced the issue if
| it was an outdated script or page anyway. Professionals might
| handle this more professionally, or less because it's about
| money and not just making good software, idk
| ethbr1 wrote:
| > _Syntax errors coming from your web application mean
| there is a page somewhere with a bugged feature, or perhaps
| the whole page is broken. Of course that 's worth following
| up on?_
|
| This is a government system, with apps probably built by
| lowest-bid contractors.
|
| I imagine most of us would be horrified by the volume of
| everyday failed queries from deployed apps.
| pockmarked19 wrote:
| Reminds me that the recently discovered "leak emails using
| YouTube" exploit kicked off from reading what is essentially, a
| schema.
|
| https://brutecat.com/articles/leaking-youtube-emails
| robocat wrote:
| > kicked off from reading what is essentially, a schema.
|
| I wouldn't call json a schema.
|
| In the HN discussion tptacek replied that "$10,000 feels
| extraordinarily high for a server-side web bug":
| https://news.ycombinator.com/item?id=43025038
|
| However his comment assumes monetisation is selling the bug;
| (tptacek deeply understands the market for bugs). However I
| would have thought monetisation could be by scanning as many
| YouTube users as possible for their email addresses: and then
| selling that limited database to a threat actor. You'd start
| the scan with estimated high value anonymous users. Only
| Google can guess how many emails would have been captured
| before some telemetry kicked off a successful security audit.
| The value of that list could possibly well exceed $10000.
| Kinda depends on who is doxxed and who wants to pay for the
| dox.
|
| It's hard to know what the reputational cost to Google would
| be for doxxing popular anonymous accounts. I'm guessing video
| is not so often anonymous so influencers are generally not
| unknown?
|
| I'm guessing trying to blackmail Google wouldn't work (once
| you show Google an account that is doxxed, they would look at
| telemetry logs or perhaps increase telemetry). I wonder if
| you could introduce enough noise and time delay to avoid
| Google reverse-engineering the vulnerability? Or how long
| before a security audit of code would find the vulnerability?
|
| Certainly I can see some governments paying good money to dox
| anonymous videos that those governments dislike. The Saudis
| have money! You could likely get different government
| security departments to bid against each other... Thousands
| seems doable per dox? The value would likely decrease as you
| dox more.
| pockmarked19 wrote:
| > I wouldn't call json a schema.
|
| What you see there is a protobuf, serialized as JSON. If a
| protobuf definition isn't a schema, I don't know what is.
| Volundr wrote:
| I'm not an attacker, just a boring old software dev. If there's
| an SQL Injection I'd say all bets are off re: schema.
|
| That said I've definitely worked on applications where knowing
| the schema could help you exfill data in the absence of a full
| injection. The most obvious being a query that's constructed
| based on url parameters, where the parameters aren't
| whitelisted.
|
| So I actually do agree that the schema could potentially be of
| marginal benefit to the attacker.
| butlike wrote:
| Wouldn't admitting this in court pin you with some sort of
| negligence? (if you knew having a schema revealed would
| compromise your app in some way).
| jaxgeller wrote:
| 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 wrote:
| 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 wrote:
| Ended up finding the majority of Michigan through scraping.
|
| For example, https://www.cityofflint.com/wp-
| content/uploads/2023/06/Annua...
| aqueueaqueue wrote:
| 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 wrote:
| Because once you have the schema you can issue FOIA requests
| that include queries for them to run.
| hot_gril wrote:
| What if you guess common table names? Wonder if they send
| back the error message.
| pudding12345 wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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
| lubujackson wrote:
| 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.
| alexashka wrote:
| 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 wrote:
| 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.
| abfan1127 wrote:
| am I the only disappointed there's no mention of little Bobby
| Tables?
| ajkjk wrote:
| 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 wrote:
| 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.
| tptacek wrote:
| 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 wrote:
| See here: https://news.ycombinator.com/item?id=43176625
|
| FOIA requester responded in comments saying they received a tip
| indicating illegal practices, and noted in his article that he
| had previously uncovered evidence of over-policing in black
| neighborhoods.
| Terr_ wrote:
| > 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 wrote:
| 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_ wrote:
| 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.
| butlike wrote:
| It's a reverse vlookup
| lq9AJ8yrfs wrote:
| 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.
___________________________________________________________________
(page generated 2025-02-25 23:00 UTC)