MySQL berücksichtigt keine Gross- und Kleinschreibung bei select?

Manchmal habe ich echt das Gefühl, ein paar Jahre ganz feste kognitiv eingeschränkt gewesen zu sein. Per Zufall stellte ich heute fest, was das „ci“ bei den Datenbank Collations in Mysql heisst.

<musik typ="leicht-nervig" creator="Synthesizer" dauer="10" />
Moderator: Wahaaas bedeutet das Zeh Ih (_ci) bei den Datenbank Collations in MySQL?
Kandidat: Hmmm, uff, kann ich es im Kontext haben?
Moderator: Natürlich! Zeh Ih wie in utf8_general_ci.
Kandidat: Ui, das weiss ich nicht, gibt es Auswahlmöglichkeiten?
Moderator: Ja sicher:

  1. collation identifier
  2. childish internet
  3. clown institut
  4. corporate identity
  5. case insensitive


Und die Lösung: CASE INSENSITIVE

Was bedeutet _ci in der Praxis?

Tabelle:

+----+----------+----------+------------------------------------------+
| id | username | salt     | password                                 |
+----+----------+----------+------------------------------------------+
|  1 | Admin    | 2IT8mKiX | 8fd334609270a4b78c536dbdee4182b5b1d00bb7 |
+----+----------+----------+------------------------------------------+

Tja, folgender Tabelleneintrag wird gefunden mit folgenden Selects:

SELECT * FROM users WHERE username='Admin'; -- Klar, logo
SELECT * FROM users WHERE username='ADMIN'; -- Ui
SELECT * FROM users WHERE username='admiN'; -- Uiui
SELECT * FROM users WHERE username='admin'; -- Uiui, huch

Und das impliziert wiederum, dass ein User, der sich ADMIN nennt, unter Umständen vor dem eigentlichen Admin gefunden wird. Das ist doch eher hässlich. Wieso ist mir das nicht früher aufgefallen..?

Und die Lösung?

Die Collation ändern! Von utf8_general_ci zu utf8_bin und schon verhält sich die Datenbank erwartungskonform.

Was bedeutet dies für Rails?

Zum Einen sollte die Collation in der Datei config/database.yml festgelegt werden:

development:
  adapter: mysql
  encoding: utf8
  collation: utf8_bin
  database: lomas_development
  pool: 5
  username: lomas
  password: sagichnit
  socket: /var/run/mysqld/mysqld.sock

Leider wird die Collation bei Tests in Ruby on Rails nicht berücksichtigt! Das treibt mich zur Verzweiflung, echt!

Ausserdem gibt es noch etwas zu beachten bei validates_uniqueness_of. validates_uniqueness_of ist case-sensitive. Das heisst, auch hier werden admin und ADMIN akzeptiert. Erst ein weiterer Parameter verbietet dies:

  validates_uniqueness_of :username, :case_sensitive => false