php - Why mysqli_connect() must receive the password parameter with no encryption?

Question

I created a user to use my database

CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_pass';
GRANT ALL PRIVILEGES ON * . * TO 'my_user'@'localhost';
FLUSH PRIVILEGES;

The pass was stored in encrypted mode on the mysql.user table.

But when i use the mysqli_connect() method, I find that when I give the pass as parameter, I must give the non-encrypted, original value that I used on the query if I don′t want to get the famous "Connect failed: Access denied for user 'my_user'@'localhost' (using password: YES)".

But that is actually a different string value that the one stored on the database (encrypted version).

How is it? Would not be more secure to give as parameter the encrypted version in case someone access the .PHP file where I establish the connection? Why mysqli_connection doesn′t accept it? Is there a way to do it?

Answer 1

You as a user don't know how the password is encrypted (it's actually not encrypted at all, but hashed).

You just pass the password, and MySQL can perform the same hashing as on the original password and compare it to what is stored. If you pass the hashed version, the whole benefit of hashing is gone: If somebody gets the hashes from the server, they can just use those to log in as if they were normal passwords. The hash has then become the password.

Added benefit is that MySQL, because it has the original password, could re-hash it with a better algorithm, add a bit of salt and store that improved version. If it never gets the original, that can't be done. PHP's password functions support this as well. You can check with password_needs_rehash if a hashed password is still hashed properly, and update your database if it isn't.

So, for security, you will need to take other measures. These are at least:

• Store the password in an include file that lives outside of the document root. That way, nobody can open that file directly.
• You can prevent include files to be opened without being included (for instance by checking for a define that was set in index.php). That's nice, but if PHP fails due to a configuration error, people can just browse the file's source, so stick the the previous rule.
• Always make a special database user. Don't use root. Give this user just enough right to operate the database, but no more. No rights to drop tables for instance.
• Always give that user a unique password. You don't have to remember this password. Just generate random garbage with plenty of characters.
• Change the password regularly. Maybe you could even script that and store the updated password in the config.