Base64エンコードされた画像ファイルをMySQLでMEDIUMBLOBにUPDATEしたい

Question

###前提・実現したいこと
初心者です。
android端末から、外部サーバのDBに画像を保存しようとしています。

android上でbitmapをBase64のStringにエンコードしJSONに詰め、
外部サーバ上のPHPで受け取った後にバイナリに変換、
MySQLで、TABLEに既に存在する行のMEDIUMBLOB型カラムをUPDATEしたいと考えていますがうまくゆきません。
$mysqli->affected_rows
でクエリの結果を確認してみると、-1が返されます。

MySQLのMEDIUMBLOB型カラムにはバイナリを投げればよいとの認識なのですが、以下のコードではそれが実現できていないのでしょうか?

宜しくお願いします。

###該当のソースコード
(PHP)
//androidからPOSTされたJSONを受け取る
$json_string = file_get_contents('php://input');

//連想配列型にデコード
$obj = json_decode($json_string,true);
//Base64型にエンコードされた画像データを取り出す
$img = $obj['strBase64'];
//バイナリデータに戻す
$fileData = base64_decode($img);

//略

//画像の投稿
$mysqli->query("UPDATE xxx SET image = $fileData WHERE name = 'xxx'");

//略

###試したこと
MySQLでのバイナリデータ登録には16進法記述への変換が必要、との記述を見つけたので、
$hexFileData = bin2hex($fileData);
のようにして変換した値をクエリに使用してみましたが、こちらもうまくゆきませんでした。

Answer 1

$mysqli->query("UPDATE xxx SET image = $fileData WHERE name = 'xxx'");

文字列リテラルとして扱うのなら$fileDataの値を直接SQL文の中に書いてもだめですね。SQL文の中の文字列リテラルの書きかたはMySQLマニュアル「文字列リテラル」を参照。正しく文字列リテラルにするには'…'で囲んだだけではだめなのがわかります。

MySQLでのバイナリデータ登録には16進法記述への変換が必要、との記述を見つけたので、

これは、SQL文の中でバイナリ文字列を直接記述する場合は16進数リテラルを使うこともできる、というものです。詳細はMySQLマニュアル「16 進数リテラル」参照。文字列リテラルにするにはbin2hex()の結果をそのままSQL文の中に書いてもだめなのがわかります。

---

上のようにデータを直接SQL文の中に埋め込む方法は、難しいし間違いも起きやすいので、プリペアド・ステートメントを使うことを勧めます。次のようなコードでできます。

$stmt = $mysqli->prepare("UPDATE xxx SET image = ? WHERE name = 'xxx'");
$stmt->bind_param("b", $fileData);
$stmt->execute();

ここでは、SQL文を準備 (prepare) し、そのパラメータ (SQL文中で?で表される) にデータをバインドしてから、文を実行しています。データが文字列になるようにエスケープしたりする必要はありません。PHPマニュアル MySQLiのprepareも参照。

しかしこれだと、バインドするデータのサイズが大きくなると動作しません (具体的には、MySQLのmax_allowed_packet設定パラメータの値より大きいサイズのデータは扱えません)。データサイズが大きくなるおそれがあるのなら、データをパケットサイズで分割して送るようにします。下記のようにします。

Comments

[takg]

ご丁寧にありがとうございます。

prepared statementの存在は認識しておりましたが、
エスケープの回避というメリットがあることは存じ上げませんでした。
MySQLリファレンスのソースも添えて頂き、大変勉強になりました。
ご教示頂いた方法を試してみようと思います。

また、データサイズが大きいケースの対処法も添えて頂き、助かりました。
おそらくその点も考慮に入れる必要がありそうです。

素朴な疑問なのですが、
＞正しく文字列リテラルにするには'…'で囲んだだけではだめなのがわかります。
というご指摘は、MySQLリファレンスを読む限りでは特殊文字のエスケープに関するものだと思うのですが、
$fileData = $mysql_real_escape_string($fileData);
$mysqli->query("UPDATE xxx SET image = '$fileData' WHERE name = 'xxx'");
等の手段を採るならば、一応は正しくクエリが実行されることになるのでしょうか？
(もちろんこの方法を使うつもりはありません。)

[ikedas]

おっしゃる通り、エスケープの話です。そんなような感じのコードで動くと思います。
が、そのコードの方法だと、プログラム中に現れるすべての文字列パラメータをエスケープするよう気をつけておかなければなりません。万一どこかでエスケープを忘れると「SQLインジェクション」を起こせるようになってしまいます。

プリペアド・ステートメントを勧める理由は、BLOBを簡単に扱えるからだけでなく、SQLインジェクションを回避したコードを確実に書けるという点も大変重要だと思います。

[takg]

確かに、POSTされたすべての変数をエスケープするのは危険で不毛ですね。
良く分かりました。

御陰様でクエリ実行周りの知識が整理されました。
ご助言ありがとうございました。

[takg]

もう1点、プリペアド・ステートメントに関してお伺いしても宜しいでしょうか？
何度も申し訳ありません。

以前の件に関しましては、ikedas様の方法でUPDATEに成功致しました。
ご教授頂いたプリペアド・ステートメントを用いて、過去に書いたクエリ実行コードを改変しているのですが、SELECT文に関する部分で、

$mysqli->query("SELECT * FROM xxx WHERE name = 'yyy'");
だとうまくゆくのに、

$stmt = $mysqli->prepare("SELECT * FROM xxx WHERE name = ?");
$stmt->bind_param("s", $searchWord);
$searchWord = "yyy";
$stmt->execute();
だとうまくゆかない($mysqli->affected_rowsが-1を返す)
という状況に陥り、原因が分からず困っております。

何か問題点等ございましたら、お教え願えないでしょうか?

[ikedas]

「$searchWord = "yyy";」を書く順番がおかしくないですか。
エラーの詳細を、$mysqli->errorや$stmt->errorで確認するようにして下さい。

[takg]

mysqli->errno,mysqli->error,stmt->error,mysqli->sqlstate
のどれを試しても、エラーは返されず、ただ
$mysqli->affected_rowsが－1を返してくる状況です。

＞「$searchWord = "yyy";」を書く順番がおかしくないですか。
このご指摘について、具体的にどういった点が間違っているとお考えかお伺いしてもよろしいでしょうか?
お手間お掛けし申し訳ありません。

[ikedas]

あ、そうか。おかしくないですね。
PHPマニュアルのmysqli_stmt::$affected_rowsの説明を読んで下さい。

Answer 2

単純に、クオートしてないからでは？

Comments

[takg]

仰る通り、引用符をつけるとクエリは成功致しました。
引用符が必要になるのはVARCHAR型のみとの誤った認識がありました。

その場合、別の方に指摘頂いた(おそらくエスケープシーケンス関連の)問題が生じるようですので、
prepared statementを利用する方向で探ってみようと思います。

ご助言ありがとうございました。

Answer 3

JSONではなく、バイナリーでPOSTすればいいのではないでしょうか？(multipart)

Comments

[takg]

恥ずかしながらmultipartを用いたPOSTの手法を存じ上げませんでした。
この方が汎用性が高く、またBase64エンコードに伴うデータ量増加という点からも
良いように思われました。
仰るように、バイナリによるPOSTを検討してみようと思います。

ご助言ありがとうございました。