I want to implement a token-based authentication system that is strongly resistant to insider attacks. That is, someone who has read-only access to the code and the database shouldn't be able to use someone else's token. It means that JWT is not the way to go, because if someone knows the secret key of the token signatures (that is stored in plain text on the server) they can compromise the tokens of everybody (provided that they know the payload of the user). Besides, I want something simpler to implement than OpenID/OAuth.
Thus, I thought about having access tokens for each user stored in the database. Each one of them would have a time-to-live of 1 day. But if you know the token of an user, 1 day is more than enough to do something nasty with it, like deleting the account for example. Hence, I want to add another security layer and hash the tokens in the database.
The problem is, the password_hash() function of PHP uses salt so it is not deterministic. What it means is that I cannot do that:
// Insert a new user
$token = generateToken(size=32);
$tokenHash = password_hash($token, PASSWORD_DEFAULT);
$sql = "
INSERT INTO `User` (username, password_hash, token_hash)
VALUES (:username, :password_hash, :token_hash)
";
$stmt->prepare($sql);
$stmt->execute([
":username" => $username,
":password_hash" => $paswordHash
":token_hash" => $tokenHash
]);
// Find an user with their token
$tokenHash = password_hash($token, PASSWORD_DEFAULT);
$sql = "SELECT * FROM `User` WHERE token_hash = :token_hash";
$stmt = $db->prepare($sql);
$stmt->execute([":token_hash" => $tokenHash]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
Indeed, $tokenHash will in practice never be equal to the value stored in the database, and the correct function to do this comparison is password_verify(). But retrieving all the users of the database and using password_verify() for each of them would be way too inefficient.
What is the correct way to find an user (or more generally a database record) using an hashed value in his columns?
Thank you for your help.
As you already found out, one cannot use a salt to create searchable hashes. Hashing with a fast unsalted algorithm like SHA-256 is perfectly fine though, as long as your tokens are very strong. This means the token should contain at least 24 random characters of the alphabet 0..9, a..z, A..Z.
Salting and key-stretching are measures for weak user passwords, to prevent dictionary attacks and to make brute-force attacks unpractical. Strong random tokens can be hashed without this precautions.
I wrote some example code how such tokens could be generated.