Lazy Diary @ Hatena Blog

PowerShell / Java / miscellaneous things about software development, Tips & Gochas. CC BY-SA 4.0/Apache License 2.0

帰省時に使う私物を実家に置いておけるか

文化的まちがいさがし
  • (A) 自分の家庭を持ったあとも、帰省時に使う私物を実家に置いておける。何なら自分の部屋まで残っている。
  • (B) 自分の家庭を持ったあとは、帰省時に使う私物は実家に置いておけない。帰省するときは自分用の布団を車に積んでいく。

これは土地柄なんですかね？ 家に対する帰属意識の違いともまた違うように思うのですが。

IN clause with 100,001 entries in Oracle

Oracle SQL

Background

You will get the following error when you specify over 1000 entries in IN clause in Oracle:

ORA-01795: maximum number of expressions in a list is 1000

This is by design, and the following document says "You can specify up to 1000 expressions in expression_list." docs.oracle.com

There are some hacks to bypass this constraint. If you want to write IN clause like this:

SELECT NAME TBL_A FROM TBL_A WHERE ID IN ('1', '2', ..., '999', '1000', '1001');

You can write it with tuples with dummy values like this:

SELECT NAME TBL_A FROM TBL_A WHERE (ID, 1) IN (('1', 1), ('2', 1), ..., ('99999', 1), ('100000', 1), ('100001', 1));

stackoverflow.com

Question

I had a question about how many entries you can specify in IN clause with the tuples. In StackOverflow, some say the limit is 100,000*1, and some say 70,000*2, and there is no clear evidence.

Experiment

So, let's experiment. I installed Oracle on my machine. The version is:

SQL> SELECT BANNER_FULL FROM v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

And made a table like this:

CREATE TABLE TBL_A (ID CHAR(4), NAME VARCHAR2(20));
INSERT INTO TBL_A VALUES ('1', 'JOHN DOE');

And run this PowerShell script. This script creates a SELECT statement and copies it in the clipboard. Note that you cannot paste the result of this script into sqlplus because the line breaks are ignored by sqlplus. You have to paste and re-copy it in some text editor and paste it to sqlplus. You can change the number of entries with the sequence in the head of this script. If you specify 100000..100001, you will get two SELECT statements with 100000 entries and 100001 entries.

100000..100001 | ForEach-Object { "SELECT NAME, " + $_ + " TBL_A FROM TBL_A WHERE (ID, 1) IN (" + (((1..$_) | ForEach-Object { $crlf = ''; if ($_ % 100 -eq 1) { $crlf = "`n" };  $crlf + "('" + $_ + "', 1)" }) -join ", ") + ");" } | Set-Clipboard

Result

In my environment, the IN clause with 100,000 entries, 100,001 entries, 120,001 entries are all successfully executed in sqlplus. At least, "up to 100,000 entries" seems wrong in Oracle 21c.

For someone who wants to do a replication study, I have uploaded the SQL statement with 100,001 entries in Gist. SELECT with IN clause that contains 100,001 entries for Oracle 21c · GitHub

Of course, the performance is terrible. It takes some minutes to execute. In my environment, it takes 03:57.67 for 100,000 entries, and 5:18.24 for 120,001 entries. You had better rewrite the statement with EXISTS clause for performance.

I've got ORA-03113 error when I tried the IN clause with 200,000 entries. This error seems caused by the length of the SQL statement rather than the number of entries in IN clause. The error is resolved when I change the literals in IN clause to a shorter string.

Conclusion

To conclude, in Oracle 21c, the maximum number of entries you can specify in IN clause with tuples remains unknown. At least, you can specify 200,000 entries. I could not find the upper limit in the official Oracle document, so this number would be changed without notice.

*1:https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause#comment85743305_17019130

*2:https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit#comment70283759_9084247

OracleでIN句に100,001個のエントリを指定する

Oracle SQL

背景

OracleでIN句の内容が1000個を超えると以下のエラーが発生する。

ORA-01795: maximum number of expressions in a list is 1000

これはOracleの仕様で、以下のドキュメントに"You can specify up to 1000 expressions in expression_list."と記載されている。 docs.oracle.com

この制約を回避するハックとして、

SELECT NAME TBL_A FROM TBL_A WHERE ID IN ('1', '2', ..., '999', '1000', '1001');

とする代わりに

SELECT NAME TBL_A FROM TBL_A WHERE (ID, 1) IN (('1', 1), ('2', 1), ..., ('99999', 1), ('100000', 1), ('100001', 1));

のようにダミーの値を含んだタプルをIN句に指定する方法が知られている。 stackoverflow.com

問題

ここで、タプルを指定した場合にIN句の中に指定できる内容の上限は何か、という問題がある。stackoverflowのコメントでは、最大100,000個*1と言われみたり、最大70,000個*2だと言われてみたりはっきりしない。

