With ever the constant updates to popular and important apps and websites, how do the developers ensure the update preserves the user and site’s original database, considering the possibility that the updates might contain data-corruptive bugs or exploits for users?
This question is not addressing the frequent database leaks, since they only involve release of read only data and exploits only exist in the form of accessing user accounts and data, not altering the site’s database at large itself.
Even if there is a backup for the database, undoing changes that affect millions or people negatively would create a ton of uproar, especially if the site is based on real time interactions, like stock broker apps, social media apps and instant messengers. However I have personally never heard of such incidents.
Do companies at such large scales have extreme QA in place, depend on user feedback and reporting, or just have been lucky they haven’t been exploited yet? Or am I completely wrong and these incidents do occur?
Keep in mind that I am an amateur in this domain. I have worked with MySQL databases for educational purposes and personal projects but I found the state of databases very fragile, like the ability to nuke the entire database with just 2-3 words. This fact made me come up with this question.
(Developer here)
With hope, unmet deadlines, and false promises.
Seriously, observing good practices, and testing.
For example, your cenario of nuking a database with a couple of words “DROP SCHEMA…” can be easily prevented by access restrictions. The DB user that the application uses to access the database simply has no permissions to invoke such command. You can for example only allow access through stored procedures and views. Or no direct DB access at all; but only through (also limited) APIs exposed by the underlying system.
So you’ll have a set of procedures and/or views, or higher level APIs (where for example you don’t directly INSERT a purchase order in an ERP database table, but craft a JSON or XML containing the order information, and send it via API to the ERP system, which validates it and -hopefully- ensures integrity).
Layers of protection, restrictions, validations, etc.
The API exposed by the ERP, or the procedure you made available to the backend simply can’t do destructive things.
Most changes will be at the app/ui layer, and when you need to change the “lower” layers, you’re dealing with units much easier to verify and test.
Think about a waiter you interact with in a restaurant. You order, get your food, and pay through them, but would you trust them all the food safety protocols from farming/fishing, handling, storing, cooking, etc, to your table? Or could they personally withdraw your money from a physical vault of your bank, and carry the money to the restaurant’s bank vault? No. Each layer in this highly complex transaction becomes more specialized, restricted, robust, regulated and secured. The user interface may be disposable, replaceable and inconsistent, but it has little access to what is critical.
PS: of course even then, sometimes shifts happen.