MySQL case sensitive

Hello!
I have this query:

			  elseif(mysql_num_rows(mysql_query("SELECT `username` FROM `users` WHERE `username`='".$username."'")) >= "1"){
				  $error = '<div class="red">'.$txt['alert_username_already_taken'].'</div>';
			  }

The queries all over the website are case sensitive. I’m trying to find a fix.
For example:
if the input of the above query ($username = “MyUser”) while there is username existed in name “Myuser” - it will not show any error and it will update the username.

How I can fix it?

I don’t know about MySQL, but in Oracle you can force the db column value to lower- or upper-case for comparison, and do likewise from the server-side aspect.

For example, if this were ColdFusion (and I’m intentionally omitting parameterized query variables for brevity):



WHERE upper(USERNAME) = '#uCase(form.username)#'

What Collation are the fields set to use?

btw, Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.

Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.

This is a terribly slow approach, but it works. Remember most DBs perform upper on that row for the entire table, not just the ones you’re looking for. So if you have 10,000,000 users, you’re performing upper() or lower() on every single one before you do anything.

Personally, I store usernames in 2 columns. 1 as lowercase for identification and unique and another as full with whatever capitalization they used when they created it.


CREATE TABLE users (
  id integer NOT NULL,
  username varchar(50) NOT NULL,
  full_username varchar(50) NOT NULL,
  password char(60) NOT NULL,
  PRIMARY KEY (id)
);

CREATE UNIQUE INDEX IX_user_username ON users(username);

Then just use the server-side language to convert the username to store to username and store the unmodified version in full_username when the user registers.

(I actually store the second column in the user_info table, not the main user table though)

this is the key point, as well as the datatype (non-binbary versus binary)