Jump to content

How to check if a user name exists?


Tom8001
Go to solution Solved by ginerjm,

Recommended Posts

hi, how can i check if a username exists? i tried this 

$usrsql = "SELECT * FROM $tbl_name WHERE username='$username' AND password='$password'";
        $usrres = mysql_query($usrsql);
        $usrcount = mysql_num_rows($usrres);

        if($usrres && mysql_num_rows($usrcount)>0) {

            die("Username is already taken!");
        } 
Link to comment
Share on other sites

  • Solution

Does that code actually run?  (do you have error checking turned on?

 

Try:

 

$usrsql = "SELECT * FROM $tbl_name WHERE username='$username' AND password='$password'";
$usrres = mysql_query($usrsql);
if (!$usrres)
{
    (handle query failure situation)
    exit();
}
if (mysql_num_rows($usrres) > 0
            die("Username is already taken!");
    else
       (handle no username situation)
        
  • Like 1
Link to comment
Share on other sites

Are you wanting to check if a username "exists" or are you trying to authenticate a user? If you are only wanting to know if the username exists, why does the query do a check on the password?

 

To check if a username exists, I would do this

 

SELECT id FROM table_name WHERE username = 'value_to_check' LIMIT 1

Then check if the number of rows returned is 0 or 1.

 

Note that mysql_ extensions are deprecated and should not be used. Use either mysqli_ or PDO. Plus, you shoudl also use a prepared statement to guard against SQL Injection.

Link to comment
Share on other sites

Doing a SELECT query to check the existence of a value is a very, very poor approach and leads to race conditions: If two users ask for an unregistered name at the same time, then your script lets them both use the name.

 

While this may sound unlikely for a low-traffic website, a malicious user might actually do this on purpose in order to trigger bugs in your application. If your code expects the usernames to be unique, who knows how it will react to two users with the same name?

 

So, no, this is not an acceptable solution. The database needs to check the uniqueness of the username. And that's exactly what a UNIQUE constraint does.

 

When you've added the constraint, you simply do your INSERT query. If the database complains about a duplicate value, you know that the name is already taken and tell the user about it:

<?php

// assign proper names to MySQL error codes
define('MYSQL_ERROR_DUPLICATE_ENTRY', 1062);



// test values
$_POST['name'] = 'foo';
$_POST['password'] = 'bar';

$database = new PDO('mysql:host=localhost;dbname=test', 'YOUR_DB_USER', 'YOUR_DB_PASSWORD', array(
	PDO::ATTR_EMULATE_PREPARES => false,
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
));

$registrationStmt = $database->prepare('
	INSERT INTO
		users
	SET
		name = :name,
		password = :password
');

try
{
	$registrationStmt->execute(array(
		'name' => $_POST['name'],
		'password' => $_POST['password'],
	));
}
catch (PDOException $error)
{
	// check if the name is already taken
	$errorCode = $error->errorInfo[1];
	if ($errorCode == MYSQL_ERROR_DUPLICATE_ENTRY)
	{
		echo 'This name is already taken.';
	}
	else
	{
		throw $error;
	}
}

This is absolutely reliable, plus it is shorter and more efficient (because there's no need for a second query).

Edited by Jacques1
Link to comment
Share on other sites

You may add an (inaccurate) Ajax pre-check as an extra usability feature, but the actual uniqueness check must be done by a database constraint. It cannot be implemented with normal queries.

What I do is check one check at the database constraint and one at registration time with a validation check (no Ajax).

 

Here's the method I use for the validation check:

  public function checkAvailability($username) {
    $db = Database::getInstance();
    $pdo = $db->getConnection();

    $this->query = "SELECT 1 FROM users WHERE username=:username";

    $this->query_params = [':username' => $username];

    $this->stmt = $pdo->prepare($this->query);

    $this->stmt->execute($this->query_params);

    $this->result = $this->stmt->fetch();

    if ($this->result) {
      return 'Username is invalid or not available, please re-enter!';
    }
  } 

If the validation doesn't get it, when the user's data is submitted and by some chance there is another user with the same name then it will catch it as Jacques1 has already. The only bummer is the user will have to re-enter all his/her data, but I can't see that happening unless it's a really busy website (A good thing :happy-04: ) or a freak happening. 

Edited by Strider64
Link to comment
Share on other sites

 

Does that code actually run?  (do you have error checking turned on?

 

Try:

$usrsql = "SELECT * FROM $tbl_name WHERE username='$username' AND password='$password'";
$usrres = mysql_query($usrsql);
if (!$usrres)
{
    (handle query failure situation)
    exit();
}
if (mysql_num_rows($usrres) > 0
            die("Username is already taken!");
    else
       (handle no username situation)
        

 

This works great!, thanks all for your help :)

Link to comment
Share on other sites

Doing a SELECT query to check the existence of a value is a very, very poor approach and leads to race conditions: If two users ask for an unregistered name at the same time, then your script lets them both use the name.

 

While this may sound unlikely for a low-traffic website, a malicious user might actually do this on purpose in order to trigger bugs in your application. If your code expects the usernames to be unique, who knows how it will react to two users with the same name?

 

So, no, this is not an acceptable solution. The database needs to check the uniqueness of the username. And that's exactly what a UNIQUE constraint does.

 

The OP did not ask about "creating" a record and ensuring that the username does not already exist. He asked about verifying that a username does not exist. Many sites implement an AJAX call to check a username after the user enters the value in a form, but before they have submitted the form. So, yes, what I provided was a perfectly acceptable solution for what was asked.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.