mysql查询用散列密码更新数据库

Question

I'm building a registration and log in form and I would like to hash my user passwords on registration and my main problem at this point is how to write the query to update the passwords when the user is signing his email and pass for the 1st time and I want to add mysqli_insert_id() to the query to follow the unique id's for each user.

so I have database named testdb with users inside.

my code is perfectly working until the moment when you have to query the password and update it.

First I'm hashing my passwords

$password = $_POST['password'];

$hashed_password = password_hash($password, PASSWORD_BCRYPT);

$query = "UPDATE `users` SET `password` = '$hashed_password' WHERE id = "

As you can see I have problem with my query which should update passwords in my DB.

I have this code written until this point so I need help to proceed my UPDATE query

if (array_key_exists("submit", $_POST) ) {

    // connect to our db
    $link = mysqli_connect("localhost", "root", "", "secretdi");
    // check for connection
    if ( mysqli_connect_error() ) {
        die("Database Connection Error");
    }

    $error = "";

    if ( !$_POST['email'] ) {
        $error .= "An email address is required<br>";
    }

    if ( !$_POST['password'] ) {
        $error .= "A password is required<br>";
    }

    if ( $error != "" ) {
        $error = "<p>There were error(s) in your form:</p>".$error;
    } else {

        $query = "SELECT id FROM `users` WHERE `email` = '".mysqli_real_escape_string($link, $_POST['email'])."' LIMIT 1";

        $results = mysqli_query($link, $query);

        if ( mysqli_num_rows($results) > 0 ) {
            $error = "That email address is taken.";
        } else { 

            $query = "INSERT INTO `users` (`email`, `password`) VALUES('".mysqli_real_escape_string($link, $_POST['email'])."','".mysqli_real_escape_string($link, $_POST['password'])."') ";

            if (!mysqli_query($link,$query)) {
                $error = "<p>Could not sign you up - please try again later</p>";
            } else {

                $password = $_POST['password'];

                $hashed_password = password_hash($password, PASSWORD_BCRYPT);


                $query = "UPDATE `users` SET `password` = '$hashed_password' WHERE id = "


                echo "Sign up successful";
            }

        }

    }

Answer 1

You should never, under any circumstances, store the password in plain-text.

This means that when you register the user you should hash the password right away and store it in the hashed format when you perform the insert statement. Your current logic stores the password in plain-text, and if the registration is successful then you attempt to update the password to become the hashed one. This means that if anything fails you might end up with having only the plain-text password in your database, and either way it means that at a certain point it does exist there in plain-text at some given point for any user which we don't want.

Besides, the update statement serves no purpose other than wasting a few extra lines of code.

Something like this should do:

if ( mysqli_num_rows($results) > 0 ) {
    $error = "That email address is taken.";
} else { 
    $password = $_POST['password'];
    $hashed_password = password_hash($password, PASSWORD_BCRYPT);

    $query = "INSERT INTO `users` (`email`, `password`) VALUES('".mysqli_real_escape_string($link, $_POST['email'])."','".$hashed_password."') ";

    if (!mysqli_query($link,$query)) {
        $error = "<p>Could not sign you up - please try again later</p>";
    } else {
        echo "Sign up successful";
    }
}

If you really want to stick to your plan, however, then you can select the user id for the user you just created (e.g. by using the email identifier which is hopefully unique) and use it as such to identify which user to update. Please don't do that.

Note: I recommend taking a look at mysqli's prepared statements to ensure a higher level of security instead of escaping individual variables.