Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

[Mike Bayer]

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
>>  
>> .
> 
 

 --
 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 

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

[Mike Bayer]

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
>  
> .
 
>>> 

>>> --
>>> 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
>>>  
>>> .
>> 
> 

> --
>  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 

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

[Simon King]

Not in the traditional sense, no. ORDER BY is implemented by the
database, and with client-side encryption, the database only ever sees
encrypted strings.

Simon

On Fri, Jul 10, 2020 at 8:41 AM Justin Van Vuuren  wrote:
>
> Also, regarding the client side approach, would one be able to do an order by 
> query?
>
> On Thu, 9 Jul 2020 at 21:28, Justvuur  wrote:
>>
>> A, ok ye, I understand, then I prefer client side.
>> I just need to sort out the encryption then... I'm not too familiar with 
>> encryption algorithms but at the moment its using a text key from a config 
>> file and the mode is CBC which I guess is what is generating a different 
>> value each time right?
>> In Mike's example he's using ECB mode which is the default.
>> What would be the best mode to use for my scenario? A good balance between 
>> secure and performance that works for client side?
>>
>> On Thursday, 9 July 2020 20:18:52 UTC+2, Jonathan Vanasco wrote:
>>>
>>>
>>>
>>> On Thursday, July 9, 2020 at 2:12:36 PM UTC-4, Justvuur wrote:

 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,
>>>
>>>
>>> That is how client-side encryption works.  If you want to search for 
>>> "secrets", you need to use server-side encryption (which depends on the 
>>> database). In those systems, the server will decrypt the column in every 
>>> row when searching - which can be a performance issue.
>>>
 The thing is this type of comparison wont work, the algorithm generates a 
 different string each encryption for the same string.
>>>
>>>
>>>  What are you using for your encryption key? The key should be persistent, 
>>> and should always generate the same output for a given input.  In the 
>>> example from Michael Bayer, a random uuid is used as a placeholder.
>>
>> --
>> 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/58a78976-3ebe-428b-824f-165b15f61cb4o%40googlegroups.com.
>
> --
> 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/CAJK07SZwotU64v22pmRJn4SR6aV2cb%2B6U_tKMwJxgG9Pe0cUQA%40mail.gmail.com.

-- 
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/CAFHwexdJquCGHv0B5aAOUgkFBXijxtEM48Fo0YoSFrSOsNo4Zw%40mail.gmail.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

[Justin Van Vuuren]

Also, regarding the client side approach, would one be able to do an order
by query?

On Thu, 9 Jul 2020 at 21:28, Justvuur  wrote:

> A, ok ye, I understand, then I prefer client side.
> I just need to sort out the encryption then... I'm not too familiar with
> encryption algorithms but at the moment its using a text key from a config
> file and the mode is CBC which I guess is what is generating a different
> value each time right?
> In Mike's example he's using ECB mode which is the default.
> What would be the best mode to use for my scenario? A good balance between
> secure and performance that works for client side?
>
> On Thursday, 9 July 2020 20:18:52 UTC+2, Jonathan Vanasco wrote:
>>
>>
>>
>> On Thursday, July 9, 2020 at 2:12:36 PM UTC-4, Justvuur wrote:
>>>
>>> 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,
>>>
>>
>> That is how client-side encryption works.  If you want to search for
>> "secrets", you need to use server-side encryption (which depends on the
>> database). In those systems, the server will decrypt the column in every
>> row when searching - which can be a performance issue.
>>
>> The thing is this type of comparison wont work, the algorithm generates a
>>> different string each encryption for the same string.
>>>
>>
>>  What are you using for your encryption key? The key should be
>> persistent, and should always generate the same output for a given input.
>> In the example from Michael Bayer, a random uuid is used as a placeholder.
>>
> --
> 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/58a78976-3ebe-428b-824f-165b15f61cb4o%40googlegroups.com
> 
> .
>

-- 
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/CAJK07SZwotU64v22pmRJn4SR6aV2cb%2B6U_tKMwJxgG9Pe0cUQA%40mail.gmail.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

[Justvuur]

A, ok ye, I understand, then I prefer client side.
I just need to sort out the encryption then... I'm not too familiar with 
encryption algorithms but at the moment its using a text key from a config 
file and the mode is CBC which I guess is what is generating a different 
value each time right?
In Mike's example he's using ECB mode which is the default.
What would be the best mode to use for my scenario? A good balance between 
secure and performance that works for client side?

On Thursday, 9 July 2020 20:18:52 UTC+2, Jonathan Vanasco wrote:
>
>
>
> On Thursday, July 9, 2020 at 2:12:36 PM UTC-4, Justvuur wrote:
>>
>> 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, 
>>
>
> That is how client-side encryption works.  If you want to search for 
> "secrets", you need to use server-side encryption (which depends on the 
> database). In those systems, the server will decrypt the column in every 
> row when searching - which can be a performance issue.
>
> The thing is this type of comparison wont work, the algorithm generates a 
>> different string each encryption for the same string.
>>
>
>  What are you using for your encryption key? The key should be persistent, 
> and should always generate the same output for a given input.  In the 
> example from Michael Bayer, a random uuid is used as a placeholder.
>

-- 
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/58a78976-3ebe-428b-824f-165b15f61cb4o%40googlegroups.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

['Jonathan Vanasco' via sqlalchemy]

On Thursday, July 9, 2020 at 2:12:36 PM UTC-4, Justvuur wrote:
>
> 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, 
>

That is how client-side encryption works.  If you want to search for 
"secrets", you need to use server-side encryption (which depends on the 
database). In those systems, the server will decrypt the column in every 
row when searching - which can be a performance issue.

The thing is this type of comparison wont work, the algorithm generates a 
> different string each encryption for the same string.
>

 What are you using for your encryption key? The key should be persistent, 
and should always generate the same output for a given input.  In the 
example from Michael Bayer, a random uuid is used as a placeholder.

-- 
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/2506c7ef-7a66-4662-a40b-db6e70b93347o%40googlegroups.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

[Justvuur]

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
>>  
>> 
>> .
>>
>>
>>
>> --
>> 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 

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

[Justvuur]

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
>  
> 
> .
>
>
>
> --
> 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
>  
> 
> .
>
>
>

-- 
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.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

[Mike Bayer]

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
>>>  
>>> .
>> 
> 

> --
>  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/1164ebf3-4ee3-4434-a21c-fe12ce57f556n%40googlegroups.com
>  
> .

-- 
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/0b4b7fa0-0cfb-466c-be0f-8f48d6a56eef%40www.fastmail.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

[Mike Bayer]

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+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/583d5772-50e4-484a-93e2-fcab29532116n%40googlegroups.com
>  
> .

-- 
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/3540258e-cafa-40ab-b994-9fb1394495da%40www.fastmail.com.

[sqlalchemy] Encrypt/Decrypt specific column(s)

[Justvuur]

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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/583d5772-50e4-484a-93e2-fcab29532116n%40googlegroups.com.