8 ms·

I don't understand the argument that knowing the column names doesn't help an attacker? Especially in a database that doesn't allow wildcards, doesn't it make t

I don't understand the argument that knowing the column names doesn't help an attacker? Especially in a database that doesn't allow wildcards, doesn't it make things much easier if you know you can do '); SELECT col FROM logins, as opposed to having to guess the column name? And I don't think I disagree with the court on schema vs. file layouts either. It's not the file layout, but it's analogous: it tells you how the "files" (records) are laid out on the "file system" (database tables). For example, denormalization is very analogous to inlining of data in a file record. The notion that filesystems are effectively databases itself is a well known one too. How do you argue they aren't analogous?

ic4l 2y ago
I agree with you. Knowing the exact column names can speed up an attack and, in some cases, make it more feasible. Why don’t they just request disclosure of what’s actually stored and allow renaming of the columns? It seems odd that knowing the exact column names would be necessary if the goal is simply to understand what data is being stored and its intended purpose.
- lIl-IIIl 2y ago
  I wonder if that would be considered a "new report", which they don't have to provide.
  - philipov 2y ago
    They can either have their cake or eat it. If they don't want to obfuscate the column names, they have to provide the data with the original ones.
  - deleted 2y ago
    [deleted]
- thaumasiotes 2y ago
  > Knowing the exact column names can speed up an attack and, in some cases, make it more feasible. If I'm looking at a database, I like knowing column names, but I like knowing table names more.
IshKebab 2y ago
'); SELECT * FROM logins --
- dataflow 2y ago
  This fails if either the UI sanitizes wildcards, or if the database prohibits them, or if it produces so much data that you can't ingest it in time, etc.
  - wglb 2y ago
    Sanitization almost always fails. This becomes an arms race.
    - valenterry 2y ago
      If you do it wrong, yes. Sure, there is no 100% security, but honestly, it's 2025. We already know the techniques how to prevent SQL injection of any kind. I wrote about this here: https://valentin.willscher.de/posts/sql-api/ https://valentin.willscher.de/posts/sql-api/
      - IshKebab 2y ago
        Right but the case that is being imagined here is a site that perfectly sanitises * but somehow still allows SQL injection? I don't think so.
        dataflow 2y ago
        > Right but the case that is being imagined here is a site that perfectly sanitises * but somehow still allows SQL injection? I don't think so. It could literally just reject anything with asterisks. It doesn't even need to do anything perfectly, it just needs to do it enough to produce hurdles for you. Like blowing through the number of attempts you realistically have remaining.
        2y ago
chaps 2y ago
The Department of Justice disagrees and voluntarily releases column and table names: https://www.justice.gov/afp/media/1186431/dl?inline= https://www.justice.gov/afp/media/1186431/dl?inline=
tczMUFlmoNk 2y ago
You can always `SELECT table_name, column_name, data_type FROM information_schema.columns`, which is part of the SQL standard. https://www.postgresql.org/docs/current/infoschema-columns.html https://www.postgresql.org/docs/current/infoschema-columns.h... Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot. And there's only so many combinations of `SELECT {id,userid,user_id,uid} FROM {user,users,login,logins,customer,customer}` before you find something useful.
- zachrip 2y ago
  That's a good point, has anyone hardened a database by locking out users who select columns that don't exist? Or run other dubious queries? This would obviously interrupt production but if someone is running queries on your db it's probably worth it?
  - Waterluvian 2y ago
    On the surface that’s a very attractive idea. A sort of “you shouldn’t be in here, even if we left the door unlocked.”
    - closeparen 2y ago
      So if you deploy code before you run the associated db migration, or misspell a column name, you magnify the impact from whichever code paths (& application tier nodes) are running the broken SQL, to your entire production environment.
      - zachrip 2y ago
        Yeah it's definitely something that could do more harm than good to a company long term. But I'm sure there are instances where this tradeoff is worth it. They would invest more heavily in runbooks or maybe even ci that runs migrations on deploy. Deleting columns would need to be done on your deploy + 1. Probably no rollback at all.
      - 2y ago
