一般社団法人 全国個人事業主支援協会

COLUMN コラム

はじめに

SQLの中級者になるための本として有名な以下の二冊を読みました。

どちらも大変勉強になりましたので、「概念」と「レシピ集」の2つに分けてアウトプットします。
概念には、SQLやRDBの基礎的な概念や設計などを記載し、
レシピ集には、このような場合はこうした方が良い、または、こうしない方が良いというような具体的な知識について記載します。
本記事はレシピ集であり、概念はこちらに記載しています。
自分のメモとしての用途がメインですので、内容についてはご容赦ください。

レシピ集

ユースケースに対して有効な方法や、アンチパターンを記載する。
大量にあるので、箇条書きでどんどん書いていく。
自分が同じような場面に遭遇したときに思い出せたら良いなというものを抜粋して記載する。
詳細は紹介しないので、気になる方は書籍を読んでほしい。

データ定義

  • 主キーのカラム名をつけるとき
    • 「id」ではなく、主キーが識別する対象のエンティティを表す名前にすべき
    • 代理キーを使う場合、bugsテーブルの主キーはbug_idが良い
    • 主キーと外部キーが同じ名前であれば、結合する時にUSING句が使えるので便利
    • 個人的には、
      • 意味のある名前がつけられるのであればその方が良い
      • 代理キーを使うのであれば、idでも問題ない気もするが、業界的に有名な本なので従っておいた方が無難かな
  • 外部キー制約をつけるとき
    • 参照アクションを必要に応じて利用する（ON DELETE, ON UPDATE）
  • テーブルサイズが大きくなり、テーブルを分割したい
    • パーティショニング（水平、垂直）
      • 物理的に分割するが、論理的には分割しない。論理的に分割しないので複雑にならない
    • 従属テーブルの導入
  • 金額など誤差の許されない数字をカラムに持たせたい
    • NUMERICやDECIMALを利用する（これらは固定精度で少数点数を表す）
    • FLOATは誤差が生じるので、科学技術計算など概数でも良い場合のみに利用する。
      • DBMSによって異なるが、例えば以下のような不都合がある
        • hourly_rateがFLOATの時、以下のようなSELECT文は結果を返さない
          INSERT INTO accounts(account_id, houry_rate) VALUES(1, 59.95);
          SELECT * FROM accounts WHERE houry_rate = 59.95
        • FLOAT型の値をSUM関数などで計算する時、誤差が累積されて大きくなる
  • カラムにいれられる値を特定の値に限定したい
    • bug_statusテーブルなどを作成して、外部キー制約をつける
    • ENUM型やCHECK制約による制限は基本的にはアンチパターン（以下が問題にならなければOK）
      • ENUM型のカラムをソートすると辞書順ではなく、ENUMの定義順でソートされる（知らないと驚く）
      • 有効な値の完全なリストを取得するためには、INFORMATION_SCHEMAなどを見る必要がある
      • とりうる値を削除・変更するときに、既存データの修正が大変
      • 他のDBMSへのマイグレーションが大変（ENUM型はMySQL固有。CHECK制約も仕様が統一されていない）
  • 画像ファイルを管理したい
    • 以下の2つのパターンを検討して判断する
      • 画像ファイルは外部ストレージで管理して、そのパスをDBで管理する
        • DBの容量を減らせる（DBのバックアップも早くなる）
        • 画像ファイルのプレビューや編集が楽になる
      • 画像ファイルをBLOB型にしてDBで管理する
        • 誤ったファイルパスが登録されているという懸念がない
        • レコードと画像を一緒にトランザクション管理できる
          • レコードの削除と画像の削除が連動する（画像の削除し忘れなどがない）
          • ロールバック時に画像も一緒にロールバックできる
          • レコード更新時にロックされるので、他のクライアントが同時に画像更新できない
        • DBのバックアップに画像も含まれる
        • DBの管理権限で画像へのアクセスも管理できる

データ操作

  • 北海道, 青森, …沖縄という県単位で人口が記録されているテーブルがあったとして、これを東北、関東、九州といった地方単位で人口を集計したい
    • CASE式が有効
      • WHERE句やHAVING句で条件分岐するのではなく、CASE式で条件分岐できるようになると表現力が大きく広がる
  • 移動平均を求めたい
    • ウィンドウ関数が有用
    • 相関サブクエリはアンチパターン（ウィンドウ関数が使えない場合の代用手段）
  • ページネーション
    • データの総件数を取得するCOUNT(*)と、対象レコードだけを取得するSELCT(*)の二つに分けるのはアンチパターン
    • ウィンドウ関数が有用
      •  ウィンドウ関数を使えば一つのクエリで実現できる
      • これはウィンドウ関数のPARTITION BYがレコードを集約せずに、計算結果（COUNT(*)）をそのまま元のテーブルに列として追加するだけ、という情報保全性が働くから実現できることである
      • 具体的にはこのようなSQLで実現できる（だいぶシンプル）
  • 宿泊期間がオーバーラップ（重複）している客をリストアップする
    • ウィンドウ関数が有用
  • name, family_id, addressの3つのカラムを持つAddressesテーブルがある時、同じ家族だけど住所が不一致なレコードを検出したい
    • 自己結合が有用
  • 何回かにわたる会合とその出席者を記録するテーブルがある時、出席しなかった人物を求めたい
    • NOT EXISTSが有用（全体集合から出席した人の集合に含まれない人だけを抜き出す）
    • EXCEPTでも可能（ただし、EXCEPTはMySQLなどではまだ実装されていない）
  • すべての教科について50点以上を取っている生徒を選択したい
    • NOT EXISTSが有用（すべての教科が50点以上である、を、50点未満である教科が一つも存在しない、に読み替える）
  • 最頻値を求めたい
    • HAVING句を使う（HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM ~);）
  • バスケット解析をしたい（ビールと紙オムツと自転車を全て置いている店舗を検索したいなど）
    • HAVING句を使う
      • 集合に対する条件を設定する時はHAVING句を使う
  • 異なるテーブルの中身が等しいかどうか確認したい
    • UNIONが有効
      • UNIONは重複を排除するので、UNIONした結果の行数が元のテーブルの行数と一致していれば等しいとわかる
      • UNIONとINTERSECTの結果が等しい（UNIONの結果からINTERSECTの結果を引いた結果が空集合）という方法でも可能
  • 商品マスタから、売上履歴テーブルに存在する商品を重複なく取得したい（売上のあった商品を探したい）
    • EXISTSが有用
    • INNER JOINとDISTINCTの組み合わせでも可能だが、DISTINCTはソートが発生するので基本的にアンチパターン
  • データをランダムに取得したい
    • 主キーが連番で並んでいる場合は、1から主キーの最大値までの間の値をランダムに選択する
      • 欠番がある場合は、欠番の次の値を選択する（欠番の次の値の選択率が高くなることに注意）
    • 主キーのリストを取得して、ランダムに1つの主キーを選択する
    • オフセットを用いてランダムに選択する
    • DBMS製品ごとの機能を利用する（ほとんどのDBMSには独自機能として実装されている）
    • ランダムにソートして最初のレコードを取得する方法は基本的にアンチパターン
      • パフォーマンスが悪い
        • インデックスが効かない
        • 1レコードだけ必要なのに全レコードをソートする
      • 逆に言えば、データの量が少ない場合は問題ない
  • 全文検索したい
    • DBMS製品ごとの機能を利用する（ほとんどのDBMSには独自機能として実装されている）
    • 転置インデックスを自作する
    • パターンマッチ述語（LIKEなど）はパフォーマンスが悪い（前方一致以外はインデックスが効かない）のでアンチパターン

RDBとSQLの注意事項

RDBやSQLには色々な機能があるが、意外と知らない仕様があったり、機能が豊富な分、不適切な使い方ができてしまったりする。
また、DBMSによって機能が異なることもある。
ここではRDBとSQLについて、知っておくべきことや気をつけるべきことを記載する。

言語要素別(句, 関数, 式, 演算子など)

余談だが、SQLにおける句や述語などの分類は以下を参考にしてつけた。

CASE式

  • CASE式の評価は真になるWHEN句が見つかった時点で打ち切られて、残りのWHEN句は無視される（評価されない）
  • CASE式には単純CASE式と検索CASE式がある。単純CASE式ではNULLと比較してはいけない。
    例えば以下の単純CASE式の場合、二つ目のWHEN句は（col1_1 IS NULLではなく）col1_1 = NULLの省略系なので絶対に真にならない。
    CASE col_1
      WHEN 1 THEN '○'
      WHEN NULL THEN '×'
    END
  • CASE式は「文」ではなく「式」である。式だから、実行時に評価されて一つの値に定まる。
    • つまり、列名や定数を書ける場所には常に書くことができる。
  • ENDは必須なので書き忘れると構文エラーになる
  • ELSE句を省略すると、暗黙に「ELSE NULL」の扱いになる。バグの温床になるので明示的にELSE句をつける癖をつける

ウィンドウ関数

  • ウィンドウ関数とは以下の3つの操作を一つの関数に詰め込んだもの
    • PARTITION BY句によるレコード集合のカット
    • ORDER BY句によるレコードの順序付け
    • フレーム句によるカレントレコードを中心としたサブセットの定義
  • ウィンドウ関数とは手続き型言語の考え方をSQLに輸入した機能である
    • フレーム句はカーソルの概念をSQLにとりこんだもの
  • PARTITION BYは集約を伴わない（情報保全的）。ここがGROUP BYと違うところ。

IN句とEXISTS句

  • NOT INのサブクエリで使用されるテーブルの選択列にNULLが存在する場合、SQL全体の結果は常に空になる
  • サブクエリをとる場合は、EXISTSの方が早い。理由は２つ。
    • もし結合キーにインデックスが張られていれば、結合対象の実テーブルは見にいかず、インデックスを参照するのみで済む
      • INの場合は、サブクエリから実行し、その結果を一時的なワークテーブル（インラインビュー）に格納し、その後、ビューを全件走査する。一般にワークテーブルにはインデックスが存在しない
    • EXISTSは一行でも条件に合致する行を見つけたらそこで検索を打ち切るので、INのように全件走査の必要がない
      • これはNOT EXISTSでも同様
    • ただし、最近のDBMSはINを使った場合でもパフォーマンスを上げられるように改善を図っている
    • また、INで書いた方が可読性が高いため、将来的にはIN述語がアンチパターンと見なされることは少なくなっていくだろう
  • INの引数に、スカラ値ではなく値のリストを取ることが可能（行比較）
    • SELECT * FROM Addresses2 A1 WHERE (id, state, city) IN (SELECT id, state, city FROM Addresses2 A2);

GROUP BY句

  • ソートやハッシュの演算が発生する
  • テーブルを集約した場合、SELECT句に書くことのできる要素は以下の3つである
    • GROUP BY句で指定した集約キー
    • 集約関数（SUM、AVGなど）
    • 定数
  • GROUP BYで集約を行うことによって、SQLが扱う対象は、「行」というゼロ階の存在から、「行の集合」という一階の存在へ変化する
    • その時点で、行の属性は全て参照負荷になる。
    • ただし、MySQLとSQLiteでは行の属性をSELECT句に書けてしまうが、予期しない値が返される可能性があるので、注意すること

HAVING句

  • GROUP BYと一緒に使う必要はない（単体でも使える）
  • 必ず集約を伴うのでソートかハッシュが行われる
  • WHERE句で書ける条件はHAVING句には書かない
    • 必ずソートやハッシュがが行われるので、できるだけ事前に行数を絞り込んでからソートした方が良い
    • HAVING句は集約後のビューに対する条件を設定するが、集約後のビューは元テーブルのインデックスまで引き継がないケースが多い（WHERE句であればインデックスが効く）
  • HAVING句は集約を行いながら並行して動作する
  • 「中間テーブルを作成　→ WHERE句で絞り込み」ではなく、HAVING句を使う
    • 無駄な中間テーブルは作成しない

極値関数（MAXやMINなど）

  • 集計の際にNULLを排除する
  • 入力（引数）が空テーブル（空集合）だった場合はNULLを返す

集約関数（SUMやCOUNTなど）

  • COUNT以外の集約関数は入力（引数）が空テーブル（空集合）だった場合はNULLを返す
  • COUNT(*)はNULLを数えて、COUNT(列名)はNULLを数えない
  • ソートが発生する

結合（JOIN句）

  • 外部結合では、外部結合であるということがわかりやすくなるのでOUTERを省略しない
    （LEFT JOINではなくOUTER LEFT JOIN）
  • 結合と集約を併用するケースでは、集約よりも結合を先に行うことで、中間テーブルを省略できる
    （集合演算としての「掛け算」として機能するから。つまり、一対一や一体多の場合は結合によって行数が増えることがない）

DISTINCT修飾子

  • 重複排除のため暗黙のソートを発生させる

集合演算子（UNION, EXCEPTなど）

  • 重複排除のため暗黙のソートを発生させる
  • ALLオプションをつけると重複を排除しないためソートが発生しない
  • UNIONとEXCEPTよりもINTERSECTのほうが先に実行される（加算と減算よりも乗算の方が先に実行される）
  • UNIONとINTERSECTは冪等性をもつ（同じ集合をいくつ足して（掛けて）も結果は不変）

インデックス

ポビュラーなBツリーインデックスの場合、二分探索による高速検索が可能なように工夫されている。
Bツリーインデックスが効かなくなるかもしれないアンチパターンな書き方を箇条書きで記載する。

なお、INDEXは規格ではなく、各DBMSのインデックスを独自実装している。
そのため、各製品のドキュメントを良く読むことと、MENTORの原則に従い検証することが必要。
（MENTORとはMeasure、Explain、Nominate、Test、Optimize、Rebuildの頭文字をとったもの）
個人的に確認したことがある内容については書籍の内容に補足して追記する。（こちらも参照）

  • 索引列に加工を行っている
    • 「インデックスを利用するときは、列は裸」これが基本の「き」
  • インデックス列にNULLが存在する
    • NULLは正当な値ではなく、これをどのように扱うかの統一的な基準がないため、複雑な状況。
      一応、MySQLとPostgreSQLで試したところ問題なさそうだった。
  • 否定系を使っている
    • <>、!=、NOT INのような否定系はインデックスを使用できない
    • 全ての値を走査しないと指定した値以外の全ての値が選択できていることを確認できないからだと思う。
      ただし、以前PostgreSQLで確認したときはインデックスが効いた。
  • ORを使っている
    • col_1とcol_2に別々の索引がある場合、または（col1_1, col_2）に複合索引を張っている場合のいずれも、ORを使って条件を結合するとインデックスが利用できなくなるか、使えたとしてもANDに比べれば非効率な検索になる
    • どうしてもOR条件を指定したい場合は、ビットマップインデックスが向いているが、これも更新コストが高くなるというデメリットがあるので、使い所が限定されるインデックスである
  • 複合索引の場合に列の順番を間違えている
    • （col1_1, col_2, col_3）に対してこの順番で複合インデックスが貼られている場合、必ず最初の列（col_1）を先頭に書かなばいけないし、順番が崩れてもいけない
    • 中には、順番が崩れていてもインデックスが利用できるDBMSもあるが、順番が正しい場合に比べてパフォーマンスは落ちる
  • 後方一致、または中間一致のLIKE述語を用いている
    • LIKE述語を使う場合は前方一致検索のみ索引が使用される
  • 暗黙の型変換を行っている
    • 暗黙の型変換はオーバーヘッドを発生させるだけでなく、インデックスまで使用不可になる。明示的な型変換をすべき。

NULL

NULLへの対処を記載する。

NULLがなぜ悪いか

まずNULLはなぜ悪いのか。代表的なものは以下の通り。

  • SQLのコーディングにあたり、人間の直感に反する3論理値を考慮せねばならない
  • IS NULL、IS NOT NULLを指定する場合、インデックスの利用に制限が入りパフォーマンスが悪い
    • 多くのDBMSではインデックスにNULLが多いとインデックスが参照されなかったり、Oracleのようにそもそもインデックスが使用されない実装もある
  • 四則演算またはSQL関数の引数にNULLが含まれると「NULLの伝播」が起こる
  • SQLの結果を受け取るホスト言語にいて、NULLの組み込み方が標準化されていない。
    また、DBMS間でもNULLの扱いに関する仕様が不統一。
  • 通常の列の値と違って、NULLは行のどこかに余分なビットを持つことで実装されている。
    そのため記憶領域を圧迫したり、検索パフーマンスを悪化させたりする。
  • NULLを含むカラムに作成するユニークインデックスの「ユニーク」の意味が各RDBMSで違う
    • たとえば、複数のNULLを含む列にユニークインデックスを作成するとき、NULLの重複によってエラーになったりならなかったりする
  • 通常の列の値ではないため、ORDER BY句によるソートの際のルールを意識する必要がある
    • NULLは定義域に含まれる値ではないため、本来は順序もつけられない。
      しかし、実務上はレポートのどこかに表示しなければならないので、一般には最大値か最小値として扱われる。
      これがまた実装によってどちらがデフォルトかが異なり、話をややこしくしている
  • プリペアドステートメントのパラメータでNULLを一般値のように使えない（NULLの場合は処理を分岐するなどの工夫が必要）

NULLの伝播について少し説明する。
以下のように四則演算の対象にNULLが含まれていた場合、演算結果は全てNULLになる。

  • 1 + NULL = NULL
  • 2 – NULL = NULL
  • 3 * NULL = NULL
  • 4 / NULL = NULL
  • NULL / 0 = NULL

NULL排除の指針

このようにNULLを使うことの不都合はたくさんある。
しかし、NULLを完全に排除することは難しい。
それでも、できる限りNULLを使うべきではない。
以下ではNULL排除の指針を記載する。

コードの場合

解決策は未コード用のコードを割り当てること。

例えば、ISOの性別コードでは、1: 男性、2: 女性、0: 未知、9: 適用不能が存在する。
性別がわからない人はコード0、法人はコード9を選択すれば良い。

また、不明な顧客コードをDBに登録しないといけない場合、不明を表すコードとして「XXXXX」を用意する。
ここで「99999」のようなコードを使うことは避けるべき。
なぜなら、コードには多くの場合、数字が使われるため、ありえないと思って未コード化用コードに採用したコードを持つ顧客が現実に出現してしまう可能性があるからである。
そのため、コードは必ず数値型でなく文字型で宣言すべき。

名前の場合

名前の場合も方針はコードの場合と同じく、不明を表す値を与えることが解決策。
「不明」でも「UNKNOWN」でも、開発チーム内で共通了解の得られた適当な名前を与える。
一般に、名前はコードに比べてキーに使われる頻度は少なく、付加的な意味しか持たない場合が多いので、あまり撲滅に目くじらを立てる必要もないが、やっておくにこしたことはない。

数値の場合

数値型の列の場合、最も良いと考えるのは、最初からNULLを0に変換してDBへ登録すること。
NULLを許可しておいて集計時にNULLIF関数やIS NOT NULL述語で排除する、という方法は勧めない。
経験上、NULLを0に吸収させて問題化したことはあまりない。

ただし、「（所有している車の）ガソリンタンクが空なのと、車を持っていない」は異なるものであり、現実的には以下のようになる。

  • 0に変換する
  • どうしても0とNULLを区別したい場合だけ、NULLを許可する

日付の場合

日付が開始日や終了日など「期限」を表す場合は、「0001-01-01」や「9999-12-31」のように可能な最小値・最大値を使うことで対応できる。
例えば、社員の入社日やカードの有効期限はこの方法で対応できる。

デフォルト値がそもそもわからない場合、たとえば歴史上の事件が起きた年月や誰かの誕生日など、「未知」のNULLに該当する場合は、意味のある値をいれることはできない。
この場合は、NULLを許可しても良いだろう。

まとめ

まとめると以下の通り。

  1. まずデフォルト値を入れられないか検討する
  2. どうしようもない場合だけNULLを許可する

NULLと付き合う

できるだけNULLを排除したいがNULLを許容しなければならないこともある。
その場合に上手くNULLと付き合う方法を記載する。

NULLの検索

NULLを比較する時は、IS NULLやIS NOT NULLを使わないといけないことは有名。
だが、比較述語IS DISTINCT FROMも利用できる。これは非等価演算子<>のように利用できる。
具体的には以下の二つのクエリが同等になる。

  • SELECT * FROM bugs WHERE assigned_to IS NULL OR assigned_to <> 1;
  • SELECT * FROM bugs WHERE assigned_to IS DISTINCT FROM 1;

ただし、IS DISTINCT FROMをサポートしていないDBMSもあるので注意。
また、MySQLはIS DISTINCT FROMと同等な<=>という独自の演算子を提供している。

動的なデフォルト値

NULLの場合はデフォルト値を表示したいことがある。
本来であれば、テーブル定義としてデフォルト値を設定した方が良いが、そうもいかない場合だ。
その場合は、COALESCE関数を使う。

SELECT  first_name || COALESCE(' ' || middle_initial || ' ', ' ') || last_name AS full_name FROM Accounts;

SQLプログラミング作法

一般的なプログラミング言語ではリーダブルコーディングが重要視されるが、SQLでは軽視されがち。
書籍で紹介された中で「なるほど！確かに！」と思った作法をピックアップして記載する。

  • 意味のある名前をつけるべき
    • 特にインデックスと制約は、明示的に名前を与えないとDBMSが自動的にランダムな識別子を付与してしまうので注意が必要
    • 名前に使うことが許されている文字は三種類
      • アルファベット
      • 数字
      • アンダーバー（_）
  • なるべくコメントはあった方が良い
    • SQLは宣言型言語であるため、同じ処理をするにも手続き型言語に比べて非常に凝縮されたコードに多くの処理を詰め込む。
      そのため、手続き型言語ほどコードに語らせることが難しい。
    • SQLは段階的なデバッグがほとんどできない。机上デバッグしやすいようにコメントがあった方が良い。
  • 大文字と小文字
    • 予約語は大文字、列名やテーブル名は小文字で書くのが一般的。
      なお、SQLにおいて大文字と小文字は見た目上の違いしかなく、DBMS上ではどちらも同じと扱われる。
  • ワイルドカードは使わない
    • ワイルドカードを使うと、論理的に不要な列まで含まれるため、コードの可読性が低下し、仕様変更にも弱くなる。
      結果の形式が列の並び順に左右されるため、テーブルの列の順番を入れ替えたり、追加・削除が発生したときに結果が狂う原因となる。
  • 標準語を話す
    • 可読性やマイグレーションに弱くなるので、実装依存の関数や演算子を使わない
      • DECODE（Oracle）、IF（MySQL）、NVL（Oracle）、STUFF（SQL Server）など
    • 結合には標準の構文を使う
      • 内部結合は、INNER JOIN
      • 外部結合は、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN
      • 結合条件はON、または、USING
        • WHERE句でWHERE F.state = B.stateみたいに結合することはやめる
  • 相関サブクエリは使わない
    • ウィンドウ関数を使う
  • FROM句から書く
    • SQLの実行順序は以下の通りなので、SELECT句からでなく、実行順序に従ってFROM句から書いた方が自然にロジックを追える
      • FROM -> WHERE ->GROUP BY -> HAVING -> SELECT (-> ORDER BY)
      • ※ ORDER BYは正確にはSQLの一部ではない

セキュリティ対策

パスワード

ハッシュして格納するのは当たり前だが、ソルトも付加した方が良い。
その場合、パスワードごとに異なるソルト値を付加する。
そうすることで、仮に攻撃者にDBへアクセスされ、辞書攻撃された場合でも、ソルト値がついていれば、攻撃者はそのソルト値の解析もしなければならないため、より安全になる。

また、SQLクエリの中でハッシュ化するのではなく、アプリケーションコードの中でハッシュ化すること
SQLクエリの中でハッシュ化するということは、クエリの中にパスワードの平文が書かれているということなので、ネットワークパケットを傍受された場合に危険。（アプリケーションサーバーとDBサーバーが別という前提）

SQLインジェクション

少し長くなるのでSQLインジェクションだけ本セクションにまとめる。

まずSQLインジェクション対策の王道は2つ。

  • そもそも無効な値をはじく（バリデーションする）
  • プリペアドステートメントを利用する。

無効な値をはじけるのが一番良いだろう。
はじけない場合はプリペアドステートメントを利用することになるだろうが、プリペアドステートメントにも弱点がある。
そのため、場合によっては独自のエスケープ関数を用意する必要があるかもしれない。
プリペアドステートメントの弱点は以下の通り。

  • 不適切な最適化が行われる可能性がある
    • 例えば、statusカラムにインデックスが張ってあるとして、行の99%には’ACTIVE’、残りの1%には’BANNED’が入っているとする。
      status = 'ACTIVE'で検索する場合はインデックスを利用した方が早いが、status = 'BANNED'の場合はインデックスを利用すると非効率。
      このような場合、プリペアドステートメントでstatus = ?という式を使うとオプティマイザは作成済みのクエリを実行する際にどちらの値が来るのか判断できず、不適切な最適化が行われる可能性がある。
  • 値のリストを1つのパラメータにすることはできない（値の数だけパラメータを用意する必要がある）
  • テーブル名や列名、SQL予約後をパラメータとして扱えない（文字列リテラルとして解釈される）
    • 例えばテーブル名をパラメータとして扱おうとすると、テーブル名に文字列リテラルを入力したかのように処理され、構文エラーになる

SQLインジェクション対策はコードレビューが大切。
コードレビューをするときには以下のガイドラインに従うこと。

  1. アプリケーション変数や文字列連結、文字列置換によって構築されているSQLステートメントを特定する
  2. SQLステートメントで使われている、全ての動的コンテンツの起点を辿り、外部ソースからくる全てのデータを特定する
    （ユーザ入力、外部ファイル、環境まわり、外部のウェブサービス、サードパーティーのコード、DBから取得した文字列など）
  3. これらの外部コンテンツには全て潜在的なリスクがあると想定する。
    フィルター、バリデーター、マッピング配列などを用いて、これら信頼性の低いコンテンツを変換する
  4. プリペアドステートメントまたは検証済みのエスケープ関数を用いて、SQLステートメントと外部データを組み合わせる
  5. 他にもストアドプロシージャなどの、動的SQLステートメントが隠れている可能性がある場所をチェックすることも忘れない

NoSQL

RDBの課題として大きく2つある。

  • 性能と信頼性のトレードオフ
    • データを一元管理し、厳密なトランザクション管理するためにストレージを共有する構成を取る必要があり、ストレージがシングルボトルネックになる
    • SQLの表現力が強力で柔軟であるため、かえって複雑な処理を実行できてしまう。
      特に結合やサブクエリなどの複雑な処理を大規模なデータに実行することで大規模なスローダウンを引き起こすことがある。
  •  データモデルの限界
    • RDBは表現することが苦手なデータの種類がいくつかある。代表的なものは以下。
      • グラフ
        • 組織図のような木構造のグラフ（非循環グラフ）
        • SNSのユーザ関係を表すようなネットワークグラフ（循環グラフ）
      •  非構造データ
        • JSONやXMLなど。

これらの対策としてNoSQLが有用。
RDBのパフォーマンスの問題を解決する手段として考えられた方針は大きく以下の二つ。

  • データモデルを単純化し、複雑なデータ操作を制限する
    • MongoDBやCouchDBなどのドキュメント指向型DB
  • シングルボトルネックポイントをなくしスケールアウト可能にする
    • 具体的には、RedisやmemcachedなどのKVS

これらのNoSQLはRDBと相互補完的に利用されている。
また、RDBでもJSONやXMLをサポートするようになってきており、RDBがNoSQLを内包するようになるかもしれない。

The following two tabs change content below.

植木 宥登

最新記事 by 植木 宥登 (全て見る)

この記事をシェアする

  • Twitterでシェア
  • Facebookでシェア
  • LINEでシェア
記事一覧

関連記事

階層どうしの連絡方法
階層どうしの連絡方法
コロナによる影響
コロナによる影響
Selenium WebDriverでChrome拡張機能ポップアップのスクリーンショットを取得する




全体の人気記事

カテゴリー