Using PHP to compare MySql Password()

Question

I have found this post helpful MySQL password() function to PHP but I am having trouble applying the solution offered there to my problem.

A password was stored in a Mysql using Password(). I want to adapt this script to compare the entered password with the one stored in the database, rather than use the 'crypt()' function.

    public function authenticate($user,$pass) {
        $mysqli = new mysqli(DBHOST,DBUSER,DBPASS,DB);
        if ($mysqli->connect_errno) {
        error_log("Cannot connect to MySQL: " . $mysqli->connect_error);
        return false;
        }
        $safeUser = $mysqli->real_escape_string($user);
        $incomingPassword = $mysqli->real_escape_string($pass);
        $query = "SELECT * from users WHERE username ='{$safeUser}'";
        if (!$result = $mysqli->query($query)) {
            error_log("Cannot retrieve account for {$user}");
            return false;
        }

        // Will be only one row, so no while() loop needed
        $row = $result->fetch_assoc();
        $dbPassword = $row['password'];
        if (crypt($incomingPassword,$dbPassword) != $dbPassword) {
        error_log("Passwords for {$user} don't match");
        return false;
        }
        $this->id = $row['id'];
        $this->firstName = $row['first_name'];
        $this->lastName = $row['last_name'];            
        $this->username = $row['username'];
        $this->email = $row['email'];
        $this->dateJoin = $row['dateJoin'];
        $this->school = $row['school'];
        $this->level = $row['level'];
        $this->isLoggedIn = true;
        $this->_setSession();
        return true;
    } //end function authenticate

Is there an easy way to adapt this script? Do I just add

AND `password` = PASSWORD('{$incomingPassword}')

to my query? This seems a little clumsy.

You need to be using prepared/parameterized queries. Otherwise you are wide open to SQL injection attacks, and your code randomly failing when folks use apostrophes and what not. — Brad
– Brad, Commented Mar 20, 2014 at 22:25
real_escape_string is perfectly sufficient against SQL injection. — M Miller
– M Miller, Commented Mar 20, 2014 at 22:25
You should see the "note" in the manual for the password function: dev.mysql.com/doc/refman/5.0/en/… I would recommend using the crypt function and storing the resultant hash in the database. You can use salting techniques and other better methods as well. — M Miller
– M Miller, Commented Mar 20, 2014 at 22:27
yes, you can just use 'AND password = PASSWORD('{$incomingPassword}')' to where clause. or you can use build-in md5 function, like " where password=md5('{$incomingPassword}')" — Ming M Zheng
– Ming M Zheng, Commented Mar 20, 2014 at 22:28
MD5 is not recommended to use as a password storage method. @PaulManning It's considered too fast. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 21, 2014 at 0:12

martinstoeckli · Accepted Answer · 2014-03-21 09:19:10Z

1

Are you really sure the passwords where hashed with the MySql Password() function, because this function is not meant to be used in applications? It is not possible to store passwords safely and verify passwords in an SQL-query directly.

You really should use a slow hashing function like BCrypt, and salting is mandatory. That means, that you need a two step process, first get the stored password hash by username with an SQL-query, then extract the salt from the hash and do the verification.

The recommended way to hash passwords with PHP is the new function password_hash():

// Hash a new password for storing in the database.
// The function automatically generates a cryptographically safe salt.
$hashToStoreInDb = password_hash($password, PASSWORD_BCRYPT);

// Check if the hash of the entered login password, matches the stored hash.
// The salt and the cost factor will be extracted from $existingHashFromDb.
$isPasswordCorrect = password_verify($password, $existingHashFromDb);

If you are interested in more in-depth information about this topic, you can have a look at my tutorial about safely storing passwords.

answered Mar 21, 2014 at 9:19

martinstoeckli

24.2k7 gold badges63 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Russell Over a year ago

Thanks for your response and excellent tutorial pitched right at my level!

Collectives™ on Stack Overflow

Using PHP to compare MySql Password()

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related