Database backups in production

what is your best recipe for keeping production database backups ?
obviously I have multiple options for backups, but my question is related to scenario lik this:

the production dB got hacked and the admin has not noticed until the next day (or even a week).
if the backup is created then malicious code (within the table) is stored in dB,
how to avoid it ?
how to scan database against it ?
what’s your best approach ?
run it externally VS internally (from the project) ?

In my case it’s MySQL, but the same will probably apply to all SQL databases.

It is not simple answer to this question. It is some (not so cheap) solutions to contain this case scenario.

If you wish simple solution, I suggest to have backups like snapshoots for the long period of time. At my personal oppinion is the best solution if you make your backups on data tapes. When you store it, it cannot spread across the system. Then you can restore on a separate server and play with it to figure out the solution.

For more complex and automated solution, you can contact me personally.

I hope this helped somehow.

Well , it is not a atter of a storage for me. I can create either PHP or SQL screipt to do backups. I am concerned about the whole concept. How does this work with the big databases and companies who deal with millions of transactions a day.
For example if some CRAP gets implemented in the record and a specific table, how to get this restored to the previous state without interruptin the system and loosing other data, which got into the system in the meantime. I do not have much experience in maintaining production database. that is the reson for my question

This is not really SC related, but a generic problem. The database should always listen to localhost and nothing more, to avoid a direct attack on the database. So malicious data can only be entered by flaws in your application, like XSS and SQL injection. If your field is large enough to store javascript then you must take measures to prevent. Some generators are protected automatically that even stored javascript in the database will be displayed as text and gets not executed. Now back to your question, If you have issues with your database, then restoring is not so simple. Depending on when the data was entered, I doubt if you will find that data easily. If it is just a record, you might manually fix the issue. Backups might contain the same bad data, so you can never be sure, unless you have an automated date/time stamp, so you can trace back when it happend. But suppose a month ago, you cannot ‘just’ go back in your backups for a month. If you know the record, and you have a decent audit trail then you might want to recover from there. But only if the crap was inserted using your application. Then you have the values of the old- and new record. I do it that way, but fortunately never had to use it for recovery. But if your database was directly compromised, or updated by some other process, then I don’t see an automated way.

1 Like

You are right. I wonder what are the best measures in SC projects to protect the Apps and the data. Built in SC encryltpion might not be enough. I also wonder how other protect specific fields. Is type() sand character type selection enough ? etc

As the author of a similar tool, I know security is always a prominent issue. You strive to make the product as safe as possible. But as soon as you allow custom modifications (like adding code in events), this code can be the weakest link. You cannot do much to verify this except provide rules that users must comply with. But there is no way to verify. Encryption of parameters that pass the URL is one measure. CSRF tokens are another. Escaping input fields for bad tokens. etc. etc. etc. You can find much information on the OWASP website, even a tool to do some basic security tests.