背景
調査の途中で「この ID の一覧だけ抜きたい」となることがあります。
表計算ソフトに縦一列で並んだ値を、SQL の IN 句に貼り替える、あれです。
私はこれをエディタの矩形選択でやっていました。
末尾にカンマを付けて、前後を引用符で囲んで、最後の1個だけカンマを消す。
毎回やっているのに毎回もたつくので、ツールにしました。
そもそも IN 句の何が面倒か
IN 句は「この列がこの一覧のどれかに一致する行」を選ぶ書き方です。
SELECT * FROM users WHERE user_id IN (1001, 1002, 1003);
面倒なのは中身の作り方のほうです。
値が文字列なら1つずつ単一引用符で囲む必要があり、数値なら囲んではいけない（囲むと型変換が起きる DBMS がある）。
そして値の中に単一引用符が混ざっていると、そこで文字列が閉じてしまって構文エラーになります。
作ったもの
貼り付けた値を、IN 句・カンマ区切り・VALUES の行・INSERT 文・OR の連結のどれかに整えるだけのツールです。
処理はすべてブラウザの中で完結していて、サーバーには何も送っていません。
入力と出力を1つ置きます。
左に次の4行を貼り、クォートを「常に単一引用符で囲む」にした場合です。
tokyo
osaka
o'hara
nagoya
出力はこうなります。
user_id IN (
'tokyo', 'osaka', 'o''hara', 'nagoya'
)
o'hara が 'o''hara' になっている点が本題です。
SQL の文字列リテラルでは、単一引用符そのものを表すには2個続けて書きます。
バックスラッシュではありません（MySQL は設定によってバックスラッシュも受けますが、標準の書き方は二重化のほうです）。
変換部分は下記の1関数だけです。
v が1件ぶんの値、mode は auto（数値ならそのまま）／quote（常に囲む）／none（囲まない）です。
function isNumeric(s) { return /^-?(?:0|[1-9]\d*)(?:\.\d+)?$/.test(s); }
function quoteValue(v, mode) {
if (mode === 'none') { return v; }
if (mode === 'auto' && isNumeric(v)) { return v; }
return "'" + v.replace(/'/g, "''") + "'";
}
isNumeric を先頭ゼロ不可にしてあるのは意図的です。
007 のような社員コードを数値と判定して引用符を外すと、文字列型の列と比較したときに黙って結果が変わる DBMS があります。
迷ったら囲む側に倒したほうが事故が小さい、という判断です。
1000件の壁
もう1つ入れたのが件数の警告です。
IN のリストに直接書ける式の数には上限があり、Oracle Database は1000個までとしています。
超えると ORA-01795 になります。
ツールは1001件目から警告を出します。
「多いですね」ではなく、超えたときにどうするかまで書くようにしました。
- 値を一時表に入れて
JOINする - リストを分割して
ORでつなぐ
実測として、1200件を貼ると件数表示が 1200 になり、警告が出ました。
警告の有無を件数の閾値だけで決めているので、Oracle 以外を使っている人には過剰かもしれません。
それでも、上限を知らずに本番で踏むよりはましだと考えて残しています。
手作業の補助であって、実行するクエリの作り方ではない
ツールの説明文に1つだけ強めの注意を置きました。
アプリケーションから実行するクエリは、文字列連結ではなくプレースホルダ（バインド変数）を使ってください、というものです。
このツールがやっているのは、まさに「文字列を連結して SQL を作る」ことです。
手元で1回きりの調査クエリを組むぶんには構いませんが、同じやり方をアプリケーションに持ち込むと SQL インジェクションの入口になります。
便利なツールほど、そのまま製品コードへ写されるので、境界は本文に書いておくべきだと思いました。
エスケープ規則と DBMS の上限は、人が毎回思い出すのではなく、道具の側に持たせる。今回作って良かったのはその2点だけです。
本記事はAI補助で執筆した、個人開発の紹介記事です。