News for hackers. Stuff that matters.
Re:I Went to SQL Injection Court by pavon on Tuesday February 25, 2025 @02:24PM (#43176149)
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.
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @02:56PM (#43176512)
Schema is definitely software, a operating protocol, source code, and file layout. Maybe also documentation.
Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @03:12PM (#43176723)
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.

Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @03:49PM (#43177210)
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. For example, insert row -> check that customer ID unique -> reject if not. 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.

Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @03:52PM (#43177239)
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.
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @04:00PM (#43177326)
SQL schemas aren't Excel spreadsheets.
Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @04:08PM (#43177426)
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.
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @04:19PM (#43177561)
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.

Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @04:29PM (#43177688)
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".
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @04:49PM (#43177957)
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, not imperative. Those are types of programming languages, so software.

Re:I Went to SQL Injection Court by n_plus_1_acc on Tuesday February 25, 2025 @03:49PM (#43177217)
An Excel formula should be considerd a kind of software, because you cab do code golf in it.
Re:I Went to SQL Injection Court by pavon on Tuesday February 25, 2025 @04:54PM (#43178017)
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.

Re:I Went to SQL Injection Court by hot_gril on Wednesday February 26, 2025 @01:54AM (#43181365)
The schema defines how the DBMS sets up its tables and such, so it does quite a bit imo. And if the schema isn't stored in any doc cause just manually punched in CREATE TABLE once, yeah what you said about creating new docs.
Re:I Went to SQL Injection Court by paulddraper on Tuesday February 25, 2025 @02:30PM (#43176215)
How is a database schema not a file layout?
Re:I Went to SQL Injection Court by kasey_junk on Tuesday February 25, 2025 @02:33PM (#43176258)
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.

Re:I Went to SQL Injection Court by ludston on Tuesday February 25, 2025 @02:50PM (#43176430)
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.
Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @03:12PM (#43176736)
In a Microsoft Word document, the section headings also tell Word how to lay out the Word document file.
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @04:18PM (#43177545)
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.

Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @04:29PM (#43177690)
You have found an argument that proves too much.
Re:I Went to SQL Injection Court by Xelynega on Wednesday February 26, 2025 @01:16AM (#43181189)
Yea coupled with the courts arguments the interpretation of sections in a document as a "file format" means no files with sections can be released via FOIA requests
Re:I Went to SQL Injection Court by Xelynega on Wednesday February 26, 2025 @01:15AM (#43181184)
Does your interpretation not mean that(coupled with the court ruling that file formats can't be foia'd) any document with sections cannot be requested via FOIA?
Re:I Went to SQL Injection Court by hot_gril on Wednesday February 26, 2025 @01:49AM (#43181342)
If this format is reused across many files, they might want to give the contents of those docs in a different format from the original.
Re:I Went to SQL Injection Court by ludston on Tuesday February 25, 2025 @08:06PM (#43179502)
Arguably, all requests for files could be returned with all of the letters in the document but scrambled in a random order soas to obfuscate the file layout.
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @03:18PM (#43176822)
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.
Re:I Went to SQL Injection Court by chaps on Tuesday February 25, 2025 @03:19PM (#43176842)
The DBMS is almost definitely going to be mentioned in RFP or specification documentation. As it was in this lawsuit.
Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @03:19PM (#43176837)
So?
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @03:24PM (#43176897)
So if you have the schema and the DBMS, you probably know how data is arranged in the files ("files" in the filesystem sense).
Re:I Went to SQL Injection Court by chaps on Tuesday February 25, 2025 @03:41PM (#43177118)
Is your argument that government agencies should also withhold the names of filing cabinet manufacturers? :)
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @03:58PM (#43177306)
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.

Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @04:07PM (#43177416)
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.
Re:I Went to SQL Injection Court by hyperpape on Tuesday February 25, 2025 @03:32PM (#43176998)
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.

Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @04:01PM (#43177340)
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?
Re:I Went to SQL Injection Court by darkarmani on Wednesday February 26, 2025 @03:12PM (#43187687)
The gov't releasing the hardware and software licencing used in CANVAS already gives that away.
Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @02:36PM (#43176282)
Another way to think about it is that if a SQL schema is a file, so is an Excel spreadsheet template.
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @03:30PM (#43176963)
File or file layout? Cause both of these are probably stored as files, .sql and .xltx respectively.
Re:I Went to SQL Injection Court by paulddraper on Tuesday February 25, 2025 @03:53PM (#43177251)
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.
Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @04:09PM (#43177438)
I don't follow your point.
Re:I Went to SQL Injection Court by paulddraper on Tuesday February 25, 2025 @07:47PM (#43179375)
Yes, it's a file format.

(Kinda a file format inside a file format inside a file format.)

Re:I Went to SQL Injection Court by tptacek on Tuesday February 25, 2025 @08:13PM (#43179551)
"Excel" is a file format, but my point is that if a schema is a file format, so are the contents of an Excel spreadsheet.
Re:I Went to SQL Injection Court by atkulp on Tuesday February 25, 2025 @05:36PM (#43178463)
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.
Re:I Went to SQL Injection Court by hyperpape on Tuesday February 25, 2025 @03:09PM (#43176691)
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).
Re:I Went to SQL Injection Court by paulddraper on Tuesday February 25, 2025 @03:19PM (#43176843)
> 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.

Re:I Went to SQL Injection Court by kelnos on Tuesday February 25, 2025 @03:52PM (#43177247)
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.

Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @04:07PM (#43177415)
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 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.

Re:I Went to SQL Injection Court by hyperpape on Tuesday February 25, 2025 @03:28PM (#43176936)
> 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.

Re:I Went to SQL Injection Court by paulddraper on Tuesday February 25, 2025 @03:32PM (#43176989)
> it is a component of a schema

So if you have the schema, you have the tables.

Re:I Went to SQL Injection Court by dools on Tuesday February 25, 2025 @02:47PM (#43176410)
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.
Re:I Went to SQL Injection Court by michaelmrose on Tuesday February 25, 2025 @02:34PM (#43176266)
Because it doesn't describe how data is laid out on disk.
Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @02:55PM (#43176501)
Neither does a file layout. FS will decide that... even then, not physically.
Re:I Went to SQL Injection Court by kelnos on Tuesday February 25, 2025 @03:54PM (#43177268)
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.

Re:I Went to SQL Injection Court by hot_gril on Tuesday February 25, 2025 @04:36PM (#43177789)
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.

Re:I Went to SQL Injection Court by 0x457 on Tuesday February 25, 2025 @06:57PM (#43179046)
We can't FOIA details about how xls file laid out internally, despite that xls file being FOIA'ble itself. That's the file-format we're talking about.
Re:I Went to SQL Injection Court by gregw2 on Tuesday February 25, 2025 @04:53PM (#43178014)
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.