Comparing hashed password in query
<p>(PHP and MySQL are not my area of expertise, so this might be a simple one-liner).</p>

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

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

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

<p>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?</p>

<pre><code>SELECT 
     id 
FROM 
     users 
WHERE 
     (username = @UN) 
     AND (password = SHA256(CONCAT(@PWD, salt)))
</code></pre>

<p>This obviously doesn't work because SHA256 is not defined.</p>

<h2>Edit</h2>

<p>Just found out that there is <code>SHA2</code> in MySQL 5.5.5 that can do SHA-256:</p>

<pre><code>SELECT 
     id 
FROM  
     users 
WHERE 
     username = @UN 
     AND password = SHA2(CONCAT(@PWD, salt), 256)
</code></pre>

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

<h2>Edit 2</h2>

<p>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.</p>

<h2>Edit 3</h2>

<p>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.</p>