dmurray 2y ago
And this part seems self-defeating: > Attackers like me use SQL injection attacks to recover SQL schemas. The schema is the product of an attack, not one of its predicates”. If it's the product of an attack, but not the end goal, surely it's of value to the attacker? It seems clear to me that the statute does, as worded, in principle allow the city not to disclose the database schema - it would compromise the security of the system, or at the very least, it would for some systems, so each request needs to be litigated individually. The proposed amendment sounds like a good way to fix this - is it likely that will pass?
- lmm 2y ago
  > If it's the product of an attack, but not the end goal, surely it's of value to the attacker? Well sure, but it doesn't help them attack. That's like arguing that since the bank robber wants dollar bills, dollar bills must be a useful tool for breaking into bank vaults.
  - dmurray 2y ago
    If both sides agreed to the analogy of giving the bank robber the blueprints to the vault, I think any lay judge would agree that endangers the bank's security.
    - lmm 2y ago
      I'd say it's more like knowing the layout of the drawers inside the cage. If a robber is inside the cage, they've already won. And if an auditor is checking the bank has what it says it does, they've got legitimate grounds to ask which money is in which drawer, and "no, it's a security risk" is not a good answer.
- tptacek 2y ago
  Lots of things are "of value". That's not the bar the statute sets. To the extent something isn't per se exempted by the statute (as the outcome of the case established schemas are), the burden is on the public body to demonstrate that disclosure Would jeopardize the security of the system.
AdamJacobMuller 2y ago
> And I don't think I disagree with the court on schema vs. file layouts either. I disagree that the law should prohibit disclosing "file layouts" but it's pretty clear that the law does block that, and I fundamentally agree with you that schemas are directly analogous to file layouts and thus restricted.
- tptacek 2y ago
  A SQL schema literally does not indicate the locations of data inside of a file. In fact, the whole reason schemas exist is to decouple the relationships between table rows and the pages and indexes that store that data. We had relational databases before SQL, and there are non-SQL relational (and non-relational) databases today, but you program them, at the query level, with code that is aware of what tables live where. A schema is the opposite of a file layout. A schema is to a file layout what a Google search is to an IP address.
  - HDThoreaun 2y ago
    I dont think "file layout" has to mean the exact location of every byte. An abstract file layout is still a file layout.
    - tptacek 2y ago
      It is in literally no sense a layout; the whole point of a schema is that it doesn't tie you down to a layout. SQL schemas make sense even in the absence of files!
      - maratc 2y ago
        You suggest that we interpret "file formats" as exactly this -- no more, no less. This approach is also called "textualism". The other option is to interpret "file formats" in the context of the law that includes these words. Or: what exactly did the lawmakers have in mind when they said that (a) government needs to provide information; (b) except for several cases, of which one is (c) "file formats". What kind of information did they think it was ok for the government not to provide? I agree with the Court's argument that "the information about how the actual information is stored and connected one piece to another" is what the lawmakers meant in this case. - If the actual information is stored in the files, the government does not need to disclose how these files are organized ("file formats"). - If the actual information is stored in the database, the government does not need to disclose how the database is organized (database schema). - If the actual information is stored in the block memory -- with structs and pointers -- the government does not need to disclose the structs and the pointers. The "textualist" opponent would of course argue, as OP did, that the second and the third example aren't excepted by clause (c) because "when there is no file, there could be no file format". This however is missing the point (in my opinion), as it doesn't see the forest for the trees.
