0

Hi I have created a registration/sign up form that registers new users and adds them to my database. On testing however I have found that someone can register with a username that has already been taken. Here is my code:

<?php
require('db.php');
// If form submitted, insert values into the database.
if (isset($_REQUEST['username'])){
        // removes backslashes
    $username = stripslashes($_REQUEST['username']);
        //escapes special characters in a string
    $username = mysqli_real_escape_string($con,$username); 
    $email = stripslashes($_REQUEST['email']);
    $email = mysqli_real_escape_string($con,$email);
    $password = stripslashes($_REQUEST['password']);
    $password = mysqli_real_escape_string($con,$password);
    $trn_date = date("Y-m-d H:i:s");
    $query = "INSERT into `users` (username, password, email, trn_date)
VALUES ('$username', '".md5($password)."', '$email', '$trn_date')";
        $result = mysqli_query($con,$query);
        if($result){
            echo "<div class='form'>
<h3>You are registered successfully.</h3>
<br/>Click here to <a href='login.php'>Login</a></div>";
        }
    }
?>

I have tried changing the query statement to select all usernames from the database and checking if the result of the query pulls back the same username enterd, and returning "username taken" if so, and if not in the else statement running the instert statement shown above and printing the succesfully registered message.

Any help?

Rebecca McGowan
  • 85
  • 1
  • 2
  • 8
  • Why don't you add a Unique index in your MySQL Database on the column `username`? – Sablefoste Mar 03 '17 at 17:48
  • http://stackoverflow.com/questions/20296777/trying-to-check-if-username-already-exists-in-mysql-database-using-php – hasan movahed Mar 03 '17 at 17:49
  • @Sablefoste do you mind providing some more information? Im a bit unsure on what you mean? Im fairly new to all this so sorry if I am being stupid here – Rebecca McGowan Mar 03 '17 at 17:51
  • @hasanmovahed looked at that, and tried implementing it however it didnt seem to work. I could however have implemented it wrong. – Rebecca McGowan Mar 03 '17 at 17:52
  • Sure, have a look at https://www.sitepoint.com/use-unique-indexes-mysql-databases/. MySQL will return an error if the Username already exists. – Sablefoste Mar 03 '17 at 17:53
  • ***You really shouldn't use [MD5 password hashes](http://security.stackexchange.com/questions/19906/is-md5-considered-insecure)*** and you really should use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. Make sure you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. – Jay Blanchard Mar 03 '17 at 17:58
  • [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) – Jay Blanchard Mar 03 '17 at 17:58
  • 1
    [Think (twice) before posting an answer for this question](http://meta.stackoverflow.com/q/344703/), it may change your mind. – Jay Blanchard Mar 03 '17 at 18:00

1 Answers1

1

Okay, so this is what you could do.

First off, create a unique index for your username column like this:

CREATE TABLE users (
... create columns...
PRIMARY KEY (user_id),
UNIQUE (username),
INDEX login (username, password)
);

And then in your PHP script for registration, check if the username exists:

    $q = "SELECT user_id FROM users WHERE username='$filtered_username_variable'";
    $r = mysqli_query ($con, $q);

    if (mysqli_num_rows($r) == 0) { // No: of rows returned. 0 results, Hence the username is Available.

            //code to INSERT into Database

    } else { // The username is not available, print error message.

                echo 'Uh-oh, This username has already been registered.';

    }
Ashil John
  • 7,362
  • 4
  • 19
  • 34