Currently I have a PostgreSQL database that contains existing data. I would like to start encrypting some of the data going into the database. Hence there will be a mixture of legacy data that’s not encrypted and new (going forward) data that will be encrypted, until I’m able to retroactively encrypt all the data. So from my understanding I can’t currently just encrypt the column and instead need to do it before it hits the database.
I have no issue encrypting data and putting it in the database and then decrypting the data when consuming it in the backend using pycrypto.
However, I’m having trouble using the data in a SQLAlchemy query filter to find existing matching data to avoid duplicates. For example; I have a table with x amount of rows. When I enter a new row I want to first check the table to make sure a similar row does not already exist based on a specific field. Normally, if the data was not encrypted I would do something like this;
# New item I would like to entry into the db new_item_code = xxx # First I want to check if the item already exist item = Item.query.filter(Item.item_code == new_item_code).first()
item doesn’t exist I can go ahead and enter it into the Item table. IF it already exist I will ignore the new item.
My issue is I don’t know how to query the table when the data is encrypted, because the encrypted value will always be different. Therefore, I would first have to decrypt the values in the table and then perform the query. For instance in the example above I would need to find a way to decrypt the
Item.item_code part. I first thought of using a SQLAlchemy
@hybrid_property , but I run into decoding issues because I’m trying to decrypt an attribute instead of a value.
The next thing I was going to try is to first return all the rows (only the
item_code) place it in a
dict and then decrypt all the values in the
dict and check if the
new_item_code exist in the
dict. However, this doesn’t seem like a good solution and I’m worried about performance, especially when the table gets very large.
Is there a better way to decrypt a value from a table and use it in a SQL alchemy query filter as shown above?
I was able to solve my issue by using SymmetricEncryptionClientSide and performing the encryption on the client side through a SQLAlchemy hybrid property.
Answered By – BulkHulk