记录输入时，MYSQL会自动解密我的密码

Question

I have a script that adds an email address and password to a table. I first search to see if the email address exists in the table. If it does, I give an error message. If it does not, I add the record.

Then, using mysqli_insert_id(), I run another query to update the record I just added, encrypting the password with md5.

But every time I run it, the record is added, but the password does not get updated with the md5 version of the password. I have echo'd the query and it shows that it should be updating the password with the encryption, but it doesn't. Any ideas?

<?php
session_start();
error_reporting(E_ALL);

if (array_key_exists("submit", $_POST)) {
    $link = mysqli_connect("localhost", "eits_Admin", "WebSpinner1", "EITS_Sandbox");
    if (!$link) {
        die("Database connection error");
    }
    $error = '';
    if (!$_POST['email']) {
        $error .= "<br/>An email address is required";
    }
    if (!$_POST['password']) {
        $error .= "<br/>A password is required";
    }   
    if ($error != "") {
        $error = "There were errors in your form - ".$error;
    } else {
        $query = "select id from secretdiary 
                  where email = '".mysqli_real_escape_string($link, $_POST['email'])
                ."' limit 1";
        // echo $query;
        $result = mysqli_query($link, $query);
        if (mysqli_num_rows($result) > 0) {
            $error = "That email address is not available.";
        } else {
            $query = "insert into secretdiary 
                                (email,password) 
                      values ('" . mysqli_real_escape_string($link, $_POST['email']) 
                        . "', '" 
                        . mysqli_real_escape_string($link, $_POST['password']) . "')";

            if (!mysqli_query($link, $query)) {
                $error = "Could not sign you up at this time. Please try again later.";
            } else {
                $encPass = md5(md5(mysqli_insert_id($link)) . $_POST['password']);
                $query = "update secretdiary 
                            set password = '" . $encPass 
                        . "' where id = " . mysqli_insert_id($link) . " limit 1";
                echo $query;
                $result = mysqli_query($link,$query);
                echo "Sign up successful.";
            }
        }
    }
}
?>
<div id="error"><? echo $error; ?></div>
<form method="post">
  <input type="email" name="email" placeholder= "Your Email">
  <input type="password" name="password" placeholder="Password">
  <input type="checkbox" name="stayLoggedIn" value=1>
  <input type="submit" name="submit" value="Sign Up!">
</form>

Answer 1

You've got a lot of lines of code for a relatively simple process. Personally your form error handling such as if it's empty (in this case) can be remedied by adding required at the end of each HTML form input element (This is what I'd do)

Secondly, md5 isn't safe for hashing passwords (you're hashing a password not encrypting it)

Thirdly here's a way to hash the password from the form using Bcrypt which is much better than using md5 hashing. So do whatever error checking you need to do before like counting the usernames and if row > 0 die('username exists) Example of full code at base using PDO

When checking the users login simply use password_verify() function to do so

Tidy code helps people on SO understand what your problem is and is generally nicer to read. I know you may just be looking for something that 'Does the job' But it helps you when debugging and us when you're asking for help.

I'm going to give you a way that is marginally more secure than your one.

index.php

    <form method="post" id="regform" action="register.php">
    <input type="text" name="username" placeholder="Enter your email    Address"required/>
    <input type="password" name="password" placeholder="Enter your password" required/>
    <input type="submit" class="indexbttn" id="indexbttn" name="enter"value="enter"/>
</form>

connect.php

<?php
$servername = "localhost";
$dbusername = "root";
$dbpassword = "root";
$dbname = "fyp";
try{
$pdo = new PDO("mysql:host=$servername;dbname=$dbname",$dbusername,   $dbpassword);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
print "Error! Unable to connect: " . $e->getMessage() . "<br/>";
die();
}
?>

register.php

<?php
session_start();
require_once ('connect.php');
error_reporting(E_ALL);
ini_set('display_errors', 1);

 if(isset($_POST['enter'])){
  $username = !empty($_POST['username']) ? trim($_POST['username']) : null;
  $pass = !empty($_POST['password']) ? trim($_POST['password']) : null;
  $check (!filter_var($_POST['username'], FILTER_VALIDATE_EMAIL));

  $cnt = "SELECT COUNT(username) AS num FROM users WHERE username = :username";
  $stmt = $pdo->prepare($cnt);
  $stmt->bindValue(':username', $username);
  $stmt->execute();
  $row = $stmt->fetch(PDO::FETCH_ASSOC);
  if($row['num'] > 0){
      die('That username already exists!');
  }
  $passHash = password_hash($pass, PASSWORD_BCRYPT, array("cost" => 12));
  $insrt = "INSERT INTO users (username, password) VALUES (:username, :password)";
  $stmt = $pdo->prepare($insrt);
  $stmt->bindValue(':username', $username);
  $stmt->bindValue(':password', $passHash);
  $result = $stmt->execute();
  if($result){
    header( "refresh:5;url=index.php" );
echo 'You will be redirected in  5 seconds. If not, click <a       href="index.php">here</a>.';
  }
}
?>

login.php

<?php
 session_start();
require("connect.php");
  if(isset($_POST['enter'])){
$username = !empty($_POST['username']) ? trim($_POST['username']) :   null;
 $pass = !empty($_POST['password']) ? trim($_POST['password']) : null;
$rtrv = "SELECT username, password, userid FROM users WHERE username =       :username";
  $stmt = $pdo->prepare($rtrv);
  //Bind value.
  $stmt->bindValue(':username', $username);
  //Execute.
  $stmt->execute();
  //Fetch row.
   $user = $stmt->fetch(PDO::FETCH_ASSOC);
  //If $row is FALSE.
  if($user === false){
  //Could not find a user with that username!
  die('Incorrect username');
  }
  else{
      $validPassword = password_verify($pass, $user['password']);
    if($validPassword){
        $_SESSION['user_id'] = $user['username'];
        $_SESSION['logged_in'] = time();
        header( "Location: /protected.php" );
        die();
      } else{
          die('Wrong password!');
    }
   }
 }
 ?>