CODEWITHSUNDEEP
phpmysqlcase-insensitive
Tim
Tim

Reputation: 7056

mysql and php suddenly case-sensitive?

Basically, I've changed something in my php login code and now when I log in, the MySQL requires that the case matches?!

    $match = "select * from users where username = '".$username."' and password = '".$password."';";

    echo $match;

If my username's CaSe doesn't match the database then it doesn't return any rows.

If a user registers as ADMIN then I want them to be able to log in as "admin" or "AdMiN" etc. any ideas?

Many thanks :)

Upvotes: 1

Views: 650

Answers (1)

Matt Gibson
Matt Gibson

Reputation: 38238

The collation for that column has changed, I guess. See this MySQL page about case sensitivity in string comparison to learn more. To insulate yourself from problems like that, something like:

$match = "select * from users where UPPER(username) = '".strtoupper($username)."' and password = '".$password."';";

is the usual trick. As others have observed, though, understanding how collation works in the database and using it appropriately is the best solution. Getting the database collation right and relying on it should allow the database to use any index on the username column, rather than scanning the table.

And also make sure that you're avoiding SQL injection attacks -- what happens in your current code if a user enters a username containing a single quote?

You may also want to consider what characters you'll allow as usernames, and what character set you're using -- is "René" a valid username? Will it successfully pass from your front-end, through the database and back out unmolested by character set problems? Joel Spolsky's excellent article on Unicode is probably worth a read at some point.

Upvotes: 8

Related Questions