CODEWITHSUNDEEP
phpsqlsql-server
user9847333
user9847333

Reputation:

generate a random password and update the database

I have to update the passwords for all of the existing users with random unique password. I have about 4000 users using SQL SERVER.

My code is

$userSQL = "SELECT a002UserID FROM tbl002password WHERE a002UserID LIKE 'non%'";
$userrs  = $db->query($userSQL);
$userList1 = array();
while($row = $userrs->fetch(PDO::FETCH_OBJ)){
    $userList1[] = $row->a002UserID;
}
foreach($userList1 as $uid){
    function generatePassword ($length = 8)
    {
        $password = '';
        $chars = array_merge(range('a', 'z'), range('A', 'Z'), range(0, 9));
        for ($i = 0; $i < $length; $i ++) {
            $password .= $chars[array_rand($chars)];
        }
        return $password;
    }

    $pass_gen = generate_password();
    $insertPass = "UPDATE tbl002password SET a002password = '$pass_gen' WHERE a002UserID = '$uid'";
    $db->exec($insertPass);
    echo "updated $uid";
}

If remove the 'for' loop

Upvotes: 0

Views: 1287

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81990

Why loop through the 4,000 rows?

I'd apply a single update

Example

Declare @tbl002password  table (a002UserID  varchar(50),a002password varchar(50))
Insert Into @tbl002password values
 ('JS1234'   ,'Password1')
,('nonTI5656','Password2')
,('nonSC5657','Password3')

Update @tbl002password set a002password=right(NewID(),3)+left(abs(convert(bigint,HashBytes('MD5', a002UserID))),5)
 Where a002UserID like 'non%'


Select *
 From  @tbl002password

The Updated Table

a002UserID  a002password
JS1234      Password1
nonTI5656   FED25902
nonSC5657   3C815639

Upvotes: 1

rf1234
rf1234

Reputation: 1605

I have just run this in a sandbox:

$userList1 = array(1, 2, 3, 4, 5);

function generatePassword ($length = 8)
    {
        $password = '';
        $chars = array_merge(range('a', 'z'), range('A', 'Z'), range(0, 9));
        for ($i = 0; $i < $length; $i ++) {
            $password .= $chars[array_rand($chars)];
    }
    return $password;
}; 


foreach($userList1 as $uid){
    $pass_gen = generatePassword();

    echo $pass_gen . '   ';
}

The result is this:

ve6XzQzA   cL93fvVH   GtdP3LP3   8rKDRGJ2   qjt9j6Rv

Which looks fine to me. There is one obvious syntax error in your code. You call "generate_password();" while the function is actually called "generatePassword". I agree to the points Raymond Nijland made regarding your SQL. I would recommend you change this as well.

Upvotes: 0

Sammitch
Sammitch

Reputation: 32272

// 1. Don't declare functions inside loops. Functions should generally only ever be declared
//    in either the global scope, or a class definition.
function generatePassword ($length = 8) {
    $password = '';
    $chars = array_merge(range('a', 'z'), range('A', 'Z'), range(0, 9));
    for ($i = 0; $i < $length; $i ++) {
        $password .= $chars[array_rand($chars)];
    }
    return $password;
} 

$userSQL = "SELECT a002UserID FROM tbl002password WHERE a002UserID LIKE 'non%'";
$userrs  = $db->query($userSQL);

$userList1 = array();
while($row = $userrs->fetch(PDO::FETCH_OBJ)) {
    $userList1[] = $row->a002UserID;
}

// 2. Preparing statements like this increase performance as they are only parsed _once_,
//    but executed many times.
$insertPass = $db->prepare("UPDATE tbl002password SET a002password = ? WHERE a002UserID = ?");
foreach($userList1 as $uid) {
    $pass_gen = generate_password();
    $insertPass->execute([$pass_gen, $uid]);
    echo "updated $uid";
}

Upvotes: 1

Related Questions