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

2 Gedanken zu “MySQL berücksichtigt keine Gross- und Kleinschreibung bei select?

  1. Hey,

    utf8_bin hat aber, zumindest bei mir, ebenfalls zur Folge, dass z.B. im phpmyadmin man diese Felder nicht mehr einschauen kann ([BINARY – 4Bytes]). Bearbeiten geht weiterhin.

    Man kann ebenfalls in das SQL Statement, sollte man case sensitivität benötigen ein where BINARY user = ‚ADMIN‘ machen.

    Wenn man sich jetzt überlegt, was das für seine ganzen auf MySQL entwickelten Applikationen zu bedeuten hat: Registriert sich ein User TesT und man prüft vorher, dass der Name TesT noch nicht vorhanden ist, bekommt man auch test, Test etc. zurück und der Name wäre somit schon vergeben und der User muß sich einen anderen aussuchen. Also in dem nicht weiters tragisch.

    Hoffe ich habe noch etwas mehr Licht ins Dunkle gebracht.

  2. Hm, bei mir klappts mit dem Ansehen „phpMyAdmin – 2.11.8.1deb1“.

    Vielen Dank für den Hinweis mit „BINARY“. Das Problem ist hier, dass man damit die „DB.Unabhängigkeit“ verlässt und bei einem Wechsel im Code drin rumwurschteln muss.

    Auch mit dem zweiten Hinweis hast Du recht. Man muss einfach konsistent bleiben :)…

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.