Case sensitive password query but not username

Question

What's the easiest way to re-phrase this query so that the username is not case-sensitive but the password is case-sensitive?

$query = mysql_query("select * from login where password='$password' AND username='$username'", $connection) or die(mysql_error());

please refer this answer: http://stackoverflow.com/questions/5938037/how-to-make-a-select-in-php-mysql-case-insensitive — Sourabh Kumar Sharma, May 21 '15 at 14:26
Unrelated to your question, please don't directly use PHP variables in your queries, as it makes you vulnerable to SQL injection. — Muhammad Abdul-Rahim, May 21 '15 at 14:27
Further to the above comment, don't use `mysql` functions, as they are being deprecated. Switch to `mysqli` or `pdo`. — Tim Lewis, May 21 '15 at 14:35
As another aside, please consider using `mysqli_` instead of `mysq_l` as `mysql_` is now deprecated. — Kevin Nagurski, May 21 '15 at 14:35
Storing passwords in plain text is bad practice. Please see this ancient (2007) blog post from Jeff Atwood. [http://blog.codinghorror.com/youre-probably-storing-passwords-incorrectly/](http://blog.codinghorror.com/youre-probably-storing-passwords-incorrectly/) — spencer7593, May 21 '15 at 14:57
**DON'T** store the password as plain text and **don't** write your own "encryption" routine. Use well-established encryption (or hashing) code for passwords. — axiac, May 21 '15 at 15:11
[**B.5.5.1 Case Sensitivity in String Searches** https://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html](https://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html) — spencer7593, May 21 '15 at 15:14

axiac · Accepted Answer · 2015-05-21T15:02:04.540

The correct way to do this is to let the database handle the case sensitivity. In order to do that you have to change the collation of the field username or password (it depends of their current collation).

All you have to do is to change the collation of the field username to a collation whose name ends with _ci (it stands for case insensitive) and the collation of the field password to a collation whose name ends with _cs (from case sensitive).

The actual names of the collations depend on the character set of your table.

For example, this is the definition of a table that uses latin1 as a charset:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `password` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If you change the collation you don't need to worry about the case sensitivity of the values any more and you don't need to change the queries.

Sample code using the table above

mysql> INSERT INTO `users` (`username`, `password`)
    -> VALUES ('John Doe', 'passwd1'), ('john doe', 'PASSWD1');
Query OK, 2 rows affected (0.00 sec)


mysql> SELECT * FROM `users` WHERE `username` = 'JOHN DOE';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | John Doe | passwd1  |
|  2 | john doe | PASSWD1  |
+----+----------+----------+
2 rows in set (0.00 sec)


mysql> SELECT * FROM `users` WHERE `password` = 'passwd1';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | John Doe | passwd1  |
+----+----------+----------+
1 row in set (0.00 sec)

Alternative solution, using the excellent suggestion of @spencer7593

You don't need to change the collation of the fields (it is recommended but if you cannot do it then this is an alternative solution). You can force the collation you want in the query:

Force case sensitive comparison of username:

mysql> SELECT * FROM `users` WHERE `username` COLLATE latin1_general_cs = 'john doe';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | john doe | PASSWD1  |
+----+----------+----------+
1 row in set (0.00 sec)

Force case insensitive comparison of password:

mysql> SELECT * FROM `users` WHERE `password` COLLATE latin1_general_ci = 'passwd1';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | John Doe | passwd1  |
|  2 | john doe | PASSWD1  |
+----+----------+----------+
2 rows in set (0.00 sec)

Or, if OP can't (or doesn't want to) change the collation of the column(s), OP can also specify collation (using the **`COLLATE`** keyword) in the SELECT statement. +1 [https://dev.mysql.com/doc/refman/5.5/en/charset-collate.html](https://dev.mysql.com/doc/refman/5.5/en/charset-collate.html) Available collation names can be retrieved with `SHOW COLLATION` statement. — spencer7593, May 21 '15 at 14:54
@spencer7593 excellent idea! I learned something today too. Thank you. — axiac, May 21 '15 at 15:02
good answer to the general problem, but encourages a terribly bad habit of storing a plaintext password :/ — pala_, May 21 '15 at 15:08

score 1 · Answer 2 · answered May 21 '15 at 14:27

1

Just normalise the case.

$query = mysql_query(
    "
        SELECT *
        FROM login
        WHERE
            password='$password'
            AND LOWER(username)=LOWER('$username')
    ",
    $connection
) or die(mysql_error());

answered May 21 '15 at 14:27

Kevin Nagurski

1,889
11
24

This way you cannot use an index on `username`. – axiac May 21 '15 at 14:33
True, best to user `LOWER` when inserting users into the DB and then you can use just the second `LOWER` for the comparison. – Kevin Nagurski May 21 '15 at 14:34
I thought that queries are case-insensitive? This would only work if you could force the query to be case-sensitive. – Andy Groom May 21 '15 at 15:44
@AndyGroom see axiac's answer above. My answer only applies if you're case sensitive. – Kevin Nagurski May 21 '15 at 15:58

score -1 · Answer 3 · answered May 21 '15 at 14:27

Quite Simply you can use a LIKE, for example:

$query = mysql_query("select * from login where password='$password' AND username LIKE '$username'", $connection) or die(mysql_error());

Alternatively you can do partial matches like so, but this is not what you need:

$query = mysql_query("select * from login where password='$password' AND username LIKE '%$username%'", $connection) or die(mysql_error());

Case sensitive password query but not username

3 Answers3

Sample code using the table above

Alternative solution, using the excellent suggestion of @spencer7593