MySQLでANDとORを併用する時の落とし穴とその回避策

はじめに

複雑なSQLクエリの解析や、複数条件を含むクエリの作成は面倒ですよね。
ただ、解析が不十分のままSQLを実行すると、後になって思わぬインシデントを引き起こす可能性があります。
この記事では、私がMySQLの検索クエリを作成する際に経験した失敗例と、そこから学んだ教訓を共有します。

事例：意図しない検索結果を取得していた。。。

以下では、実際のケースに類似した例を用います。

私は特定の店舗を指定して、その店舗に所属するユーザを取得する検索クエリを作成したつもりでした。
しかし、実際にはほぼすべてのユーザ情報を取得していました。
これはセキュリティ上のリスクとなりうるため、絶対に避けなければなりません。
なぜこのような検索結果になってしまったのでしょうか。

以下に、私が経験した失敗例を再現したテーブル構成を示します。
usersテーブル

id	name	store	delete_flag
1	佐藤	東京店	0
2	鈴木	大阪店	0
3	高橋	東京店	1
4	田中	名古屋店	0
5	伊藤	大阪店	1

検索条件は以下の通りです。

• 「東京店 または 大阪店のユーザ」かつ 「delete_flag が 0」

この時、本来取得したいのは次の2レコードです。

id	name	store	delete_flag
1	佐藤	東京店	0
2	鈴木	大阪店	0

しかし、実際に取得された結果は次のようになりました。

id	name	store	delete_flag
1	佐藤	東京店	0
2	鈴木	大阪店	0
3	高橋	東京店	1

id=3のユーザはdelete_flag=1であるにもかかわらず、意図せず取得されています。
ユーザ数が更に多い場合や、他の複数のWHERE条件がある場合、このような誤取得の影響は広がり、予期せぬ全件取得や不要なデータ漏洩の原因となります。
なぜこのような結果が返ってきたのでしょうか。
デバッグをしてみると、実際に実行されたSQLは以下でした。

SELECT *
FROM users
WHERE store = '東京店' OR store = '大阪店' AND delete_flag= 0;

何が誤りかは気づきましたか？？

原因：OR条件を括弧で囲まなかった

MySQLでは、複数の論理演算子（AND、OR）を含む場合、演算子ごとの優先順位に従って処理されます。

MySQL 8.0 演算子の優先順位
https://dev.mysql.com/doc/refman/8.0/ja/operator-precedence.html
次のリストには、演算子の優先順位をもっとも高いものから順番に示しています。 同じ行に並んで記載されている演算子は、優先順位が同じものです。
（略）
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

今回のクエリでは、以下の条件を追加していました。

AND delete_flag = 0

しかし、先に記述したOR条件を括弧で囲まなかったため、MySQLは次のように解釈しました。

store = '東京店' OR (store = '大阪店' AND delete_flag = 0)

その結果、東京店のユーザは delete_flag に関係なく取得されることになり、意図しないレコードが含まれてしまいました。
つまり、MySQLに正しい優先順位で評価させることが出来ていなかったのです。
意図どおりに「東京店 または 大阪店のユーザ」かつ「delete_flag が 0」を取得するには、OR 条件を括弧で囲み、優先順位を明示する必要があります。
正しいSQL：

SELECT *
FROM users
WHERE (store = '東京店' OR store = '大阪店')
  AND delete_flag= 0;

回避策

実務で実行するSQLクエリは、上で示した例がかわいく思えるほど、はるかに複雑なWHERE句を含む場合が多いと思います。
正確なクエリを記述するための方法として、私が実務で扱っているLaravelのクエリビルダで活用できる方法を紹介します。

1.複雑なクエリはtoSQL()で出力して生クエリを確認

toSQL()は、クエリビルダで生成したSQLを文字列として取得できるメソッドです。
これを画面上やログに出力することにより、AND/ORの括弧の配置や結合条件がどのように評価されるかを正確に把握できます。
これは開発時のデバッグ時に、簡単で有効な方法だと思います。

$users = DB::table('users')
    ->whereRaw("(store = ? OR store = ?)", ['東京店', '大阪店'])
    ->where('delete_flag', 0);

dd($users->toSQL());

2.クロージャを利用する

Laravelのクエリビルダでは、括弧で条件をグループ化したい場合は クロージャを使うのが標準的な方法です。

Logical Grouping
https://laravel.com/docs/12.x/queries#logical-grouping
クエリで意図した論理的なグループ化を実現するために、複数の「where」句を括弧で囲んでグループ化する必要が生じる場合があります。実際、予期しないクエリ動作を避けるため、orWhereメソッドの呼び出しは常に括弧で囲むべきです。これを実現するには、whereにクロージャを渡す方法があります。

クロージャとは、括弧で囲みたい条件を function($query) { ... } の中に記述することで、条件をグループ化できる仕組みです。

$users = DB::table('users')
    //store = '東京店' OR store = '大阪店'の条件を括弧で囲むクロージャ
    ->where(function ($query) { 
        $query->where('store', '東京店')
              ->orWhere('store', '大阪店');
    })
    ->where('delete_flag', 0);

クロージャ内の条件は 外側の条件とは別の小さなグループ として評価されます。
その結果、生成されるSQLには自動的に括弧が追加され、AND/ORの優先順位を明確に指定できます。

おわりに

複雑なクエリを利用する際は、意図したクエリが正確に実行されていることを確認しておかないと、思わぬ落とし穴にはまってしまいます。
今回の事例を通して、生のクエリを解析できる力は重要だと思いました。
最終的に生成されるSQLを理解できれば、AIにクエリを生成させる場合でも、あるいは異なるフレームワークのクエリビルダを使用する場合でも、いつでも正確なクエリを実行できるコードが書けるでしょう。
SQLを利用している皆さんの参考になれば幸いです。