How can I check for 'hashed' and 'non-hashed' password to allow users to log in?

Adam
Adam used Ask the Experts™
on
I have an existing table of users where the user passwords have not been 'hashed'. I am updating a website which will use this table, and this updated website will use the hash function.

However, the existing table just displays the passwords as normal text and are visible in the table.  

My problem is that when older (existing) users try to log in with a password which has not been hashed, they can't log in, because (I think )  the password_verify function I am using is looking for a hashed password. When it doesn't see the hashed password, logon fails.

if(password_verify($password, $teacher['password']))  //i.e.Password Match - only works with the password hash function!
								{
								log_in_teacher($teacher);							

Open in new window



I understand that for security, all passwords should now be hashed, and this will apply to new users signing up to the site. However, for existing users whose passwords are not hashed, what would be the best approach for allowing them to login with their existing password? I'm thinking along the lines of using a variation of the password_verify function which also checks non hashed passwords, and at the same time, mailing existing members and ask them to login to update their passwords - which would then 'hash them'.

So I guess my question is how do I amend my function to allow hashed and non-hashed passwords to be checked?

Many thanks,

Adam
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
Perhaps a temporary two-stage sign-in process? First check the hash of the password entered and if that fails, check for a password match? If the latter succeeds, force the user to change their password and store the hash. You could combine this with an email requesting that the user changes their password to speed up the process of removing unhashed passwords.

This is assuming you don't know what is hashed and what is not in your database. If you do, can you not just replace the password with a hash and have some method of recording that you have done this so that you can force the user to enter a new and different password?
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Why not just hash the password you get from the db before you pass it to the password_verify function.

That way you don't mess with your existing process and after the passwords in the db have been hashed you can just remove the line that hashes the result.

$query = "SELECT * FROM users WHERE username=?";
$stmt = $db->prepare($query);
$stmt->execute($username);
$result = $stmt->fetch(PDO::FETCH_OBJ);
// CHANGE TO USE THE HASHING ALGO YOU NEED
$result->password =  password_hash($result->password, PASSWORD_DEFAULT); 

Open in new window

Now send it through the normal process
ste5anSenior Developer

Commented:
Imho, the truth lies between Martin's and Julian's answer:

You need to hash all existing passwords (lookup salt+pepper and bcrypt before). Then you change your login verification.

BUT: You must enforce a password change. Otherwise you haven't gained anything for your existing users. Cause the plaintext versions still exists in the backups.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark BradyPrincipal Data Engineer

Commented:
I had to do something similar years ago and it was very simple. Write a script to load all plain text passwords, md5() them and save back to the password field. Then in your login script after the user posts their plain text password you md5() that and check it against the hashed password in the database. If they don't match it is a failed login.
This way your users will not be required to change password and they will not be affected by your update.
Hi,

md5 is not enough secure, these day we use bcrypt with a cost and salt

check this for more info
https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
PHP has the built-in password_hash for this.

A simple script using PDO to read in your records, loop through them, and update to a hashed password:

$users = $db->query("SELECT id, password from users");

$update = $db->prepare("UPDATE users SET password = :password WHERE id = :id");
$update->bindParam("password", $password);
$update->bindParam("id", $id);

while ($user = $users->fetch()):
    $password = password_hash($user->password, PASSWORD_DEFAULT);
    $id = $user->id;
    $update->execute();
endwhile;

Open in new window

Don't forget - your password column in the database will need to be at least 60 character ... varchar(60), but the recomendation is 255.

Obviously make sure you don't hash passwords that have already been hashed.
ste5anSenior Developer

Commented:
btw, as Chris mention length of the password column:

when you use hashes, then there is no need for a upper limit of characters in a password, cause the hashing function reduces this. The hash itself has always the same length.
Well, the reason left for a value of a upper limit like 1024 characters is to avoid an attack on the hashing function (DoS). But here you can chose pretty large numbers.
AdamTrying to learn php

Author

Commented:
Hi all,

Many thanks for all the useful information here. Several solutions here I think.

I also tried updating my SQL table directly within phpMySQL using:

UPDATE teacher_table
SET password = MD5(password);

Open in new window


Which updated / encrypted the passwords but still wouldn't allow the user to login - maybe because my code didn't like MD5? When I manually took a text password and then encrypted it using an online PHP5 PASSWORD HASH GENERATOR, and put the resultant hashed password back into the table - this worked. However, as there are around 2000 entries, this would take too long to do manually.

Chris, Julian - I like your solutions as they seem fairly straight forward.

Chris,  I took your script and put it on a separate page, which I figured I could just load up to get the script to run. I've no experience with PDO, but I was assuming it didn't require me to add anything to my page for it to run. Anyway, I amended your script using my table name and DB Connection:

<?php require_once('private/initialize.php'); ?>
<?php include('header.php');?>
<?php include('navbar.php'); ?>


<section id="teacher_resources">
		 <div class="container">
		 	  <div class="row">
			  	   <div class="col-md-12">
				   <br /><br />
				   
				   <?php
					$users = $db_connection->query("SELECT id, password from teachers_table");

					$update = $db_connection->prepare("UPDATE teachers_table SET password = :password WHERE id = :id");
					$update->bindParam("password", $password);
					$update->bindParam("id", $id);

					while ($user = $users->fetch()):
						$password = password_hash($user->password, PASSWORD_DEFAULT);
						$id = $user->id;
						$update->execute();
					endwhile;

					?>
				    <h2>Passwords have been converted!</h2>
				    </div>
				</div>	
		 </div>		  		 
</section>

Open in new window




However, when I load up this page, nothing happens to the entries in the password column. Apologies, but am I missing something obvious?

Many thanks to everybody who's helped me with this.

Adam
AdamTrying to learn php

Author

Commented:
Sorry - should add that when I remembered to turn errors on, this was displayed:

Fatal error: Uncaught Error: Call to a member function bindParam() on boolean in /home/orango5/orangoteacher.com/passwordconvert.php:16 Stack trace: #0 {main} thrown in /home/orango5/orangoteacher.com/passwordconvert.php on line 16


Line 16 is -                   $update->bindParam("password", $password);


And passwordcovert.php is the single page with the script...
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Hey Adam,

First off, as has already been mentioned, don't use MD5!

Now for the script. The error you're receiving means that the prepare statement didn't work, so it returned false instead of a statement. As I said, this is based on PDO, so you will need to make sure your connection is a PDO one, and not a mysqli one. You haven't shown the code that creates the $db_connection, so here's a full example of the script you need:

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
 
$username = 'username';
$password = 'password';
$dsn      = 'mysql:host=localhost;dbname=yourDBName;charset=utf8mb4'; 
$options  = [
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
];

try {
    $db_connection = new PDO($dsn, $username, $password, $options);

    $users = $db_connection->query("SELECT id, password from teachers_table");

    $update = $db_connection->prepare("UPDATE teachers_table SET password = :password WHERE id = :id");
    $update->bindParam("password", $password);
    $update->bindParam("id", $id);

    while ($user = $users->fetch()):
        $password = password_hash($user->password, PASSWORD_DEFAULT);
        $id = $user->id;
        $update->execute();
    endwhile;

    echo "Passwords have been converted!";

} catch(PDOException $e) {
    die( $e->getMessage() );
}

Open in new window

Update the $username, $password and change the yourDBName part of the DSN to match your own database name.

If you want to test the code before actually doing the update, then comment out the $update->execute() line, and add the following:

var_dump($id, $password);

Now instead of the table actually being updated, you'll just a dump a list of values to the screen.

If you'd prefer a mysqli approach, let me know.
AdamTrying to learn php

Author

Commented:
Hi Chris,

Yes please. I'd really like to avoid using PDO, and just stick with mysqli. I'm almost getting to grips with mysqli written in procedural form, and even seeing in OO form makes me scratch my head more. I know PDO is supposed to be fairly similar, but for me it's different enough to make me sigh louder and longer.

My code which creates my mysqli connection is -

$db_connection = db_connect(); which calls...


function db_connect (){	
$connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME); //these are defined elsewhere
confirm_db_connect();
return $connection;	
	}

