Is it considered good or bad practice to use MySQL's password function to hash passwords used by an application? I can see pros and cons. I'm curious if there is a general consensus on whether it is good or bad.
The docs for MySQL's PASSWORD() function states:
The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications.
Read "You're Probably Storing Passwords Incorrectly" for better advice on hashing and storing passwords.
MD5 and SHA-1 are considered to be too weak to use for passwords. The current recommendation is to use SHA-256.
I contributed a patch to MySQL to support a SHA2() function, and the patch was accepted, but since their roadmap has changed it's not clear when it will make it into a released product.
In the meantime, you can use hashing and salting in your programming language, and simply store the result hash digest in the database. If you use PHP, SHA-256 is available in the hash() function.
update: MySQL 5.5.8 was released in December 2010, and that release contains support for the SHA2() function.
update 2: In MySQL 8.0, the PASSWORD() function has been removed, so you really can't use it for application passwords.