I've done some more digging... It seems when I did the search for
"secrets", the text is encrypted and compared to the value in the columns,
see below:
SELECT
student_form_fields.field_value AS student_form_fields_field_value,
student_form_fields.student_id AS student_form_fields_student_id,
student_form_fields.field_id AS student_form_fields_field_id
FROM
student_form_fields
WHERE
student_form_fields.field_value = %(field_value_1)s
LIMIT %(param_1)s
{'param_1': 1, 'field_value_1':
'G1/jmJvsZLCOHcWRGUetNyF1388aJ4E9ocn3FBwuIOk='}
The thing is this type of comparison wont work, the algorithm generates a
different string each encryption for the same string.
Should it not decrypt the value(s) in the column and compare it to the
provided text/query param which in this case is secrets and not
"G1/jmJvsZLCOHcWRGUetNyF1388aJ4E9ocn3FBwuIOk="?
On Thursday, 9 July 2020 19:15:32 UTC+2, 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/728d7426-816b-4eb0-ad3c-3f9cdba0c48ao%40googlegroups.com.
