使用哈希值更新DataBase中的所有行 [英] Update All Rows in DataBase with a hash value

问题描述

使用Php& MySQL，我想更新DataBase中特定列的所有行，

例如。 ：在名称列中有一行包含MyName，

这是我的计划我试图实现&逻辑：

1. 循环数据库




2. Get每行的当前值&

获取每行的现有值&哈希和&更新，这是我想要更新完整的数据库，

如何实现这个？

这个答案不是这样的。

我给你一个链接到PHP password_hash（） code> password_verify（）示例。

这里是那个链接。左边的链接是PDO的。以下此处链接与mysqli类似。

在PDO链接中查看行

  $ hp = password_hash（$ ctPassword，PASSWORD_DEFAULT）; //散列密码，使用
  

所以让我们假设你现在有一个列， c $ c> ctPassword 。您将 alter table 并为 hashedPassword 添加一个新列。按照我提供的链接，相应地调整，用更新语句将 ctPassword 的值哈希为 hashedPassword >

然后彻底测试。如果一切正常，请删除 ctPassword 列，再从不使用。 要清除，切勿在数据库中存储明文密码。存储单向哈希值，并对其进行验证。上述链接显示了如何。

编辑

这里完全是从PHP，我认为这需要驱动from，相对于mysql哈希函数，yuck。总之，你正在使用PHP，它们的鲁棒散列和验证将是闪耀。在我看来最好的做法，而mysql的人并不完全花费心理带宽。我所有在mysql尽可能做。但从来没有这个话题，使用哈希。让PHP驱动这一个。

模式

  b $ b（id int auto_increment主键，
 userName varchar（40）not null，
 ctPassword varchar（40）not null  - 明文密码（基本上是人类可读的）
  - 注意，不是ct的一个很好的定义，但它暗示它没有被哈希为安全
）; 
 
插入一个（userName，ctPassword）值
（'Brenda'，'我看电视太多'），
（'Drew'，'PatriotsWorldChamps'），
（'stealth_guy'，'JFIDU& JF_Anchovies'）; 
  

沿着这个概念，嘿，现在我想要安全哈希。我可能会被黑客入侵。

   -  http://dev.mysql.com/doc/refman/5.7/en/alter -table.html 
 alter table sometable添加列hashedPassword varchar（255）; 
  - 现在我有4列，hashedPassword当前是可空的
显示create table sometable; - 确认这个事实
  

PHP循环访问并更新一个新列，一个散列概念（我认为我们都在堆栈上看到1M次）

用于修补的PHP ：

 <？php 
 //打开错误报告，或者为什么没有发生什么事情
 mysqli_report（MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT） ; 
 // mysqli_report（MYSQLI_REPORT_ALL）; 
 error_reporting（E_ALL）; 
 ini_set（display_errors，1）; // Begin Vault 
 
 //来自安全保险柜的凭据，不是硬编码的
 $ 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）; //通过col名称将结果绑定到vars中
 $ stmt-> bindColumn（'ctPassword'，$ cPassword）; // ditto 
 
 // http://php.net/manual/en/pdostatement.fetch.php 
 while（$ row = $ stmt-> fetch（PDO :: FETCH_BOUND ））{
 //因为我们循环通过这里，$ theId和$ cPassword变量将自动神奇地更新
 //为我们，因为他们已绑定如上所示
 $ hPassword = password_hash（$ cPassword，PASSWORD_DEFAULT）; //我们现在有一个基于原始清除文本的散列密码
 echo $ cPassword。 。 $ hPassword。 < br>; 
 //每次使用相同的数据运行此哈希值时，由于盐的更改，哈希值将不同
 //基于上述PASSWORD_DEFAULT（查看password_hash的手册页）
 $ sqlUpdate = UPDATE sometable set`hashedPassword` ='$ hPassword'where`id` = $ theId; 
 
 $ db-> query（$ sqlUpdate）; 
} 
 // ..其他清理根据需要
} catch（PDOException $ e）{
 echo'Connection failed：'。 $ e-> getMessage（）; 
 exit（）; 
} 
？> 
  

运行php脚本，验证结果。这些都是我的，你的会不同。如果再次运行，您的设置将与您的设置不同。代码中提到的原因。

  select * from sometable; 
 
 + ---- + ------------- + --------------------- + -------------------------------------------------- ------------ + 
 | id | userName | ctPassword | hashedPassword | 
 + ---- + ------------- + --------------------- + ---- -------------------------------------------------- -------- + 
 | 1 | Brenda |我看电视太多| $ 2y $ 10 $ pJ5maui2OlrIPAtISf4u2OqeqEXU9ycDpCNNpp6xDh1uzIv / 6ybuW | 
 | 2 | Drew |爱国者$ 2y $ 10 $ kHAKRSeHLi9cghPKTKox / .kXiFgq6ELWwExGcVvbf1yYprtTvi.Ba | 
 | 3 | stealth_guy | JFIDU& JF_Anchovies | $ 2y $ 10 $ HOkBAkP7ZVIZ7NQB50aKAuhG5WjLHU9AtJCiY2E6h / M2YZuxc2l5K | 
 + ---- + ------------- + --------------------- + ---- -------------------------------------------------- -------- + 
  

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 ?

解决方案

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 |
+----+-------------+---------------------+--------------------------------------------------------------+

这篇关于使用哈希值更新DataBase中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！