in reply to How to call MySQL's PASSWORD() from Class::DBI?

Answer to actual question: Use Class::DBI's before_create, before_set_column, before_update, and select triggers to modify the plaintext value using PASSWORD().

Answer to larger (and unasked) design question: Don't use MySQL's PASSWORD() if you don't have to. It doesn't use salt, making it much easier to perform dictionary attacks.

What I've done for similar situations is a table that stores a primary key id (and auto_increment in MySQL), a 32-bit salt value (simple integer will do), and a CHAR(40) column that stores a hex-encoded SHA1 hash. Creating the hash from the password and salt is done like this (in pseudo-code):

hex( SHA1( salt . SHA1(salt . plaintext_passwd )))

[download]

You can add some other known-constant data in there if you like.

In Class::DBI, I add extra methods to the table's class called hash_passwd() and generate_salt(), which do the obvious things. I consider breaking these into seperate methods to be essential, because you may need to change these two things on a whim (hash_passwd() because you never know when a flaw might be found in the hash algorithm you're using, and generate_salt() because you may need to change the size of the salt (which bit me recently when we switched from a DBMS that supported unsigned integer fields to one that didn't)). From there, you can use the triggers above to change the password as necessary before it gets to the database. It may also be useful to support change_passwd() and check_passwd() methods.

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

: () { :|:& };:

Note: All code is untested, unless otherwise stated

Replies are listed 'Best First'.
Re: Re: How to call MySQL's PASSWORD() from Class::DBI?
by Anonymous Monk on Dec 23, 2003 at 21:50 UTC
    Don't use MySQL's PASSWORD() if you don't have to. It doesn't use salt, making it much easier to perform dictionary attacks.
    It's also weak against brute force attacks.
[reply]
Re: Re: How to call MySQL's PASSWORD() from Class::DBI?
by monsieur_champs (Curate) on Dec 24, 2003 at 11:45 UTC

    Dear hardburn

    Thank you for care and answer that "larger and unasked question". This system is a legacy, and I need to maintain it as best as I can. The system is like a box full of (quite) poor design constructs and (really) bad project choices.

    I don't know about the implications of changing the method used to keep the passwords, but I will study this and change it as soon as possible. I not finished building the system documentation yet.

    Is this a high priority task or I can live with it for some time? The system is exposed at the internet, firewall protected. I would like to read more comments about this matter.

    Thank you very much and Merry Christmas.

    "In few words, translating PerlMonks documentation and best articles to other languages is like building a bridge to join other Perl communities into PerlMonks family. This makes the family bigger, the knowledge greather, the parties better and the life easier." -- monsieur_champs
[reply]

      I don't know your situation entirely, but guessing from the fact that this is an older system, I would bet that there are a lot of other problems with it, and MySQL's PASSWORD() function is probably the least of them. In fact, you could probably store plaintext passwords and wouldn't lose much (though since encrypted passwords are already there, you might as well keep them). This isn't to say it should never change, but you could probably spend time on other parts of the system first.

      Again, this is just from someone completely unfamiler with your situation, so take all of the above with the properly-sized chunk of salt.

      ----
      I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
      -- Schemer

      : () { :|:& };:

      Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom