Check if username already exists using PHP

Question

I would like to check whether or not a username already exists in my database. If it does, I would like to redirect back to my signup page. The code I have works to add the usernames but does not check if the username exists. Please help!!! This is the code for register.php page. The code completely skips the check for the 'username' and inserts into the Database if it exists or not.

    <?php  
         error_reporting (E_ALL ^ E_NOTICE);    
         include ('dbconn.php');
         session_start();

         $GLOBALS[$error_message];
         $GLOBALS[$username];

         if(isset($_POST['submit']))
           {     
               $error = array();        
               if(empty($_POST['username']))
                 {
                   $error[] = 'Please enter a username. ';
                 }
               else 
                 {
                   $username = mysqli_real_escape_string($connection, $_POST['username']);
                 }

               if(empty($_POST['password']))
                 {
                   $error[] = 'Please enter a password. ';
                 }
               else
                 {
                   $password = mysqli_real_escape_string($connection,$_POST['password']);   
                 }

               if(empty($_POST['cpassword']))
                 {
                   $error[] = 'Please confirm password. ';
                 }
               else
                 {
                   $cpassword = mysqli_real_escape_string($connection,$_POST['cpassword']);
                 }

               if($password == $cpassword)
                 {
                   $mainpassword= $password;
                 }
               else
                 {
                   $error[] = 'Your passwords do not match. ';
                 }      

               if(empty($error))
                 {                     
                   $query = "SELECT * from User WHERE username=' ".$username." ' ";
                   $result = mysqli_query($connection, $query) or die
                       (mysqli_error($connection));

                  if(mysqli_num_rows($result)> 0)
                  {
                   $multi = "Sorry ! This Username is not available...Please choose another";
                  }
                  else{ $sql="INSERT INTO user(username,password)VALUES                          ('$username','$password')";

              mysqli_query($connection, $sql) or die(mysqli_error($connection));
              header('Location:/MySQLi/confirmation.php'); }
}
               else
                  {
                    $error_message = '<span class="error">';
                    foreach($error as $key => $values) {
                    $error_message.="$values";
                    }
                    $error_message.="</span><br/><br/>";    
                    } 
                 }
?>

Answer 1

You are manually adding spaces around your username so it will look like it does not exist:

$query = "SELECT * from User WHERE username=' ".$username." ' ";
                                             ^             ^

Should be:

$query = "SELECT * from User WHERE username='".$username."' ";

Using a prepared statement would avoid that problem and potential sql injection problems in one go:

$query = "SELECT * from User WHERE username=?";

Also make sure you consistently use your table- and column names: User is not necessarily the same as user.

Also note that you should never store plain-text passwords in a database, you should salt and hash them.

Answer 2

SQL table names can be case-sensitive, so 'user' and 'User' are not the same. If your table is named 'user' with a lower-case, then 'SELECT * FORM User' with upper-case may not give any results.

If you simply want to check existence, you can select '1' and add limit 1 so the query doesn't scan the whole table, like this:

"SELECT 1 FROM user WHERE username='" . $username . "' LIMIT 1";

Or you could make your username column UNIQUE, then the INSERT statement would fail if a username already existed, giving you a simple one-query check that could both insert a new user as well as let you know if that name is already taken.

Finally, your code is wide open to SQL Injection attacks. Always escape user input or use prepared statements.