6 ms·

SQL injection draws attention to the fact that almost all web applications run as root, in regards to the most important asset: the data. It’s odd that this ha

SQL injection draws attention to the fact that almost all web applications run as root, in regards to the most important asset: the data. It’s odd that this hasn’t got more attention. It should be easier to write backends that tie data access more closely to user credentials without the backend trying to enforce that itself. Is there anything out there that makes this easy to do?

Hendrikto 5y ago
> almost all web applications run as root That‘s largely a configuration problem. They don‘t have to run as root, most of the time.
- eloff 5y ago
  With regards to the database. Not UNIX root.
- eliaspro 5y ago
  Should there ever be a web application that needs to run as root, it should be rewritten. All logic requiring root privileges should be moved into a separate system service with a minimal attack surface to which the web application delegates those special tasks.
  - vntok 5y ago
    You misunderstood the grandparent's comment and replied to a confused parent. The application does not need to run as root, however even when it runs as a normal user, it generally has full read/write access to the application database. Which is a problem that is not easily solvable.
- raxxorrax 5y ago
  I think you need root permissions to start a network service on port 80 on Windows. There is a security risk here which was addressed in a fairly recent Chrome release that local addresses need a respective CORS rule. At least I believe so. Disadvantage with the Windows behavior is that a webservice running on said port has elevated permissions and if you surf the web with the same machine, a script inserted into a site could launch attacks against it. Of course the site itself could be malicious, in that case the CORS rule wouldn't do much. Of course you are correct, running directly on port 80 might just be a bad idea, but for development purposes I might do that.
  - Vogtinator 5y ago
    Ports < 1024 are defined as "privileged", so it's not just a Windows thing. What usually happens is that the process only has the necessary capability for a brief period to open the port and drops it immediately before processing any requests.
- iso1631 5y ago
  It was poorly phrased, but the OP meant that web apps usually have full unrestricted access to the database. Clearly that's not entirely true (your webapp likely doesn't need truncate, or the ability to add/change stored procedures, or the schema) but it's broadly there. Your database is effectively chmod o+r
kenniskrag 5y ago
There is row level security. But that usually means many db users or sometimes usergroups are supported. https://www.postgresql.org/docs/current/ddl-rowsecurity.html https://www.postgresql.org/docs/current/ddl-rowsecurity.html
tremon 5y ago
There isn't, at least not really, since webapps are designed as full-service kiosks: all of the data management, user management and access control are supposed to be handled from the same console. If you want the database to enforce access controls, then every user must exist as a separate entity in the database, which is good, but then the webapp also needs the permissions to manage user accounts in the database, which is bad for security, and the webapp can no longer use connection pooling, which is bad for performance. There are tricks you can use, such as implementing row-level security and tying it to some concept of end user identity, but even ignoring that ORM's don't understand or support RLS and developers therefore won't use it: it's still the same problem of letting the fox guard the henhouse. The webapp is the single control point for both user access and user administration (including self-service account creation and password reset), so whatever solution you come up with, the webapp will need to have super-user access to do that, and therefore if the webapp is compromised, your data-level access controls can be compromised too. So, in order to even begin thinking about securing your data, the webapp should probably be split into multiple reduced-access microservices that handle different aspects of the webapp function. As long as we're talking about a single monolithic backend, any attempt at scope mitigation can (and likely will) be defeated.
- konha 5y ago
  There are some easy wins most applications could use but don‘t most of the time. Like having sensible permissions for your app-db-user. Running migrations in a separate process? No need to have permission to do schema changes. Events are only written to the db and not queried/viewed from the app itself? Grant only INSERT rights to the table etc.
- formerly_proven 5y ago
  It's not that we don't know how to build things securely, it's just that we routinely ignore even basic principles of security 'cause it's easier. Virtually every webapp violates complete mediation and so it's just unreasonable to expect security. Similarly, most developers don't care for security and even less about principles and system design. Again, it's unreasonable to expect secure outcomes from that.
friendzis 5y ago
If your database supports session wide environment variables you can attach user/group/permissions object to the session and have your queries/functions/procedures check that. If your database supports writable views (e.g. via triggers) you can build parametric views. Both are not plug-and-play modules in your framework and requires actual DBA, though.
cm2187 5y ago
I always wondered, how do they exfiltrate the data? The website isn’t designed to display any schema. Do they save it as file from the SQL server? Can’t that be locked down? The attacker would still be able to delete but at least not extract.
- bflesch 5y ago
  If you're able to inject SQL, and the return value of the SQL query is not directly displayed to the user, you may use a timing-based side channel to exfiltrate data. E.g. in order to exfiltrate the string "Test123" you would go character-by-character, starting with the first character "T". For each ASCII character you would wait 10ms, as "T" is ASCII #84 [1] you'd sleep() for 84*10=840ms. This sleep() can be measured from the attacker side because the SQL query will block the HTTP response. This way, without "seeing" a result, the attacker is able to return data. [1] https://en.m.wikipedia.org/wiki/File:ASCII-Table-wide.svg https://en.m.wikipedia.org/wiki/File:ASCII-Table-wide.svg
  - Ginden 5y ago
    We're there any actual timings attacks executed like this? I'm talking about stack "attacker => internet => reverse-proxy => app => database", not extracting data over LAN from exposed app.
    - bflesch 5y ago
      Yes, that's one way to extract data by (ab)using a blind sql injection vulnerability. If you look at sqlmap [1], they offer two techniques for blind sql injection: boolean-based and time-based. Boolean-based should be used when the app just returns an error page (or not) based on your sql injection. The time-based approach should be used when no error page appears but the SQL is still executed. But when I look at sqlmap docs for the time-based approach [2] I think I got the initial explanation wrong. It will do a 5 second delay if a certain condition is met, e.g. "Is the first character of the value an 'T'? If yes, wait 5 seconds; if not, return immediately". And then send hundreds of requests in parallel to iterate over all positions & possible characters. [1] https://github.com/sqlmapproject/sqlmap/wiki/Usage#sql-injection-techniques-to-test-for https://github.com/sqlmapproject/sqlmap/wiki/Usage#sql-injec... [2] https://github.com/sqlmapproject/sqlmap/wiki/Usage#seconds-to-delay-the-dbms-response-for-time-based-blind-sql-injection https://github.com/sqlmapproject/sqlmap/wiki/Usage#seconds-t...
ozim 5y ago
SQL injections for me are solved problem. Frameworks and ORMs are making it easier on back-ends to tie data access to user credentials. The same with multi-tenancy if you use good framework with ORM you have all the tools to do filtering on higher level of abstraction. The same with referential consistency on database if it is there - ORM will help you to load data that is tied to that account. Making user like web app users on database level would make all development/ops really costly. If someone is writing SQL queries directly, he has to have a good reason for it. Like if it is plugin for Wordpress it probably is not that easy to use ORM.

Hendrikto 5y ago

> almost all web applications run as root That‘s largely a configuration problem. They don‘t have to run as root, most of the time.

eloff 5y ago
With regards to the database. Not UNIX root.
eliaspro 5y ago
Should there ever be a web application that needs to run as root, it should be rewritten. All logic requiring root privileges should be moved into a separate system service with a minimal attack surface to which the web application delegates those special tasks.
- vntok 5y ago
  You misunderstood the grandparent's comment and replied to a confused parent. The application does not need to run as root, however even when it runs as a normal user, it generally has full read/write access to the application database. Which is a problem that is not easily solvable.
raxxorrax 5y ago
I think you need root permissions to start a network service on port 80 on Windows. There is a security risk here which was addressed in a fairly recent Chrome release that local addresses need a respective CORS rule. At least I believe so. Disadvantage with the Windows behavior is that a webservice running on said port has elevated permissions and if you surf the web with the same machine, a script inserted into a site could launch attacks against it. Of course the site itself could be malicious, in that case the CORS rule wouldn't do much. Of course you are correct, running directly on port 80 might just be a bad idea, but for development purposes I might do that.
- Vogtinator 5y ago
  Ports < 1024 are defined as "privileged", so it's not just a Windows thing. What usually happens is that the process only has the necessary capability for a brief period to open the port and drops it immediately before processing any requests.
iso1631 5y ago
It was poorly phrased, but the OP meant that web apps usually have full unrestricted access to the database. Clearly that's not entirely true (your webapp likely doesn't need truncate, or the ability to add/change stored procedures, or the schema) but it's broadly there. Your database is effectively chmod o+r

kenniskrag 5y ago

There is row level security. But that usually means many db users or sometimes usergroups are supported. https://www.postgresql.org/docs/current/ddl-rowsecurity.html https://www.postgresql.org/docs/current/ddl-rowsecurity.html

tremon 5y ago

