April 17th, 2013, 04:02 PM
Protecting password hashes
when it comes to hashing passwords, there's a broad consesus that bcrypt (or similar algorithms) creates sufficently strong hashes that withstand average brute force attacks.
However, there's still one big problem: The hashes themselves are not very well protected. The application has full access to all hashes, so as soon as somebody manages to do an SQL injection, he/she can simply download the hashes and crack them locally (or publish them). bcrypt does slow that down at lot, but if the passwords aren't strong enough, they won't last very long.
Interestingly, there is a way to restrict access to the password hashes and use the database as another layer of security:
The idea is to disallow direct read access to the hashes and only let it check particular passwords through a database function. So it's no longer possible to actually get the hashes. You can only check if a certain hash is valid for a particular user. The hashes themselves never leave the database.
Start by revoking the SELECT password privilege from the application role. Create a new role for elevated privileges (with a very strong password!) and give it access to the password column. As this role, create two functions: one for getting the salt part from a bcrypt hash (the first 29 characters) and one for checking a hash for a user. The functions must be declared as SECURITY DEFINER so that they run with the privileges of the current role.
FUNCTION get_salt(email_addr VARCHAR(254))
SQL SECURITY DEFINER
DECLARE salt CHAR(29);
email_address = email_addr
FUNCTION authenticate_user(email_addr VARCHAR(254), hash CHAR(60))
SQL SECURITY DEFINER
DECLARE success BOOLEAN DEFAULT 0;
email_address = email_addr
AND password = hash
In the application, you first have to fetch the salt. You use it to hash the password and then check it with the second function.
This should be done in a transaction to make sure the stored hash doesn't change between getting the salt and checking the calculated hash:
So instead of letting the application check the hashes -- which requires full access --, this is done by the database itself. The application merely calculates the hash based on the salt and sends it to the database.
$authenticated = false;
$database->query('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
// get salt
$salt_stmt = $database->prepare('
'email_address' => $email_address
$salt = $salt_stmt->fetchColumn();
// check password hash
$auth_stmt = $database->prepare('
SELECT authenticate_user(:email_address, :hash);
':email_address' => $email_address
, ':hash' => crypt($password, $salt)
$auth_stmt->fetchColumn() === 1;
This idea isn't new. For example, PostgreSQL has a native bcrypt implementation, so you could actually do the whole authentication process within the database system. However, this means passing the plaintext password to the database, which obviously increases the risk of exposing it. It could be leaked through log files or error messages. So the approach above is probably more secure.
In any case: This is all unverified and untested (I mean actual tests, not me running it a few times on my PC). So I don't recommend using it for anything but experiments.
Last edited by requinix; April 19th, 2013 at 05:33 PM.
April 18th, 2013, 11:55 AM
Only problem with this I see is password resets. If the password field is hidden from the code then there must be a stored procedure somewhere to update it, and it must be given the new (hashed) password. Which means that any SQL injection point can be used to easily reset anyone's password, like
Which is significantly worse than the current situation where you'd have to either:
SELECT 1, 2, 3 FROM table WHERE field = "injection here" UNION SELECT resetpassword("firstname.lastname@example.org", "n3wp455w0rdh45h"), 2, 3
a) Find and break the password - so far impossible
b) Discover a place in code using mysqli_multi_query - I've never actually seen it used
c) Find an injection point of an UPDATE query specifically on the user table - requires planetary alignments
 There may also be a procedure to change the password which can require the old password to confirm, but for resetting the password that's not an option.
Last edited by requinix; April 19th, 2013 at 05:32 PM.
April 19th, 2013, 08:26 AM
This would indeed be an issue if you used a function to update the password hashes. However, there's no reason to do that, because it's neither necessary nor sensible. The application can keep the UPDATE privilege despite not having read access. And it wouldn't even make sense to wrap the UPDATE in a function, because that simply doesn't improve security: If the application can update arbitrary rows through a function, you might as well allow it to do raw UPDATEs.
But I agree that functions can be evil if they have side effects, because suddenly you're able to alter data in (arbitrary) SELECT statements. In MySQL, it would probably be a good idea to always use a procedure rather than a function if a certain action has side effects. Since procedures require a separate CALL statement, this would prevent them from being "embedded" in other queries.
And of course password resets are problematic by nature. Or rather: setting the password reset tokens is (the reset itself requires the token). I think the only solution is to keep this out of the application completely.
My plan was to use separate cron jobs for tasks like generating a reset token. The cron jobs aren't publicly accessible and don't take any external input. They use a privileged database role to do critical actions.
So resetting a password would work like this:
- The application adds a reset request, but it does not generate the token.
- A cron job loops through the requests, generates a secret token for each one, sends it to the corresponding email address and stores a hash of the token in the database.
- The user passes the plaintext token to the application, which updates the password for the corresponding user.
Since the application doesn't have access to the reset tokens, resetting a password via an SQL injection requires knowing a particular token -- which means the whole injection is pretty much pointless.
Those cron jobs would also allow you to hide the email addresses from the application and prevent them from being leaked.