MySQLを使ったログインシステムでちょっと

たとえ休日だろうとも仕事をやらないと時間が足りなくなってきているので仕事をやっています。

で、そのときにMySQLを使ってログインを行うシステムを作っていたときに起こった出来事。

もしかしたらいろんなシステムでこのはまりがあるかも・・・

ユーザー名の大文字小文字が同一視されている!

ちょっとびっくりした現象です。とあるログインをおこなすシステムを作っているときにわかったのですが、

MySQLを使ってユーザー名およびパスワードを判定するシステムですが、ユーザー名に使っている小文字が大文字になってもログインが通過してしまう、ということに気がつきました。

ユーザー名だけならまだましの可能性もありますが、これってちょっと気持ち悪いですよね。ユーザー名の総当たりの部分が大幅に減ることになるので。

MySQLの照合順序のデフォルトは「大文字小文字を同一視する」

これが鍵になります。いまならだいたいのシステムでMySQLを使って文字列を扱うなら通常はUTF-8になると思うのでそれを前提にしてやります。

で、このとき、テーブルに設定される照合順序はだいたいutf8_general_ciもしくはutf8_unicode_ciになります。

このciというのがくせ者で、case-insensitiveの略であり、「大文字小文字を区別しない」という意味を持ちます。

(なお、「大文字小文字を区別する」UTF-8の照合順序はciをcs(case-sensitive)にしたもの・・・はないので、utf8_binという方式を使います。)

つまり、たとえば

CREATE TABLE loginuser (
	userid integer primary key auto_increment,
	username varchar(64) not null,
	password varchar(128) not null
) Engine=InnoDB;

というテーブルを作った上で

SELECT userid FROM loginuser WHERE username = 'username' AND password = 'password';

としてログイン確認をしたときと

SELECT userid FROM loginuser WHERE username = 'UserName' AND password = 'PassWORD';

としてログイン確認したときでは同じ答えが返ってきてしまいます。

まあ、パスワードの方はこんな直接的なやり方はしていない(だいたいパスワード文字列のハッシュをとってそれを入れているはずな)のでまだいいですが、

それでもユーザー名側がどちらでもよいことになると大変なことになります。

大文字小文字の同一視は文字列検索時にはとてもよいのですが、こういうときには大変ですね。

で、対応法は?

やり方は4つくらいあります。使いやすい物を選んでください。

1. データベース作成時にcollateによって「大文字小文字を区別する」照合順序を設定する

データベースの作成権があるときには使って使えないことはないやり方ですがあまりおすすめしません。

というのも、対象のデータベースの文字列検索がすべて「大文字小文字を区別する」形式になってしまうので対象のテーブル以外の検索に影響するからです。

ほとんどのテーブルの検索では「大文字小文字を区別しない」検索が有利であることは言うまでもないと思います。

ちなみに、これをやるときは

CREATE DATABASE loginsystem DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

のように記述します。

2. テーブル作成時にcollateによって「大文字小文字を区別する」照合順序を設定する

というわけで、照合順序の適応範囲をテーブル単位に落とします。

上記の例で言うなら

CREATE TABLE loginuser (
	userid integer primary key auto_increment,
	username varchar(64) not null,
	password varchar(128) not null
) Engine=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

のようにしてテーブルを作成します。

利点は「テーブルの作成時に記述するだけでよいのでシステムを組んでいるときに考える必要がない」ということ。

欠点は「対象のテーブルに大文字小文字を区別しない検索をしたい要素があるときに検索順序を毎回変更する必要がある」ということ。

この方法が使えるかどうかは欠点が無視できるような状態になっていることが条件になるでしょう。

3. SELECTのクエリを行う段階で強制的に照合させる

この場合も二通りの方法があります。まずは

SELECT userid FROM loginuser WHERE username = 'username' AND password = 'password' collate utf8_bin;

と文の最後にcollateをつけて照合順序を変更するもの。もう一つは

SELECT userid FROM loginuser WHERE username = binary 'username' AND password = 'password';

と「大文字小文字を区別する」検索を行いたい要素にbinaryをつけてバイナリとして扱わせることです。

前者の場合は記述は楽なのですが、特定の方法でデータベースおよびテーブルを作成しないとうまくいかないこととWHERE句のアイテムすべてに照合順序が適応されるためによけいになりがちであること。

後者の場合は変更したい要素にだけつけるので安全なのですが、binaryをつける位置によってはSELECTの速度が大幅に下がることと、アイテムを間違えないように記述する必要があること。

どちらがよいかは気分次第で。後者の方がおすすめです。(前者は失敗することが多いようなので)

ちなみに、binaryをつける場合はどうやら要素名よりも対象値に付ける方が早いらしいです。

というわけで皆さんも気をつけましょう

というか、utf8_unicode_ciの「同一性」がとんでもないな~と思いました。

大文字小文字だけならず半角/全角やひらがな/カタカナにも及ぶというのは使い勝手がいい場合と悪い場合が混じると思います。

その辺の使い分けも上記の方法である程度変更が可能なのでそういうことも考えながら組むといいと思います。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

この記事のトラックバック用URL