Skip to main content
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Henry Hollenstain
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Henry Hollenstain@hhollenstain )

Storing Encryption Key Version As A Prefix On The Encrypted Field In MySQL

By on
Tags: , ,

A while back, in one of our ColdFusion applications, I had to rotate the encryption key being used to encrypt a column in our MySQL database. This particular feature wasn't powered by a native database encryption control - all the encryption and decryption was being performed within the ColdFusion application itself using the encrypt() and decrypt() functions. After all was said and done, I added a new column to the database table to hold the "version" of the encryption key being used for each record. But, in retrospect, I wish I had just included the "version" of the key as a prefix on the field value itself.

My workflow for rotating the encryption key in my ColdFusion and MySQL application was as follows:

  1. ALTER the database table to include a new encryptionKeyVersion column that defaults to 1, the current version. Deploy this to production.

  2. Generate a new encryption key (version 2) for each production environment and deploy them to the production key-management system. All keys are stored outside of the application source code and are provided as ENV (environment) values to the ColdFusion application.

  3. Update the ColdFusion application logic to generate all new encrypted values using the new key version (and to store the new version number in the database record). Deploy this to production so that all new records going forward will use the new key.

  4. Script a background task that iterates over the MySQL database table looking for all records using the old encryption key (version 1). Then for each matching row, decrypt the current value using the old key (version 1), re-encrypt it using the new key (version 2), and then persist it back to the database. Deploy this to production.

  5. Run the background script.

  6. Once the background script is done, delete it. Also, delete any old encryption key logic.

  7. Revoke the old encryption key.

No matter how I store the "version" of the encryption key, this workflow is more-or-less the same. But, it's that first step - the database schema migration - that never quite sat right with me. I can't even articulate why - it just felt like an unnecessary amount of work.

After I was done with the migration, I started to think about bCrypt (and other one-way password hashing algorithms in ColdFusion); and, one thing that I find fascinating about these algorithms is that they store metadata about the hashing operation right there within the hash payload.

For example, if you look at the bCrypt hash format, you'll see that the hash payload contains several embedded segments:

  • Algorithm
  • Computational cost
  • Salt
  • Hash

To be clear, this is all one String. The segments are defined using both delimiters and fixed-length measurements. But, that's all completely opaque to the ColdFusion developer; the bCrypt algorithm parses the hash, extracts the segments, and compares the generated hashes all transparently as part of the hashing operation. The developer doesn't have to care about any of this.

By embedding this metadata in the hash itself, bCrypt (and the other popular one-way password hashing algorithms) provide for an adaptive hashing strategy. Meaning, the algorithm and computational complexity of the hashing can be easily changed over time in response to the ever-increasing processing power of computers. Essentially, allowing the password hashing to remain brute-force resistant over time by becoming computationally more challenging.

ASIDE: The password hashing doesn't remain brute-force resistant automatically. Your ColdFusion application code has to choose to increase the computational cost over time as computer hardware capabilities increase. But, the password hashing format makes this an easy(ish) task.

Circling back to the problem of encryption key versions, in retrospect, it would have been nice to just start storing the key version in the encrypted payload itself. Something like:

v=2,{ encrypted_value }

... where v=2 is indicating the use of the version 2 encryption key.

This way, I wouldn't have had to add a new column to the MySQL database table. And, I could have passed around the encrypted payload as a single value instead of also having to pass around the encryption key version.

And, this kind of embedded approach would also allow me to (more) easily change the encryption algorithm as well. In addition to the key version, I could embed the algorithm right there in the payload:

v=2,a=aes,{ encrypted_value }

... where a=aes is indicating that the current encryption algorithm is AES (Advanced Encryption Standard).

The more I think about this approach, the more I am realizing that what makes it attractive is that it turns the encrypted value into an opaque token abstraction.

Consider Object Oriented Programming (OOP) for a moment: one "code smell" in OOP is when you read a number of properties out of an Object in order to perform an action on that object (the "anemic domain model"). With proper encapsulation, you should instead be asking the Object to do something and the Object should consume those same values internally.

I'm kind of feeling like embedding versions (and even algorithms) in the encrypted value creates the same kind of encapsulation. Instead of my ColdFusion application always passing around multiple bits of information about the encryption and decryption process, I should just pass around a single opaque payload as an abstraction that hides the implementation details.

Anyway, I'm just thinking out load here. I've been trying to Google this topic; but, I'm not finding the right combination of keywords to yield helpful results. I'd be very curious to hear how other people approach this problem in their ColdFusion applications.

Reader Comments

Post A Comment — I'd Love To Hear From You!

Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.