mcv 2y ago
Yeah, I think it's still useful info for an attacker. But only if the system was actually developed by amateurs who never heard of parameterized queries. I find it a bit bizarre that the city uses "our system was developed with no consideration for security" as a valid defense.
gwd 2y ago
> I don't understand the argument that knowing the column names doesn't help an attacker? So Kevin Mitnick supposedly did most of his hacking using "social engineering". He'd call up some person, pretend to be in some other department within their organization, and ask them for some specific bit of information he needed to further his attack (or ask them to change some specific thing that would allow him to further his attack). Would knowing the structure of Illinois governmental organizations help someone perform social engineering attacks against them? Yes, absolutely. Should Illinois therefore keep the internal structures of their organizations -- the department names and the officials who run them -- secret? No, absolutely not. First of all, if an attacker doesn't know them, they'll just use other social engineering attacks to figure them out; i.e., hiding the structure doesn't stop social engineering attacks, it just slows them down. Secondly, the value to the public of being able to navigate governmental structures far outweighs the cost of potential attacks. This seems to me to be a direct analog: The "organizational structure" is the "database schema", and the "willingness to help a random person on the phone who seems to know what they're talking about" is the "SQL injection vulnerability". If an attacker knows the schema, their job is faster; but if they don't know the schema, they'll just use attacks to figure out the schema; so keeping it private doesn't stop an attack, only slow it down. And the benefit to the public of being able to issue FOIA requests far outweighs the cost of potential attacks.
econ 2y ago
If you have an injection friendly application then that is the security problem. Say someone hacks the db, is the problem easy to guess table names? The column should never have be called "passwords"? Perhaps 30 years ago that would sound good. Obscurity should hardly ever be a line of defense. If it is the only defense the problem isn't that it wasn't obscure enough. Edit: I'll do you one better. If you so much as suggest that obscurity is good security you actually openly invite people to fool around with your applications. The odds holes are to be found are much better than elsewhere.
- HDThoreaun 2y ago
  What do you do when you know you've got a pile of poorly written insecure software and no money to improve it?
  - econ 2y ago
    I probably delete everything and pretend it never happened. It depends ofc on the worse case scenario. What can i do/afford to deal with the greatest risk? I might use it on a machine without internet.
fsckboy 2y ago
>It's not the file layout, but it's analogous...How do you argue they aren't analogous? laws don't get to be analogous foia request: "I'd like the report the committee prepared about the costs for the new bridge" response: "denied. the report contains costs laid out in tables with headings, which while not being schemas are analogous, with schemas not being files but being analogous"

ic4l 2y ago

I agree with you. Knowing the exact column names can speed up an attack and, in some cases, make it more feasible. Why don’t they just request disclosure of what’s actually stored and allow renaming of the columns? It seems odd that knowing the exact column names would be necessary if the goal is simply to understand what data is being stored and its intended purpose.

lIl-IIIl 2y ago
I wonder if that would be considered a "new report", which they don't have to provide.
- philipov 2y ago
  They can either have their cake or eat it. If they don't want to obfuscate the column names, they have to provide the data with the original ones.
- deleted 2y ago
  [deleted]
thaumasiotes 2y ago
> Knowing the exact column names can speed up an attack and, in some cases, make it more feasible. If I'm looking at a database, I like knowing column names, but I like knowing table names more.

IshKebab 2y ago

'); SELECT * FROM logins --

dataflow 2y ago
This fails if either the UI sanitizes wildcards, or if the database prohibits them, or if it produces so much data that you can't ingest it in time, etc.
- wglb 2y ago
  Sanitization almost always fails. This becomes an arms race.
  - valenterry 2y ago
    If you do it wrong, yes. Sure, there is no 100% security, but honestly, it's 2025. We already know the techniques how to prevent SQL injection of any kind. I wrote about this here: https://valentin.willscher.de/posts/sql-api/ https://valentin.willscher.de/posts/sql-api/
    - IshKebab 2y ago
      Right but the case that is being imagined here is a site that perfectly sanitises * but somehow still allows SQL injection? I don't think so.
      - dataflow 2y ago
        > Right but the case that is being imagined here is a site that perfectly sanitises * but somehow still allows SQL injection? I don't think so. It could literally just reject anything with asterisks. It doesn't even need to do anything perfectly, it just needs to do it enough to produce hurdles for you. Like blowing through the number of attempts you realistically have remaining.
        2y ago

