How do I check a table to see if a string is already in use?

Question

I am using this code to check my username column (username is primary) in my userdb table to see whether or not the string is already there. If it isn't there then it adds the string entered from a previous form into the username column in my table. But if it is there then it says "(Username) is already in use!".

This works when I put an entry in the username column such as "Sam" and then when I enter Sam into the previous form. But if I have "Sam" in the username column and then enter sam with all lowercase into the previous form, it displays Duplicate entry 'sam' for key 1.

I just want it to say that the string is already in use no matter what kind of casing I enter into the previous form.

$result = mysql_query("SELECT username FROM userdb") or die(mysql_error()); 
$row = mysql_fetch_array( $result );
$checkuser = $row['username'];

if ( $checkuser == $username ) {
    echo "<font color='red'>" .$username. "<font color='black'> is already in use!";
    die(mysql_error());
} else {
    mysql_query("INSERT INTO userdb (username, password) VALUES('$username', '$password' ) ") or die(mysql_error());;
    echo "Data Inserted!";
}

Answer 1

Change the code to:

strtolower($checkuser) == strtolower($username)

Answer 2

$result = mysql_query("SELECT count(*) FROM userdb where UCASE(username)=UCASE($checkuser);"

Check the number of rows in the result.

Pseudocode:

If num_rows>0
    Username exists;

Answer 3

You shouldn't do it this way, because it can lead to race conditions. That's what happens if, between your check and your insert, somebody else inserts that username into the table.

The correct way to do it is to have a primary key on the username and insert the record, catching an exception or error code returned from the DBMS.

If the username is already in there, it won't be inserted again and you'll catch the error.

If it's not there, it will be inserted and you'll get no error.

In terms of your casing problem, I'd either convert all user names to lowercase before insertion or checking, or insert the mixed-case version and lower-case both the DB copy and local copy in all checks.

Answer 4

The short answer is:

If you want the usernames to be case-insensitive, you need to store them in the database in a case-insensitive manner. Convert the given username to all lower-case (or all upper-case) first.

$lcusername = strtolower($username);

Then use $lcusername wherever you use $username in the code you supplied.

The long answer is:

You don't need to check for the pre-existence of a key in a table. Assuming you have a PRIMARY KEY attribute on the username field, simply try to your data into the table (after converting it to lower-case). If the database returns a "duplicate key" error, you know the username already existed and you can display the error message. If the database returns that it successfully inserted the row (perhaps by using the mysql_affected_rows function). This saves you from having to do a SELECT before doing the INSERT, greatly simplifying your code.

You should also escape all the strings that you'll be inserting into the table to help avert potential security holes.

$lcusername = strtolower($username);

// Escape strings
$esclcusername = mysql_real_escape_string($lcusername);
$escpassword = mysql_real_escape_string($password);

mysql_query("INSERT INTO userdb (username, password) VALUES('$esclcusername', '$escpassword') ");
if (mysql_affected_rows() == -1) {
    // Display error message
} elseif (mysql_affected_rows() == 1) {
    // Yay! Insert successful
} else {
    // Affected rows is 0. Something went wrong
}

Answer 5

The only way to do this correctly and avoid race conditions* is attempt to insert the user's data and examine any errors to see if the error is a duplicate key error. If you were using PDO (you should be) it would throw an exception which you could catch and examine. I think you can still trap and examine errors using PHP's standard error handling, I'm just not sure how.

*The race condition here is this:

1. Check user name is unique, it is.
2. Another user creates an account with that username
3. You now attempt to create an account with that username for the original user, which causes an error.