My Webpage doesn't recognize the data fulfilled in a registration form

Question

I'm currently doing a webpage, and by now I'm focused on the log in and registration forms. I have also a sql database connected. When I register a new user with the registration form, the database is updated succesfully. The problem is that when I try to log in with that user, the page doesn't recognize it. Besides, if I try to log in with an user that I introduced manually with Netbeans, it recognize it.

$con = mysqli_connect("localhost", "root", "mypassword");
if(!$con) {
    exit('Connect Error (' . mysqli_connect_errno() .') ' . mysqli_connect_error());
}

mysqli_set_charset($con, 'utf-8');

mysqli_select_db($con, "my_database");

$user = mysqli_real_escape_string($con, htmlentities($_POST['new_mail']));
$password = mysqli_real_escape_string($con, htmlentities($_POST['new_passwd']));
$sql = "INSERT INTO usuarios (usuario, clave) VALUES ('". $user ."' , ' ".md5($password)."')";
mysqli_query($con, $sql);
if(mysqli_affected_rows($con) > 0) {
     ?>
    <script type='text/javascript'>
        alert('You have been registered succesfully. Now you can access our website');
    </script>
    <?php
    header("Location: login_page.html");

    echo "<br><br><a href='index.php'>Go back</a>";
} else {
    if(mysqli_errno($con) == 1062) {
        echo "The e-mail address introduced is already on the system.";
        echo "<br><a href='register.html'>Try again</a>";
    } else {
        echo "Error: " .$sql . "<br>" . mysqli_error($con);
    }
}

That's the code I use after fulfilling the registration form. The next one is the one I use after the log in form.

    $con = mysqli_connect("localhost", "root", "mypassword");
if(!$con) {
    exit('Connect Error (' . mysqli_connect_errno() .') ' . mysqli_connect_error());
}


if(mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_errno());
    exit();
}

mysqli_set_charset($con, 'utf-8');

mysqli_select_db($con, "my_database");

$user = mysqli_real_escape_string($con, htmlentities($_POST['username']));
$password = mysqli_real_escape_string($con, htmlentities($_POST['password']));

$sql = "SELECT * FROM usuarios WHERE usuario='" . $user ."' AND clave='" . md5($password) . "'";
mysqli_query($con, $sql);
if(mysqli_affected_rows($con) > 0) {
    //echo "Welcome " . $_SESSION['username'] . "!";
    //echo "<br><br><a href='user_page.php'>Main Page</a>";
    //echo "<br><a href= 'close_session.php'>Close Session</a>";
    header("Location: main_page.html");
} else {
    exit ("The user or password introduced are not correct");
}
$row = mysqli_fetch_row($sql);
$_SESSION['user'] = $row;
$_SESSION['username'] = $row[0];
mysqli_free_result($sql);

?>

Thank you for your help.

Answer 1

Few mistakes that you are doing on your registration page.

1. You are not using prepared statements.

2. You are using md5() instead of password_hash() and password_verify() to secure your passwords.

3. You are using cleansing mechanism on the password which you should't as this may change the original password.

With the above you should use prepared statements and take the advantage of password hash and verify,

therefore your register page. should look :

<?php
$con = mysqli_connect("localhost", "root", "mypassword");
if (!$con) {
    exit('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}

mysqli_set_charset($con, 'utf-8');

mysqli_select_db($con, "my_database");

$user     = $_POST['new_mail'];
$password = $_POST['new_passwd'];

$hash = password_hash($password, PASSWORD_DEFAULT);

//check if user is not registered already, I'm not sure if you have user_id, what I know you should have id which is auto increment, then select that id
$sql  = "SELECT user_id FROM usuarios WHERE usuario = ? ";
$stmt = mysqli_prepare($con, $sql);
mysqli_stmt_bind_param($stmt, 's', $user);
mysqli_stmt_execute($stmt);
$result   = mysqli_stmt_get_result($stmt);
$num_rows = mysqli_num_rows($result);

if ($num_rows > 0) {
    //user exists

    echo "The e-mail address introduced is already on the system.";
    echo "<br><a href='register.html'>Try again</a>";
} else {
    //user does not exist register the user
    $query  = "INSERT INTO usuarios (usuario, clave) VALUES (?,?)";
    $insert = mysqli_prepare($con, $query);
    mysqli_stmt_bind_param($insert, "ss", $user, $hash);

    if (mysqli_stmt_execute($insert)):
?>

        <script type='text/javascript'>
        alert('You have been registered succesfully. Now you can access our website');
    </script>
    <?php
        header("Location: login_page.html");

        echo "<br><br><a href='index.php'>Go back</a>";
    else:
        printf("Error: %s.\n", mysqli_stmt_error($insert));
    endif;
}
?>

Then login

<?php
session_start();
$con = mysqli_connect("localhost", "root", "mypassword");
if (!$con) {
    exit('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}


if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_errno());
    exit();
}

mysqli_set_charset($con, 'utf-8');

mysqli_select_db($con, "my_database");

$user     = $_POST['username'];
$password = $_POST['password'];


#ONLY SELECT THE SPECIFIC COLUMNS YOU NEED, DON'T USE#

$sql  = "SELECT clave,anotherColumn,anotherColumn  FROM usuarios WHERE usuario= ? ";
$stmt = mysqli_prepare($con, $sql);
mysqli_stmt_bind_param($stmt, 's', $login);
mysqli_stmt_execute($stmt);
$result   = mysqli_stmt_get_result($stmt);
$num_rows = mysqli_num_rows($result);

if ($num_rows > 0) {

    $row = $row = mysqli_fetch_assoc($result);

    if (password_verify($password, $row['clave'])) {

        //passwords set sections, redirec
    } else {

        //user password does not match the stored hash return message
    }


} else {

    //username does not exist, do something
}


?>