調査方法

じゃぁ試してみよう、ということで、手元にOracleを入れて試してみた。Oracleのバージョンは以下。

SQL> SELECT BANNER_FULL FROM v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

こんな感じでテーブルを作り……

CREATE TABLE TBL_A (ID CHAR(4), NAME VARCHAR2(20));
INSERT INTO TBL_A VALUES ('1', 'JOHN DOE');

こんな感じのPowerShellで、SELECT文をクリップボードにコピーする。注意として、この内容をそのままWindowsのsqlplusにペーストしても、改行が無視されて途中で内容が途切れてしまう。いちどテキストエディタ等にペーストしてから、再度コピーしてsqlplusにペーストしよう。スクリプトの最初のシーケンスを変更すれば複数のSELECT文を作ることができる。この場合、IN句が100,000個と、100,001個のSELECT文がクリップボードにペーストされる。

100000..100001 | ForEach-Object { "SELECT NAME, " + $_ + " TBL_A FROM TBL_A WHERE (ID, 1) IN (" + (((1..$_) | ForEach-Object { $crlf = ''; if ($_ % 100 -eq 1) { $crlf = "`n" };  $crlf + "('" + $_ + "', 1)" }) -join ", ") + ");" } | Set-Clipboard

実行結果

試しに実行してみたところ、IN句が100,000個のケース・100,001個のケース、120,001個のケースはいずれも問題なく実行できた。少なくとも、100,000個が上限というのはOracle 21cでは誤りのようだ。 試したい人のために、実際に使ったSQL文（100,001個のケース）を以下のGistにアップロードした。

SELECT with IN clause that contains 100,001 entries for Oracle 21c · GitHub

もちろん性能は非常に悪く、分単位の時間がかかる。実測したところ、手元では100,000件のケースで03:57.67、120,001件のケースで5:18.24かかった。性能を考慮するなら、可能であればEXISTS句＋副問い合わせの形に書き換えるのがよいだろう。

200,000個のケースでは、sqlplusから実行しようとしたところORA-03113エラーが発生したが、これはIN句の個数ではなくSQL文の長さが問題のようで、IN句に指定する定数の長さを短かくしたところ問題なく実行できた。

結論

結論として、Oracle 21cの場合、タプルを使った場合にIN句に指定できる内容の個数の上限は不明。少なくとも200,000件は指定が可能だった。Oracle公式のドキュメントには上限の個数の記載が見当たらなかったので、その意味では予告なく変更される可能性もあるだろう。

*1:https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause#comment85743305_17019130

*2:https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit#comment70283759_9084247

セキュリティ対策を発注元に断わられたことを記録する方法は？

Software Development Computer Security ないものねだり

背景

要求仕様にセキュリティ対策の記載がない場合でも、十分に認知されているリスクはベンダが対策を行う必要があるとした東京地裁平成26年1月23日判決（平23(ワ)32060号）*1が出て以降、この点についてソフトウェア請負開発を行うベンダの立場は弱く、また求められる責務は多くなっていると言えるでしょう。

2019 MPOWER Cybersecurity Summitで徳丸先生が登壇されていた際*2は、ベンダ側としては「セキュリティ対策が必要であることを発注元に明示し、発注元から断わられたことを記録しておくことで、裁判になった場合のリスクを低減する（過失相殺が認定されるようにする）」が自衛策になるという話でした。上記の判例を所与のものとした場合、この方針しかないだろうというのは納得です。

問題

では、「セキュリティ対策が必要であることを発注元に明示」するのは、いつ・どこで・だれがどのように提示するのがよいのでしょうか？ また、裁判になった場合のリスクを低減するには、記録をどのような形で作成し、どのような形で保存しておくのがよいのでしょうか？

