5 ms·

I do this for my mobile app, because the users are often working in remote areas without cell coverage or any network at all. I have a pretty simple strategy t

I do this for my mobile app, because the users are often working in remote areas without cell coverage or any network at all. I have a pretty simple strategy though: I use SQLite on the mobile devices and when the device is back within network coverage, take a copy of the SQLite database, zip it up and throw it up to the server. It ends up in an S3 bucket (all the device database backups end up with a UUID as their name), kick off an automatic process via a Lambda (triggered by S3) that imports the SQLite database into the bigger DB, job is done. It works pretty well, SQLite databases compress REALLY well. The only tricky bit is having to check the version code of the database in case I have an old version of the app floating around in the wild (it happens).

firloop 5y ago
What do you do with the backups?
- smackeyacky 5y ago
  Keep them for a while, do an s3 sync to a local machine once a week for a good old tape backup, and use an s3 policy that sends older backups to s3 glacier. They are relatively small so this is manageable.
yjftsjthsd-h 5y ago
Is that safe? That sounds like intentional SQL injection
- vbitz 5y ago
  It sounds pretty safe. Your only copying rows from one database into another database. Unless you accept arbitrary SQL strings from the user it's not a significant security risk. It does open up some possible vulnerabilities like can the user overwrite other people's information but mitigating that requires the same validation layer you should have anyway.
  - smackeyacky 5y ago
    Exactly. It won't overwrite stuff that isn't yours, everythin is in a silo in the database.
    - tehbeard 5y ago
      As another comment pointed out, it's more dealing with carefully crafted db files that trigger issues or exploits, like a zipbomb would for archive processing.
  - vlovich123 5y ago
    SQLite is explicitly not safe to be used on arbitrary DB files and there’s a nontrivial amount of exploits on it from DOS to heap overflows to remote code execution that stem from untrusted SQL queries or processing untrusted DB files [1]. At a minimum you have to follow [2] but you don’t get to say “it’s safe to open malicious files or process unrelated queries“ and “SQLite has a good security track record because all our CVEs are only from untrusted queries and malicious input files and CVEs are useless anyway“. Those are facially contradictory positions likely written by different team members that reflect their individual perspective rather then there being a well thought or security stance (at least in my opinionated viewpoint). [1] https://www.sqlite.org/cves.html https://www.sqlite.org/cves.html [2] https://www.sqlite.org/security.html https://www.sqlite.org/security.html
    - smackeyacky 5y ago
      I do have a few of these in my importer but thanks for the links, will be implementing a few more of these safety measures.
- smackeyacky 5y ago
  Its safe enough. The user never sees the database they send, and the import routine does an extract and conversion that only uses a few tables. None of it goes near the user auth infrastructure except to check that the sender is who they claim to be.
  - 8192kjshad09- 5y ago
    A user can trivially sniff the traffic, realize you are sending a zipped SQLITE database, and craft a malicious binary file, zip it, and send it to your API. What the user sees or can do using your app is irrelevant from a security perspective. You are opening an untrusted binary file using SQLite on your backend. This is 100% not safe. You should convert to JSON or some other serialization before you send it, then your API should only accept JSON. Zipping a SQLite database is not a good serialization method... Accepting and opening an arbitrary sqlite binary file is asking for trouble.
    - smackeyacky 5y ago
      These are good points, I do have some security concerns raised above that need addressing but overall, the profile of this app is tiny and the the chain of ownership is relatively secure so the risks seem pretty low to me.
    - KronisLV 5y ago
      In my eyes, the following should be equal: - a binary file that's used for storing data, like an SQLite database - a text file that's used for storing data, like an XML or JSON file Someone else linked a list of CVEs and how those could be exploited, the look of which isn't that bad: https://news.ycombinator.com/item?id=28690837 https://news.ycombinator.com/item?id=28690837 Those problems could be addressed in a pretty easy way, plus if you're security conscious, just run the importer in an entirely separate container which will basically be single use (possibly distroless, if you want to go that far), with resource limits in place. But that's not my point. My point is that both of the data formats should be pretty much equal and them not being so in practice is just a shortcoming of the software that's used - for example, even spreadsheets ask you before executing any macros inside of them. There definitely should be a default mode of addressing such files for just reading data, without handing over any control of the computer to them. > Zipping a SQLite database is not a good serialization method... Therefore, with this i disagree. SQLite might be flawed, but zipping an entire dataset and sending it over the network, to be parsed and merged into a larger one is an effective and simple solution. Especially, given that your app can use SQLite directly, but probably won't be as easy to make while storing the state as a large JSON file, which will incur the penalty of having to do conversion somewhere along the way. Here's why i think it's a good approach: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html Who's to say that JSON/XML/... parsers also wouldn't have CVEs, as well as the application server, or back end stack, or web server that would also be necessary? In summary, i think that software should just be locked down more to accomodate simple workflows.
- nine_k 5y ago
  Well, create a zip file with all SQLite tables converted to CSV. This can be done in a streaming fashion, using very little extra space. Hopefully your CSV parser has fever vulnerabilities.
gizzlon 5y ago
So what happens if the client and server have a conflict? E.g. their versions of the data diverged.. These "merge conflicts" seems like something that must be handled in a custom way for many apps.
midrus 5y ago
This sounds very naive to me. Maybe it works for your specific use case but it is never that easy most of the times. What happens if the user has your application on two devices? What happens if you want the server to update this data? What happens if the data involves multiple users? What you're doing here is not much more than a backup.

firloop 5y ago

What do you do with the backups?

smackeyacky 5y ago
Keep them for a while, do an s3 sync to a local machine once a week for a good old tape backup, and use an s3 policy that sends older backups to s3 glacier. They are relatively small so this is manageable.

yjftsjthsd-h 5y ago

Is that safe? That sounds like intentional SQL injection

vbitz 5y ago
It sounds pretty safe. Your only copying rows from one database into another database. Unless you accept arbitrary SQL strings from the user it's not a significant security risk. It does open up some possible vulnerabilities like can the user overwrite other people's information but mitigating that requires the same validation layer you should have anyway.
- smackeyacky 5y ago
  Exactly. It won't overwrite stuff that isn't yours, everythin is in a silo in the database.
  - tehbeard 5y ago
    As another comment pointed out, it's more dealing with carefully crafted db files that trigger issues or exploits, like a zipbomb would for archive processing.
- vlovich123 5y ago
  SQLite is explicitly not safe to be used on arbitrary DB files and there’s a nontrivial amount of exploits on it from DOS to heap overflows to remote code execution that stem from untrusted SQL queries or processing untrusted DB files [1]. At a minimum you have to follow [2] but you don’t get to say “it’s safe to open malicious files or process unrelated queries“ and “SQLite has a good security track record because all our CVEs are only from untrusted queries and malicious input files and CVEs are useless anyway“. Those are facially contradictory positions likely written by different team members that reflect their individual perspective rather then there being a well thought or security stance (at least in my opinionated viewpoint). [1] https://www.sqlite.org/cves.html https://www.sqlite.org/cves.html [2] https://www.sqlite.org/security.html https://www.sqlite.org/security.html
  - smackeyacky 5y ago
    I do have a few of these in my importer but thanks for the links, will be implementing a few more of these safety measures.
smackeyacky 5y ago
Its safe enough. The user never sees the database they send, and the import routine does an extract and conversion that only uses a few tables. None of it goes near the user auth infrastructure except to check that the sender is who they claim to be.
- 8192kjshad09- 5y ago
  A user can trivially sniff the traffic, realize you are sending a zipped SQLITE database, and craft a malicious binary file, zip it, and send it to your API. What the user sees or can do using your app is irrelevant from a security perspective. You are opening an untrusted binary file using SQLite on your backend. This is 100% not safe. You should convert to JSON or some other serialization before you send it, then your API should only accept JSON. Zipping a SQLite database is not a good serialization method... Accepting and opening an arbitrary sqlite binary file is asking for trouble.
  - smackeyacky 5y ago
    These are good points, I do have some security concerns raised above that need addressing but overall, the profile of this app is tiny and the the chain of ownership is relatively secure so the risks seem pretty low to me.
  - KronisLV 5y ago
    In my eyes, the following should be equal: - a binary file that's used for storing data, like an SQLite database - a text file that's used for storing data, like an XML or JSON file Someone else linked a list of CVEs and how those could be exploited, the look of which isn't that bad: https://news.ycombinator.com/item?id=28690837 https://news.ycombinator.com/item?id=28690837 Those problems could be addressed in a pretty easy way, plus if you're security conscious, just run the importer in an entirely separate container which will basically be single use (possibly distroless, if you want to go that far), with resource limits in place. But that's not my point. My point is that both of the data formats should be pretty much equal and them not being so in practice is just a shortcoming of the software that's used - for example, even spreadsheets ask you before executing any macros inside of them. There definitely should be a default mode of addressing such files for just reading data, without handing over any control of the computer to them. > Zipping a SQLite database is not a good serialization method... Therefore, with this i disagree. SQLite might be flawed, but zipping an entire dataset and sending it over the network, to be parsed and merged into a larger one is an effective and simple solution. Especially, given that your app can use SQLite directly, but probably won't be as easy to make while storing the state as a large JSON file, which will incur the penalty of having to do conversion somewhere along the way. Here's why i think it's a good approach: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html Who's to say that JSON/XML/... parsers also wouldn't have CVEs, as well as the application server, or back end stack, or web server that would also be necessary? In summary, i think that software should just be locked down more to accomodate simple workflows.
nine_k 5y ago
Well, create a zip file with all SQLite tables converted to CSV. This can be done in a streaming fashion, using very little extra space. Hopefully your CSV parser has fever vulnerabilities.

gizzlon 5y ago

So what happens if the client and server have a conflict? E.g. their versions of the data diverged.. These "merge conflicts" seems like something that must be handled in a custom way for many apps.

midrus 5y ago

This sounds very naive to me. Maybe it works for your specific use case but it is never that easy most of the times. What happens if the user has your application on two devices? What happens if you want the server to update this data? What happens if the data involves multiple users? What you're doing here is not much more than a backup.