Comparing hashed password in query

Question

(PHP and MySQL are not my area of expertise, so this might be a simple one-liner).

I just grabbed a piece of code from the Internet that performs user registration. Among other things, it hashes password using these lines:

$salt = dechex(mt_rand(0, 2147483647)) . dechex(mt_rand(0, 2147483647)); 
$password = hash('sha256', $_POST['password'] . $salt); 
for($round = 0; $round < 65536; $round++){ $password = hash('sha256', $password . $salt); }

which, if I understand correctly, is using SHA-256 to store the hash of password and salt values in the password field.

Now I need to write a query that when supplied with the username and password, returns the UserID value. Reading MySQL manual, it looks like there is no built-in function for SHA-256 (old SHA and MD5 are supported). Or is that not the case?

SELECT 
     id 
FROM 
     users 
WHERE 
     (username = @UN) 
     AND (password = SHA256(CONCAT(@PWD, salt)))

This obviously doesn't work because SHA256 is not defined.

Edit

Just found out that there is SHA2 in MySQL 5.5.5 that can do SHA-256:

SELECT 
     id 
FROM  
     users 
WHERE 
     username = @UN 
     AND password = SHA2(CONCAT(@PWD, salt), 256)

but this also doesn't return any rows. Maybe I'm not doing exactly what the register page above is doing.

Edit 2

I was prbably not clear enough. I'm querying the database from .NET, not PHP. The PHP code I included was just to show how it was being stored in the first place. This means I have to rely on MySQL's capabilities for doing hashing, or do it in .NET maybe, if MySQL can't do it in a query.

Edit 3

I ended up fetching salt value against user name in the first step, then hashing in .NET exactly the same way as done in PHP code above and then passing that to MySQL to get user id.

You "grabbed a piece of code" without knowing what it does to handle securely storing your passwords? That's insanely irresponsible. — user229044, May 31 '16 at 12:05
Are you saving the salt in users table? if yes get the salt value and perform the same hashing using php for password entered by user and pass the password to the my sql query. — Srinivasa Reddy, May 31 '16 at 12:07
@SrinivasaReddy What? No one will save salt in users table EVER. — Justinas, May 31 '16 at 12:08
@Justinas You don't know what salting is for. Storing your per-user salt in the users table is fine. — user229044, May 31 '16 at 12:11
@meagar: You saved me a heart attack with your last comment. I'm storing salt in the table. — dotNET, May 31 '16 at 12:12
The original code is performing an additional 65536 rounds of SHA256 encryption after the original hash. It'd be possible to do that purely in MySQL, but it'll be a lot clearer in code. — iainn, May 31 '16 at 12:17
@dotNET Secure salt storage is the least of your worries. Use a real password hashing algorithm. The SHA family of functions are not meant for this purpose. — user229044, May 31 '16 at 12:17
Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure that you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, May 31 '16 at 13:16

Justinas · Answer 1 · 2016-05-31T12:13:07.667

2

When confirming email and password you request user by email

SELECT * FROM users WHERE username = :username;

And than compare database password with passed password:

if ($db->password == $this->hash($_POST['password'])) {
    //Match
} else {
    //Does not match
}

Or you can pass hash to MySql from PHP side:

SELECT id FROM users WHERE username = :username AND password = :passwordHash

Best would be to use PHP function password_hash() that will generate salt for you and make password something like $2y$10$.vGA1O9wmRjrwAVXD98HNOgsNpDczlqm3Jq7KnEd1rVAGv3Fykk1a.
To verify password you just call password_verify($_POST['password'], $dbPasswordHash).

More about passwords

edited May 31 '16 at 12:13

answered May 31 '16 at 12:07

Justinas

41,402
5
66
96

Maybe I was not clear enough. I'm accessing the database from .NET, not PHP. The PHP code I included was just to show how it was being stored in the first place, which means I have to rely on MySQL's capabilities for doing hashing (or do it in .NET maybe?). – dotNET May 31 '16 at 12:14
1

@dotNET You marked question as PHP question and even mentioned that you use PHP, you even show example of PHP code. Nowhere you mention `.NET`... – Justinas May 31 '16 at 12:18

score 0 · Answer 2 · edited May 23 '17 at 11:52

The generally excepted practice is to generate a hash from the user supplied password in your application code, then check that hash against the one stored in the db.

Also, php has build in functions to handle this, and they will almost certainly be better designed than some home made option, especially one you copied without fully understanding.

To save the password initially:

$hashed = password_hash($_POST['password']);
DB::insert('users',['email'=>$email, 'password'=>$hashed]);

To check at login:

$user = DB::select('users',['email=?', $_POST['email']]);
if($user && password_verify($_POST['password'], $user['password'])){
   //good to go
}

http://php.net/manual/en/function.password-hash.php

http://php.net/manual/en/function.password-verify.php

EDIT as per your comment, if you need to varify the passwords in .NET application, the following SO answer might be usefull:

looking for c# equivalent of php's password-verify()

Thanks. I ended up fetching salt value against user name in step 1, then hashing in .NET exactly the same way as done in PHP code above and then passing that to MySQL to get user id. — dotNET, May 31 '16 at 14:12

Comparing hashed password in query

Edit

Edit 2

Edit 3

2 Answers2