Can Django models use MySQL functions?

Question

Is there a way to get 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 create something like the following:

When loading the model: SELECT AES_DECRYPT (field name, password) FROM tablename

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

Answer 1

I would define a custom modelfield for the column that you want to encrypt / decrypt. Override the to_python method to start decryption when loading the model and get_db_prep_value to start encryption when saving.

Remember to set the metaclass of the models.SubfieldBase class, otherwise these methods will not be called.

Answer 2

Instead of loading the model, you can create a property on your model, and when access is available to it, 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 turn to mything.foobar , and the first access will retrieve the decryption from the database, holding it for later access.

This also has the advantage that if any of your code is not used for decryption, this will not happen.

Answer 3

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 4

It definitely hacks, but it seems that Django will not let you do it otherwise. It's also worth noting that to_python will be called every time you change a value in python in addition to when it will be loaded first.

 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) 

Answer 5

After a deep search in the ORM implementation of Django,

I found that this can be solved something like this:

 class EncryptedField(models.BinaryField): @staticmethod def _pad(value): return value + (AES.block_size - len(value) % AES.block_size) * b('\x00') def _encrypt(self, data): if not data: return None return self.cipher.encrypt(self._pad(data.encode('utf8'))) def _decrypt(self, data): if not data: return None return self.cipher.decrypt(force_bytes(data)).rstrip(b'\x00').decode('utf8') @property def cipher(self): return AES.new(KEY, mode=AES.MODE_CBC, IV=self._iv) def get_db_prep_value(self, value, connection, prepared=False): if value is not None: value = self._encrypt(value) if value: value = binascii.hexlify(value) return value def get_placeholder(self, value, compiler, connection): return 'unhex(%s)' 

Answer 6

Using Django signals , you can do anything when the model instance is saved, but as far as I know, you cannot activate anything to read.

EDIT: I feel bad, it seems you can do something when initializing a model instance.