Re: Use case for #14914 (to_db_python)

[Alejandro Dubrovsky]

On Wednesday, August 7, 2013 5:22:29 PM UTC+10, Anssi Kääriäinen wrote:
>
> On Monday, August 5, 2013 8:02:52 AM UTC+3, Jani Tiainen wrote:
>>
>> Hi, 
>>
>> You seem to found kind of an issue which happens with GeoDjango part as 
>> well. Most of the geodjango operations require quite heavy to/from data 
>> mangling while reading and/or writing data. 
>>
>> Currently there isn't clean solution to tell (per field) how data should 
>> be treated per backend. Django ORM works pretty well for a primitives like 
>> numbers, strings and such but when it goes to complex datatypes (like your 
>> encrypted field). 
>>
>> It would be really useful to have something to allow data mangling on a 
>> when reading/writing data from/to database per backend basis. Unfortunately 
>> such a feature isn't easy to implement due the current way how ORM works. 
>>
>> If you require such a functionality now, you should take a look how 
>> different GeoDjango backends deal with the similiar problem. 
>>
>
> I agree that there is room for improvement in how data conversions in the 
> ORM work. If I recall correctly different backends work slightly 
> differently, and when to_python() is called isn't consistent. Improvements 
> in this area are welcome.
>
> For the original use case I think it would be better to do the encryption 
> already in the original SELECT. I am not sure if there is any easy way to 
> do that currently...
>
>  - Anssi
>

Yes, doing it in the SELECT would be even better in this case, since then 
the encrypted data wouldn't ever have to reach Python, but I didn't see any 
easy way to do that (although I must admit I was trying to steer clear of 
the SQL compiler section) 

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Use case for #14914 (to_db_python)

[Alejandro Dubrovsky]

Thanks. I've had a look at GeoDjango and it did help. I've hacked something 
that works well enough for my purposes, but it assumes that the default 
connection is the one holding the data.

I agree with you that it would be useful if the data mangling/demangling 
stage would be more easily overridable.

On Monday, August 5, 2013 3:02:52 PM UTC+10, Jani Tiainen wrote:
>
> Hi, 
>
> You seem to found kind of an issue which happens with GeoDjango part as 
> well. Most of the geodjango operations require quite heavy to/from data 
> mangling while reading and/or writing data. 
>
> Currently there isn't clean solution to tell (per field) how data should 
> be treated per backend. Django ORM works pretty well for a primitives like 
> numbers, strings and such but when it goes to complex datatypes (like your 
> encrypted field). 
>
> It would be really useful to have something to allow data mangling on a 
> when reading/writing data from/to database per backend basis. Unfortunately 
> such a feature isn't easy to implement due the current way how ORM works. 
>
> If you require such a functionality now, you should take a look how 
> different GeoDjango backends deal with the similiar problem. 
>
> On Thu, 1 Aug 2013 21:23:40 -0700 (PDT) 
> Alejandro Dubrovsky  wrote: 
>
> > Looking around for a way to get the connection on deserialisation, I ran 
> > into #14914  which was 
> closed 
> > wontfix 3 years ago with a request for a use case for the change. 
> > 
> > Here is my use case: 
> > 
> > I'm writing an encrypted char field, with encryption happening at the 
> > database end (MS-SQL). Decryption looks a bit like this: 
> > 
> >  
> > OPEN SYMMETRIC KEY keyname 
> > DECRYPTION BY CERTIFICATE somecertificate 
> > 
> > SELECT CAST(DECRYPTBYKEY(fieldname) AS VARCHAR(2048)) 
> > FROM atable 
> > 
> > CLOSE SYMMETRIC KEY keyname 
> >  
> > 
> > and analogously for the encryption. 
> > 
> > The way I thought of doing that was by modifying get_db_prep_value for 
> > encryption and to_python for decryption, but I ran into the lack of 
> > connection at the to_python stage, and nothing like to_db_python. 
> > Does this constitute a legitimate use case for to_db_python or is there 
> a 
> > better way to go about this? 
> > Note: I'd prefer if any discussion would stay away from the validity of 
> > using DB-based symmetric encryption on specific fields. 
> > 
> > In the more general sense, I'd expect the method to be useful for any 
> > stored-procedure-heavy location where the extracted field has to be 
> > post-processed by some function that runs on the database to be made 
> sense 
> > of. 
> > 
> > Thanks 
> > 
> > 
> > -- 
> > You received this message because you are subscribed to the Google 
> Groups "Django developers" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an email to django-develop...@googlegroups.com . 
> > To post to this group, send email to 
> > django-d...@googlegroups.com. 
>
> > Visit this group at http://groups.google.com/group/django-developers. 
> > For more options, visit https://groups.google.com/groups/opt_out. 
> > 
> > 
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Use case for #14914 (to_db_python)

