Class::DBI - performing action on column before it is used or saved

[duct_tape]

Hello,

I used to use a custom inhouse Object-Relation mapping module that a previous employee developed, but have been recently looking into Class::DBI. The old module did not support relationships between tables, and I really like this feature of Class::DBI. The interface was also a bit kludgy. One thing that it did have was the ability to have 'presave' and 'postload' methods for each column.

I have a database that stores passwords encrypted with Blowfish. I would use the postload method to decrypt it as soon as it was read, and then the presave method to encrypt it so that it was written back to the database encrypted.

I am trying to mimic this behavior in Class::DBI and I am getting stuck. I want it to be done as transparently as possible.

Here are the things I have tried so far:

* Overriding 'password' accessor. However this doesn't work when it comes to create()'ng so the password is stored plaintext in the database.

* Overriding normalize_column_values() to encrypt the password, and then having the accessor decrypt the password. This worked for the most part, except that it was an ugly hack and find_or_create() didn't work because normalize_column_values() wasn't called until after the search.

* Using triggers for 'select', 'before_create', and 'before_update'. This worked about as well as above. create()'s and retrieve()'s worked but find_or_create() failed due to the triggers not being called yet.

* Using a has_a relationship for 'password' and a custom EncryptedPassword class. I can't get this to work properly though. When using create() the class is 'inflated' using the plaintext password, otherwise it is called with the encrypted password and I am not sure how to tell how the constructor for the custom class was called.

This method seems like the best bet though, as looking through the CDBI code searching 'deflates' has_a relationships automatically. I'm just not completely sure how to go about writing this class.

If you'd like to see some example code of what I am trying to do let me know. I am using MySQL for my database, so triggers/stored procedures at the database level are not an option.

Has anyone done anything similar to this? Tips, tricks, advice? I am relatively new to Class::DBI, and so far I am very impressed with it. I just can't seem to get over this hurdle. :(

I was thinking I could change how the _do_search method in CDBI works so that it calls triggers first, but I am not real familiar with the codebase yet and I want to make sure that I am not overlooking something obvious.

I've also asked this on the cdbi-talk mailing list, but I thought I would post here as well to see if I could get some other ideas.

Regards,
Bradley C Bailey

[hardburn]

Take a look a the TRIGGERS section of Class::DBI. You seem to want to set before_create, before_set_$column, and select.

Personally, I solved a similar problem (specifically, saving passwords with SHA1 and a salt value) by overriding create. Then methods called check_passwd and new_passwd were created for checking passwords and getting a new password, respectivily. With SHA1, it's hard to get the orginal text back, so there was no point in trying to override password to get the plaintext version back. Instead, the methods provided would do everything you'd want a password object to do, which ends up being a better OO design anyway, since there is more encapsulation.

[duct_tape]

Instead of using the before_set_$column trigger, I found before_update to be what I needed. However, this still doesn't solve the problem 100%, as the search methods don't work with the triggers. :(

I suppose I could create a method like decrypted_password in my class to deal with this. I was just hoping I could make it more transparent.

Thanks for the reply. :) I will do some more thinking on the subject.

[hardburn]

It's actually better if you don't decrypt the password at all. Instead, encrypt the password you got from the user exactly the same as the one in the database was and compare them.

Never having passwords (or other secure data) in memory is a Holy Grail that isn't always possible, but it's a good goal.

[cees]

You definately want to use the 'has_a' relationship for this.

* Using a has_a relationship for 'password' and a custom EncryptedPassword class. I can't get this to work properly though. When using create() the class is 'inflated' using the plaintext password, otherwise it is called with the encrypted password and I am not sure how to tell how the constructor for the custom class was called.

The trick with using a 'has_a' object with the 'create' method is that Class::DBI will expect you to pass an object for the 'password' column when calling 'create'. Since you are passing a string, it thinks it needs to inflate your string first which means it tries to decrypt the string you have passed in.

If you want to be able to provide a plain text string to 'create' and still use the has_a relationship with the password field, then you will have to write an 'inflate' method that can distinguish between the plaintext and encrypted formats and 'do the right thing' (ie if it sees plaintext, leave it alone, and if it sees encrypted data, decrypt it.

See this node for some info on how you can do this with Dates. It doesn't apply directly, but it might give you some more insight...

Good luck in learning about Class::DBI. You won't regret the move

- Cees

[jdtoronto]

Class::DBI problems seem to be popular today!

I haven't tried them yet, but according to the doc's Class::DBI has an "add_trigger" method where you specify the trigger point and then give a coderef to execute. Surely this would do wyat you want?

jdtoronto

[duct_tape]

Yes they do! Apparently I was replying to your question while you were replying to mine. :)

I have been playing with the triggers and they work for the most part. The main problem I am having with them in this situation is that they are not called in the search methods of CDBI. So for instance I can't do something like this:

my $obj MyModule->search(password => 'blahblah');

Because the trigger for 'select' hasn't been called so it is literally checking the string 'blahblah' against the encrypted password in the database. :/

[edoc]

No guarentees that this is the best solution (in fact I kinda doubt it), but.. I would probably look at using before_create & before_update triggers, TEMP column, and overriding the search method. Beware though, there may still be issues with this method when another class interacts with this one automatically through relationships (I haven't dug deep enough to work out all the bits yet...)

__PACKAGE__->columns( TEMP => qw/ password / );
__PACKAGE__->add_trigger(before_create => sub {
                 my ($self, %args) = @_;
                 $self->{enc_pass} = $self->encrypt_password($self->{p
+assword});
             });

__PACKAGE__->add_trigger(before_update => sub {
                 my ($self, %args) = @_;
                 $self->enc_pass = $self->encrypt_password( $self->pas
+sword );
             });

sub search{
  my ($self, %query) = @_;
  my @results = $self->SUPER::search( %query );
  foreach my $result(@results){
     $result->password( $self->decrypt_password( $result->encpass ) );
  }
}

$object->password should be used to get/set the plaintext password, while in the database it is encrypted an stored as enc_pass.