-2

I found a solution to the problem referenced below that may help people using PHP PDO. I tested it and it works but I'm not sure it is the cleanest code or the best. Any improvements are welcome.

Here is the original problem for reference:

I want to hash passwords that are already in a MySQL database. I can already hash new passwords using the php 5.5 hashing API but I want to know if there is a way to take all the old plain text passwords and convert them to bcrypt hashes. I am thinking now of copying the passwords to a new row called 'hash' and, after checking that they copied correctly, convert them to hashes. I am not sure how to copy the password row and rename it on the same table, or how to hash all of these most efficiently, though.

Any insight would be appreciated.

Here is the solution:

    <?
    // IMPORTANT: only call this script one time or you will double hash and the passwords input by your users won't work anymore

    // Get Configuration file
    require("configsecuresavedgames.php");

    // Connect to your server

    $dbh = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8" , $user, $pass);  
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);


    ///////////////////////////////////////////////////////
    // Upload new score
    ///////////////////////////////////////////////////////

// set variable $x to 1 to start at ID 1 and then update each row in a loop, adding 1 to the $x variable once done 

$x = 1; 
// Note: Change the statement below so that the number is larger to match the number of users in your database

while($x <= 100) {

// select hash for each row...
  $stmt = $dbh->prepare("SELECT hash FROM $tname WHERE id = $x"); 
    $stmt->execute();

    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 

// set $hash variable to hash (from database) for the respective row

    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['hash'];
    $hash = $row ['hash'];
}

// update hash row with new hash data (note: prior to running the script make sure that you've copied all plain text passwords to the hash row in the database.


    $newhash = password_hash($hash, PASSWORD_DEFAULT);
    $sql = "UPDATE securesavegames SET hash = '$newhash' WHERE id = $x";


     // Prepare statement
    $stm = $dbh->prepare($sql);

    // execute the query
    $stm->execute();

    // echo a message to say the UPDATE succeeded
    echo $stm->rowCount() . " records UPDATED successfully";

    // add to $x so that the hash for the next 'id' will be updated, then the loop will continue.

 $x++;

}  
$dbh = null;

?>
brett
  • 19
  • 6
  • no. hash = meat grinder. a hash turns a cow into hamburger. There is no way to take the hamburger and turn it back into the original cow. You'll have to get your users to re-enter their passwords so they can be run through the new hash. – Marc B Nov 04 '14 at 14:14
  • 2
    @MarcB I think the passwords he is referring to are already plain text. – Flosculus Nov 04 '14 at 14:18
  • ah well, right... in that case `update yourtable set new_hash = hash(old_password_field)`. You could do an in-place `set pw=hash(pw)`, but then you'd lose the original plaintext ones. – Marc B Nov 04 '14 at 14:28
  • http://stackoverflow.com/questions/4039748/in-mysql-can-i-copy-one-row-to-insert-into-the-same-table – sridesmet Nov 04 '14 at 14:28
  • Hi MarcB, So could I just run the above SQL query in phpmyadmin and get hashes for my passwords in a new 'hash' row? That is what I'd like to do basically. – brett Nov 04 '14 at 17:26
  • @MarcB - MySQl has no appropriate hash functions available to hash passwords. Even the `password()` function should not be used to hash passwords, instead one needs a slow key derivation function like BCrypt or PBKDF2 with a cost factor. – martinstoeckli Nov 05 '14 at 09:17
  • @brett - Your found solution is open to SQL-injection. You are calling `$dbh->prepare()` but there is nothing to prepare, because the parameters where already added to the SQL in an unsafe way. – martinstoeckli Nov 21 '14 at 09:25
  • Can injection occur even though there is no user input ( no form or POST data)? I was only planning to run the script once and then remove it from my server. Is this still a problem? If this is prone to injection, what would be a better way? Thanks for the input. I appreciate it. – brett Nov 22 '14 at 16:20
  • @brett - You are right, in this case it should be safe, assuming that the variable `$tname` is hardcoded also. Nevertheless this is not the idea of preparing a statement, use placeholders instead. – martinstoeckli Nov 23 '14 at 17:15

1 Answers1

1

If your passwords are in plain text, just pass them through the hash and you'll be set.

There's not an easy way to have one query do your update, because password_hash is a PHP function. You will have to fetch the values and then loop through them. Prepared statements help a lot with this.

 $sql = 'SELECT record_id, password FROM table';
 $res = $mysqli->query($sql);

 $sql = 'UPDATE table SET password = ? WHERE record_id = ?';
 $prep = $mysqli->prepare($sql);
 $prep->bind_param('si', $pass, $record);

 while($row = $res->fetch_assoc()) {
      $pass = password_hash($row['password']);
      $record = $row['record_id'];
      $prep->execute();
 }
Machavity
  • 30,841
  • 27
  • 92
  • 100
  • Thanks, but how do I pass them through the hash if they are already in the database? Is there a SQL query that will do this for all of the passwords at the same time? I feel pretty good about the php script to hash passwords when users are adding their data but how do I take what's already in the database and run the passwords through the php 5.5 hashing API? The passwords are in plain text (not hashed at all yet). – brett Nov 04 '14 at 17:23
  • I updated it with a loop you could use. There's not an easier way because you can't mix PHP into an `INSERT SELECT` statement like that. If you want to break the loop up, just add `WHERE password NOT LIKE "$%"` to your initial query and just run it over and over until everything is updated – Machavity Nov 04 '14 at 18:18
  • Thank you Machavity, but I have been getting the following error with the code and I'm not really sure what it means: "Fatal error: Call to a member function query() on a non-object..." What is this referring to? Thanks, – brett Nov 17 '14 at 04:57
  • In my example, `$mysqli` is an [instance of the mysqli object](http://us3.php.net/manual/en/mysqli.construct.php) – Machavity Nov 17 '14 at 13:19
  • Thank you for your help but I found an way that works with PDO. I was having difficulty getting it to work with mysqli because I have less experience with it. Where can I post my solution in case it is useful for others? – brett Nov 20 '14 at 17:22