Django: how to wrap DB function on top of the field while querying records from that model and inserting using Django Custom Model Field?

Question

I have a text field in the model. Which holds the encrypted string in that field. Also, I have 2 functions in the DB to Encrypt and Decrypt. So, I want to wrap these DB functions on top of that field and save the encrypted string in the DB and while retrieving decrypt function should be wrapped on that field, and the object should have the decrypted string in that attribute.

I am thinking of using the Custom Field in Django. Please suggest to me how to do that?

class User(models.model):
    name = models.CharField()
    age = models.IntegerField()
    email = models.TextField()

for example, let's take the email field, I want to have an encrypted string in that text field.

Let's take DB functions encrypt_email, decrypt_email

when I am running the following

user_objects= User.objects.filter(age__gt = 20)

It should run the query like as follows

SELECT *, decrypt_email(email) as email FROM users where age > 20;

Also when Inserting it should run the query as follows

INSERT INTO users (name, age, email) VALUES ('Rafiu', 25, encrypt_email('abc@gmail.com'));

can you share some code regarding this? – ruddra May 11 '20 at 12:23 — ruddra, May 11 '20 at 12:23
@ruddra updated the description. – Rafiu May 11 '20 at 12:36 — Rafiu, May 11 '20 at 12:36

score 0 · Accepted Answer · answered May 14 '20 at 14:16

I have analyzed the Django Framework and implemented it as follows with the custom model field.

from django.db.models.expressions import Col
from django.db.models import Func, TextField, Value
from django.utils.functional import cached_property


class EncryptEmail(Func):
    def __init__(self, expression, **extra):
        super().__init__(
            expression,
            **extra
        )

    function = 'encrypt_email'
    template = "%(function)s(%(expressions)s)"


class CustomCol(Col):

    def as_sql(self, compiler, connection):
        qn = compiler.quote_name_unless_alias
        base_sql = "{alias}.{col}".format(alias=qn(self.alias), col=qn(self.target.column))
        sql_str = "{f_name}({n_name})".format(f_name='decrypt_email', n_name=base_sql)
        return sql_str, []


class EncryptedTextField(TextField):
    description = "Values Saved Encrypted and retrieved Decrypted"

    def pre_save(self, model_instance, add):
        setattr(model_instance, self.attname, EncryptEmail(Value(getattr(model_instance, self.attname))))
        return getattr(model_instance, self.attname)

    def select_format(self, compiler, sql, params):
        sql_str = "{n_name} as {rn_name}".format(n_name=sql, rn_name=self.attname)
        return sql_str, params

    def get_col(self, alias, output_field=None):
        if output_field is None:
            output_field = self
        if alias != self.model._meta.db_table or output_field != self:
            return CustomCol(alias, self, output_field)
        else:
            return self.cached_col

    @cached_property
    def cached_col(self):
        return CustomCol(self.model._meta.db_table, self)


class User(models.model):
    name = models.CharField()
    age = models.IntegerField()
    email = EncryptedTextField()

It is working as expected.

Django: how to wrap DB function on top of the field while querying records from that model and inserting using Django Custom Model Field?

1 Answers1