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

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

--

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.

[Justvuur]

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?

So with this, I would be able to query/filter an encrypted column and it will automatically perform the decryption to test the column?

[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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1164ebf3-4ee3-4434-a21c-fe12ce57f556n%40googlegroups.com.

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

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[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="?

[Jonathan Vanasco]

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.

[Justvuur]

Ahhhh, 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?

[Justin Van Vuuren]

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

--

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

[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

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAJK07SZwotU64v22pmRJn4SR6aV2cb%2B6U_tKMwJxgG9Pe0cUQA%40mail.gmail.com.

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0cb3753d-8f5e-41af-8770-ec15dd538fa2o%40googlegroups.com.

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a71b6e9e-a82f-4257-a06c-3344e6dfb226%40www.fastmail.com.