Admin module - modify SQL Query used for Edit and Save Models

[Marc R]

I have a model where I have used AES_ENCRYPT(value, key) to save data in a MySQL database.  It is set as a varbinary field.

So the issue is; when trying to edit the model in Django, it does not render a widget for the field.

I've tried the aesfield from github but that does some funky thing with HEX and tagging the type of encryption used.  Not what I want, as the database already has 8300 records and other applications that access it using queries like
select field1, field2, AES_DECRYPT(field3, 'key') from ...

So I can't change how the data is stored or I'll break the other applications.

How can I setup customer "SELECT' and "SAVE" (insert, update), queries for a model in django?
Or am I stuck creating my own form and processing and related CRUD operations for this Model?

Thanks,

[Marc R]

Anyone? I would really like to know who I can modify the query as its being built so that for a specific field I can set the "field" to AES_DECRTYP(fieldname,key) on a select and AES_ENCRYPT(fieldname,key) on insert/update

[Shawn Milochik]

https://docs.djangoproject.com/en/1.5/howto/custom-model-fields/#converting-database-values-to-python-objects
https://docs.djangoproject.com/en/1.5/howto/custom-model-fields/#converting-python-objects-to-query-values

Have a look at these two methods of a custom field. You can pretty easily make your own.

You can use this as an example (see the EncryptedCharField class):

http://djangosnippets.org/snippets/2489/

[Marc]

Thanks, i'll play with that and see what I can come up with.

Docs are good, but sometimes really hard to read/find what you need :)

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/6BQxdM04Hi0/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Shawn Milochik]

On Fri, May 3, 2013 at 8:06 AM, Marc <war...@gmail.com> wrote:

Thanks, i'll play with that and see what I can come up with.

Docs are good, but sometimes really hard to read/find what you need :)

 

Oh come on, you mean "get_prep_value" and "to_python" weren't obvious? ;o)

 

[Tom Evans]

This deals with decrypting in python an encrypted value retrieved from
the database (and vice-versa). The OP wants to decrypt the value in
the database, which afaict is not possible easily.

Cheers

Tom

[Marc]

So Tom: i can't use those methods Shawn pointed out?  What I was hoping I can do is override the code that builds the SQL query.

Further looking I think thats correct; as I did get a module working I found and played with which uses those methods and they don't seem to manipulate the query that is built by django, just the value passed. 

What isn't obvious in the Doc's Shawn is what is passed into and what is expected to be returned. The docs are not clear at all about that at all.

[Marc]

I really think django should add an easy way to override what django uses to build the query for each Field and make it part of the Field settings,
for example:

models.CharField(override_sql_newupdate='AES_ENCRYPT(fieldname,key)', override_sql_select='AES_DECRYPT(fieldname,key)',...

would be very handy as then you could use all sorts of the powerful features in a DB.

I get that it may then make the use of an ORM less "useful", but it sure would be easier than having to always due RAW sql calls on the object to get the same result.

[Shawn Milochik]

On Fri, May 3, 2013 at 8:37 AM, Marc <war...@gmail.com> wrote:

So Tom: i can't use those methods Shawn pointed out?  What I was hoping I can do is override the code that builds the SQL query.

Further looking I think thats correct; as I did get a module working I found and played with which uses those methods and they don't seem to manipulate the query that is built by django, just the value passed. 

What isn't obvious in the Doc's Shawn is what is passed into and what is expected to be returned. The docs are not clear at all about that at all.

Sorry for the confusion. To be clear, I am suggesting that you make your own Field subclass, instead of using, for example, a CharField or TextField. The third link in that e-mail gave a working example of one. It should be very easy. It doesn't change the SQL at all -- it converts automatically between the plain text and encrypted value using those two functions named in the docs.

 

[Marc]

Thanks. I understood and that doesn't work for my project as python/django can't handle the returned bytes I need to use.
my project requires that the values are stored using the result of aes_encrypt from MySQL because other systems not made in python expect that and cannot be changed at this stage.

--

[Shawn Milochik]

On Fri, May 3, 2013 at 10:22 AM, Marc <war...@gmail.com> wrote:

Thanks. I understood and that doesn't work for my project as python/django can't handle the returned bytes I need to use.
my project requires that the values are stored using the result of aes_encrypt from MySQL because other systems not made in python expect that and cannot be changed at this stage.

There's no Python package available that can handle the AES encryption and decryption in a way that MySQL can understand?