#1
  1. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014

    Protecting password hashes


    Hi,

    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.

    sql Code:
    DELIMITER //
     
    CREATE
    	FUNCTION get_salt(email_addr VARCHAR(254))
    RETURNS CHAR(29)
    SQL SECURITY DEFINER
    BEGIN
    	DECLARE salt CHAR(29);
    	SELECT
    		LEFT(password, 29)
    	INTO
    		salt
    	FROM
    		users
    	WHERE
    		email_address = email_addr
    	;
    	RETURN salt;
    END //
     
    DELIMITER ;

    sql Code:
    DELIMITER //
     
    CREATE
    	FUNCTION authenticate_user(email_addr VARCHAR(254), hash CHAR(60))
    RETURNS BOOLEAN
    SQL SECURITY DEFINER
    BEGIN
    	DECLARE success BOOLEAN DEFAULT 0;
    	SELECT
    		EXISTS (
    			SELECT
    				1
    			FROM
    				users
    			WHERE
    				email_address = email_addr
    				AND password = hash		
    		)
    	INTO
    		success
    	;
    	RETURN success;
    END //
     
    DELIMITER ;


    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:

    PHP Code:
    $authenticated false;

    $database->query('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
    $database->beginTransaction();

    // get salt
    $salt_stmt $database->prepare('
        SELECT get_salt(:email_address)
    '
    );
    $salt_stmt->execute(array(
        
    'email_address' => $email_address
    ));
    $salt $salt_stmt->fetchColumn();

    // check password hash
    if ($salt)
    {
        
    $auth_stmt $database->prepare('
            SELECT authenticate_user(:email_address, :hash);
        '
    );
        
    $auth_stmt->execute(array(
            
    ':email_address' => $email_address
            
    ':hash' => crypt($password$salt)
        ));
        
    $authenticated =
            
    $auth_stmt->fetchColumn() === 1;
    }

    $database->commit();

    if (
    $authenticated)
    {
        ...

    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.

    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.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    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
    Code:
    SELECT 1, 2, 3 FROM table WHERE field = "injection here" UNION SELECT resetpassword("admin@example.com", "n3wp455w0rdh45h"), 2, 3
    Which is significantly worse than the current situation where you'd have to either:
    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

    [edit] 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.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo