Check for duplicate user from MySQL database [duplicate]

Question

I would like to check for duplicates in a MySQL database when registering an user.

If the user exists display an error to that effect, else sign up.

I know there's a few questions like this but I found it hard to paste any of them into my code.

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    //two passwords are the same
    if($_POST['password'] == $_POST['confirmedpassword']) {

        $username = $mysqli->real_escape_string($_POST['username']);
        $password = md5($_POST['password']);

        $_SESSION['username'] = $username;
        $_SESSION['password'] = $password;

        $sql = "INSERT INTO members(username, password)"
            . "VALUES ('$username','$password')";

            //if query is successful redirect to login.php
            if ($mysqli->query($sql) === true)
                $_SESSION['message'] = 'Success';
            header("location: login.php");
        } else {
            $_SESSION['message'] = "User couldnt be added";
        }
    } else {
        $_SESSION['message'] = "Passwords dont match";
    }
}

Answer 1

I added some salt to your md5 password to make it seem more secure, but actually this solution is not secure either. To encrypt passwords in PHP it is advisable to use the password_hash() function like this:

$pass = password_hash($password, PASSWORD_BCRYPT);

password_hash() creates a new password hash using a strong one-way hashing algorithm.

and later test it with password_verify():

password_verify ( $passToTest , $knownPasswordHash );

more the functions here: http://php.net/password-hash, http://php.net/password-verify.

Also, since you are using MySQLi consider using prepared statements, or at least properly filter your input data before applying it to the database. More on prepared statements: http://php.net/prepared-statements.

I added a select statement to check if the user already exists in the table prior to adding the user to the database.

When using header() to change page location put exit() or die() in the next line of code if you want to exit immediately and don't want other code to execute.

Here is your code with the addition of the select statement:

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
    //two passwords are the same
    if($_POST['password'] == $_POST['confirmedpassword']) 
    {
        $username = $mysqli->real_escape_string($_POST['username']);

        // You might consider using salt when storing passwords like this
        $salt = 'aNiceDay';
        $password = md5(md5($_POST['password'].$salt).$salt);

        $_SESSION['username'] = $username;
        $_SESSION['password'] = $password;

        $sql = "SELECT `username` FROM members WHERE `username` = '".$username."'";
        $result = $mysqli->query($sql);

        if(mysqli_num_rows($result) > 0)
        {
            echo 'User exists.';
            // Do something.
        }
        else
        {
            $sql = "INSERT INTO members(username, password) VALUES ('".$username."','".$password."')";

            if($mysqli->query($sql) === true)
            {
                $_SESSION['message'] = 'Success';
                header("location: login.php");
                // Important to put exit() after header so other code
                // doesn't get executed.
                exit();
            }
            else
            {
                $_SESSION['message'] = "User couldn't be added";
                echo "User couldn't be added.";
            }
        }
    }
    else
    {
        $_SESSION['message'] = "Passwords dont match";
    }
}
?>