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 [email protected].
>>>>>> 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 [email protected].
>>>> 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 [email protected].
>> 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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/567394f6-b411-4b04-9b99-d9486d119951%40www.fastmail.com.
