Can Django models use MySQL functions?

Question

Is there a way to force Django models to pass a field to a MySQL function every time the model data is read or loaded? To clarify what I mean in SQL, I want the Django model to produce something like the following:

On model load: SELECT AES_DECRYPT(fieldname, password) FROM tablename

On model save: INSERT INTO tablename VALUES (AES_ENCRYPT(userinput, password))

Answer 1

Using Django signals you can do stuff when a model instance is saved, but as far as I know you can't trigger anything on read.

EDIT: My bad, it seems you can do stuff when initializing a model instance.

Answer 2

Instead of on model load, you can create a property on your model, and when the property is accessed, it can read the database:

def _get_foobar(self):
    if not hasattr(self, '_foobar'):

        cursor = connection.cursor()
        self._foobar = cursor.execute('SELECT AES_DECRYPT(fieldname, password) FROM tablename')[0]
    return self._foobar
foobar = property(_get_foobar)

Now after loading, you can refer to mything.foobar, and the first access will retrieve the decryption from the database, holding onto it for later accesses.

This also has the advantage that if some of your code has no use for the decryption, it won't happen.

Answer 3

I would define a custom modelfield for the column you want encrypted/decrypted. Override the to_python method to run the decryption when the model is loaded, and get_db_prep_value to run the encryption on saving.

Remember to set the field's metaclass to models.SubfieldBase otherwise these methods won't be called.

Answer 4

Here is a working solution, based in part on (http://www.djangosnippets.org/snippets/824/):

class Employee(models.Model):
   social_security_number = models.CharField(max_length=32)

   def _get_ssn(self):
       cursor = connection.cursor()
       cursor.execute("SELECT AES_DECRYPT(UNHEX(social_security_number), %s) as ssn FROM tablename WHERE id=%s", [settings.SECRET_KEY, self.id])
       return cursor.fetchone()[0]

   def _set_ssn(self, ssn_value):
       cursor = connection.cursor()
       cursor.execute("SELECT HEX(AES_ENCRYPT(%s, %s)) as ssn", [ssn_value, settings.SECRET_KEY])
       self.social_security_number = cursor.fetchone()[0]

   ssn = property(_get_ssn, _set_ssn)

And the results:

>>> from foo.bar.models import Employee
>>> p=Employee.objects.create(ssn='123-45-6789')
>>> p.ssn
'123-45-6789'

mysql> select * from foo_employee;
+----+----------------------------------+
| id | social_security_number           |
+----+----------------------------------+
| 31 | 41DF2D946C9186BEF77DD3307B85CC8C |
+----+----------------------------------+
1 row in set (0.00 sec)

Answer 5

It's definitely hackish, but it seems Django won't let you do it any other way at the moment. It's also worth noting that to_python will be called every time you change the value in python in addition to when it is first loaded.

from django.db import connection, models
import re

class EncryptedField(models.TextField):
    __metaclass__ = models.SubfieldBase

    def to_python(self, value):
        if not re.match('^*some pattern here*$', value):
            cursor = connection.cursor()
            cursor.execute('SELECT AES_DECRYPT(%s, %s)', [value, settings.SECRET_KEY])
            return cursor.fetchone()[0]
        return value

    def get_db_prep_value(self, value):
        cursor = connection.cursor()
        cursor.execute('SELECT AES_ENCRYPT(%s, %s)', [value, settings.SECRET_KEY])
        return cursor.fetchone()[0]


class Encrypt(models.Model):
    encrypted = EncryptedField(max_length = 32)