Hash all passwords in database with one query

Just had some typos which I fixed in my original question. After doing that the code runs, I still get the trying to get property of non object error and all the passwords in the database end up hashed but identical. I guess I need a loop to run in the update query. This was after I changed resultSet to fetchAll(PDO::FETCH_ASSOC)

Thanks Chris, I am getting an error about the user id. I am not passing in any ID's. I just want to literally loop through every record and hash the passwords. Do I need the ID for that as I am not passing it in from anywhere.

So, I modified it a bit:

$this->db->query("SELECT user_password FROM `TABLE 23`");
$results = $this->db->resultSet();

$update = $this->db->query("UPDATE `TABLE 23` SET `user_password` = :user_password");

foreach ($results as $user):
    $update->execute([
        'user_password' => password_hash($user->user_password, PASSWORD_BCRYPT, [12]),
    ]);
endforeach;
		
	}

Select allOpen in new window

But it's giving me an error :

Uncaught Error: Call to a member function execute() on null

Hey,

Your error is because you're still using query() and not prepare():

$update = $this->db->query("UPDATE ....

Should be

$update = $this->db->prepare("UPDATE ...

And yes ... you'll need the ID, so your UPDATE query knows which record to update.

I tried prepare and it gave me an error. Will post it now...

Call to undefined method Database::prepare()

Database class:

	// Prepare statement with query
	public function query($sql) {
		$this->stmt = $this->dbh->prepare($sql);
	}

Select allOpen in new window

	// Execute the prepared statement
	public function execute() {
		return $this->stmt->execute();
	}

Select allOpen in new window

Here is my latest. It only gives me an execute() error now as I changed prepare to query which is really prepare, lol.

$this->db->query("SELECT user_id, user_password FROM `TABLE 23`");
$results = $this->db->resultSet();

$update = $this->db->query("UPDATE `TABLE 23` SET `user_password` = :user_password WHERE user_id = :user_id");

foreach ($results as $user):
    $update->execute([
        'user_id' => $user->id,
        'user_password' => password_hash($user->user_password, PASSWORD_BCRYPT, [12]),
    ]);
endforeach;	

Select allOpen in new window

Sorry to keep posting so quickly and changing things but I am desperate which is why I am trying everything and anything. After changing it around a bit it complained that I was not binding anything so I tried this. I get no errors but no data changes in the database:

$this->db->query("SELECT user_id, user_password FROM `TABLE 23`");
$results = $this->db->resultSet();

$update = $this->db->query("UPDATE `TABLE 23` SET `user_password` = :user_password WHERE user_id = :user_id");
		$this->db->bind(":user_password", $user['user_password']);
		$this->db->bind(":user_id", $user['user_id'] );

foreach ($results as $user):
    $this->db->execute([
        'user_id' => $user->user_id,
        'user_password' => password_hash($user->user_password, PASSWORD_BCRYPT, [12]),
    ]);
endforeach;	

Select allOpen in new window

If I print_r() $user it shows me:

stdClass Object ( [user_id] => 1 [user_password] => pass1 ) stdClass Object ( [user_id] => 2 [user_password] => pass2 )

Select allOpen in new window

So, it seems that part of the query is working as it is getting the data

This is hashing the passwords now but not sure it is correct.

$this->db->query("SELECT user_id, user_password FROM `TABLE 23`");
$results = $this->db->resultSet();

$this->db->query("UPDATE `TABLE 23` SET `user_password` = :user_password WHERE user_id = :user_id");
$this->db->bind(":user_password", $hashed_pass);
$this->db->bind(":user_id", $user_id);

foreach ($results as $user):
   
        $user_id = $user->user_id;
        $hashed_pass = password_hash($user->user_password, PASSWORD_BCRYPT, [12]);
		$this->db->execute();
   		
endforeach;	

Select allOpen in new window

Hmmm,

As you've got your DB stuff wrapped in your own class, I'm not sure what's going on behind the scenes.

When preparing and executing, you either bind the parameters OR you pass them into the execute() method - you're trying to do both. In the first instance, you're just binding the user_password parameter back to itself:

$this->db->bind(":user_password", $user['user_password']);

So if that get's run, nothing will happen. You're just setting the password to the password!

You'll need to look through your DB class to see exactly what you're doing with the execute() method

That code looks much better and should work.

What do you mean by "not sure if it's correct"

I need to check that the passwords actually work for the correct user or do you think it will?

No reason why it wouldn't.

Don't forget, you need to use the password_verify() function :)

I forgot something off. I want to create a unique id for each as well like this:

$this->db->query("SELECT user_id, user_password, `uid` FROM `TABLE 23`");
$results = $this->db->resultSet();

$this->db->query("UPDATE `TABLE 23` SET `user_password` = :user_password, SET `uid` = :uid WHERE user_id = :user_id");
$this->db->bind(":user_password", $hashed_pass);
$this->db->bind(":user_id", $user_id);
$this->db->bind(":uid", $uid);

foreach ($results as $user):
   
		$uid = hash("crc32b", rand());
        $user_id = $user->user_id;
        $hashed_pass = password_hash($user->user_password, PASSWORD_BCRYPT, [12]);
		$this->db->execute();
   		
endforeach;

Select allOpen in new window

But I get this:

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET `uid` = '2ce7db88' WHERE user_id = '1''

Select allOpen in new window

You've got an extra SET in your query:

$this->db->query("UPDATE `TABLE 23` SET `user_password` = :user_password, SET `uid` = :uid WHERE user_id = :user_id");

Haha, clearly I don't think clearly under immense pressure. Thank you, you have saved me!

Know what you mean !

Glad it's sorted :)

Hi Chris, not sure if you can still see this but I have a lot of data now, not just the few rows I was testing with and I get this error:

Fatal error: Maximum execution time of 30 seconds exceeded in...

Any suggestions? Perhaps php.ini?

I changed this line from 30 seconds to:

max_execution_time = 30000     ; Maximum execution time of each script, in seconds

Select allOpen in new window

But it is still giving me the error for 30 seconds. I stopped MAMP and restarted it. Same result.

Think I might have been able to overwrite php.ini with my .htaccess

<IfModule mod_php7.c>
php_value max_execution_time 3000
</IfModule>

Select allOpen in new window

It's going a lot longer than 30 seconds.

Hmmm. not sure. Double check that you've changed the correct php.ini. Don't know about it on a Mac, but on Windows there are a couple of copies of php.ini.

Run a quick phpinfo() script in your browser and it should show you the path to your active php.ini. You can also check the max_execution_time value in there.

Setting the value to 30,000 seems a bit extreme - you're basically allowing your script to run for 8 and a half HOURS before bombing - probably not advisable !

I'm guessing this is a one-off script that you're wanting to run, so it may make sense to break down the amount of records it's updating. Password hashing isn't the fastest process, but it certainly hsould take that long. How many records are you trying to update!

Haha, I don't have 8.5 hrs! 5500 records :)

The .htaccess trick seems to be working. Yeah,  I modified the php.ini in the php 7 folder.

Still waiting... Maybe it will take 8.5 hrs!

Right. On my installation of WAMP, there is a php.ini file in my PHP7 folder - this is NOT the one the webserver uses. That's only used if you call PHP from the command line (terminal). The one Apache (the web server) uses is under the apache folder:

C:\wamp\bin\apache\apache2.4.17\bin\php.ini <-- The Web Server uses this one
C:\wamp\bin\php\php7.0.8\php.ini <-- NOT this one!

Not sure if this is the same on a Mac.

If you run phpinfo() you'll see which one your webserver uses - that's the one you should be editing.