[Anssi Kääriäinen]

On Monday, August 5, 2013 8:02:52 AM UTC+3, Jani Tiainen wrote:
>
> Hi, 
>
> You seem to found kind of an issue which happens with GeoDjango part as 
> well. Most of the geodjango operations require quite heavy to/from data 
> mangling while reading and/or writing data. 
>
> Currently there isn't clean solution to tell (per field) how data should 
> be treated per backend. Django ORM works pretty well for a primitives like 
> numbers, strings and such but when it goes to complex datatypes (like your 
> encrypted field). 
>
> It would be really useful to have something to allow data mangling on a 
> when reading/writing data from/to database per backend basis. Unfortunately 
> such a feature isn't easy to implement due the current way how ORM works. 
>
> If you require such a functionality now, you should take a look how 
> different GeoDjango backends deal with the similiar problem. 
>

I agree that there is room for improvement in how data conversions in the 
ORM work. If I recall correctly different backends work slightly 
differently, and when to_python() is called isn't consistent. Improvements 
in this area are welcome.

For the original use case I think it would be better to do the encryption 
already in the original SELECT. I am not sure if there is any easy way to 
do that currently...

 - Anssi

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Use case for #14914 (to_db_python)

[Jani Tiainen]

Hi,

You seem to found kind of an issue which happens with GeoDjango part as well. 
Most of the geodjango operations require quite heavy to/from data mangling 
while reading and/or writing data.

Currently there isn't clean solution to tell (per field) how data should be 
treated per backend. Django ORM works pretty well for a primitives like 
numbers, strings and such but when it goes to complex datatypes (like your 
encrypted field).

It would be really useful to have something to allow data mangling on a when 
reading/writing data from/to database per backend basis. Unfortunately such a 
feature isn't easy to implement due the current way how ORM works.

If you require such a functionality now, you should take a look how different 
GeoDjango backends deal with the similiar problem.

On Thu, 1 Aug 2013 21:23:40 -0700 (PDT)
Alejandro Dubrovsky  wrote:

> Looking around for a way to get the connection on deserialisation, I ran 
> into #14914  which was closed 
> wontfix 3 years ago with a request for a use case for the change.
> 
> Here is my use case:
> 
> I'm writing an encrypted char field, with encryption happening at the 
> database end (MS-SQL). Decryption looks a bit like this:
> 
> 
> OPEN SYMMETRIC KEY keyname
> DECRYPTION BY CERTIFICATE somecertificate
> 
> SELECT CAST(DECRYPTBYKEY(fieldname) AS VARCHAR(2048))
> FROM atable
> 
> CLOSE SYMMETRIC KEY keyname
> 
> 
> and analogously for the encryption.
> 
> The way I thought of doing that was by modifying get_db_prep_value for 
> encryption and to_python for decryption, but I ran into the lack of 
> connection at the to_python stage, and nothing like to_db_python.
> Does this constitute a legitimate use case for to_db_python or is there a 
> better way to go about this?
> Note: I'd prefer if any discussion would stay away from the validity of 
> using DB-based symmetric encryption on specific fields.
> 
> In the more general sense, I'd expect the method to be useful for any 
> stored-procedure-heavy location where the extracted field has to be 
> post-processed by some function that runs on the database to be made sense 
> of.
> 
> Thanks
> 
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "Django developers" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-developers.
> For more options, visit https://groups.google.com/groups/opt_out.
> 
> 

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
For more options, visit https://groups.google.com/groups/opt_out.

Use case for #14914 (to_db_python)

[Alejandro Dubrovsky]

Looking around for a way to get the connection on deserialisation, I ran 
into #14914  which was closed 
wontfix 3 years ago with a request for a use case for the change.

Here is my use case:

I'm writing an encrypted char field, with encryption happening at the 
database end (MS-SQL). Decryption looks a bit like this:


OPEN SYMMETRIC KEY keyname
DECRYPTION BY CERTIFICATE somecertificate

SELECT CAST(DECRYPTBYKEY(fieldname) AS VARCHAR(2048))
FROM atable

CLOSE SYMMETRIC KEY keyname


and analogously for the encryption.

The way I thought of doing that was by modifying get_db_prep_value for 
encryption and to_python for decryption, but I ran into the lack of 
connection at the to_python stage, and nothing like to_db_python.
Does this constitute a legitimate use case for to_db_python or is there a 
better way to go about this?
Note: I'd prefer if any discussion would stay away from the validity of 
using DB-based symmetric encryption on specific fields.

In the more general sense, I'd expect the method to be useful for any 
stored-procedure-heavy location where the extracted field has to be 
post-processed by some function that runs on the database to be made sense 
of.

Thanks


-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
For more options, visit https://groups.google.com/groups/opt_out.