質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.65%

laravelでクエリビルダを使用した時のcollationエラー

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 476

rena1996

score 1

前提・実現したいこと

前提として
下のようなMySQLのuserテーブルがあります。  

ID name address
1 tanaka hogehoge@example.com
2 suzuki fugafuga@example.com
3 yoshida piyopiyo@example.com

テーブルの情報が下記です。

Field Type Collation
id int(10) unsigned NULL
name varchar(255) utf8_unicode_ci
address varchar(255) utf8_unicode_ci

この際に

SELECT * from user WHERE name="Suzuki";


というクエリを送ると全角半角、大文字小文字区別せずに下記のように引っ張ってきてしまいます。

ID name address
1 suzuki fugafuga@example.com

そこで、

SELECT * from user WHERE name="Suzuki" collate utf8_bin;


とすると、全角半角大文字小文字を区別してくれます。
つまり、クエリを送った結果は「Empty set」となります。

以上をLaravelのクエリビルダを用いて実行する場合に

$name = "Suzuki"
$query = "SELECT * FROM user WHERE name=$name collate utf8_bin";

$result = DB::connection('example')->select($query);


とするとほしい結果が得られますが、SQLインジェクションの問題があると考えたので、

$name = '"'."Suzuki".'"';

$result = DB::connection('example')->select('SELECT * FROM user WHERE name = ? collate utf8_bin', [$name]);


としました。

発生している問題・エラーメッセージ

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8_bin' is not valid for CHARACTER SET 'binary' (SQL: SELECT * FROM user WHERE name = "Suzuki" collate utf8_bin)

試したこと

ダブルクオーテーションをシングルクオーテーションにしたり、
utf8_binがいけないのかと思いutf8_general_ciに変えて実行しましたが、解決せずでした。

また、MySQLの対象テーブルのcollation設定をutf8_binに変更することでMySQL側で区別してくれるのだとは思いますが、都合上、MySQL側の変更は無しという前提でお願い致します。

補足情報(FW/ツールのバージョンなど)

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • rena1996

    2019/08/08 15:23

    Kosuke_Shibuya様
    migration機能を使ってはテーブル設定を変更するということでしょうか?

    キャンセル

  • Kosuke_Shibuya

    2019/08/08 15:25

    利用しているのかしていないのかを質問したんですが。

    キャンセル

  • rena1996

    2019/08/08 15:50

    しております。

    キャンセル

回答 2

checkベストアンサー

+1

migration を利用しているということなので。

まず、collation について説明します。

また、MySQLの対象テーブルのcollation設定をutf8_binに変更することでMySQL側で区別してくれるのだとは思いますが、都合上、MySQL側の変更は無しという前提でお願い致します。

この書き方だと、あなたの理解が不十分だと感じましたので。

collationは、Database, table, column それぞれに設定することができます。
未設定である場合は、my.ini のデフォルト設定になります。

おそらく現状は database の collation 設定が、utf8_binに設定されているのでしょう。
大文字小文字の区別をしたくないのであれば、utf8_unicode_ci にするというアプローチは正解です。
ただ、 SQLクエリを投げるたびに selectRaw() を利用するなど、めんどくさくてやってられません し、Laravelの恩恵が全くありません。なので、悪手も悪手。

都合上、MySQL側の変更は無しという前提でお願い致します。

とは書かれていますが、これはあくまでDatabeseレベルの話であって、tableレベルの話ではないと推測します。

で、あれば、migrationでtableもしくはcolumnのcollationを設定すれば済むのではありませんか?

Schema::table('table_name', function(Blueprint $table){
    $table->string('name')->collation('utf8_unicode_ci')->change();
});

こんな感じで。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

プリペアドステートメントのパラメータを送るときちんとクォーテーションもつけてくれるので、
わざわざ自前でクォーテーション付けて送る必要はないと思います。

※MySQLはシングルでもダブルでも通りますが、SQLで文字列扱いにしたいならシングルクォーテーションで癖づけておいたほうが良いです

$name = '"'."Suzuki".'"';
↓
$name = 'Suzuki';

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/08/08 15:28

    ご指摘ありがとうございます。
    上記のように変更すると
    ```
    SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8_bin' is not valid for CHARACTER SET 'binary' (SQL: SELECT * FROM user WHERE name = Suzuki collate utf8_bin)
    ```
    このようなエラーになります。
    一度このエラーに遭遇し、Suzukiがクォーテーションで囲まれていないため上記エラーになるのかと思い、"Suzuki"とした次第でございます。
    結局は同じでしたが…
    シングルクオーテーションの件、そのようにいたします。ありがとうございます。

    また、上記エラーの対策方法ございましたらご教示頂けませんでしょうか。

    キャンセル

  • 2019/08/08 15:31

    エラー同じですね。
    私は直面したことはないので「調べた結果」になりますが、
    https://akamist.com/blog/archives/1051
    やはりcharaset問題なのではないかなと思っています。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.65%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる