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

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
AdamTrying to learn phpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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?
2
Julian HansenCommented:
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
1
ste5anSenior DeveloperCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark BradyPrincipal Data EngineerCommented:
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.
0
lenamtlCommented:
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
0
Chris StanyonWebDevCommented:
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.
0
ste5anSenior DeveloperCommented:
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.
0
AdamTrying to learn phpAuthor 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
0
AdamTrying to learn phpAuthor 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...
0
Chris StanyonWebDevCommented:
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.
0
AdamTrying to learn phpAuthor 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
0
Julian HansenCommented:
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!!!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AdamTrying to learn phpAuthor 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
0
Julian HansenCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.