フォト蔵APIで取得する画像 URL が最近のサイト不具合以降、某クラウドサービスとおぼしき クエリ文字列 を含でいることに最近気づきました。そこで、データベース内へ既に取り込んでしまった画像URLは SQL 文で、これから取り込む分はPHP で クエリ文字列 を 削除 する仕組みを考えてみます。
Hong Kong Tram Archive
弊サイトで10年以上続いている老コンテンツ、Hong Kong Tram Archiveは、日々自分で撮った香港トラム画像を時系列、車番別に閲覧できるシンプルなページです。
2万枚を超える膨大な画像データをレンタルサーバにホスティングするのは怖いので、当初より画像は画像SNSサービスのフォト蔵へアップロードし、画像URLへのリンクをサイト内のデータベースに保持する仕組みを採っています。
フォト蔵API
この画像URLをフォト蔵からまとめて取得するのに利用しているのが、フォト蔵APIです。
追加した画像のURLをフォト蔵APIで取得、データベースへインポートする仕組みをPHPで組んで、定期的にこれをブラウザで叩いて更新しているのですが、2023年9月より続くフォト蔵の不具合を機に、URLの仕様に変化がありました。
図1.フォト蔵API画像情報取得例
1つの画像から、大きさの異なる3つの画像URLが得られるのですが、そのいずれにもクエリ文字列が連結されるようになりました。ちなみに、クエリ文字列はあってもなくても画像を開くことは可能です。
|
1 2 3 4 |
変更前） https://kura1.photozou.jp/pub/650/127650/photo/271439348.jpg 変更後） https://kura1.photozou.jp/pub/650/127650/photo/271439348.jpg?AWSAccessKeyId=V06ZQL3UFMGJXOMW7WPB&Expires=1698902029&Signature=0LTLotnvAksMbGgdve%2BF6qPws%2BE%3D |
SQL文でクエリ文字列を一括削除
この仕様変更に気づかず、既に大量のレコードをデータベースへインポート済みも、フィールド長超過により尻切れ不完全な状態で格納されているので、そのままでは利用できません。
フォト蔵APIからインポートするPHPを対処する前に、テーブル内の3つのフィールド（ url1 , url2 , url3 ）に格納されたURLから?以降を一括削除するSQL文を組んでみました（MySQL 5.1.73にて）。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SET @URLFIELD = 'url1'; /* SET @URLFIELD = 'url2'; SET @URLFIELD = 'url3'; */ SET @stmt = CONCAT( 'SELECT ',@URLFIELD,', REPLACE(',@URLFIELD,', SUBSTR(',@URLFIELD,', INSTR(',@URLFIELD,', "?")), "") AS TRIMMED FROM photozo WHERE INSTR(',@URLFIELD,', "?") ORDER BY pid ASC' /* 'UPDATE photozo SET ',@URLFIELD,' = REPLACE(',@URLFIELD,', SUBSTR(',@URLFIELD,', INSTR(',@URLFIELD,', "?")), "") WHERE INSTR(',@URLFIELD,', "?")' */ ); PREPARE stmt FROM @stmt; EXECUTE stmt; |
図2.MySQL Workbenchの例
やっていることは、文字列中の特定の文字 ? の位置を INSTR で取得、その位置以降の文字列を SUBSTR で取得、 REPLACE でそれを長さゼロ文字列に入れ替えるというもの。
SQL文の中に@変数を使う際の振る舞いの特性から、かなり見にくい記述になっていますが、上記 SELECT 文では、クエリ文字列を含むレコードの抽出、 UPDATE 文のコメントを外して SELECT 文側へ掛けると、一括削除更新されます。
@変数の使い方に手間取りましたが、これで既存レコードの修正は完了です。
PHPでのインポート時にクエリ文字列を削除
次に、今後インポートする際にPHP上で予めクエリ文字列を除去するよう、インポートプログラムを変更したいと思います。
これまで、APIから得たxmlで INSERT 文を実行するループを、次のように組んでいました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$root = simplexml_load_string($url); $stmt = $pdo->prepare("INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (:pid, :aid, :ttl, :height, :width, :irul, :ourl, :turl, :rtime)"); foreach ($root->info->photo as $photo) { . . . $stmt->bindParam(':irul', $photo->image_url, PDO::PARAM_STR); $stmt->bindParam(':ourl', $photo->original_image_url, PDO::PARAM_STR); $stmt->bindParam(':turl', $photo->thumbnail_image_url, PDO::PARAM_STR); . . . $stmt->execute(); } |
strstr 関数を使うと、次のようにとてもシンプルに実現できるように思えるのですが、
|
1 |
$stmt->bindParam(':irul', strstr($photo->image_url, '?', true), PDO::PARAM_STR); |
検索文字が対象文字列中に含まれない場合、 false が返ってくるのみなので使えません。
結局、 strcspn 関数でURL文字列の左端から ? が登場するまでの長さを得て、 substr 関数でその長さ分を左端から抜き出すようにしました。これならクエリ文字列が含まれない場合も、元の文字列をそのまま得ることができます。
|
1 2 3 |
$stmt->bindParam(':irul', substr($photo->image_url, 0, strcspn($photo->image_url, "?")), PDO::PARAM_STR); $stmt->bindParam(':ourl', substr($photo->original_image_url, 0, strcspn($photo->original_image_url, "?")), PDO::PARAM_STR); $stmt->bindParam(':turl', substr($photo->thumbnail_image_url, 0, strcspn($photo->thumbnail_image_url, "?")), PDO::PARAM_STR); |
フォト蔵画像のhttpからhttpsへの対応
フォト蔵の画像URLは長らくhttpのままでしたが、今回の変更でようやくhttpsへ移行され、httpでのアクセスはできなくなりました。ただ、恒久的なものかしばらく見極めたいので、これについてはデータベースレコードを書き換えずに、クライアントサイドのJavaScriptで対応しています。
|
1 2 3 4 5 6 7 |
変更前） img = $('<img />') .attr({src:data[i].url3}) 変更後） img = $('<img />') .attr({src:data[i].url3.replace('http:','https:')}) |
