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.

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.

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

---

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

Basically, you just do a set_sql() with SQL that you need, using placeholders, Class::DBI creates a function for you to use, and you call it with parameters. Nice and easy.

This is not easy to do with Class::DBI. You are probably better off not using the MySQL PASSWORD() command and using perl's crypt() to achieve the same results (or even better, us MD5 or SHA1). And a quote from the MySQL docs suggests you shouldn't be using this function anyway:

Note: The PASSWORD() function is used by the authentication system in MySQL Server, you should NOT use it in your own applications

If you want to handle this on the perl side with MD5 or SHA1 hashes you can use the trigger support in Class::DBI to handle the UPDATE statement above (a before INSERT or UPDATE trigger can hash the password field before it is sent to the database).

As for the SELECT statement, I would probably just create a custom method in your Class::DBI module that checked the password for you. It could hash the password and do the SQL call and return true or false. Your call to check a password could then simply be My::User->check_password($username, $password). There are examples in the Class::DBI docs that explain how to create your own custom SQL statements and wrap them up in a method.

- Cees

Side note. If you are changing a MySQL database password, then be aware that MySQL has the much simpler syntax SET PASSWORD=PASSWORD('mypassword'), which allows non-anonymous users to change their password.

What you are doing with the "user" table looks suspiciously complex, unnecessary, and possibly insecure. Look at the manual.

Not exactly. I'm trying to change a PASSWORD() encripted value inside a table that belongs to my system, not to MySQL. Thank you for the insight and tip. Indeed, I've decided to use the triggers approach suggested above, but can't figure out what I'm doing wrong with the triggers, yet.

---

"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

---