How to use password() in sql?

Question

I am trying to verify user login my matching the input password to the password input by user

My insert query:

insert into login (Emp_id, Emp_Fname, Emp_Lname, Username, Password) values (5, 'TestFName', 'TestLName', 'Test', password('april'));

it stores the password as this value :

*72B46CDA233C759A88BEF81F59F66D78B26B2848

select * from login where password = '*72B46CDA233C759A88BEF81F59F66D78B26B2848';  -- this line shows me the result

select password('april'); -- this returns *72B46CDA233C759A88BEF81F59F66D78B26B2848

select * from login where password = 'password(april)'; -- this returns an empty set

Is there any alternative to this line of code?

Answer 1

I think you need to use:

select * from login where password = password('april');

So, don't quote the whole password function, just the argument to the function.

Answer 2

One cannot safely store passwords with pure SQL commands, instead a dedicated password-hash function of the development language should be used. In PHP this would be the functions password_hash() and password_verify() for the verification of the password.

Even more, MySql's password() function was never intended to be used with user passwords and is deprecated (will be removed in future versions). Have a look at the second note box in the documentation.

The reason why you cannot left the hashing to the SQL command is, that salted password hashes cannot be searched for in the database. The searching has to be done by user name only and afterwards one can verify the found password hash with the user input. A more in-depth explanation you can find in this answer.

Answer 3

https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_password says:

This function is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.

PASSWORD() is used by the authentication system in MySQL Server; you should not use it in your own applications.

That wasn't an idle warning. https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_password says:

This function was removed in MySQL 8.0.11.

So don't use PASSWORD() — unless you plan to never upgrade to MySQL 8.0.

---

Besides that, you have some problems in your code.

insert into login (Emp_id, Emp_Fname, Emp_Lname, Username, Password) 
values (5, 'TestFName', 'TestLName', 'Test', password('april'));

I wouldn't use password (or any other hashing function) in this way, because you still have the plaintext password in your SQL statement. This ends up getting logged in query logs and statement-based binary logs, so it's a security weakness. That is, anyone who can get access to your logs can inspect the passwords.

Instead, hash the password in your app, and then put the result of that hash into your SQL statement.

Which hashing function you use depends on the language you use to write your application code. @martinstoeckli mentions a couple of functions that are used by PHP developers, but those won't be the same for other programming languages. You don't mention which language you use.

Likewise, when you search for a login that has that password, it works if you search for a specific hash string, but this doesn't work:

select * from login where password = 'password(april)'; -- this returns an empty set

The reason is that you're searching for the string 'password(april)'. Putting an expression in quotes means to use that literal string — it won't execute the function and use the result of it.

Again, you don't want to calculate the hash using SQL anyway. That puts the plaintext password into query logs and is not good for security.

You want to produce the hash string in your app, and then use the hash string in searches, like your first example. But not using the PASSWORD() function — using some application code function.

select * from login where password = '*72B46CDA233C759A88BEF81F59F66D78B26B2848';

(The hash string above is based on your example. It's a hash produced by MySQL's PASSWORD() function, only as strong as a SHA1 hash, which is known to be unsuitable for passwords.)

---

Actually, my preferred method is not to search for a password at all. Search for the login, and return the password hash string that is stored in the database.

select password from login where user = 'billkarwin'

Then in the application code, compare the hash string you fetched from the database against the re-calculation of the hash string based on the user's input when they're trying to log in.