Clever Geek Handbook

SQL Server PDO UPDATE loop error

I am trying to write a script to hash temporary passwords. Currently, the temporary password and the hashed version are stored in the same table. Once I get them hashed, I'm going to dump the intact column. Problem: My script sets all hashed passwords according to the last entry in the table.

$conn = new PDO("sqlsrv:Server=localhost;Database=database", "sqlUser", "password");
$result = $conn->prepare("SELECT tempPassword FROM employeeTable");
$result->execute();
$sql = "UPDATE employeeTable SET hashTempPass = :tempPass ";

while($data = $result->fetch(PDO::FETCH_ASSOC)) 
{
    $tempPass = $data['tempPassword'];  
    $q = $conn->prepare($sql);
    $q->bindValue(':tempPass', $tempPass);  
    $q->execute();
}

Results as such

user1    unhashPass1    hashpass3
user2    unhashPass2    hashpass3
user3    unhashpass3    hashpass3

While i need

user1    unhashPass1    hashPass1
user2    unhashPass2    hashPass2
user3    unhashpass3    hashpass3

I hope this question is clear enough. Thank.

Edit: Since I was struggling with this, I am not yet hashing passwords until I get this code.

+3


source to share


1 answer


You are close, but you need to change your query:

SELECT user, tempPassword FROM employeeTable

and

UPDATE employeeTable SET hashTempPass = :tempPass WHERE user = :user

this will allow you to capture both the hash and the temporary password.



Then you can update the table based on the temporary password:

$conn = new PDO("sqlsrv:Server=localhost;Database=database", "sqlUser", "password");
$result = $conn->prepare("SELECT hash, tempPassword FROM employeeTable");
$result->execute();

//prepare it only once (no need to send query to server at each iteration
$sql = "UPDATE employeeTable SET hashTempPass = :tempPass WHERE user = :user";
$q = $conn->prepare($sql);

while($data = $result->fetch(PDO::FETCH_ASSOC)) 
{
    $tempPass = $data['tempPassword'];  
    $user = $data['user']; 

    $q->bindValue(':tempPass', $tempPass);  
    $q->bindValue(':user', $user);  

    $q->execute();
}

Note

  • I named the field user

    , but you will need to adjust that column the field to match your table of course.
  • UPDATE

    without a suggestion WHERE

    can lead to the biggest mistake a DBA can make.
+2


source






More articles:


All Articles