MySQLで指定(isset)されたカラムのみUPDATEしたい

Question

前提・実現したいこと

クエリパラメータに渡されたカラムのみ値を更新する update.php を作っています。
DBへはmysqliを使用して接続しています。
カラムは二十数個ありますが、すべてを指定する必要はなく、指定されなかったカラムは更新しないようにしたいです。
パラメータ名とカラム名は対応しており、例えば「id=taro123&age=20&pref=Tokyo」とパラメータを渡した場合、
「UPDATE table SET age = 20, pref = 'Tokyo' WHERE id = 'taro123'」のようなSQLを発行したいです。

SQLインジェクション対策が必要で、できればSQL一文で実現したいと思っています。
※WHERE句のidをユーザーが指定できてしまう脆弱性は考えなくて構いません。

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

SQLインジェクション対策のため、普段はprepare()を使用していますが、update.phpに指定できるパラメータの数も可変であるため、この場合にどのようにbind_param()に引数を与えればよいかわからず、止まってしまっています。

該当のソースコード

現状、以下のように一つずつissetでチェックして実装していますが、非常に冗長なため、もっと効率の良い方法はないでしょうか？

$db = new mysqli($dbHost, $dbUser, $dbPasswd, $dbName);
if ( isset($_POST['familyName']) ) {
  $stmt = $db->prepare('UPDATE table SET familyName = ? WHERE id = ?');
  $stmt->bind_param('ss', $_POST['familyName'], $_POST['id']);
  $stmt->execute();
  $stmt->close();
}
if ( isset($_POST['firstName']) ) {
  $stmt = $db->prepare('UPDATE table SET firstName = ? WHERE id = ?');
  $stmt->bind_param('ss', $_POST['firstName'], $_POST['id']);
  $stmt->execute();
  $stmt->close();
}
if ( isset($_POST['age']) ) {
  $stmt = $db->prepare('UPDATE table SET age = ? WHERE id = ?');
  $stmt->bind_param('is', $_POST['age'], $_POST['id']);
  $stmt->execute();
  $stmt->close();
}
～～～以下、二十数カラム分続く～～～
$db->close();

考えた別解

• prepare()を使用することを諦めて、事前にパラメータが不正でないか全てチェックし、安全なSQL文を作ってquery()で実行

$sql = 'UPDATE table SET ';
if ( isset($_POST['familyName']) )
  $sql .= "familyName = {$_POST['familyName']}, ";
if ( isset($_POST['firstName']) )
  $sql .= "firstName = {$_POST['firstName']}, ";
～～～以下続く～～～
$sql .= "WHERE id = {$_POST['id']}";
$db->query($sql);

これもif文が連続し、あまり変わらない気がしています。。。

• 先にSELECTでデフォルト値を取得しておいて、全てのカラムを指定する

$stmt = $db->prepare('SELECT * FROM table WHERE id = ?');
$stmt->bind_param('s', $_POST['id']);
$stmt->execute();
$values = $stmt->get_result()->fetch_assoc();
$values['familyName'] == $_POST['familyName'] ?? $values['familyName'];
$values['firstName'] == $_POST['firstName'] ?? $values['firstName'];
～～～以下同～～～
$stmt = $db->prepare('UPDATE table SET familyName = ?, firstName = ?, ......... WHERE id = ?');

もっと美しい方法はありませんでしょうか？
またMySQL自体の扱いも不慣れで、直接の質問以外の箇所でも構文等に不自然、無駄な箇所があればご指摘頂けると幸いです。
どうか、皆様の知恵をお借りしたく、宜しくお願い致します。

Answer 1

カラムは二十数個ありますが、すべてを指定する必要はなく、指定されなかったカラムは更新しないようにしたいです。

動的SQLという事になりますね。

SQLインジェクション対策のため、普段はprepare()を使用しています

prepareする事が、SQLインジェクション対策ではありません。
バインド変数を使用する事がSQLインジェクション対策です。

prepareしたいなら、SQLを固定にする必要がありますので、「指定されなかったカラムは更新しない」ではなく「指定されなかったカラムの値は変更しない」という事であれば、指定が無い場合のバインド変数にNullを設定する事で、以下の様なSQLで可能です。

update XXX set yyy=coalesce(バインド変数, yyy)

Comments

[num47y]

>できればSQL一文で実現したいと思っています。
これに対する回答がまさにCOALESCE関数でした。非常にスマートな解決法、ありがとうございました。

Answer 2

項目数がそれだけあるなら、その分だけSETを構成する必要があるので、それだけの分岐は必要です。

update.phpに指定できるパラメータの数も可変であるため、

配列に突っ込んでいってその分だけ回してbindすれば良し。

※例なので実装イメージ。未検証。

$set = [];
$params = [];
$params_type = [];
if ( isset($_POST['familyName']) )
  $set[] = " familyName = ? ";
  $params[] = $_POST['familyName'];
  $params_type[] = 's';
if ( isset($_POST['firstName']) )
  $set[] = " firstName = ? ";
  $params[] = $_POST['firstName'];
  $params_type[] = 's';

/* 中略 */
//$setが0件の場合ってある？あるならSQLそのもの実行しないように回避してください。

$sql .= implode(',' , $set);
$sql .= ' WHERE id = ?';

  $params[] = $_POST['id'];
  $params_type[] = 's'; //idがsでいいの？

  $stmt = $db->prepare($sql );
  $stmt->bind_param(implode('' , $params_type),  $params);

PDOのほうが書きやすいかなぁ。

疑問：
蛇足レベルですけど、isset()なら空文字が来ても更新されるのですけど、

<?php
$a = [null,0,'0','1','a'];

echo '$b is';
var_dump(isset($b));

foreach($a as $p){
   echo $p.' is '; 
    var_dump(isset($p));    
}

それはバリデーションでチェックしてるということなんですかね。
それとも空文字更新ありという仕様なのか。

Comments

[num47y]

コードを拝見し、bind_paramの可変長部分の引数って配列でいいの？と思い検索をしたところ、引数のアンパックができるのですね。恥ずかしながらそれを知りませんでした。
回答はおそらく...演算子の抜けかと思いますが、とても参考になりました。

検索語彙が増えたことで、次のような記事も発見し、様々参考にさせて頂きながら解決できました。
https://agohack.com/bind_param-dynamic-array/

最終的には編集を許可するパラメータ名と型を配列で持ち、$_POSTをforeachで回してkeyがその配列に存在すれば$setに追記する、あとはm.ts10806さんの回答というコードにすることにしました。
$paramNames = [
　'familyName' => 's',
　'firstName' => 's',
　'age' => 'i',
];

$paramsType = $params = $set = [];

foreach ( $_POST as $key => $value ) {
　if ( array_key_exists($key, $paramNames) ) {
　　$set[] = $key . '=?';
　　$params[] = $value;
　　$paramsType[] = $paramNames[$key];
　}
}

※補足頂いている内容ですが、今回はパラメータさえ渡っていれば空文字でも更新する仕様ですので問題ありません。お気遣いありがとうございます。

この度はありがとうございました。

[m.ts10806]

SQLもPHPからすると文字列なので、
「共通部分以外を動的に生成する文字列」を想定して組むと良いと思います。