insert もupdate も同一のSQL で行えるので一見とても便利です。
ですが、存在しないID が指定された場合に勝手にレコードが作成される危険があります。
upsert のサンプル
id と名前だけの簡単なテーブルを作ります。
SQL
create table account (id bigserial primary key, name text);このテーブルのupsert 文はこちらになります。
SQL
insert into account (id, name)
values (1, 'Green')
on conflict (id)
do update set
name = EXCLUDED.name;こちらは何度実行してもエラーになりません。
1回目はinsert が行われます。
2回目以降はid=1 のレコードが既にあるので、update が行われます。
SQL
postgres=# insert into account (id, name)
values (1, 'Green')
on conflict (id)
do update set
name = EXCLUDED.name;
INSERT 0 1
postgres=# select * from account;
id | name
----+-------
1 | Green
(1 row)画面から不正ID が送付される場合
名前変更画面から、id=1 と name=’Green’ というデータが飛んで来て、先ほどのupsert 文を実行するします。
通常時は問題ありません。
もし、id=99999 という不正なデータを画面が送付するとどうなるでしょうか。
このようなupsert 文が発行されます。
SQL
insert into account (id, name)
values (99999, 'Evil')
on conflict (id)
do update set
name = EXCLUDED.name;on conflict (id) 以降の処理はid のレコードが存在する場合に実行されるものです。
id=99999 のデータがないのでinsert が走ります。
結果としてこのように99999 のデータが登録されてしまいます。
SQL
postgres=# select * from account;
id | name
-------+-------
1 | Green
99999 | Evil
(2 rows)また、自動採番（シーケンス値）が99999 を追い越してしまうため、将来99999 が割り当てられた際に既に存在するIDとしてエラーになります。
セキュリティ的に画面から送付する値は信用してはいけません。
ブラウザから送付する値を詐称することは簡単にできてしまいます。
upsert を使わなければ問題は起きない
もし、upsert ではなく通常のupdate であれば問題は起きません。
SQL
update account set name = 'Evil' where id = 99999;
--> UPDATE 0 （更新されるレコードはない）id=99999 のデータは存在しないため、更新することはできません。
まとめ
upsert をinsert とupdate のどちらにも使える便利なものと考えると危険です。
更新はupdate 、作成はinsert と明確に分けた方が安全で無難です。
【広告】
コメントを残す