PHP and MySQL get a column and make a password

[Pedroski55]

Hi, I'm new here and I should say, I am a raw amateur at PHP and MySQL. All I can do is look at how others do similar things and try and adapt it.

I have a MySQL table: Neil_Details. It has 4 columns: id, number, name, password.

I can enter number and name from a csv file automatically, no problem. id is the Primary Key and will auto_increment.

The column 'number' is the student numbers, name is their Chinese names.

I want to take the column number and generate a password from each number in the column password.  That's all!

Quote

$password = password_hash($number, PASSWORD_DEFAULT);

People tell me, "Don't use their student numbers," but once I know how to do this in principle, I can splice part of the number and Chinese name together somehow and make a more secure password.

I just want to learn how to do this in principle. Then I can do it for each class.

So far I have this, which doesn't seem to work, although I get no errors in /var/log/apache2/error.log

Could you please help me along a bit with some tips, pointers, links?

My index.php first calls '/makePassword/form.html.php'

This is called makePassword.php.

Thanks for any tips!

<?php
        //start PHP session
        session_start();
     
        //check if login form is submitted
        if(isset($_POST['gettable'])){
            //assign variables to post values
            $tablename = $_POST['tablename'];
            
// login to the database dbname=allstudentsdb
        include $_SERVER['DOCUMENT_ROOT'] . '/includes/studentdb.inc.php';
        
        $stmt = $pdo->prepare('SELECT * FROM ' . $tablename . ' WHERE number = :number');
                $stmt->execute(['number' => $number]);
                $password = password_hash($number, PASSWORD_DEFAULT);
                echo 'password is ' . $password;
                $stmt = $pdo->prepare('INSERT INTO ' . $tablename . ' (password) VALUES (:password)');
                try{
                        $stmt->execute(['password' => $password]);
     
                        
                    }
                    catch(PDOException $e){
                        $_SESSION['error'] = $e->getMessage();
                    }
               }
               echo 'Passwords made!';

// the input form to get the mysql table name
               header('location: ' . '/makePassword/form.html.php');
               ?>

 

[requinix]

Don't use their student numbers. If you create passwords based on something any student can know, then you create passwords that any student can know.

Create a random password. It takes no effort to do this, and there is absolutely no good reason to do it the dumb way when a better way is available and easy.

bin2hex(random_bytes(6))

That's all it takes.

[Pedroski55]

4 reasons for this:

1. You don't know these students. They will continually lose their pws.

2. At the moment, I use an old method from the book PHP and MySql: Novice to Ninja. That uses MD5($studentnumber + 'allstudentsdb')

3. When I get this to work, like I said, I can split the student number, mix it with the Chinese name, it will actually be very safe, because only I will know how it is made up.

4. The web page is only for homework, and, because of the virus, online classes at the moment. There is nothing, repeat nothing of a sensitive nature involved.

But first, I want to get the above working to insert the password in the password column. Any tips on that?

[requinix]

1 minute ago, Pedroski55 said:

1. You don't know these students. They will continually lose their pws.

I don't care. If they can't remember their passwords then it's their fault. It's not your job to give them easy to remember passwords.

 

1 minute ago, Pedroski55 said:

2. At the moment, I use an old method from the book PHP and MySql: Novice to Ninja. That uses MD5($studentnumber + 'allstudentsdb')

Anything that suggests using MD5 for passwords is bad and you should never, ever look at again.

 

1 minute ago, Pedroski55 said:

3. When I get this to work, like I said, I can split the student number, mix it with the Chinese name, it will actually be very safe, because only I will know how it is made up.

Aren't you going to tell the students their passwords?

 

1 minute ago, Pedroski55 said:

4. The web page is only for homework, and, because of the virus, online classes at the moment. There is nothing, repeat nothing of a sensitive nature involved.

I don't care how "sensitive" you think this is. A password is a password and there is no excuse for doing it wrong.

[Pedroski55]

At the moment, the student enters his or her name student number.

The number gets MD5()ed and compared with the column password. MySql has a function MD(), so that was very easy to do. Straight from the old book I mentioned.

Right now I am stuck on getting the value of password_hash() in the column password.

The password will be the student number.

[Phi11W]

On 5/17/2020 at 10:18 AM, Pedroski55 said:

You don't know these students. They will continually lose their pws.

The problem is common to Users, everywhere.

Provide them with a mechanism to reset their own password and it becomes their problem entirely. 

 

On 5/17/2020 at 10:18 AM, Pedroski55 said:

At the moment, I use an old method from the book PHP and MySql: Novice to Ninja. That uses MD5($studentnumber + 'allstudentsdb')

MD5 is completely broken.  Update to something at least vaguely secure.

 

On 5/17/2020 at 10:18 AM, Pedroski55 said:

When I get this to work, like I said, I can split the student number, mix it with the Chinese name, it will actually be very safe, because only I will know how it is made up.

Until somebody spends an afternoon and works out your "Magic Method" and then all of your formulaic passwords are laid bare. 
Attempting to roll your security system is almost always a Fools Errand.

Far better to randomly seed their password. 

 

On 5/17/2020 at 10:18 AM, Pedroski55 said:

The web page is only for homework, and, because of the virus, online classes at the moment. There is nothing, repeat nothing of a sensitive nature involved.

Oh really? 

On 5/17/2020 at 3:27 AM, Pedroski55 said:

I have a MySQL table: Neil_Details. It has 4 columns: id, number, name, password.

Anything that identifies a Data Subject is considered Personal Data and you are storing two of them right here.

id.  Uniquely identifies each Student.  OK, probably not much use on its own, but in context with other information, this could be used to identify, locate or track an individual. 
name.  Just how Personal does Data have to get?   

 

 

Regards, 
   Phill  W. 

 

 

 

 

 

 

 

[gizmola]

On 5/17/2020 at 2:29 AM, Pedroski55 said:

At the moment, the student enters his or her name student number.

The number gets MD5()ed and compared with the column password. MySql has a function MD(), so that was very easy to do. Straight from the old book I mentioned.

Right now I am stuck on getting the value of password_hash() in the column password.

The password will be the student number.

 

Having a default password you will never change is essentially having no security as has been pointed out previously.   

Putting that aside,  your code makes no sense to me.  

Didn't you state that you were going to load all the data (except for password) into the "tablename"?  I'm not sure why you are using a separate table per class other than you don't know how to design a small schema that would allow you to have all the students across tables, which would certainly be easier and cleaner.

Given the code you had, it seems you would just need to query with "SELECT * FROM ... table".

You get this result and fetch all the rows, then foreach through them.

On the inner loop you update each row using the primary key, and set your password column = password_hash($row['number'], PASSWORD_DEFAULT);

While looping you can echo the number/name of each student.

Here's some partial code:

<?php
  //start PHP session
  session_start();

  //check if login form is submitted
  if(isset($_POST['gettable'])){
      //assign variables to post values
      $tablename = $_POST['tablename'];
      // login to the database dbname=allstudentsdb
      include $_SERVER['DOCUMENT_ROOT'] . '/includes/studentdb.inc.php';

      $sql = 'SELECT * FROM `:tablename`';
      $stmt = $pdo->prepare($sql)
      if (!$sth->execute(array(':tablename' => $tablename))) {
        die("Unable to query $tablename");
      }

      $result = $stmt->fetchAll();

      // Set the passwords
      foreach ($result as $row) {
          echo "Name: $row['name'], Number: $row['number'] </br>";          
          // UPDATE each Row set password= :password where id = :id
          // $password = password_hash($row['number'], PASSWORD_DEFAULT);
          
      }
  } else {
      // the input form to get the mysql table name
      header('location: ' . '/makePassword/form.html.php');
  }