Why do you choose hash passwords at the application level and not use mysql SHA2 ()?

Question

I always hashed passwords in php (or something else) before embedding them in the database. Today I discovered that mysql 5.5 has built-in hashing, 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

This is not password hashing; but if it were (if you missed an unencrypted password there) ...

The database connection protocol is usually not encrypted. One of the reasons not to use this functionality is that you send the password in text form via cable. If someone controls the router along the path between your web server and the database, they can intercept this data.

Because of this, you will introduce weaknesses in the security of your system.

Answer 2

Database independence is good. I consider all DBMSs as simple SQL mechanisms.

Added

Nowadays, cool kids don't even use SQL. Instead, an intermediate Object-Relational Mapping (ORM) is used. For example, ActiveRecord in Rails or similar .

PHP ORM

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

Last thought

Finally, in terms of performance, a DBMS is often the least extensible. - The application layer can be cloned much faster than overlaying the data warehouse. Thus, your mileage may vary, but I would be careful, believing that moving more functionality to the DBMS level would be a win for the whole system.

Rather, the opposite is often the case - moving functionality from a DBMS, where it makes sense. For example, the widespread use of MemCache today, despite the DBMS systems, including its own request caches.

Answer 3

The main problem is that you cannot iterate over your hash function if you do this in MySQL. If you do not iterate your hash function, you will be vulnerable to offline brute-force attacks because SHA2 is very fast.

You really should use a well-known password storage feature like bcrypt or PBKDF2, which is probably not supported in MySQL.

See this article for a good discussion on storing passwords and why you need to use the nice slow feature.