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>>')


(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.

Geben Sie einen Kommentar ab


Bisher hat niemand diese Seite kommentiert.

RSS Feed für die Kommentare auf dieser Seite | RSS feed für alle Kommentare