趣味で作っているアプリケーションについて、データマイグレーションを行いたくなった。ちゃんとやるなら、マイグレーション用のスクリプトを書いて、メンテナンスモードにして、スクリプトを適用する……みたいな手順を踏むと思うけど、趣味プロダクトなので高速に済ませたい。SQLでSQLを組み立てればワンタイムスクリプトを書かなくてもよいのではないか、という考えに思い至ったのでメモする。
以下はSQLiteでの事例だけど、他のDBMSでも似たようなことはできると思う。
組み立て方
文字列連結
SQLiteの文字列連結は || 演算子で行う。
sqlite> SELECT 'a' || 'b'; ab
NULLと文字列を連結するとNULLになり、ターミナル上では空行が出力される。
typeof は値の型を返す関数である。空文字列ではなくNULLが出力されていることを確認した。
sqlite> SELECT 'aaa' || NULL; sqlite> SELECT typeof('aaa' || NULL); null
文字列のエスケープ
文字列連結でSQLを組み立てると、当然SQLインジェクションのリスクがある。
user テーブルに name カラムがあって、たとえば ' OR 1=1 -- という文字列が格納されていると考える。以下の例ではすでにSQLインジェクションに成功している。
sqlite> SELECT "UPDATE users SET some_flag = 1 WHERE name = '" || "' OR 1=1 --" || "'"; UPDATE users SET some_flag = 1 WHERE name = '' OR 1=1 --'
SQLiteには quote という関数があり、与えられた文字列をクォートして、さらにエスケープも行ってくれる。
sqlite> SELECT "UPDATE users SET some_flag = 1 WHERE name = " || quote("' OR 1=1 --"); UPDATE users SET some_flag = 1 WHERE name = ''' OR 1=1 --'
ちなみにシングルクォートのエスケープは '' である。
sqlite> SELECT 'a''b'; a'b
ここまでの内容で、単純なUPDATE文ぐらいなら書けるようになったと思う。
WHERE IN を書きたい
ところで、こういうSQLを生成したいときはどうしたらよいのか? IN (...) の中には複数の値が入るが、果たしてSQLで書けるのか。
UPDATE users SET some_flag = 1 WHERE id IN (...)
これは group_concat という関数を使うことで実現できる。
まず、対象となるidをかき集めるSELECT文を用意する。
-- 対象となるidをかき集めるSELECT文 SELECT id FROM users WHERE some_condition;
idを group_concat(id) に置換すると、INクエリに突っ込めそうな文字列ができる。
-- 対象となるidをかき集めるSELECT文 sqlite> SELECT group_concat(id) FROM users WHERE some_condition; 2,4,6,8,10,12,14,16,18,20
あとはうまく文字列連結してやればよい。
sqlite> SELECT 'UPDATE users SET some_flag = 1 WHERE id IN (' || group_concat(id) || ')' FROM users WHERE some_condition; UPDATE users SET some_flag = 1 WHERE id IN (2,4,6,8,10,12,14,16,18,20)
万が一、対象となるidが存在しない場合は group_concat(id) はNULLを返す。従って文字列連結した結果もNULLとなり、空行が出力される。
UPDATE users SET ... WHERE id IN () のようなクエリを実行してしまいエラー、ということにはならないようだった。そもそもSQLiteの場合はエラーにならないが……*1。
WHERE IN の中でUPDATEする対象のidをSELECTするのとの違い
SQLを生成する場合は、具体的にどういう行がUPDATEされるのかをクエリやコメントに出力できる。SELECT文だけで何行UPDATEされるかはデータに対する知識がないとすぐには計算できないけど、クエリに直接idが書いてあったら、なんか多すぎておかしい、全くUPDATEされないじゃん、みたいなことに気づけそう。
UPDATE users SET some_flag = 1 WHERE id IN (...) -- ()の中を見たら、UPDATEされたusers.idが分かる
たとえばこういう感じにしたら、対象となるユーザー名も分かってお得。UPDATE文を生成するなら、UPDATEする前の値もメモしておくと後々調査したくなったときに便利かもしれない。
sqlite> SELECT 'UPDATE users SET some_flag = 1 WHERE id = ' || id || '; -- ' || name FROM users; UPDATE users SET some_flag = 1 WHERE id = 2; -- john_doe ...
まとめ
SQLでSQLを組み立てることでデータマイグレーションを行えそう。生成したSQLをどこかにメモしておけばあとで見返すことができる。
補足 (組み立てたSQLをどう実行するのか)
SQLiteには与えられた文字列をSQLだと思って実行する eval 的な関数はない (当然標準SQLにもないと思う) ので、出力したSQLをコピペして改めて実行することになる。
標準入力から流し込んでもよいし、先頭に BEGIN; を足してからトランザクションを張りつつペーストして実行してもよい。
参考
SQLiteのドキュメントが好きで、よく読んでいる。
