How to prevent duplicate Usernames on php/mysql?

Question

Yes, before you tell me to set the username column to primary key, so it is not allowed to duplicate. I'm already using the primary key for my user_id, which is on auto increment. I was hoping to be able to put a check in place in the php, that would see if the username has been taken already and send a json_encode back to my app. BTW, the database is being hosted online, so not really localhost!

This is what I'm using right now, that is not working:

<?php
    $con = mysqli_connect("localhost", "id177667_root", "***", "id177667_loginb");
    $name = $_POST["name"];
    $username = $_POST["username"];
    $password = $_POST["password"];
    $response = array();
    $dup = mysqli_query($con, "SELECT * FROM user WHERE username=$username");
    if(mysqli_num_rows($dup) > 0) {
        $response["success"] = false;
    }
    else {
        $statement = mysqli_prepare($con, "INSERT INTO user (name, username, password) VALUES (?, ?, ?)");
        mysqli_stmt_bind_param($statement, "sss", $name, $username, $password);
        mysqli_stmt_execute($statement);
        $response["success"] = true;
    }
    echo json_encode($response);
?>

When I run the hosted link of this, I get success:true, but it should be false:

<?php
    $con = mysqli_connect("localhost", "id177667_root", "***", "id177667_loginb");
    $name = $_POST["name"];
    $username = $_POST["username"];
    $password = $_POST["password"];
    $response = array();
    $statement = mysqli_prepare($con, "SELECT * FROM user WHERE username=?");
    mysqli_stmt_bind_param($statement, "s", $username);
    mysqli_stmt_execute($statement);
    if(mysqli_num_rows($statement) > 0) {
        $response["success"] = false;
    }
    else {
        $statement = mysqli_prepare($con, "INSERT INTO user (name, username, password) VALUES (?, ?, ?)");
        mysqli_stmt_bind_param($statement, "sss", $name, $username, $password);
        mysqli_stmt_execute($statement);
        $response["success"] = true;
    }
    echo json_encode($response);
?>

Strings need single-quotes around them. like this: `...WHERE username='$username'"` - and you should use parameterized queries here as well, not just for the insert. And you can have more than one `UNIQUE` constraint. — Qirel, Nov 22 '16 at 07:43
Your first query is failing, because you need to wrap all strings in your SQL statement in singlequotes `'`, so `...WHERE username=$username"` becomes `...WHERE username='$username'"`. And you're using placeholders/prepared statement for the INSERT query - you should do the same for your SELECT query. — Qirel, Nov 22 '16 at 07:47
I am trying this, but when I just run the php on my browser I get succes:true, but it should be false! — gamer_central5, Nov 22 '16 at 07:57
I remember this exact code and you keep on deleting your questions. — Funk Forty Niner, Nov 22 '16 at 12:39

score 2 · Answer 1 · edited May 23 '17 at 12:07

You do this by setting a UNIQUE constraint on the username in the database. Then you must set MySQLI (or PDO) to throw exceptions on errors.

Then, when the user tries to register a duplicate username an exception is thrown with a specific error code and message. This can then be used to identify when you have a "duplicate username" situation, and handle it properly.

This is the best way you can be sure that no duplicated usernames are entered. If you attempt to check first, then write to the database, you will experience race conditions. Meaning two writes can happen at the same time, after both have checked and seen that the username didn't exist already.
This method can be made resistant against race conditions, by locking the table before the read and then unlocking it after writing. I wouldn't recommend this method, as the number of potential errors increase dramatically.

How to prevent duplicate Usernames on php/mysql?

1 Answers1