chaps 2y ago

The Department of Justice disagrees and voluntarily releases column and table names: https://www.justice.gov/afp/media/1186431/dl?inline= https://www.justice.gov/afp/media/1186431/dl?inline=

tczMUFlmoNk 2y ago

You can always `SELECT table_name, column_name, data_type FROM information_schema.columns`, which is part of the SQL standard. https://www.postgresql.org/docs/current/infoschema-columns.html https://www.postgresql.org/docs/current/infoschema-columns.h... Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot. And there's only so many combinations of `SELECT {id,userid,user_id,uid} FROM {user,users,login,logins,customer,customer}` before you find something useful.

zachrip 2y ago
That's a good point, has anyone hardened a database by locking out users who select columns that don't exist? Or run other dubious queries? This would obviously interrupt production but if someone is running queries on your db it's probably worth it?
- Waterluvian 2y ago
  On the surface that’s a very attractive idea. A sort of “you shouldn’t be in here, even if we left the door unlocked.”
  - closeparen 2y ago
    So if you deploy code before you run the associated db migration, or misspell a column name, you magnify the impact from whichever code paths (& application tier nodes) are running the broken SQL, to your entire production environment.
    - zachrip 2y ago
      Yeah it's definitely something that could do more harm than good to a company long term. But I'm sure there are instances where this tradeoff is worth it. They would invest more heavily in runbooks or maybe even ci that runs migrations on deploy. Deleting columns would need to be done on your deploy + 1. Probably no rollback at all.
    - 2y ago

dmurray 2y ago

And this part seems self-defeating: > Attackers like me use SQL injection attacks to recover SQL schemas. The schema is the product of an attack, not one of its predicates”. If it's the product of an attack, but not the end goal, surely it's of value to the attacker? It seems clear to me that the statute does, as worded, in principle allow the city not to disclose the database schema - it would compromise the security of the system, or at the very least, it would for some systems, so each request needs to be litigated individually. The proposed amendment sounds like a good way to fix this - is it likely that will pass?

lmm 2y ago
> If it's the product of an attack, but not the end goal, surely it's of value to the attacker? Well sure, but it doesn't help them attack. That's like arguing that since the bank robber wants dollar bills, dollar bills must be a useful tool for breaking into bank vaults.
- dmurray 2y ago
  If both sides agreed to the analogy of giving the bank robber the blueprints to the vault, I think any lay judge would agree that endangers the bank's security.
  - lmm 2y ago
    I'd say it's more like knowing the layout of the drawers inside the cage. If a robber is inside the cage, they've already won. And if an auditor is checking the bank has what it says it does, they've got legitimate grounds to ask which money is in which drawer, and "no, it's a security risk" is not a good answer.
tptacek 2y ago
Lots of things are "of value". That's not the bar the statute sets. To the extent something isn't per se exempted by the statute (as the outcome of the case established schemas are), the burden is on the public body to demonstrate that disclosure Would jeopardize the security of the system.

AdamJacobMuller 2y ago

> And I don't think I disagree with the court on schema vs. file layouts either. I disagree that the law should prohibit disclosing "file layouts" but it's pretty clear that the law does block that, and I fundamentally agree with you that schemas are directly analogous to file layouts and thus restricted.

