Flow
Round-trip check for a field-encrypted backup
1Build test data
One encrypted column, plus awkward text.
2Export, then restore to a scratch database
Not the live one.
3Compare the plain columns
Any difference means the backup changed data it shouldn't touch.
4Read the encrypted column with the new key
It should match the original text.
5Try a wrong password and a damaged file
Each should stop the restore with a clear reason.
6Trust it only after every check passes
Otherwise throw the scratch copy away.
A backup of my scheduling app only worked fully on the server that made it. Some columns are encrypted with a key from the app's configuration, and a plain dump copies that ciphertext exactly as it is. Restore it on a new installation with a different key and those fields come back as unreadable noise.
This change was meant to fix that. On export, the script decrypts those fields, encrypts them again with a key derived from the backup password, then encrypts the whole file with that password too. Restore reverses it and uses the new installation's key. The administration page's backup button likely runs the same code. Good idea. The way it finds the encrypted fields is what bothered me.
It never asks which columns are encrypted
There's no column list. The script finds every quoted string in the SQL dump. It unescapes each one and tries to decrypt it. Then it escapes it again. If decryption returns the same string, the script assumes it was plain text. So every name, note and setting passes through that loop, not only the encrypted columns.
A line break that comes back as the letter n
The dump writes a line break inside a string as a backslash and an n. The unescape step removes the backslash. The escape step doesn't restore it for an n. So if I'm reading it right, a note with line breaks would restore with letters where the breaks were, and nothing would complain. I haven't tested it yet.
Getting your string back can mean three things
When decryption fails, the code returns the input unchanged. A plain value, a damaged value and a value under the wrong key all look identical. The field encryption has no authentication tag, so occasionally a wrong key could even let garbage through.
Password in the args, plaintext in temp
The password appears on a command line where other local users can sometimes see it. The decrypted dump sits in a temporary folder between steps. A pattern failure would produce an empty backup that still reports success. Restore writes directly into the live database.
What authenticated wrapping would look like here
If you're building something similar, I'd name the encrypted columns and touch only those. I'd use encryption with an authentication tag and a version prefix, so one invalid value stops the restore. A pattern failure should be an error, not an empty file. And I'd restore into a scratch database with awkward text and compare every value.
A test note with a few line breaks in it is the next thing I want to try.