Check for duplicate user from MySQL database

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";
    }
}

What issue is coming? You need to execute select query first to check if user already exists or not. — Amit Gupta, Dec 15 '17 at 11:46
I need it to check for duplicate user. It is not checking at the moment — Bradley Coupland, Dec 15 '17 at 11:47
I am not. I need before posting - to check if user already exists — Bradley Coupland, Dec 15 '17 at 11:50
Even if you check it on the client you should check it on the server, JS is easy to override — Koas, Dec 15 '17 at 11:55
Yes you need to use jquery/ajax if you want to check the user exists or not or you can do it with bootstrap formValidation with ajax. — Amit Gupta, Dec 15 '17 at 12:04
**Don't go live with this code** nor with the code you've been given as answers. **You will get hacked** and nobody even whispered a word about this. — Funk Forty Niner, Dec 15 '17 at 12:25

Ivan86 · Accepted Answer · 2018-01-19T22:58:44.747

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";
    }
}
?>

Do or do not, there is no "try". A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. — Jay Blanchard, Jan 19 '18 at 18:33
@JayBlanchard I agree, I will edit to provide more insight for future readers. Though, I was having a similar conversation yesterday [https://stackoverflow.com/#comment83630798_48303013](https://stackoverflow.com/questions/48302267/how-to-remove-text-overlapping-in-table/48303013#comment83630798_48303013) :) Thanks. — Ivan86, Jan 19 '18 at 22:08

score 0 · Answer 2 · edited Dec 16 '17 at 16:31

So you can check that the user exists or not.

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;

        //Check user
        $CheckUserIsExist = mysqli->query("SELECT uid FROM members WHERE username='$username'");
        if(mysqli_num_rows($CheckUserIsExist)==0 ){
        $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{
      echo 'This username is already in use. Please use different username';
   }
   else{
        $_SESSION['message'] = "User couldn't be added";
   }
}
else{
    $_SESSION['message'] = "Passwords don't match";
}

Check for duplicate user from MySQL database

2 Answers2