Multipart question all related to AES encryption with MariaDB. Strangely enough, it looks like even today MariaDB only has AES with ECB mode. MySQL allows CBC and more I believe. Anyway I'm stuck with MariaDB and luckily in this situation, the highest level of security is only a nicety and not a requirement. I'm asking more for academic reasons.

My usage requirements: Store encrypted data in mariadb in an environment I consider to be secure in the first place (no unauthorized user could ever even see the encrypted data). Be able to decrypt directly through a query (for sql ORDER BY and search operations). I've create my own SQL function as a wrapper around AES_ENCRYPT() so I can add whatever additional encrypt/decrypt logic I need. The most likely scenario is someone remotely gains access to the database, so they can see the encrypted data and function used to encrypt/decrypt.

  1. ECB mode - I understand why this isn't considered secure. So does salting the passphrase (16 random Bytes) adequately fix the ECB issue for my situation, since in theory the same input and passphrase should never create the same output?

  2. Is salting a passphrase randomly have the same security benefit as an unsalted password and an IV assuming the salt and IV have the same amount of entropy? I realize ECB doesn't use IVs, I'm just asking in general.

  3. Padding Oracles - For both ECB and CBC (when mariadb implements it). This is the one I least understand. So assuming someone has stolen the database contents, how likely is it that the hacker could create their own padding oracle for an sql database? Seems like they should be able to.

  4. Supposing a hacker could create a padding oracle for a stolen sql database, they should be able to crack the encryption fairly easily. Can anything be done to mitigate this? For example, could the padding be added to the beginning of the message? From what I gather, the hack works by testing decryption on blocks in reverse order. So maybe if the padding wasn't at the end of the message, they would have to fully decrypt the message to check the padding at the beginning.


1 Answer 1

  1. No
  2. No
  3. If the attacker doesn't steal the key, they can't create their own padding oracle.
  4. As long as the key is not stolen, no mitigation is required.

Your threat model for "someone [having] stolen the database contents" seems very weak: if you make a query containing the decryption key, the key bytes themselves will likely persist in memory long after the query has been executed. See this recent paper for a discussion of realistic threat models for database compromise. (Full disclosure: I am a co-author of the paper.)

  • $\begingroup$ Thanks for the quick and simple response. I am curious for a little more detail on 1 and 2. For 1 I'm not surprised at the answer, but I'm assuming a randomly salted password is still significantly more secure than a non-salted one, correct? To clarify 3 and 4, you say as long as the "key" isn't stolen or in the query. Does that mean that if the query contains the passphrase and the actual key is derived from the password with sufficient randomness, that I should be OK (discounting the fact that its ECB in the first place? $\endgroup$
    – Ryan
    Aug 16, 2017 at 23:29
  • $\begingroup$ Just read the paper (very well written and informative). I'm going to answer my own question - the follow up to 3 and 4 - It should be possible for an attacker to recreate the key if they can see the passphrase in a log and can see the function used to create the encryption because that would tell them how to extract the salt from ciphertext. $\endgroup$
    – Ryan
    Aug 17, 2017 at 0:40
  • $\begingroup$ To derive the key from the passphrase you either need to store the salt or pass it as a query parameter. Either way, a compromise of the database would give the attacker both salt and passphrase: enough to re-derive the actual encryption key. $\endgroup$
    – pg1989
    Aug 17, 2017 at 0:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.