7 ms·

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/c

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.
    - snickell 2y ago
      Simple variation to a hard shutoff: immediately page "significant risk a successful sql exploit was found", and then slow down attackers: If an SQL query requests an unknown table, log the error, but have that query time out instead of responding with an error. Or, even better, the offending query appears to succeed, but returns fake table data, turning it into a honeypot built-in to the DB. This could be done at the application layer, or in the DB. The goal is to buy an hour for defenders to determine how to respond, or if its a red herring. There are a variety of ways of doing this without significant user impact.
- count 2y ago
  Zane Lackey (with Dan Kaminsky) gave a talk that discussed doing literally that sort of things, back in 2013. Zane went on to found Signal Sciences (acquired by Fastly), doing this sort of stuff in the 'WAF' space. https://youtu.be/jQblKuMuS0Y?t=866 https://youtu.be/jQblKuMuS0Y?t=866 (timestamp is when Zane starts talking about it)
  - hunter2_ 2y ago
    I guess the main difference is that a WAF attempts to spot things like injection (unbalanced delimiters, SQL keywords in HTTP payloads where SQL shouldn't exist, etc.) typically without knowledge of the schema, whereas GP is talking about the DBMS spotting queries where queries must exist but disagree with the schema. Might as well do both, I suppose.
    - count 2y ago
      That’s not what the talk is about - it’s using dbms query error logs to spot attackers. Stuff like “table doesn’t exist” or “invalid syntax” on your production database can be extremely high signal indications that something is wrong, potentially maliciously so.
- brk 2y ago
  In the very early 2000’s I worked at a company building something along those lines. We could analyze SQL and SMB traffic on the fly and spot anomalous access to tables/columns/files, etc. Dynamic firewalling would have been the next progression if the company didn’t have other issues.
- wglb 2y ago
  I once did an security assessment for a product such as what you describe. Among other problems with it, the product itself had SQL injection vulnerabilities For another example of what defenders are up against, see https://users.ece.cmu.edu/~adrian/731-sp04/readings/Ptacek-Newsham-ids98.pdf https://users.ece.cmu.edu/~adrian/731-sp04/readings/Ptacek-N.... This paper all but caused an upheaval in the WAF industry.
- whstl 2y ago
  WAFs help with this, but at the HTTP level. By putting “information_schema”, “sys.tables” in the filters. Not the real solution, IMO, but WAFs are useful for more than SQLi, and is the kind of tech you can ask money for.
- brohee 2y ago
  If you are mature enough to do that, you're mature enough to net SQL injections in the first place. There shouldn't be that many handwritten queries to review in the first place as most mundane DB access is usually through a framework that handles injection properly...
  - zachrip 2y ago
    I disagree, if all it took was maturity then we wouldn't see giant data breaches of the largest companies in the world weekly.
default-kramer 2y ago
A good DBA would restrict the account so that it can't access the information schema. It's easy to imagine an environment with a vigilant DBA and less vigilant web developers.
- IanGabes 2y ago
  This makes sense, but the the vast majority of tooling including ORMs, autocomplete SQL IDEs, and even suspect application code relies on table descriptions and listings provided by the information schema
  - acka 2y ago
    That is why we have development and production environments. The production environment is expected to operate in a potentially hostile space and does not need developer conveniences beyond the ability to generate alerts and produce logs, which should be stored in a safe way, everything else should be locked down as much as possible.
  - mrgoldenbrown 2y ago
    My ide logging into my local dev copy of the DB and my public facing prod application should not be using the same SQL login.
HDThoreaun 2y ago
Being able to inject doesnt mean you get the output of a select. The inject can be on non-select statements.
dataflow 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.h https://www.postgresql.org/docs/current/infoschema-columns.h. You can "always" do that? Well I just did that. My database said: no such table: information_schema.columns And what if my database had disabled this capability entirely? Also, is there anything implying SQL here at all? Can't other databases with injection "capability" have schemas? > Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot. No, you can't say it with such certainty at all. It really depends on what else you're triggering in the process of that SQL injection. You could easily be triggering something (like a password reset, a payment transaction...) where you're severely limited in your attempts. > And there's only so many combinations of `SELECT {id,userid,user_id,uid} FROM {user,users,login,logins,customer,customer}` before you find something useful. account, accounts, password, passwords, profile, profiles, credential, credentials, auth, auths, authentication, authentications, authentication_info, authentication_infos, authorization, authorizations, passwd, passwds, user_info, user_infos, login_info, login_infos, account_info, account_infos... should I keep going? And these are just the logins/passwords; what if the information of interest was something else, like parking tickets?
- lyu07282 2y ago
  > You can "always" do that? Well I just did that. My database said: no such table: information_schema.columns Don't expect attackers to give up after one try. It depends on the database software, not everyone implements this exact ANSI standard for reflection but every database supports reflection. That's why the first step after finding a SQLi is to fingerprint the database software and go from there. > And what if my database had disabled this capability entirely? You can't disable it, lots of software, database features, ORMs and clients rely on reflection. If a client can query a table they also can retrieve metadata about that table.
  - spoaceman7777 2y ago
    You can definitely disable it, in a variety of ways, for whatever role, user, etc. you wish to.
aftbit 2y ago
Ah so what you're saying is that we ought to rename our logins table to "duckwords" because nobody will ever guess that? Also we should probably store passwords in plaintext but name the column "entercod3" because nobody will think of that. Oh and we should use printf with %s to build our queries right?
deleted 2y ago
[deleted]

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.
  - snickell 2y ago
    Simple variation to a hard shutoff: immediately page "significant risk a successful sql exploit was found", and then slow down attackers: If an SQL query requests an unknown table, log the error, but have that query time out instead of responding with an error. Or, even better, the offending query appears to succeed, but returns fake table data, turning it into a honeypot built-in to the DB. This could be done at the application layer, or in the DB. The goal is to buy an hour for defenders to determine how to respond, or if its a red herring. There are a variety of ways of doing this without significant user impact.
count 2y ago
Zane Lackey (with Dan Kaminsky) gave a talk that discussed doing literally that sort of things, back in 2013. Zane went on to found Signal Sciences (acquired by Fastly), doing this sort of stuff in the 'WAF' space. https://youtu.be/jQblKuMuS0Y?t=866 https://youtu.be/jQblKuMuS0Y?t=866 (timestamp is when Zane starts talking about it)
- hunter2_ 2y ago
  I guess the main difference is that a WAF attempts to spot things like injection (unbalanced delimiters, SQL keywords in HTTP payloads where SQL shouldn't exist, etc.) typically without knowledge of the schema, whereas GP is talking about the DBMS spotting queries where queries must exist but disagree with the schema. Might as well do both, I suppose.
  - count 2y ago
    That’s not what the talk is about - it’s using dbms query error logs to spot attackers. Stuff like “table doesn’t exist” or “invalid syntax” on your production database can be extremely high signal indications that something is wrong, potentially maliciously so.
brk 2y ago
In the very early 2000’s I worked at a company building something along those lines. We could analyze SQL and SMB traffic on the fly and spot anomalous access to tables/columns/files, etc. Dynamic firewalling would have been the next progression if the company didn’t have other issues.
wglb 2y ago
I once did an security assessment for a product such as what you describe. Among other problems with it, the product itself had SQL injection vulnerabilities For another example of what defenders are up against, see https://users.ece.cmu.edu/~adrian/731-sp04/readings/Ptacek-Newsham-ids98.pdf https://users.ece.cmu.edu/~adrian/731-sp04/readings/Ptacek-N.... This paper all but caused an upheaval in the WAF industry.
whstl 2y ago
WAFs help with this, but at the HTTP level. By putting “information_schema”, “sys.tables” in the filters. Not the real solution, IMO, but WAFs are useful for more than SQLi, and is the kind of tech you can ask money for.
brohee 2y ago
If you are mature enough to do that, you're mature enough to net SQL injections in the first place. There shouldn't be that many handwritten queries to review in the first place as most mundane DB access is usually through a framework that handles injection properly...
- zachrip 2y ago
  I disagree, if all it took was maturity then we wouldn't see giant data breaches of the largest companies in the world weekly.

default-kramer 2y ago

A good DBA would restrict the account so that it can't access the information schema. It's easy to imagine an environment with a vigilant DBA and less vigilant web developers.

IanGabes 2y ago
This makes sense, but the the vast majority of tooling including ORMs, autocomplete SQL IDEs, and even suspect application code relies on table descriptions and listings provided by the information schema
- acka 2y ago
  That is why we have development and production environments. The production environment is expected to operate in a potentially hostile space and does not need developer conveniences beyond the ability to generate alerts and produce logs, which should be stored in a safe way, everything else should be locked down as much as possible.
- mrgoldenbrown 2y ago
  My ide logging into my local dev copy of the DB and my public facing prod application should not be using the same SQL login.

HDThoreaun 2y ago

Being able to inject doesnt mean you get the output of a select. The inject can be on non-select statements.

dataflow 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.h https://www.postgresql.org/docs/current/infoschema-columns.h. You can "always" do that? Well I just did that. My database said: no such table: information_schema.columns And what if my database had disabled this capability entirely? Also, is there anything implying SQL here at all? Can't other databases with injection "capability" have schemas? > Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot. No, you can't say it with such certainty at all. It really depends on what else you're triggering in the process of that SQL injection. You could easily be triggering something (like a password reset, a payment transaction...) where you're severely limited in your attempts. > And there's only so many combinations of `SELECT {id,userid,user_id,uid} FROM {user,users,login,logins,customer,customer}` before you find something useful. account, accounts, password, passwords, profile, profiles, credential, credentials, auth, auths, authentication, authentications, authentication_info, authentication_infos, authorization, authorizations, passwd, passwds, user_info, user_infos, login_info, login_infos, account_info, account_infos... should I keep going? And these are just the logins/passwords; what if the information of interest was something else, like parking tickets?

lyu07282 2y ago
> You can "always" do that? Well I just did that. My database said: no such table: information_schema.columns Don't expect attackers to give up after one try. It depends on the database software, not everyone implements this exact ANSI standard for reflection but every database supports reflection. That's why the first step after finding a SQLi is to fingerprint the database software and go from there. > And what if my database had disabled this capability entirely? You can't disable it, lots of software, database features, ORMs and clients rely on reflection. If a client can query a table they also can retrieve metadata about that table.
- spoaceman7777 2y ago
  You can definitely disable it, in a variety of ways, for whatever role, user, etc. you wish to.

aftbit 2y ago

Ah so what you're saying is that we ought to rename our logins table to "duckwords" because nobody will ever guess that? Also we should probably store passwords in plaintext but name the column "entercod3" because nobody will think of that. Oh and we should use printf with %s to build our queries right?

deleted 2y ago

[deleted]