Sequelize querying with encrypted fields using after* before* hooks

Question

Hey there I have a question about the best way to store data encrypted in my database. I use Node.js, a MySQL database and sequelize 6.6.5 as ORM.

Here's what I do:

• With beforeCreate and beforeUpdate hooks I'm encrypting my data before storing it in the database.
• With the beforeFind hook I encrypt the condition for querying before doing so.
• And with afterCreate, afterUpdate and afterFind hooks I decrypt the
  data to work with it after creating updating or querying for it.

But the the querying itself raises some problems for me which I think come with the way I encrypt my data. I use the Node.js crypto module with the aes-256-cbc algorithm and a random IV for every encryption. With the random IV every encryption results in a different string. That's why even if I use the beforeFind hook to encrypt my condition the query will never return any result.

myModel.create({myField: "someData"}); 
// with the beforeCreate hook encrypting this the database will contain something like this
// myField: "1ac4e952cf6207e5fd79630e0e82c901"


myModel.findAll({ where: { myField: "someData" } });
// The beforeFind hook encrypts this condition but as mentioned the result is not the same 
// as the encrpyted value in the database
// It will look something like this:
// { where: { myField: "e203a4e22cf654w5fd7390300ef2c2f2" } }

// Because "1ac4e952cf6207e5fd79630e0e82c901" != "e203a4e22cf654w5fd7390300ef2c2f2"
// the query results in null

I obviously could use the same IV to encrypt my data which then would lead to every encryption of the same source resulting in the same encrypted string but I would rather not do that if there is any other way to make it work like this.

So basically my two question are:

Is there a way to make this work with the an encryption using a random IV? Or is there an even better way to store the data encrypted in the database?

Thank you all in advance!

Answer 1

The purpose of the random (salt) part is exactly to prevent what you are trying to do.

I'm not sure about your use case but sometimes it's ok to encrypt without a salt (same data => same hash), sometimes (think of the user password) absolutely not ok.

From what you have posted I don't know where you are saving the random part, otherwise how do you decrypt the data?