There isn't, at least not really, since webapps are designed as full-service kiosks: all of the data management, user management and access control are supposed to be handled from the same console. If you want the database to enforce access controls, then every user must exist as a separate entity in the database, which is good, but then the webapp also needs the permissions to manage user accounts in the database, which is bad for security, and the webapp can no longer use connection pooling, which is bad for performance. There are tricks you can use, such as implementing row-level security and tying it to some concept of end user identity, but even ignoring that ORM's don't understand or support RLS and developers therefore won't use it: it's still the same problem of letting the fox guard the henhouse. The webapp is the single control point for both user access and user administration (including self-service account creation and password reset), so whatever solution you come up with, the webapp will need to have super-user access to do that, and therefore if the webapp is compromised, your data-level access controls can be compromised too. So, in order to even begin thinking about securing your data, the webapp should probably be split into multiple reduced-access microservices that handle different aspects of the webapp function. As long as we're talking about a single monolithic backend, any attempt at scope mitigation can (and likely will) be defeated.

konha 5y ago
There are some easy wins most applications could use but don‘t most of the time. Like having sensible permissions for your app-db-user. Running migrations in a separate process? No need to have permission to do schema changes. Events are only written to the db and not queried/viewed from the app itself? Grant only INSERT rights to the table etc.
formerly_proven 5y ago
It's not that we don't know how to build things securely, it's just that we routinely ignore even basic principles of security 'cause it's easier. Virtually every webapp violates complete mediation and so it's just unreasonable to expect security. Similarly, most developers don't care for security and even less about principles and system design. Again, it's unreasonable to expect secure outcomes from that.

friendzis 5y ago

If your database supports session wide environment variables you can attach user/group/permissions object to the session and have your queries/functions/procedures check that. If your database supports writable views (e.g. via triggers) you can build parametric views. Both are not plug-and-play modules in your framework and requires actual DBA, though.

cm2187 5y ago

I always wondered, how do they exfiltrate the data? The website isn’t designed to display any schema. Do they save it as file from the SQL server? Can’t that be locked down? The attacker would still be able to delete but at least not extract.

bflesch 5y ago
If you're able to inject SQL, and the return value of the SQL query is not directly displayed to the user, you may use a timing-based side channel to exfiltrate data. E.g. in order to exfiltrate the string "Test123" you would go character-by-character, starting with the first character "T". For each ASCII character you would wait 10ms, as "T" is ASCII #84 [1] you'd sleep() for 84*10=840ms. This sleep() can be measured from the attacker side because the SQL query will block the HTTP response. This way, without "seeing" a result, the attacker is able to return data. [1] https://en.m.wikipedia.org/wiki/File:ASCII-Table-wide.svg https://en.m.wikipedia.org/wiki/File:ASCII-Table-wide.svg
- Ginden 5y ago
  We're there any actual timings attacks executed like this? I'm talking about stack "attacker => internet => reverse-proxy => app => database", not extracting data over LAN from exposed app.
  - bflesch 5y ago
    Yes, that's one way to extract data by (ab)using a blind sql injection vulnerability. If you look at sqlmap [1], they offer two techniques for blind sql injection: boolean-based and time-based. Boolean-based should be used when the app just returns an error page (or not) based on your sql injection. The time-based approach should be used when no error page appears but the SQL is still executed. But when I look at sqlmap docs for the time-based approach [2] I think I got the initial explanation wrong. It will do a 5 second delay if a certain condition is met, e.g. "Is the first character of the value an 'T'? If yes, wait 5 seconds; if not, return immediately". And then send hundreds of requests in parallel to iterate over all positions & possible characters. [1] https://github.com/sqlmapproject/sqlmap/wiki/Usage#sql-injection-techniques-to-test-for https://github.com/sqlmapproject/sqlmap/wiki/Usage#sql-injec... [2] https://github.com/sqlmapproject/sqlmap/wiki/Usage#seconds-to-delay-the-dbms-response-for-time-based-blind-sql-injection https://github.com/sqlmapproject/sqlmap/wiki/Usage#seconds-t...

ozim 5y ago

SQL injections for me are solved problem. Frameworks and ORMs are making it easier on back-ends to tie data access to user credentials. The same with multi-tenancy if you use good framework with ORM you have all the tools to do filtering on higher level of abstraction. The same with referential consistency on database if it is there - ORM will help you to load data that is tied to that account. Making user like web app users on database level would make all development/ops really costly. If someone is writing SQL queries directly, he has to have a good reason for it. Like if it is plugin for Wordpress it probably is not that easy to use ORM.