4 ms·

There is a lot we can do as programmers to make data leaks of that scope a lot harder to pull off. I think web apps of a certain magnitude should not be talking

by Udo 11y ago

There is a lot we can do as programmers to make data leaks of that scope a lot harder to pull off. I think web apps of a certain magnitude should not be talking directly to the database, they should be talking to their internal data API that resides on a different server. The reason for this is that a completely separate data layer is easier to lock down, and also way easier to monitor for unusual activity. Ideally, that internal API server couldn't even communicate with the internet directly. I've been doing web programming for a long time, and while direct DB access is both convenient and fast, I have to admit it's not easy to monitor these databases for breaches, or to stop them from spilling their entire contents if so requested.

JulianMorrison 11y ago
Yeah. I feel like an ideal architecture would be to use NSQ and transmit not queries, but a minified JSON bundle of query "name" with query parameters. Then have stateless DB service nodes preloaded with canned, named queries on secured VMs whose only access to the outside world is NSQ. Bonus, you can tap off DB requests with an ephemeral channel and log them.
- mpeg 11y ago
  That sounds like SOAP + SQL Server stored procedures (full replication mode to log db requests, then monitor any SQL queries). I'm assuming other vendors have similar set ups. I've ran systems like that, and I'm glad things are a lot easier to maintain now (stored procs are a pain in the ass, whether they exist in the database or in the app layer). Query injection is solved by parameterising queries (never sanitising them yourself), it should not be an issue nowadays, "advanced SQL injection" is a lie, either you can inject or you can't. If you can inject, it doesn't matter how many protections you put in place, you can always extract data via timing attacks or boolean conditions.
  - JulianMorrison 11y ago
    Your SQL is going to be "stored" one way or another, whether it's implicitly in the internal workings of an ORM, explicitly in code or mybatis style config, or explicitly in the DB. I think isolating it into data workers, and making them feed off a high throughput message queue (hiding restarts), minimizes the amount of redeploying needed.
icebraining 11y ago
If the frontend layer needs to access the data anyway, how does that separation help? And why is it easier to monitor for unusual activity?
- Udo 11y ago
  > If the frontend layer needs to access the data anyway, how does that separation help? It helps in multiple ways. Since you define the data API yourself, you can tailor it tightly to only the exact operations you need. This makes it more difficult to flat-out drain everything (or even query everything, depending on your app). It's also another layer of insulation. The fact that it's another, non-standard access protocol also helps. If I'm an attacker, I'll probably have a harder time figuring out how the custom data layer works; and when I'm done with that I still need to actually siphon the data, which will ideally require me to either issue millions of suspicious requests, or try to break into the API server itself, which has a drastically reduced attack surface compared to a typical web app. Which is where your second question comes in: > And why is it easier to monitor for unusual activity? Because you know what the legitimate access patterns of your app look like, you can raise flags programmatically. In fact, you can incorporate certain checks right from the time you start writing your API. It puts you, as a programmer, into a position of control and you can use the global insight you have into your app's design to your advantage by defining what's normal and what's not. Compare that with a general-purpose DB interface, which is ...well, general purpose. It doesn't know when it's doing something implausible. When something goes wrong with these databases, it's often the admin/ops people who notice it, if at all.
  - icebraining 11y ago
    > Since you define the data API yourself, you can tailor it tightly to only the exact operations you need. This makes it more difficult to flat-out drain everything (or even query everything, depending on your app). Considering that the database was designed specifically to support the website (so it's not supposed to have unrelated data), and that the frontend intermediates all access to the data, wouldn't the sum of the operations require access to all data? Why would you have data that was not needed by the website? > If I'm an attacker, I'll probably have a harder time figuring out how the custom data layer works This is just security by obscurity, except if the attacker has access to the client code, it's not even very obscure. Finding all calls to the backend API is a trivial task nowadays. > Because you know what the legitimate access patterns of your app look like, you can raise flags programmatically. Why can't you do that from the database logs? A query is just like an API call - you can raise flags if the frontend makes queries outside of a known pattern (ie, non-recognized queries or in an unexpected order).
BurningFrog 11y ago
This sounds like a fair amount of added complexity for unclear benefits in security. Also, if someone hacks into the DB server, it is all meaningless.
ZoFreX 11y ago
I think your intentions are in the right place but I don't think that would have helped here - they didn't just get in via SQL injection, they have all the source code too. That hints that they had quite a lot of access. Based on what we know about breaches in general, it's quite likely the attackers were inside the network undetected for months: https://blog.code42.com/the-heavy-cost-of-ignoring-dwell-time/ https://blog.code42.com/the-heavy-cost-of-ignoring-dwell-tim... This would be plenty of time to slowly spread through the network, learn what is stored where, and hop through servers until they got what they need - even in a setup like the one you describe.

by Udo 11y ago

JulianMorrison 11y ago

Yeah. I feel like an ideal architecture would be to use NSQ and transmit not queries, but a minified JSON bundle of query "name" with query parameters. Then have stateless DB service nodes preloaded with canned, named queries on secured VMs whose only access to the outside world is NSQ. Bonus, you can tap off DB requests with an ephemeral channel and log them.

mpeg 11y ago
That sounds like SOAP + SQL Server stored procedures (full replication mode to log db requests, then monitor any SQL queries). I'm assuming other vendors have similar set ups. I've ran systems like that, and I'm glad things are a lot easier to maintain now (stored procs are a pain in the ass, whether they exist in the database or in the app layer). Query injection is solved by parameterising queries (never sanitising them yourself), it should not be an issue nowadays, "advanced SQL injection" is a lie, either you can inject or you can't. If you can inject, it doesn't matter how many protections you put in place, you can always extract data via timing attacks or boolean conditions.
- JulianMorrison 11y ago
  Your SQL is going to be "stored" one way or another, whether it's implicitly in the internal workings of an ORM, explicitly in code or mybatis style config, or explicitly in the DB. I think isolating it into data workers, and making them feed off a high throughput message queue (hiding restarts), minimizes the amount of redeploying needed.

icebraining 11y ago

If the frontend layer needs to access the data anyway, how does that separation help? And why is it easier to monitor for unusual activity?

Udo 11y ago
> If the frontend layer needs to access the data anyway, how does that separation help? It helps in multiple ways. Since you define the data API yourself, you can tailor it tightly to only the exact operations you need. This makes it more difficult to flat-out drain everything (or even query everything, depending on your app). It's also another layer of insulation. The fact that it's another, non-standard access protocol also helps. If I'm an attacker, I'll probably have a harder time figuring out how the custom data layer works; and when I'm done with that I still need to actually siphon the data, which will ideally require me to either issue millions of suspicious requests, or try to break into the API server itself, which has a drastically reduced attack surface compared to a typical web app. Which is where your second question comes in: > And why is it easier to monitor for unusual activity? Because you know what the legitimate access patterns of your app look like, you can raise flags programmatically. In fact, you can incorporate certain checks right from the time you start writing your API. It puts you, as a programmer, into a position of control and you can use the global insight you have into your app's design to your advantage by defining what's normal and what's not. Compare that with a general-purpose DB interface, which is ...well, general purpose. It doesn't know when it's doing something implausible. When something goes wrong with these databases, it's often the admin/ops people who notice it, if at all.
- icebraining 11y ago
  > Since you define the data API yourself, you can tailor it tightly to only the exact operations you need. This makes it more difficult to flat-out drain everything (or even query everything, depending on your app). Considering that the database was designed specifically to support the website (so it's not supposed to have unrelated data), and that the frontend intermediates all access to the data, wouldn't the sum of the operations require access to all data? Why would you have data that was not needed by the website? > If I'm an attacker, I'll probably have a harder time figuring out how the custom data layer works This is just security by obscurity, except if the attacker has access to the client code, it's not even very obscure. Finding all calls to the backend API is a trivial task nowadays. > Because you know what the legitimate access patterns of your app look like, you can raise flags programmatically. Why can't you do that from the database logs? A query is just like an API call - you can raise flags if the frontend makes queries outside of a known pattern (ie, non-recognized queries or in an unexpected order).

BurningFrog 11y ago

This sounds like a fair amount of added complexity for unclear benefits in security. Also, if someone hacks into the DB server, it is all meaningless.

ZoFreX 11y ago

I think your intentions are in the right place but I don't think that would have helped here - they didn't just get in via SQL injection, they have all the source code too. That hints that they had quite a lot of access. Based on what we know about breaches in general, it's quite likely the attackers were inside the network undetected for months: https://blog.code42.com/the-heavy-cost-of-ignoring-dwell-time/ https://blog.code42.com/the-heavy-cost-of-ignoring-dwell-tim... This would be plenty of time to slowly spread through the network, learn what is stored where, and hop through servers until they got what they need - even in a setup like the one you describe.