論点

  • 情報システムの請負開発時に、ベンダ側でセキュリティリスクを識別する枠組み（ISMSのような枠組み）が整備されていない。実際には、開発するソフトウェアそのものに関するリスク以外にも、レピュテーションリスク（事故ったときに「開発ベンダは◯◯社です」と公表される場合のレピュテーションリスク）などが考えられるが、ほかにどのような観点が存在するか？
    • またそのような観点は、発注元・ベンダ・システム利用者などのステークホルダーの軸、およびISO/IEC 12207におけるプロセスの軸などをもとに、どのようにまとめられるか？
  • 情報システムの開発ベンダを総合評価方式の入札で決定する場合、どのような仕組みがあれば発注元はセキュリティ対策を実施してくれるベンダを選べるのか？ 実施すべきセキュリティ対策が要求仕様に記載されており、その内容を満たすことで技術点が加点されるのが理想ではある。一方で、平23(ワ)32060号判決は「発注元はセキュリティの素人なんだから、プロであるベンダがちゃんと対策しなさい」という内容なので、それは難しいという前提に立つ必要がある。
  • 提案書を作成して入札する調達の場合、セキュリティ対策を提案書に盛り込んでしまうと、その後で取り下げることができない。「ベンダ側はセキュリティ対策が必要だと認識しているが、対策費用を入札額に盛り込んでしまうと価格で負けてしまう」という場合、ベンダ側もセキュリティ対策を提案書に盛り込むことができない。提案書には記載していないセキュリティ対策の内容検討を、ベンダから発注元に依頼するにはどうしたらよいか？
  • 「セキュリティ対策が必要であることを発注元に明示」し、納得してもらえれば変更契約となるわけだが、一方で「変更契約が成立しなかったことの記録を目的にセキュリティ対策の必要性を発注元に説明する」ことははたして可能なのか？
  • SLCP-JCF2013や、ISO/IEC 12207において、システムの開発プロセス上「ベンダ側は必要だと認識しているが、発注元は要否を認識していなかった機能について、発注元に要否を判断してもらう」という作業を実施するタイミングが、要求仕様の設計後に定義されていない。システムの開発プロセス上、どのタイミングで実施するのがよいか？
  • ISMSでは、存在する情報システムおよび情報資産について脅威や脆弱性を識別しリスクを評価するプロセスが定義されている。情報システムおよび情報資産について変更があった場合に脅威や脆弱性を再識別することは可能である一方、「近いうちに情報システムに○○という変更がなされる予定です」というタイミングでリスクの再評価をするプロセスが定義されていない（と思われるが、見つけられていないだけ？）。現存しないシステムに対するISMS的な評価は、どのタイミングで、どのような内容をもとに行われるべきか？
    • そもそもISMSのプロセス内に、「これから開発するシステムの開発ベンダ」による評価が行われるタイミングがない。現存せず、これから開発する予定のシステムの脅威や脆弱性を識別する場合、誰が参加するべきか？
  • 「発注元から断わられたことを記録しておく」として、それをどのような形で記録しておくのが適切か？
    • 発注元とベンダとの間の合意内容に関する文書なので、もっとも確実なのは契約書と同じ形式と思われる。ただし、裁判の際に発注元側が不利になる内容を含む文書なので、発注元がハンコを突いてくれたとしても、発注元の心象の悪化は避けられないのでは？
  • セキュリティインシデントの場合、裁判に使う証拠物件としてデータを保全する手続き（Chain of custody）が定められているが、情報システム開発において証拠物件の保全に相当する手続きは存在するのか？
  • 「発注元から断わられたことを記録しておく」場合、実質的に「情報システムで発生した問題をベンダ側の重過失としない」内容になってしまうと契約として無効になってしまう（と思われる）。記録方法としては、どのような記載が適切なのか、どのような記載だと問題があるのか？

余談

上記のような話がぜんぜん議論になっていないのは、いったいどうしてなんでしょうね？

  • 判決を受けて、どの発注元もセキュリティについてちゃんと仕様書に書くようになったし、どのベンダもちゃんとセキュリティ対策をするようになった。
  • 要求仕様に書いてないんだから、手弁当でやったらやっただけベンダが損を被ることになるのでやらない。重過失認定されたら仕方ないとあきらめている。
  • そもそも判例を認知していない。
  • セキュリティってなに？ IPAっておいしいビール？ というベンダばかりで問題視もされていない。
  • セキュリティってなに？ IPAっておいしいビール？ という発注元ばかりで、セキュリティの問題が発生したらベンダを頼ることはあっても、訴えるなんて思いもつかない（やさしい世界？）。
  • 契約書に何か書いてリスク対策をしていると思っている（重過失だと契約書中の上限額が無効と気付いていない）。

*1:参考: https://www.softic.or.jp/semi/2014/5_141113/rep2.pdf

*2:https://www.e-guardian.co.jp/info/2019/20190901.html

CyberChefではANSIエスケープシーケンスの削除はできない

Emacs Eclipse できない

Eclipseのコンソールの内容をクリップボードにコピーしようとした際、ANSIエスケープシーケンスが付いてくることがある*1。テキストエディタなどでは^[[2mのようにに表示される（最初の^[ はASCIIのESC）。ANSIエスケープシーケンスについてはTeraTermのページがよくまとまっている*2。

CyberChef*3にはURLのパーセントエンコーディングを戻したり、\nなどのエスケープ文字を解釈したりする機能があるが、ANSIエスケープシーケンスを削除する機能はない。

簡単に削除するには、Eclipseであればreplace-regexpで^[.+?mを空文字で置換すればよい。エスケープシーケンスは置換対象テキスト中からコピーする。

*1:https://stackoverflow.com/q/52974174/3902663

*2:https://ttssh2.osdn.jp/manual/4/ja/about/ctrlseq.html#charattr

*3:https://gchq.github.io/CyberChef/