tptacek 2y ago
A SQL schema literally does not indicate the locations of data inside of a file. In fact, the whole reason schemas exist is to decouple the relationships between table rows and the pages and indexes that store that data. We had relational databases before SQL, and there are non-SQL relational (and non-relational) databases today, but you program them, at the query level, with code that is aware of what tables live where. A schema is the opposite of a file layout. A schema is to a file layout what a Google search is to an IP address.
- HDThoreaun 2y ago
  I dont think "file layout" has to mean the exact location of every byte. An abstract file layout is still a file layout.
  - tptacek 2y ago
    It is in literally no sense a layout; the whole point of a schema is that it doesn't tie you down to a layout. SQL schemas make sense even in the absence of files!
    - maratc 2y ago
      You suggest that we interpret "file formats" as exactly this -- no more, no less. This approach is also called "textualism". The other option is to interpret "file formats" in the context of the law that includes these words. Or: what exactly did the lawmakers have in mind when they said that (a) government needs to provide information; (b) except for several cases, of which one is (c) "file formats". What kind of information did they think it was ok for the government not to provide? I agree with the Court's argument that "the information about how the actual information is stored and connected one piece to another" is what the lawmakers meant in this case. - If the actual information is stored in the files, the government does not need to disclose how these files are organized ("file formats"). - If the actual information is stored in the database, the government does not need to disclose how the database is organized (database schema). - If the actual information is stored in the block memory -- with structs and pointers -- the government does not need to disclose the structs and the pointers. The "textualist" opponent would of course argue, as OP did, that the second and the third example aren't excepted by clause (c) because "when there is no file, there could be no file format". This however is missing the point (in my opinion), as it doesn't see the forest for the trees.

mcv 2y ago

Yeah, I think it's still useful info for an attacker. But only if the system was actually developed by amateurs who never heard of parameterized queries. I find it a bit bizarre that the city uses "our system was developed with no consideration for security" as a valid defense.

gwd 2y ago

> I don't understand the argument that knowing the column names doesn't help an attacker? So Kevin Mitnick supposedly did most of his hacking using "social engineering". He'd call up some person, pretend to be in some other department within their organization, and ask them for some specific bit of information he needed to further his attack (or ask them to change some specific thing that would allow him to further his attack). Would knowing the structure of Illinois governmental organizations help someone perform social engineering attacks against them? Yes, absolutely. Should Illinois therefore keep the internal structures of their organizations -- the department names and the officials who run them -- secret? No, absolutely not. First of all, if an attacker doesn't know them, they'll just use other social engineering attacks to figure them out; i.e., hiding the structure doesn't stop social engineering attacks, it just slows them down. Secondly, the value to the public of being able to navigate governmental structures far outweighs the cost of potential attacks. This seems to me to be a direct analog: The "organizational structure" is the "database schema", and the "willingness to help a random person on the phone who seems to know what they're talking about" is the "SQL injection vulnerability". If an attacker knows the schema, their job is faster; but if they don't know the schema, they'll just use attacks to figure out the schema; so keeping it private doesn't stop an attack, only slow it down. And the benefit to the public of being able to issue FOIA requests far outweighs the cost of potential attacks.

econ 2y ago

If you have an injection friendly application then that is the security problem. Say someone hacks the db, is the problem easy to guess table names? The column should never have be called "passwords"? Perhaps 30 years ago that would sound good. Obscurity should hardly ever be a line of defense. If it is the only defense the problem isn't that it wasn't obscure enough. Edit: I'll do you one better. If you so much as suggest that obscurity is good security you actually openly invite people to fool around with your applications. The odds holes are to be found are much better than elsewhere.

HDThoreaun 2y ago
What do you do when you know you've got a pile of poorly written insecure software and no money to improve it?
- econ 2y ago
  I probably delete everything and pretend it never happened. It depends ofc on the worse case scenario. What can i do/afford to deal with the greatest risk? I might use it on a machine without internet.

fsckboy 2y ago

>It's not the file layout, but it's analogous...How do you argue they aren't analogous? laws don't get to be analogous foia request: "I'd like the report the committee prepared about the costs for the new bridge" response: "denied. the report contains costs laid out in tables with headings, which while not being schemas are analogous, with schemas not being files but being analogous"