Yappli Tech Blog

株式会社ヤプリの開発メンバーによるブログです。最新の技術情報からチーム・働き方に関するテーマまで、日々の熱い想いを持って発信していきます。

BigQuery内にある多重エンコードされたURLのデコード

BigQuery SQL

こんにちは！データサイエンティストの山本です（ @__Y4M4MOTO__ ）です。

今回は、ヤプリのデータ基盤内にあるウェブビューURLのデコードに取り組んでみました。この記事では、その際につまづいたポイントやその解決策について記します。

ウェブビューURLのエンコード問題

ヤプリではアプリ内のスクリーンデータを計測しています。これにより、スクリーンビューの集計等の分析を行うことができます。スクリーンがウェブビュー機能の場合、スクリーン名にはウェブビューに表示するページのURLが入ります。

このとき、問題になってくるのが「ウェブビューのURLがエンコードされている」という点です。URLがエンコードされているとどのページのURLなのかが人間には解読できず、分析結果として活用しづらくなってしまいます（例えば、「 https://example.com/%E3%83%9B%E3%83%BC%E3%83%A0 のスクリーンビューは○○です」と言われても、「どのページの話…？」となりますよね）。

そこで、エンコードされたURLをデコードすることで、この問題を解消しようと試みました。デコードは試行錯誤していくつかの方法を試したので、以下にその過程を記します。

最初のデコード方法｜単純にデコードする

最初は URL の中のエンコードされた部分（ %E3%83 など）を検出してデコードする方法を採りました。

URL の中のエンコードされた部分の検出処理は次のステップで行っています:

  1. REGEXP_EXTRACT_ALL() に正規表現 %[0-9a-fA-F]{2}(?:%[0-9a-fA-F]{2})*|[^%]+ を適用し、 URL をエンコードされた部分とそれ以外の部分に分割
  2. 分割結果を UNNEST() と WITH OFFSET で連番つきの行に変換

例えば、 https%3A%2F%2Fexample.com%2Fq%3D%E3%81%BB%26param%3D%E3%81%B0%2C%E3%81%B5 という URL の場合、次のように分割されます:

URLの分割結果の一例

デコード処理は次のステップで行っています:

  1. %XX から XX の部分を抽出
  2. XX の部分を FROM_HEX() で BYETS 形式に変換
  3. 変換結果を SAFE_CONVET_BYTES_TO_STRING() で文字列へ変換

全体の実装はこちらです:

CREATE TEMP FUNCTION URLDECODE(url STRING) AS ((
  SELECT
    STRING_AGG(
      IF(
        REGEXP_CONTAINS(y, r'^%[0-9a-fA-F]{2}'), 
        SAFE_CONVERT_BYTES_TO_STRING(FROM_HEX(REPLACE(y, '%', ''))), 
        y
      ), 
      '' ORDER BY i 
    )
  FROM
    UNNEST(
      REGEXP_EXTRACT_ALL(url, r"%[0-9a-fA-F]{2}(?:%[0-9a-fA-F]{2})*|[^%]+")
    ) y
    WITH OFFSET AS i 
));

しかし、上記実装では一部の URL でエンコードされた部分が残ってしまいました。原因を調査したところ、これらの URL は二重にエンコードされていることが分かりました。

例えば、 = はエンコードすると %3D になります。これをさらにエンコードすると %253D となります。なぜなら % はエンコードすると %25 になるからです。上記実装ではデコードを1回しか行わないので、この %253D という文字列を上記実装でデコードすると %3D という文字列が残ってしまいます。

なおエンコードが重なるケースの詳細は不明ですが、ログイン関連で発生する場合がヤプリでは多そうでした（確認したところ、エンコードが重なってる URL の半数に login という文字列が含まれていました）。

次のデコード方法｜ %25 だけ先にデコードする

前述の二重エンコード問題を解決するために、前処理として %25 だけ先にデコードし、その後ほかの文字列もデコードするという方法を採りました。

実装では先ほどのクエリに次の差分を加えます:

  CREATE TEMP FUNCTION URLDECODE(url STRING) AS ((

+   WITH
+     cte AS (
+       SELECT REGEXP_REPLACE(url, r"%25","%") as r_url
+     )

    SELECT
      STRING_AGG(
        IF(
          REGEXP_CONTAINS(y, r'^%[0-9a-fA-F]{2}'), 
          SAFE_CONVERT_BYTES_TO_STRING(FROM_HEX(REPLACE(y, '%', ''))), 
          y
        ), 
        '' ORDER BY i 
      )
    FROM
      UNNEST(
+       REGEXP_EXTRACT_ALL((SELECT r_url FROM cte), r"%[0-9a-fA-F]{2}(?:%[0-9a-fA-F]{2})*|[^%]+")
      ) y
      WITH OFFSET AS i 
  ));

しかし、この実装でもまだ一部の URL でエンコードされた部分が残ってしまいました。原因調査を進めたところ、二重どころか三重や四重…と多重でエンコードされていることが原因と分かりました（ちなみに、最大で何重エンコードされているのか調べてみたところ、11重でした）。

最終的なデコード方法｜ r’%25(25)* で先にデコードする

前述の多重エンコード問題を解決するために、前処理で先にデコードする文字列を %25 ではなく %2525… という繰り返し（正規表現だと %25(25)* ）に変更しました。

実装では先ほどのクエリにさらに次の差分を加えます:

  CREATE TEMP FUNCTION URLDECODE(url STRING) AS ((

    WITH
      cte AS (
+       SELECT REGEXP_REPLACE(url, r"%25(25)*","%") as r_url
      )

〜〜（以下略）〜〜

これでエンコードされていた全ての URL を完全にデコードすることができました。

ただし、上記実装ではデコード処理を2回行っているため、エンコードの文字でない通常の % を誤解釈してデコードしてしまうリスクがあります（例: 割引5%2520日 → 割引5%20日 → 割引5 日 ）。今回は、次の理由からこのリスクは許容することにしました:

  • 通常の % が URL 中に含まれるケースはごく少数と考えられるため
  • デコード前の URL もデータとして保持しており、復元可能であるため

まとめ

この記事では、 Yappli のデータ基盤内にあるウェブビューURLのデコード処理について、実装時につまづいたポイントやその解決策について記しました。

URL デコードで困っている際にこの記事が参考になれば幸いです！