FAQ
Hi,

hope this is not too much a newby question, but it really bugs me.

I have a table with encrypted information in one field. Actually it is encrypted with the aes_encrypt function of mysql. I would like to use this function to automatically encrypt and decrypt the values in this field using the database function, but I do not find any hook where I can attach the function to DBIx::Class. I imagine it to work just automatically everytime I insert, update or read values.

Any suggestions? (In the unlikely event, that someone might know how to emulate aes_encrypt by using perl on the client side, I would appreciate this information too, but I am still interested in usage of db-functions on fields anyways)

Best Regards
Wolfgang Warner

--
"Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

Search Discussions

  • Dieter Späth at May 7, 2007 at 9:18 am
    -------- Original-Nachricht --------
    Datum: Mon, 07 May 2007 09:44:28 +0200
    Von: quarky@quantentunnel.de
    An: dbix-class@lists.rawmode.org
    Betreff: [Dbix-class] automatically applying db-functions on specific fields
    Hi,

    hope this is not too much a newby question, but it really bugs me.

    I have a table with encrypted information in one field. Actually it is
    encrypted with the aes_encrypt function of mysql. I would like to use this
    function to automatically encrypt and decrypt the values in this field using
    the database function, but I do not find any hook where I can attach the
    function to DBIx::Class. I imagine it to work just automatically everytime I
    insert, update or read values.

    Any suggestions? (In the unlikely event, that someone might know how to
    emulate aes_encrypt by using perl on the client side, I would appreciate this
    information too, but I am still interested in usage of db-functions on
    fields anyways)

    Best Regards
    Wolfgang Warner
    For reading try this:

    $rs = $schema->resultset('Employee')->search(undef, {
    +select => [ \"AES_DECRYPT(field_name,'password')" ],
    +as => [qw/ decrypted_field_name /],
    });

    More Information can be found

    http://search.cpan.org/~jrobinson/DBIx-Class-0.07005/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures

    "Using database functions or stored procedures"

    &&

    perldoc SQL::Abstract

    --
    "Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
    Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

    _______________________________________________
    List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
    Wiki: http://dbix-class.shadowcatsystems.co.uk/
    IRC: irc.perl.org#dbix-class
    SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
    Searchable Archive:
    http://www.mail-archive.com/dbix-class@lists.rawmode.org/
    --
    FastJack

    "Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
    Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail
  • Oleg Pronin at May 7, 2007 at 11:05 am
    For this to be done automatically,
    in your result source Employee:

    __PACKAGE__->resultset_attributes({
    +select => [ \"AES_DECRYPT(field_name,'password')" ],
    +as => [qw/ decrypted_field_name /],
    });


    2007/5/7, "Dieter Späth" <SHAD0WRUNNER@gmx.de>:

    -------- Original-Nachricht --------
    Datum: Mon, 07 May 2007 09:44:28 +0200
    Von: quarky@quantentunnel.de
    An: dbix-class@lists.rawmode.org
    Betreff: [Dbix-class] automatically applying db-functions on specific
    fields
    Hi,

    hope this is not too much a newby question, but it really bugs me.

    I have a table with encrypted information in one field. Actually it is
    encrypted with the aes_encrypt function of mysql. I would like to use this
    function to automatically encrypt and decrypt the values in this field using
    the database function, but I do not find any hook where I can attach the
    function to DBIx::Class. I imagine it to work just automatically
    everytime I
    insert, update or read values.

    Any suggestions? (In the unlikely event, that someone might know how to
    emulate aes_encrypt by using perl on the client side, I would appreciate this
    information too, but I am still interested in usage of db-functions on
    fields anyways)

    Best Regards
    Wolfgang Warner
    For reading try this:

    $rs = $schema->resultset('Employee')->search(undef, {
    +select => [ \"AES_DECRYPT(field_name,'password')" ],
    +as => [qw/ decrypted_field_name /],
    });

    More Information can be found


    http://search.cpan.org/~jrobinson/DBIx-Class-0.07005/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures

    "Using database functions or stored procedures"

    &&

    perldoc SQL::Abstract

    --
    "Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
    Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

    _______________________________________________
    List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
    Wiki: http://dbix-class.shadowcatsystems.co.uk/
    IRC: irc.perl.org#dbix-class
    SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
    Searchable Archive:
    http://www.mail-archive.com/dbix-class@lists.rawmode.org/
    --
    FastJack

    "Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
    Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

    _______________________________________________
    List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
    Wiki: http://dbix-class.shadowcatsystems.co.uk/
    IRC: irc.perl.org#dbix-class
    SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
    Searchable Archive:
    http://www.mail-archive.com/dbix-class@lists.rawmode.org/
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070507/14485c35/attachment.htm
  • Oleg Pronin at May 7, 2007 at 11:09 am
    To fetch the value you need to call
    $obj->get_column('decrypted_field_name'),
    not $obj->decrypted_field_name. See docs for info on how get
    $obj->decrypted_field_name working too.


    2007/5/7, Oleg Pronin <syber.rus@gmail.com>:
    For this to be done automatically,
    in your result source Employee:

    __PACKAGE__->resultset_attributes({
    +select => [ \"AES_DECRYPT(field_name,'password')" ],
    +as => [qw/ decrypted_field_name /],
    });
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070507/0d0625df/attachment.htm
  • Oleg Pronin at May 7, 2007 at 11:17 am
    Perl-side solution is very simple - use InflateColumn plugin
    (DBIx::Class::InflateColumn).

    2007/5/7, quarky@quantentunnel.de <quarky@quantentunnel.de>:
    Any suggestions? (In the unlikely event, that someone might know how to
    emulate aes_encrypt by using perl on the client side, I would appreciate
    this information too, but I am still interested in usage of db-functions on
    fields anyways)
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070507/75c8fe77/attachment-0001.htm

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedMay 7, '07 at 8:44a
activeMay 7, '07 at 11:17a
posts5
users3
websitedbix-class.org
irc#dbix-class

People

Translate

site design / logo © 2022 Grokbase