There may be cases where you want to authenticate against a hash generated in PHP and stored in MySQL. It becomes unhandy if the hash is salted. The example below shows how to authenticate against SSHA256 (read: salted SHA2 with length of 256) using only SQL.

Hashed Passwords stored by PHP's pack function marked as {SSHA256}...

See the following MySQL dump of table accounts:

+-------+-----------------------------------------------------------+
| login | password                                                  |
+-------+-----------------------------------------------------------+
| test  | {SSHA256}zO6P0kn4mday5xnjQk1sC2kjBD9tLrsrZGs15N3kUrunIilV |
| admin | {SSHA256}JT3cTsF4i8O+cPZyRu+5JZ3Swr8NswrYAH2vWL62BHRerprx |
+-------+-----------------------------------------------------------+

The example values here are stored by Zend Framework:

// base64 decode hash
$decodedHash = base64_decode($hash);

// get salted hash of password
$originalHash = substr($decodedHash, 0, -4);

// get salt
$salt = substr($decodedHash, -4);

$algo = $this->_getHashAlgoByLength(strlen($originalHash));

// recalculate salted hash of provided cleartext password
$validatedHash = pack("H*", hash($algo, $password . $salt));

Note: The values are encoded into a binary string by PHP's function pack("H*", ...).

Authenticate against {SSHA256} by using SQL only

The follwing SQL statement makes use of Ian Gulliver's base64 functions for MySQL. MySQL brings it own functions from versions >= 5.6. Please note that MySQL SHA2 function may return nonbinary strings in versions < 5.5.6. See the following statement:

SELECT login FROM accounts 

WHERE (login_name = '<<user>>') 

AND (

(LEFT(BASE64_DECODE(SUBSTRING(password FROM 10)),32)) =
UNHEX(SHA2(CONCAT('<<secret>>', RIGHT(BASE64_DECODE(SUBSTRING(password FROM 10)), 4)), 256)) 

)

Any successful authentication will return the login name. If no values are returned authentication failed.

Hope this will save you some time.