the AES library has been updated a bit so I updated the recipes at: https://github.com/sqlalchemy/sqlalchemy/wiki/SymmetricEncryptionClientSide
regardless of what encrpytion you are using, for a WHERE clause to work, you need to ensure the encrypt() function produces the same string each time. I'm using a fixed "nonce" now to ensure that works for the current version of AES. you should be able to run the function repeatedly and get the same encrypted value each time: (Pdb) aes_encrypt("some value") b'7884f37e601994409b34618ca6a41606' (Pdb) aes_encrypt("some value") b'7884f37e601994409b34618ca6a41606' (Pdb) aes_encrypt("some value") b'7884f37e601994409b34618ca6a41606' On Fri, Jul 10, 2020, at 11:16 AM, Mike Bayer wrote: > The recipe encrypts the value in the WHERE clause, however it seems like the > usage of the libraries in the example is a bit out of date for Python 3. What > Python version are you using ? > > > > On Thu, Jul 9, 2020, at 1:15 PM, Justvuur wrote: >> So there is good news and bad news hehe. >> Good news is if I query all the other columns (no encrypted) columns it >> works. All results are returned correctly and decrypted. >> Bad news, if I query the encrypted column, nothing is "found". >> >> This is my model: >> class StudentFormField(db.Model): >> __tablename__ = 'student_form_fields' >> >> student_id = db.Column(Integer, ForeignKey('students.id'), primary_key=True) >> >> field_id = db.Column(String(100), primary_key=True) >> >> decrypted_field_value = db.Column("field_value", EncryptedValue(), >> nullable=False) >> >> #field_value = db.Column(db.String) >> >> def __init__(self, student_id, field_id, field_value): >> self.student_id = student_id >> self.field_id = field_id >> self.decrypted_field_value = field_value >> >> Query Example: >> StudentFormField.query.filter_by(decrypted_field_value = 'secrets').first() >> >> Returns None even though I have an encrypted value of "secrets" in that >> column. >> Any idea what I could be doing wrong? >> >> On Thursday, 9 July 2020 16:37:17 UTC+2, Mike Bayer wrote: >>> >>> >>> On Thu, Jul 9, 2020, at 4:53 AM, Justvuur wrote: >>>> Hi Mike, >>>> >>>> This is awesome! Yes, I like the client side approach, especially the "Use >>>> TypeDecorator" method. >>>> Is there a specific version of SQLAlchemy I need? >>> >>> TypeDecorator is pretty universal so it should work for any modern version >>> in the past five years at least >>> >>> >>> >>>> So with this, I would be able to query/filter an encrypted column and it >>>> will automatically perform the decryption to test the column? >>> >>> should work, sure, try it out >>> >>> >>> >>>> >>>> >>>> On Wednesday, 8 July 2020 at 16:10:22 UTC+2 Mike Bayer wrote: >>>>> __ >>>>> I had just created all-new revised encryption examples on the wiki and >>>>> apparently I forgot to link them from the index, fixed. >>>>> >>>>> Here's two examples showing the general idea of how this can be done: >>>>> >>>>> https://github.com/sqlalchemy/sqlalchemy/wiki/SymmetricEncryptionClientSide >>>>> >>>>> https://github.com/sqlalchemy/sqlalchemy/wiki/SymmetricEncryptionServerSide >>>>> >>>>> the "client side" version is more general purpose and you can use any >>>>> encryption system you'd like. >>>>> >>>>> >>>>> >>>>> On Wed, Jul 8, 2020, at 6:55 AM, Justvuur wrote: >>>>>> Hi All, >>>>>> >>>>>> In one or two database tables I need to encrypt/decrypt a couple of >>>>>> columns. These tables/columns already exist in the DB and they already >>>>>> have data in them. >>>>>> >>>>>> What is the best way using SQL Alchemy to add encryption to these >>>>>> tables/columns? >>>>>> It needs to be efficient and the columns need to be able to work with >>>>>> querying/filtering. >>>>>> >>>>>> Regards, >>>>>> Justin >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> SQLAlchemy - >>>>>> The Python SQL Toolkit and Object Relational Mapper >>>>>> >>>>>> http://www.sqlalchemy.org/ >>>>>> >>>>>> To post example code, please provide an MCVE: Minimal, Complete, and >>>>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>>>> description. >>>>>> --- >>>>>> You received this message because you are subscribed to the Google >>>>>> Groups "sqlalchemy" group. >>>>>> To unsubscribe from this group and stop receiving emails from it, send >>>>>> an email to sqlalchemy+...@googlegroups.com. >>>>>> To view this discussion on the web visit >>>>>> https://groups.google.com/d/msgid/sqlalchemy/583d5772-50e4-484a-93e2-fcab29532116n%40googlegroups.com >>>>>> >>>>>> <https://groups.google.com/d/msgid/sqlalchemy/583d5772-50e4-484a-93e2-fcab29532116n%40googlegroups.com?utm_medium=email&utm_source=footer>. >>>>> >>>> >>>> -- >>>> SQLAlchemy - >>>> The Python SQL Toolkit and Object Relational Mapper >>>> >>>> http://www.sqlalchemy.org/ >>>> >>>> To post example code, please provide an MCVE: Minimal, Complete, and >>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>> description. >>>> --- >>>> You received this message because you are subscribed to the Google Groups >>>> "sqlalchemy" group. >>>> To unsubscribe from this group and stop receiving emails from it, send an >>>> email to sqlal...@googlegroups.com. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/sqlalchemy/1164ebf3-4ee3-4434-a21c-fe12ce57f556n%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/sqlalchemy/1164ebf3-4ee3-4434-a21c-fe12ce57f556n%40googlegroups.com?utm_medium=email&utm_source=footer>. >>> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/0cb3753d-8f5e-41af-8770-ec15dd538fa2o%40googlegroups.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/0cb3753d-8f5e-41af-8770-ec15dd538fa2o%40googlegroups.com?utm_medium=email&utm_source=footer>. > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/a71b6e9e-a82f-4257-a06c-3344e6dfb226%40www.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/a71b6e9e-a82f-4257-a06c-3344e6dfb226%40www.fastmail.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/567394f6-b411-4b04-9b99-d9486d119951%40www.fastmail.com.