MySQL SELECT statement case sensitive AND special char

Question

I am doing a login script in php and html. The username and password dont need to be encrypted so I was trying to store them directly into the database (MySQL).

But some of my users have username and password containing special characters like "é" and must be case sensitive.

I have this table:

USER username: "ÉleCTeur", password: "ÉleCTeur", username: "Paul", password: "Paul"

I tried this query that work fine with "Paul" but return nothing with "ÉleCTeur".

SELECT 
    username,
    password
FROM 
    user
WHERE
    BINARY username = 'ÉleCTeur' 
    AND BINARY password = 'ÉleCTeur'

Did I just miss something or do BINARY does not compare special characters? What methode could I use to resolve this issue?

Answer 1

You probably have inconsistent encoding between your db and the client program?

I take some time testing from MySQL CLI:

mysql> create TABLE user (username CHAR(20), password CHAR(20)) DEFAULT CHARSET=latin1 DEFAULT COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into user value("ÉleCTeur", "ÉleCTeur");
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----------+----------+
| username | password |
+----------+----------+
| ÉleCTeur | ÉleCTeur |
+----------+----------+
1 row in set (0.00 sec)

mysql> select count(*) from user where username = "EleCTeur" and password = "EleCteur";
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from user where username = "ÉleCTeur" and password = "ÉleCteur";
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

EDIT: OK, this was a (too) quick and (far too) dirty test. As Sebastien noticed it, I use case insensitive collation here. So:

mysql> select count() from user where username = "éleCTeur" and password = "éleCteur"; +----------+ | count() | +----------+ | 1 | +----------+ 1 row in set (0.01 sec)

The real answer here would be to use an case sensitive (_cs) collation. To know which one are available:

mysql> show collation where collation like '%_cs';
+--------------------+---------+----+---------+----------+---------+
| Collation          | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+----+---------+----------+---------+
| latin1_general_cs  | latin1  | 49 |         | Yes      |       1 |
| latin2_czech_cs    | latin2  |  2 |         | Yes      |       4 |
| cp1250_czech_cs    | cp1250  | 34 |         | Yes      |       2 |
| latin7_estonian_cs | latin7  | 20 |         | Yes      |       1 |
| latin7_general_cs  | latin7  | 42 |         | Yes      |       1 |
| cp1251_general_cs  | cp1251  | 52 |         | Yes      |       1 |
+--------------------+---------+----+---------+----------+---------+
6 rows in set (0.01 sec)

Not much of them on my Debian MySQL default install.

And surprisingly enough, no 'utf8_cs' !

But MySQL has _bin collation too. Not exactly useful for ordering but quite sufficient for searching:

mysql> show collation where collation like '%_bin';

+--------------+----------+----+---------+----------+---------+
| Collation    | Charset  | Id | Default | Compiled | Sortlen |
+--------------+----------+----+---------+----------+---------+
| big5_bin     | big5     | 84 |         | Yes      |       1 |
| dec8_bin     | dec8     | 69 |         | Yes      |       1 |
| cp850_bin    | cp850    | 80 |         | Yes      |       1 |
| hp8_bin      | hp8      | 72 |         | Yes      |       1 |
| koi8r_bin    | koi8r    | 74 |         | Yes      |       1 |
| latin1_bin   | latin1   | 47 |         | Yes      |       1 |
| latin2_bin   | latin2   | 77 |         | Yes      |       1 |
| swe7_bin     | swe7     | 82 |         | Yes      |       1 |
| ascii_bin    | ascii    | 65 |         | Yes      |       1 |
| ujis_bin     | ujis     | 91 |         | Yes      |       1 |
| sjis_bin     | sjis     | 88 |         | Yes      |       1 |
| hebrew_bin   | hebrew   | 71 |         | Yes      |       1 |
| tis620_bin   | tis620   | 89 |         | Yes      |       1 |
| euckr_bin    | euckr    | 85 |         | Yes      |       1 |
| koi8u_bin    | koi8u    | 75 |         | Yes      |       1 |
| gb2312_bin   | gb2312   | 86 |         | Yes      |       1 |
| greek_bin    | greek    | 70 |         | Yes      |       1 |
| cp1250_bin   | cp1250   | 66 |         | Yes      |       1 |
| gbk_bin      | gbk      | 87 |         | Yes      |       1 |
| latin5_bin   | latin5   | 78 |         | Yes      |       1 |
| armscii8_bin | armscii8 | 64 |         | Yes      |       1 |
| utf8_bin     | utf8     | 83 |         | Yes      |       1 |
| ucs2_bin     | ucs2     | 90 |         | Yes      |       1 |
| cp866_bin    | cp866    | 68 |         | Yes      |       1 |
| keybcs2_bin  | keybcs2  | 73 |         | Yes      |       1 |
| macce_bin    | macce    | 43 |         | Yes      |       1 |
| macroman_bin | macroman | 53 |         | Yes      |       1 |
| cp852_bin    | cp852    | 81 |         | Yes      |       1 |
| latin7_bin   | latin7   | 79 |         | Yes      |       1 |
| cp1251_bin   | cp1251   | 50 |         | Yes      |       1 |
| cp1256_bin   | cp1256   | 67 |         | Yes      |       1 |
| cp1257_bin   | cp1257   | 58 |         | Yes      |       1 |
| geostd8_bin  | geostd8  | 93 |         | Yes      |       1 |
| cp932_bin    | cp932    | 96 |         | Yes      |       1 |
| eucjpms_bin  | eucjpms  | 98 |         | Yes      |       1 |
+--------------+----------+----+---------+----------+---------+
35 rows in set (0.00 sec)

In the following example, I create a table using utf8 encoding and utf8_bin collation. Please note you could change encoding/collation on a per-column basis instead.

mysql> create TABLE user (username CHAR(20), password CHAR(20)) DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_bin;
Query OK, 0 rows affected (0.26 sec)

mysql> insert into user value("ÉleCTeur", "ÉleCTeur");
Query OK, 1 row affected (0.00 sec)


mysql> select count(*) from user where username = "ÉleCTeur" and password="ÉleCTeur";
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from user where username = "éleCTeur" and password="éleCTeur";
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)