在注册时，检查用户名是否存在 - 大写问题

Question

My homefried account registration system proceeds thusly:

1. complete registration form
2. check if username already exists - reject/pass
3. copy data to temp user table, send confirmation email
4. upon using link in confirmation email, copy data from temp to active user table

The other day a user emailed me to say he was getting the error message "Could not create user."

I couldn't recall ever hearing that, so I dug into my registration code and that error pops out if the query to insert the temp data into the active user table fails.

It failed because his username, which has a unique index in the table, already existed in the active user table... but he had passed the initial existing username check...?

Turned out the name he had tried to register was "User" but there was already a "user" -- so case-insensitivity is coming into play.

Does username exist? query string:

SELECT username FROM user_basic_data WHERE username='$cleanTempUsername'

Confirmed, now insert into active table string:

INSERT INTO user_basic_data (username, ...) VALUES ('$activeUsername', ...)

So the case-sensitivity apparently applies when I run a select statement ("User" != "user") but does not apply when I run an insert.

Capitalization is important to many users, so what's my fix of least resistance from here?

Answer 1

Best move would probably be to use a case-sensitive collation for your tables.

Maybe try latin1_bin or utf8_bin.