State of SQL Antipatterns in 2017

State of SQL Antipatterns in 2017

2017/07/10
SQLアンチパターンNight Part2
https://connpass.com/event/59946/

9f3a83db74bee75a64b5e6ed106a775c?s=128

Takuto Wada

July 10, 2017
Tweet

Transcript

  1. 16.

    CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT

    UNSIGNED, comment TEXT NOT NULL, ); 親idが入る Ξϯνύλʔϯৗʹ਌ͷΈʹґଘ͢Δ
  2. 17.

    SELECT c1.*, c2.*, c3.*, c4.* -- 階層毎に列が増える FROM Comments c1

    -- 1階層目 LEFT OUTER JOIN Comments c2 ON c2.parent_id = c1.comment_id -- 2階層目 LEFT OUTER JOIN Comments c3 ON c3.parent_id = c2.comment_id -- 3階層目 LEFT OUTER JOIN Comments c4 ON c4.parent_id = c3.comment_id -- 4階層目 ΞϯνύλʔϯʹΑΓى͜Δ͜ͱ ૉ๿͗͢Δނʹ Ξϯνύλʔϯ
  3. 22.

    WITH RECURSIVE CommentTree (comment_id, bug_id, parent_id, author, comment, depth) AS

    ( SELECT *, 0 AS depth FROM Comments WHERE parent_id IS NULL UNION ALL SELECT c.*, ct.depth+1 AS depth FROM CommentTree ct JOIN Comments c ON ct.comment_id = c.parent_id ) SELECT * FROM CommentTree WHERE bug_id = 1234; ΞϯνύλʔϯΛ༻͍ͯ΋ྑ͍৔߹ ڞ௨ςʔϒϧࣜ $5&ɿDPNNPOUBCMFFYQSFTTJPO Λ࢖ͬͯ࠶ؼΫΤϦΛॻ͚Δ৔߹
  4. 25.

    ղܾࡦ୅ସπϦʔϞσϧΛ࢖༻͢Δ comment_id path ൃݴऀ ίϝϯτ 1 1/ Fran ͜ͷόάͷݪҼ͸Կ͔ͳʁ 2

    1/2/ Ollie ψϧϙΠϯλʔͷ͍ͤ͡Όͳ͍͔ͳʁ 3 1/2/3/ Fran ͦ͏͡Όͳ͍ΑɻͦΕ͸֬ೝࡁΈͩɻ 4 1/4/ Kukla ແޮͳΠϯϓοτΛௐ΂ͯΈͨΒʁ 5 1/4/5/ Ollie ͦ͏͔ɺόάͷݪҼ͸ͦΕͩͳɻ 6 1/4/6/ Fran Α͠ɺ͡Ό͋νΣοΫػೳΛ௥Ճͯ͠ ΋Β͑Δ͔ͳʁ 7 1/4/6/7/ Kukla ྃղɻमਖ਼ͨ͠Αɻ ࡦܦ࿏ྻڍ 1BUI&OVNFSBUJPO
  5. 27.

    ઃܭ ςʔϒ ϧ਺ ࢠ΁ͷΫΤ Ϧ࣮ߦ πϦʔ΁ͷ ΫΤϦ࣮ߦ ૠೖ ࡟আ ࢀর੔߹ੑ

    ҡ࣋ ྡ઀Ϧετ 1 ؆୯ ೉͍͠ ؆୯ ؆୯ Մೳ ࠶ؼΫΤϦ 1 ؆୯ ؆୯ ؆୯ ؆୯ Մೳ ܦ࿏ྻڍ 1 ؆୯ ؆୯ ؆୯ ؆୯ ෆՄ ೖΕࢠू߹ 1 ೉͍͠ ೉͍͠ ೉͍͠ ೉͍͠ ෆՄ ดแςʔϒϧ 2 ؆୯ ؆୯ ؆୯ ؆୯ Մೳ ղܾࡦ୅ସπϦʔϞσϧΛ࢖༻͢Δ ղܾࡦͷൺֱද
  6. 32.

    #@# Douglas Adams ダグラス・アダムズ(イギリスのSF作家・脚本家) //} #@# “How do I count

    the number of rows by date?” This is an example of a simple task for a database programmer. This solution is covered in any introductory tutorial on SQL. It involves basic SQL syntax: 日付ごとの行数を計算する――。データベースプログラマーにとって、お馴染み のシンプルなタスクです。答えは、SQLの入門書でも必ずといっていいほど紹介さ れています。以下のような基本的なSQL構文を用いるというものです。 //source[EAV/intro/count.sql]{ SELECT date_reported, COUNT(*) FROM Bugs GROUP BY date_reported; //} #@# However, the simple solution assumes two things: しかし、この単純な解決策には、以下の2つの前提条件があります。 #@# Values are stored in the same column, as in Bugs.date_reported. * 値が同じ列に格納されていること(例: @<tt>{Bugs.date_reported}) #@# Values can be compared to one another so that GROUP BY can accurately group dates with equal values together. * 値を比較できること(@<ttb>{GROUP BY}で同じ値の日付を正確にグループ化で きるように) ݪߘ͸3F7*&8Ͱࣥච
  7. 35.
  8. 40.

    CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT

    UNSIGNED, comment TEXT NOT NULL, ); 親idが入る φΠʔϒπϦʔ ૉ๿ͳ໦
  9. 43.

    CREATE TABLE BugsProducts ( id SERIAL PRIMARY KEY, bug_id BIGINT

    UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, UNIQUE KEY (bug_id, product_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id), FOREIGN KEY (product_id) REFERENCES Products(product_id) ); 交差テーブルであっても 考え無しにとりあえず主キーを “id”にしてしまう *%ϦΫϫΠΞυ ͱΓ͋͑ͣ*%
  10. 44.

    ೥ঢ়گʹԠͯ͡ద੾ʹௐ੔͢Δ Θ͔Γ΍͍͢ྻ໊(id より bug_id) SELECT * FROM Bugs INNER JOIN

    BugsProducts USING (bug_id); ن໿ʹറΒΕͳ͍ ࣗવΩʔͱෳ߹Ωʔͷ׆༻ 同じ名前なら USINGが使える 考えた結果の“id”ならそれで良し
  11. 45.

    CREATE TABLE Impacts ( id SERIAL PRIMARY KEY, bug_id BIGINT

    UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, verified_at DATETIME NOT NULL, UNIQUE KEY (bug_id, product_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id), FOREIGN KEY (product_id) REFERENCES Products(product_id) ); 機械的な交差テーブルではなく 関係性の発生ととらえる ݱࡏ୯ͳΔަࠩςʔϒϧͰ͸ͳ͘ɺؔ܎ੑͷൃੜ͔΋͠Εͳ͍
  12. 46.

    CREATE TABLE Issues ( issue_id SERIAL PRIMARY KEY ); CREATE

    TABLE IssueAttributes ( issue_id BIGINT UNSIGNED NOT NULL, attr_name VARCHAR(100) NOT NULL, attr_value VARCHAR(100), PRIMARY KEY (issue_id, attr_name), FOREIGN KEY (issue_id) REFERENCES Issues(issue_id) ); &"7ʢΤϯςΟςΟɾΞτϦϏϡʔτɾόϦϡʔʣ FK,名前,値 FK,名前,値 …… 動的な項目を 格納したい
  13. 50.

    CREATE TABLE Screenshots ( bug_id BIGINT UNSIGNED NOT NULL, image_id

    BIGINT UNSIGNED NOT NULL, screenshot_path VARCHAR(100), caption VARCHAR(100), PRIMARY KEY (bug_id, image_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) ); ϑΝϯτϜϑΝΠϧʢݬͷϑΝΠϧʣ 物理ファイルの使用を必須と思い込む
  14. 51.

    ೥ඞཁʹԠͯ͡#-0#ܕΛ࠾༻͢Δ CREATE TABLE Screenshots ( bug_id BIGINT UNSIGNED NOT NULL,

    image_id BIGINT UNSIGNED NOT NULL, screenshot_image BLOB, caption VARCHAR(100), PRIMARY KEY (bug_id, image_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) ); 整合性、トランザクション、ロッ ク、権限管理がメリット
  15. 54.

    SELECT p.product_id, MAX(b.date_reported) AS latest, b.bug_id FROM Bugs b INNER

    JOIN BugsProducts p USING (bug_id) GROUP BY p.product_id; ΞϯϏΪϡΞεάϧʔϓʢᐆດͳάϧʔϓʣ MAX(date_reported)のbug_idが 返るとは限らない
  16. 55.

    ೥ᐆດͰͳ͍ྻΛ࢖͏ SELECT m.product_id, m.latest, MAX(b1.bug_id) AS latest_bug_id FROM Bugs b1

    INNER JOIN ( SELECT product_id, MAX(date_reported) AS latest FROM Bugs b2 INNER JOIN BugsProducts USING (bug_id) GROUP BY product_id ) m ON b1.date_reported = m.latest GROUP BY m.product_id, m.latest; 相関サブクエリ 導出テーブル JOIN の使用 などの手段がある (例は導出テーブル)
  17. 59.

    <?php $project_name = $_REQUEST["name"]; $sql = "SELECT * FROM Projects

    WHERE project_name = '$project_name'"; ↓ http://bugs.example.com/project/view.php?name=O'Hare ↓ SELECT * FROM Projects WHERE project_name = 'O'Hare'; 42-ΠϯδΣΫγϣϯ 未検証の入力をクエリにつなげて実行してしまう
  18. 60.

    ೥୭΋৴༻ͯ͠͸ͳΒͳ͍ <?php $sql = "UPDATE Accounts SET password_hash = SHA2(?,

    256) WHERE account_id = ?"; $stmt = $pdo->prepare($sql); $stmt->bindValue(1, $_REQUEST["password"], PDO::PARAM_STR); $stmt->bindValue(2, $_REQUEST["userid"], PDO::PARAM_INT); $stmt->execute(); 1.エスケープ/サニタイズ 2.プリペアドステートメント 3.動的値のクオート 4.ホワイトリスト
  19. 61.

    <?php $sql = "UPDATE Accounts SET password_hash = SHA2(?, 256)

    WHERE account_id = ?"; $stmt = $pdo->prepare($sql); $stmt->bindValue(1, $_REQUEST["password"], PDO::PARAM_STR); $stmt->bindValue(2, $_REQUEST["userid"], PDO::PARAM_INT); $stmt->execute(); 2017年にエスケープ/サニタイズはダメ プリペアドステートメントと ホワイトリストプログラミングを徹底 ೥ʹΤεέʔϓ͕࠷ॳʹདྷͪΌμϝͩʜ