Open in new window



I'd be happy enough to update the database directly (no need for a dump). I've backed up my tables in the event of things not quite working.


Many thanks.


Adam
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This assumes your user table is called user and the password field is called password
<?php
$db_connection = db_connect();

// CREATE THE UPDATE STATEMENT
$query = "UPDATE `user` SET `password`=? WHERE `id` = ?";
$updateStatement = mysqli_prepare($db_connection, $query);
mysqli_stmt_bind_param($updateStatement, "sd", $password, $id);

// GET THE RECORDS FROM THE DB
$result = mysqli_query($db_connection, "SELECT `id`,`password` FROM `user` WHERE 1"); // REPLACE 1 WITH filter to filter out your already hashed password users");
while($row = mysqli_fetch_object($result)) {
    $password = password_hash($row->password, PASSWORD_DEFAULT);
    $id = $row->id;
    // UPDATE
    mysqli_stmt_execute($updateStatement);
}

Open in new window


EDIT [NB NB]
REMEMBER TO BACKUP YOUR DB FIRST!!!
AdamTrying to learn php

Author

Commented:
Many thanks all, in particular Chris and Julian for solving this for me.

I had to change my execution time on my php.ini file as it was taking so long to update all the entries but it eventually got through it.

Had a bit of a test and it seems to be working fine. And to think this morning I was considering changing all the entries manually.

Thanks Julian, Chris and everyone and have a good weekend.

Adam
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You are welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial