ホーム » 2022 » 11月 » 07

日別アーカイブ: 2022年11月7日

2022年11月
 12345
6789101112
13141516171819
20212223242526
27282930  

検索・リンク

GROUP BY HAVINGとビューテーブル

GROUP BY HAVING

GROUP BY-HAVING では、指定されたカラムについて同じ値を持つレコードがグループ化される。SELECT 文に指定される集約関数は、グループごとに適用される。HAVING は、ある条件を満たす特定のグループを選択するための条件で、WHERE と違い、集約関数が使える。

SELECT SG.商品番号, SUM(SG.在庫量)
  FROM SG
  GROUP BY SG.商品番号 HAVING SUM(SG.在庫量) >= 500 ;

このSQLを実行すると、SG のテーブルから、商品番号が同じものだけをあつめてグループ化される。そのグループごとに在庫量のデータの合計SUMを集約し、500以上のデータが出力される。

業者番号 商品番号 在庫量
S1 G1 300
S1 G2 200
S1 G3 400
S1 G4 200
S1 G5 100
S1 G6 100
S2 G1 300
S2 G2 400
S3 G2 200
S4 G2 200
S4 G4 300
S4 G5 400

※業者番号順
 ⇒
業者番号 商品番号 在庫量 SUM(SG.在庫量)
S1 G1 300 600
(≧500)
S2 G1 300
S2 G2 400 1000
(≧500)
S3 G2 200
S1 G2 200
S4 G2 200
S1 G3 400 400
S1 G4 200 500
(≧500)
S4 G4 300
S1 G5 100 500
(≧500)
S4 G5 400
S1 G6 100 100

※商品番号でグループ化
 ⇒
商品番号 SUM(SG.在庫量)
G1 600
G2 1000
G4 500
G5 500

※抽出結果

CREATE VIEW

今までで述べてきたSQLでは、実際のテーブルを対象に、結合・選択・射影を行う命令であり、これは概念スキーマと呼ばれる、対象となるデータベース全体を理解したプログラマによって扱われる。

しかし、プログラムの分業化を行い、例えば結果の表示だけを行うプログラマにしてみれば、全てのデータベースの表を考えながらプログラムを作るのは面倒である。そこで、結合・選択・射影の演算の結果で、わかりやすい単純な表となったものであれば、初心者のデータベースプログラマでも簡単に結果を扱うことができる。このような外部スキーマを構成するための機能が、ビューテーブルである。

-- 優良業者テーブルを作る --
CREATE VIEW 優良業者 ( 業者番号 , 優良度 , 所在 )
    AS SELECT S.業者番号, S.優良度, S.所在
         FROM S
         WHERE S.優良度 >= 15 ;

-- 優良業者テーブルから情報を探す --
SELECT *
  FROM 優良業者
  WHERE 優良業者.所在 = '福井' ;

-- 文房具データベース --
CREATE VIEW 文房具データベース ( 業者名 , 商品名 , 在庫量 )
    AS SELECT S.業者名, G.商品名, SG.在庫量
         FROM S, SG, G
         WHERE S.業者番号 = SG.業者番号 and SG.商品番号 = G.商品番号 ;

ビューテーブルに対する SQL を実行すると、システムによっては予め実行しておいた CREATE VIEW の AS 以下の SQL の実行結果をキャッシュしておいて処理を行うかもしれない。システムによっては SQL の命令を 副クエリを組合せた SQL に変換し、処理を行うかもしれない。しかし、応用プログラマであれば、その SQL がどのように実行されるかは意識する必要はほとんど無いであろう。

ただし、ビューテーブルに対する 挿入・更新・削除といった演算を行うと、データによっては不整合が発生することもあるので注意が必要である。

SQL言語

教科書の流れに沿ってSQLの言語について、再掲

  • スキーマ定義
    • CREATE – 実テーブル、ビューテーブルの定義
    • GRANT – 権限の定義
  • スキーマ操作
    • DROP – 実テーブル、ビューテーブルの削除
    • REVOKE – 権限の削除
    • ALTER – テーブルの変更
    • ADD – カラムの追加
  • データ操作
    • SELECT, INSERT, DELETE, UPDATE – レコードの検索、追加・削除・更新
    • トランザクション処理
      • データベースでは、原子性などを満たすためにデータベースへの更新履歴を保持している。これらの更新履歴をデータベースに反映させ確定する処理がトランザクション処理。
      • COMMIT – データベースの更新処理を確定
      • ROLLBACK – データベースの更新処理を取り消す

ホスト言語とのインタフェースとSQLインジェクション

プログラミング言語によっては、その言語の中でSQLを使うために「組み込み型のSQL」が使えるものがある。
(COBOL,PL/Iなど)

動的メモリ管理が得意な最近のPythonやPHPなどの言語であれば、データベース参照の関数が利用できる。

SQLインジェクション

例えば、PHPでは、SQLからデータを取り出す処理は、以下のようになる。

// 検索するユーザID
$id = "t-saitoh" ;
$pdo = new PDO( '...' ) ; // データベースに接続する関数
$sql = "select name from usertable where id='$id'" ;
$query = $pdo->prepare( $sql ) ;

// 取り出せたデータに関する処理 id がプライマリキーならforeachは1回ループのはず
foreach( $query->fetcAll() as $name ) {
   // $name に取り出した名前が入っている
} 

しかし、$id の部分を、Web の入力フォームなどの値であれば、名前以外の情報が入力される場合もある。

この際に、「 $id = ” ‘ or 1==1 — ‘ ” 」といった値が入っていた場合、SQLで実行される命令は、

$id = "' or 1==1 --'" の場合
$sql = "select name from usertable where id='' or 1==1 -- ''" ;

となってしまい、本来なら1人のデータを抽出する select 命令が、全テーブルに対して該当してしまい、情報漏洩が発生するかもしれない。

「 $id = “‘; drop usertable ; — ‘” 」であれば、usertable が消されてしまい、システムが動かなくなる(サービスを提供できなくする攻撃 = DoS攻撃 – Denial-of-service attack)ことも考えられる。

他にもSQLインジェクションを使う技がある。こちらの CTF の WriteUp などが参考になる。

こういった攻撃手法は、SQLに本来の意図ではないSQL命令を紛れ込ませる攻撃ということで、SQLインジェクションという。

SQLインジェクションで発生した有名な事件では、以下のようなものがある。

対策としては、ユーザが入力したデータを用いて SQL 命令を実行する場合は、ユーザ入力をSQLとして悪用されないように、シングルクオートなどをエスケープするなどの処理が必要となる。さまざまな手法があるので、SQL無効化の専用関数を用いるべき。

また、データベースシステムは、ネットワーク経由でSQLによる処理を行うが、データベースサーバ自体がインターネットに接続されていて、パスワード攻撃によりデータベース本体に不正アクセスが行われる場合もある。一般的なデータベースを用いたシステムは、フロントエンドのWebサーバ、スレーブDBサーバ、マスタDBサーバの三層構成をとることが多いが、バックエンドのデータベースは、インターネットから隔離しフロントエンドのWebサーバのみ接続できるようにするのが一般的である。

データベースに接続する場合はパスワードにより利用者を限定することができるが、データベースシステム自体がインターネットに接続されていると、パスワード総当たり攻撃(ブルートフォース攻撃)や、パスワードスプレー攻撃(総当たり攻撃は、短時間でパスワード失敗が多発するのでシステムで接続拒否するのが一般的。これを回避するために時間をかけて総当たり攻撃をする手法)により、情報漏洩が発生する。

システム

最新の投稿(電子情報)

アーカイブ

カテゴリー