Why would you choose to hash passwords in the application layer, rather than use mysql SHA2()?

Question

I've always hashed passwords in php (or whatever) before inserting it into the database. Today I discovered that mysql 5.5 has hashing built in, so I could do something like this:

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| user_id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_uname      | varchar(63)  | YES  | UNI | NULL    |                |
| user_password   | binary(32)   | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

--set password
UPDATE users SET user_password=UNHEX(SHA2(CONCAT('username','salt'), 256))\
WHERE user_id = 1;

-- validate password 
SELECT (SELECT user_password FROM users WHERE user_id=1) = \
SHA2(CONCAT('username','salt'), 256);

Is there a reason this might be a bad idea? (I am not a mysql expert by any means)

Answer 1

Database independence is a good thing. I treat all DBMS systems as simple SQL engines.

Added

These days, the cool kids don't even use SQL. Instead, an intermediate Object-Relational Mapping (ORM) layer is used. Eg ActiveRecord in Rails or similar.

PHP ORM

A SO Question about ORM libraries for PHP. No SQL!

A final thought

Lastly, from a performance point of view, it is often the DBMS that is least expandable. -- The app layer can be cloned much faster than sharding the data store. So your mileage may vary, but I would be careful about assuming that moving more functionality to the DBMS layer would be a win for the total system.

Rather, often the opposite--moving functionality out of the DBMS where reasonable. Eg extensive use of MemCache these days despite DBMS systems including their own query caches.

Answer 2

That isn't hashing a password; but if it were (if you were passing a plaintext password there)...

The database connection protocol generally is not encrypted. One reason to not be using this functionality is that you are sending the password in plaintext over the wire. If someone has control of a router along the path between your webserver and the database, they could intercept this data.

Because of this you would be introducing a weakness in the security of your system.

Answer 3

The main problem is that you cannot iterate your hash function if you do it in MySQL. Failing to iterate your hash function leaves you vulnerable to offline brute force attacks because SHA2 is very fast.

You should really use a well-known password storage function such as bcrypt or PBKDF2, which is probably not natively supported in MySQL.

See this article for a good discussion of password storage and why you need to use a good, slow function.