0

So this has less to do with code itself and more about efficiency and practicality. At my previous job, we had multiple databases. One that was accessible through public means, and one that can only be accessed privately. The public database could essentially show everything the private one did and they were pretty much in sync, keeping up to date every 2 minutes or so. Their idea was that if the public database got destroyed with some type of SQL injection or something else malicious that destroyed the database, it didn't hurt production, and it could be immediately restored.

However, it was a pretty small scale operation, about only about 100 people accessing the db at one time, and if anything bad happened I'm pretty sure someone had to manually go in and restore the database to fix the problem.

My question is, is this a correct way to do things? When does this sort of tactic start to become incredibly inefficient if ever? Hypothetically if I were having tens of thousands of queries a day would this be unmaintainable?

Thanks for the insight.

QConscious
  • 87
  • 1
  • 1
  • 11
  • 1
    "is this a correct way to do things?" - probably not. Don't expose the database directly. Place a permissioned API over it; at a minimum read-only views. Also don't mix OLTP and OLAP concerns. – Mitch Wheat Mar 08 '17 at 05:12
  • *public databases* are always a bad idea. but: if your public DB is affectable by SQL Injection, then so is your private one - because somehow, the data has to get there, doesn't it? also, instead of data destruction, which can be mitigated by **backups**, you should rather worry about data leakage. so most of your effort should go into making your **code safe** and your database **not public** – Franz Gleichmann Mar 08 '17 at 06:49

1 Answers1

0

The first defense line must be the softwares have accessed to the database, there are many ways out there to prevent sql injections like using SPs, sending parameters instead of queries, ... although if the users of the system are providing the data (like this forum) having a private database is not better than having a copy of database (which you say is syncing every 2 minutes, any changes in the public one changes the private one as well). You can have different copy of your data with different intervals and roll back anytime to a good one anytime something happens.

I used to work for a bank, they a make copy on the Tape! It's a write once/read only tape in case something bigger than sql injection happened!

Mehrad Eslami
  • 308
  • 3
  • 13