SQL文を画面で入力して安全に実行して結果を返す

Laravelのようなフレームワークが登場する以前は、誰しもSQL文を作成してmysqli_query()のような関数に引数として渡して実行していたものです。QueryBuilderやEloquentのORMなぞ聞いたこともない時代でした。その過去に戻るわけではないですが、それと同じこと、つまりSQL文を管理画面に直接入力して実行できないかと考えた次第です。

こんなものが欲しい

以下の画面のように、SQL文を入力してその実行ボタンを押すと結果を画面で表示してくれるものが欲しいのです。この画面では、テスト１とテスト２の２つサイトがあり、それぞれのサイトで使用されているDBに対してSQL文を実行しています。

なぜこのような機能が欲しいかというと、お客さんのビジネスでは今月の売り上げなどの定型のレポートだけでなく、例えば、ある時期にこの商品を購入したお客さんの中で何人が違う時期（最初の時期以降）で同じ商品を買ったのか、とかアドホックのリクエストが結構あるからです。

また、お客さんだけでなくシステム管理者は特定のDBテーブルのidの最大値が使用DBタイプの最大値に近づいていないか、など定期的に実行するクエリーも必要です。

しかし、これらのためにいちいち画面を伴うコントローラを作成するのは面倒であるし、そのときだけに必要で後には要らなくなることも多々です。それらのときに、上のようにカスタムのSQLを入力するだけで実行でき結果を見れる、さらにクエリと結果を保存し後に必要なときにも実行できる、としたら素晴らしいと思いませんか！

管理画面でSQL文を直接入力して実行するホラー

さて、欲しい機能は素晴らしいのだけれど、直接のSQL文入力・実行でホラーとなる状況はいくつかあります。特に以下の２点。

私の目的はあくまでもクエリーでありSELECTのSQL文だけの実行です。しかし、INSERTやUPDATEさらにDELETEもSQL文なので入力されて実行されたら、完全なホラーです。これらのSQL文が入力させないようにするか、入力しても実行を避ける方法が必要です。

次は、クエリーの実行時間がやたらに長くなりシステムの負荷となることです。入力するクエリーの実行に要する時間は、クエリーが複雑となると実行する前にわかるものではありません。もし、クエリーの実行時間が指定した実行所要時間以上となったら実行を自動的に打ち切りにしたいです。

ということで、これらを避ける対策を前もって考えます。

LaravelでどうSQL文を実行する？

ホラーの対策を考える前に、まず、LaravelでSQL文を実行してみましょう。tinkerで試してみます。

>>> DB::select("select count(*) as '会員数' from users");
=> [
     {#3514
       +"会員数": 2,
     },
   ]

簡単ですね。項目名にもUTF8なら日本語でエイリアスとできます。

しかし、恐ろしいのは先に述べたように、こういうこともできてしまいます。

>>> DB::select("delete from users");
=> []

>>> User::all();
=> Illuminate\Database\Eloquent\Collection {#3529
     all: [],
   }

DBテーブルは空になってしまいました。

DB更新のSQL文の実行を防ぐ

SQL文を簡単に実行できることがわかったところで、ホラーの対策を考えてみます。いくつかあります。

その１

簡単なのはSQL文にinsert, update, deleteなどが含まれていたら、プログラムで実行させない。preg_matchとか使用してバリデーションです。しかし、updated_atとかの項目がSQL文にあったら、それもマッチしてしまうので、updateの前後にスペースがあるかなどのマッチのパターンの調整が必要です。

その２

DBのユーザーを読み込み専用とする。これがベストですがいちいちそのために読み込み専用のDBユーザー作成する必要があります。ホストするプロバイダーによっては読み込み専用と読み書きのユーザーを分けて提供してくれるところもあります。.envあるいはconf/database.phpでの設定に工夫も必要となります。

その３

DBの読み込みセッションを使う。私が使用しているMySQL5.7では以下のようにたとえDBユーザーの権限に書き込みがあっても、以下のような読み込みセッションでDB更新の句クエリーを実行不可とできます。以下のDB::statement('SET SESSION TRANSACTION READ ONLY')の実行後は、レコード削除のSQL文のはエラーとなります。

>>> DB::select('select count(*) from users')
=> [
     {#3436
       +"count(*)": 1,
     },
   ]

>>> DB::statement('SET SESSION TRANSACTION READ ONLY')
=> true

>>> DB::select('delete from users')
Illuminate\Database\QueryException with message 'SQLSTATE[25006]: Read only sql transaction: 1792 Cannot execute statement in a READ ONLY transaction. (SQL: delete from users)'

>>> DB::select('select count(*) from users')
=> [
     {#3441
       +"count(*)": 1,
     },
   ]

クエリーの実行所有時間の制限

こちらは、DBセッションにおける最大実行時間を設定することで、制限をかけることが可能です。以下は、最大実行時間を1000ミリ秒、つまり１秒としました。２秒のスリープの実行では１秒を超えたときにエラーとなります。

>>> DB::statement('SET SESSION MAX_EXECUTION_TIME=1000')
=> true

>>> DB::select('select sleep(1) from users')
=> [
     {#3438
       +"sleep(1)": 0,
     },
   ]

>>> DB::select('select sleep(2) from users')
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 3024 Query execution was interrupted, maximum statement execution time exceeded (SQL: select sleep(2) from users)'

最後に

安全にクエリのSQL文の実行の仕方が解ったところで、実行したときにクエリーの実際の所有時間も知りたいですね。
それは以下のように、前もってDB::listen()を実行すれば、実行後に取得できます。ここでも時間単位はミリ秒です。

>>> $time = 0
=> 0

>>> DB::listen(function($query) use (&$time) { $time = $query->time; })
=> null

>>> DB::select('select count(*) from users')
=> [
     {#3438
       +"count(*)": 1,
     },
   ]

>>> $time
=> 0.48