Skip to main content
Ben Nadel at cf.Objective() 2009 (Minneapolis, MN) with: Kurt Wiersma
Ben Nadel at cf.Objective() 2009 (Minneapolis, MN) with: Kurt Wiersma

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

By
Published in , , Comments (1)

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!

Post a Comment

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel