Login and Register: Holding a function from being executed

Question

I just wanted to create a script that allows me to check if the user trying to register isn't already in my database.

My code is split in two function register and login. One is called when we press the login button the other when we press the register button.

Now the thing is , whenever i click on register with a user that is already in my database, it will detect that he is existing and immediatly connect him.

What I want , is to reload the login page adding the message User is already existing in the database.

Could you help me please ?

This is the function register :

      function register()
       {
        include("database.php");
        $uname = $_POST['uname'];
        $psw = $_POST['psw'];


        $check = " SELECT User_Username FROM user WHERE User_Username = '".$uname."' ";
        $result_of_match = mysqli_query($conn,$check);
        $row_of_match = mysqli_fetch_array($result_of_match,MYSQLI_ASSOC);

            if(mysqli_num_rows($result_of_match) >=1){
                header("Location: Login.php?msg3=" . urlencode(base64_encode("User already created.")));
            } else {

                $sql1 = "INSERT INTO user(User_ID, User_Username, User_Password) VALUES (Null, '$uname', '$psw')";        
                    if(mysqli_query($conn,$sql1))
                    {header("Location: Login.php?msg4=" . urlencode(base64_encode("User successfully created.")));}

         }        
    }

and this is how i call both of my function

if($_SERVER['REQUEST_METHOD'] == "POST" and isset($_POST['register'])){
    register();
    }

if($_SERVER ["REQUEST_METHOD"]== "POST"){
    login();
} 

Answer 1

Like I mentioned in the comments, It should be impossible in the database to create duplicate users, so you should make the username or email ( if you use email as the username ) a unique index. That way the DB will blow up if a duplicate is made.

In PDO ( cant remember if Mysqli throws errors or exceptions ) I take advantage of this by just doing an insert and catching the error.

I+(PsudoCode)

$stmt = $Pdo->prepare('INSERT INTO users ( ... )VALUES( ... )');   

try{
    $stmt->execute($data);
     //do something on success

}catch(PDOExcption $e){
     if( $e->getCode() != 23000){ //if I remember right ( duplicate key )
         //Some other PDO error happened, you could re-throw it or log it etc..
     }else{
         //Send error to user saying the name is used ... etc..
     }
} 

PS. Don't put variables directly into SQL, you open yourself up to SQL injection attacks.

Consider this value for $uname ( please don't run this without a backup of your Database )

    $uname = "'; DROP TABLE user; --";

    $check = " SELECT User_Username FROM user WHERE User_Username = '".$uname."' ";

This would basically give you

 SELECT User_Username FROM user WHERE User_Username = ''; DROP TABLE user --';

The -- is a comment start in MySql like //, to escape the last apostrophe. Basically they complete the first SQL, and then inject their own query. Now there is some Database settings that might save you from this particular query, but I wouldn't bet on it ( some things like db user restrictions etc.. ).

This is pretty mundane, most likely someone would exploit your database vulnerability to inject some Javascript content, so when your users view it they could steal their cookies and hiJack their sessions, and gain access to their accounts, Or about a half dozen other nefarious things I can think of.

It's usually in their interest not to be noticed.

You can do prepared queries with MySQLi too.

That said, I prefer PDO, because it does Exceptions, named placeholder, has a more feature rich OOP interface, can handle multiple Database types (like MSSql) etc. etc.

Either one is fine, but you must absolutely use prepared statements. Even if you think the data is clean or you think you don't have any data worth stealing. It's just the right way to code, then you never have to worry about it.

UPDAtE

The easiest way to add a unique restriction is through PHPmyadmin, if you have that then find your database and the table, then click on "structure" and you should be to click the checkbox by the field and add a unique index. Otherwise you could do an Alter Table query,

ALTER TABLE {table} ADD UNIQUE INDEX {field}

A word of caution though, if you have duplicates already then it will not work, so you would have to clean them up first. You can find them with a query (something) like this

SELECT count({id}) as t FROM {table} GROUP BY {field} HAVING t > 1

Then you would have to change the username. This may be a bit of a challenge depending how many you have. But it would be possible to iterate though the above query select all users with that name iterate through that and suffix the duplicates with a number or such. ( that said it would take some code, or phpmyadmin to do it )

This may or may not affect your end users baddy. That said it's of little consequence as the situation is now when they log in they have an equal chance of not finding their own account. ie. the user experience is already bad.

Depending how much you value them you may have no choice but to change their name and notify them of it.