Update All Rows in DataBase with a hash value

Question

Using Php & MySQL, I want to Update All Rows of a Particular Column in The DataBase,

Eg. : in The Column "Name" there is a Row which Contains "MyName",

Here is my Scheme I am Trying To Achieve & The Logic:

1. Loop The DataBase

2. Get the Current Value for each rows & hash it with hash('...', value);

Get the existing values for each rows & hash it & Update, This is How I want to Update The Full DataBase,

How can I achieve this ?

Answer 1

First I must say that if you have non-sensitive data in a db, then the built-in mysql functions can give you results of hashes directly with update statements using just mysql.

This answer is not about that. It is about sensitive data, like passwords.

I gave you a link to a PHP password_hash() and password_verify() example.

Here is That Link again. That link to the left is for PDO. The following Link Right Here is similar and for mysqli.

In the PDO link look at the line

$hp=password_hash($ctPassword,PASSWORD_DEFAULT); // hashed password, using 

So let's say you have a column now with cleartext in it called ctPassword. You would alter table and add a new column for something like hashedPassword. Follow that link I provided, tweak accordingly, hash the values of ctPassword into hashedPassword with an update statement.

Then test it thoroughly. When all is right in the world, drop the ctPassword column and never use it again. To be clear, never store clear text passwords in databases. Store one-way hash values, and verify against them. The above links show how.

Edit

Here is entirely from PHP where I think this needs to be driven from, as opposed to mysql hash functions, yuck. Afterall, you are using PHP, and it is there that their robust hashing and verifying is going to shine. Best practices in my opinion, whereas the mysql folks don't exactly spend the mental bandwidth on it. I am all for doing as much as possible in mysql. But never this topic, using hashes. Let PHP drive this one.

Schema

create table sometable
(   id int auto_increment primary key,
    userName varchar(40) not null,
    ctPassword varchar(40) not null -- clear text password (means humans can read it, basically)
    -- note, not a great definition of ct but it implies it has not been hashed for safety
);

insert sometable(userName,ctPassword) values
('Brenda','I watch TV too much'),
('Drew','PatriotsWorldChamps'),
('stealth_guy','JFIDU&JF_Anchovies');

Along comes the notion, hey, I want safe hashes now. I might get hacked.

-- http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
alter table sometable add column hashedPassword varchar(255);
-- now I have 4 columns, hashedPassword is currently nullable
show create table sometable; -- confirms this fact

PHP to loop thru and update a new column meant to clean up prior not having a hash concept (that I think we have all seen 1M times on the stack)

PHP for patching:

<?php
    // turn on error reporting, or wonder why nothing is happening at times
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    //mysqli_report(MYSQLI_REPORT_ALL);
    error_reporting(E_ALL);
    ini_set("display_errors", 1);    // Begin Vault

    // credentials from a secure Vault, not hard-coded
    $servername="localhost";
    $dbname="login_system";
    $username="dbUserName";
    $password="dbPassword";
    // End Vault

    try {
        $db = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $stmt = $db->prepare("select id,ctPassword from sometable");
        $stmt->execute();
        $stmt->bindColumn('id', $theId);        // bind the results into vars by col names
        $stmt->bindColumn('ctPassword', $cPassword);        // ditto

        // http://php.net/manual/en/pdostatement.fetch.php
        while ($row = $stmt->fetch(PDO::FETCH_BOUND)) {
            // as we loop thru here, the $theId and $cPassword variables will be auto-magically updated
            // for us because they have been bound as seen above
            $hPassword=password_hash($cPassword,PASSWORD_DEFAULT); // we now have a hashed password based on orig clear text one
            echo $cPassword . "   " . $hPassword . "<br>";
            // each time you run this with same data the hashes will be different due to changes in the salt
            // based on above PASSWORD_DEFAULT (look at manual page for password_hash)
            $sqlUpdate="UPDATE sometable set `hashedPassword`='$hPassword' where `id`=$theId";

            $db->query($sqlUpdate);
        }
        // .. other cleanup as necessary
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
        exit();
    }
?>

Run the php script, verify results. Those are mine, yours will differ. Yours will even differ from yours if you run it again. Reason mentioned in the code.

select * from sometable;

+----+-------------+---------------------+--------------------------------------------------------------+
| id | userName    | ctPassword          | hashedPassword                                               |
+----+-------------+---------------------+--------------------------------------------------------------+
|  1 | Brenda      | I watch TV too much | $2y$10$pJ5maui2OlrIPAtISf4u2OqeqEXU9ycDpCNNpp6xDh1uzIv/6ybuW |
|  2 | Drew        | PatriotsWorldChamps | $2y$10$kHAKRSeHLi9cghPKTKox/.kXiFgq6ELWwExGcVvbf1yYprtTvi.Ba |
|  3 | stealth_guy | JFIDU&JF_Anchovies  | $2y$10$HOkBAkP7ZVIZ7NQB50aKAuhG5WjLHU9AtJCiY2E6h/M2YZuxc2l5K |
+----+-------------+---------------------+--------------------------------------------------------------+