State of SQL Antipatterns in 2017

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

Transcript

1. ࿨ా୎ਓ !U@XBEB
   
   +VM
   !42-Ξϯνύλʔϯ/JHIU1BSU
   4UBUFPG42-
   "OUJQBUUFSOTJO
   TRMBQ@OJHIU
   

   View Slide

2. ࿨ా୎ਓ
   JEUXBEB
   !U@XBEB
   HJUIVCUXBEB
   

   View Slide

3. 日本各地に出没するスタンド
   ελϯυ໊ϫΠϧυɾαόϯφ
   

   View Slide

4. ͓͔͛͞·Ͱ޷ධՁΛ௖͍͓ͯΓ·͢
   

   View Slide

5. Agenda
   Ξϯνύλʔϯܗࣜͷ͓͞Β͍
   ೥ʹ͓͚Δຊॻʹ͍ͭͯ
   Ξϯνύλʔϯͷ͓ข্͖͛
   ຊॻͷҙٛʹ͍ͭͯ
   
   

   View Slide

6. ۪ऀ͸ܦݧʹֶͼɺݡऀ͸ྺ࢙ʹֶͿɻ
   ᴷΦοτʔɾϑΥϯɾϏεϚϧΫ
   ຊॻͷςʔϚʹ͍ͭͯ
   

   View Slide

7. ॾ܅͸ࣗΒͷܦݧ͔Β͍͘Βֶ͔Ϳ͜ͱ͕Ͱ
   ͖Δͱ͍͏ɺશ۪͔͘ͳߟ͑Ͱ͋Ζ͏͕ɺ
   ༨͸Ή͠ΖଞਓͷࣦഊΛֶͿ͜ͱͰɺࣗ෼ͷ
   ࣦഊΛճආ͢Δ͜ͱΛ޷Ήɻ
   ᴷΦοτʔɾϑΥϯɾϏεϚϧΫ
   /VSFJO*EJPUHMBVCU BVTEFOFJHFOFO&SGBISVOHFO[VMFSOFO
   *DI[JFIFFTWPS BVTEFO&SGBISVOHFOBOEFSFS[VMFSOFO VN
   WPOWPSOFIFSFJOFJHFOF'FIMFS[VWFSNFJEFO
   

   View Slide

8. Ξϯνύλʔϯͱ͸
   ୯ͳΔ
   ΂͔Βͣू
   ͋Δ͋Δू
   ʜʜͰ͸ͳ͍
   

   View Slide

9. ໊લ
   ໨త
   Ξϯνύλʔϯ
   Ξϯνύλʔϯͷݟ͚ͭํ
   ΞϯνύλʔϯΛ༻͍ͯ΋ྑ͍৔߹
   ղܾࡦ
   ໊લॏཁ
   ຊॻͷΞϯνύλʔϯͷߏ੒
   

   View Slide

10. ྫφΠʔϒπϦʔ
    ૉ๿ͳ໦
    
    ໊෇͚ͷྫ
    

    View Slide

11. ύλʔϯ໊͕ӳޠͦͷ··ΧλΧφදهͰ͋
    Δͷ͸ɺ໨࣍Λݟ͚ͨͩͰ͸ϏοΫϦ͢Δϙ
    ΠϯτͰ͢Ͷɻ
    ͨͩɺνʔϜ಺Ͱ૬ஊ͢Δͱ͖ͳͲʹ໨ཱͭ
    ໊લ͕෇͍͍ͯΔͷ͸Ή͠Ζ͋Γ͕͍ͨͰ͢
    ͠ɺԿΑΓͳΜ͔ΧοίΑ͓ͯ͘΋͠Ζ͔ͬ
    ͨͰ͢
    http://d.hatena.ne.jp/moro/20130205/1360044434
    ͳͥΧλΧφ
    

    View Slide

12. ໊લ
    ໨త
    Ξϯνύλʔϯ
    Ξϯνύλʔϯͷݟ͚ͭํ
    ΞϯνύλʔϯΛ༻͍ͯ΋ྑ͍৔߹
    ղܾࡦ
    ࣮ྫφΠʔϒπϦʔ ૉ๿ͳ໦
    
    

    View Slide

13. ໨త֊૚ߏ଄Λ֨ೲ͠ɺΫΤϦΛ࣮ߦ͢Δ
    

    View Slide

14. ໊લ
    ໨త
    Ξϯνύλʔϯ
    Ξϯνύλʔϯͷݟ͚ͭํ
    ΞϯνύλʔϯΛ༻͍ͯ΋ྑ͍৔߹
    ղܾࡦ
    ࣮ྫφΠʔϒπϦʔ ૉ๿ͳ໦
    
    

    View Slide

15. Ξϯνύλʔϯͱ͸ԿͰ͠ΐ͏͔ɻͦΕ͸ɺ
    ໰୊ͷղܾΛҙਤ͠ͳ͕Β΋ɺ͠͹͠͹ଞͷ
    ໰୊Λੜͤͯ͡͞͠·͏Α͏ͳٕ๏Λࢦ͠·
    ͢ɻ
    ᴷ#JMM,BSXJO
    Α͔Εͱࢥͬͯཪ໨
    ʹग़ͯ͠·͏΋ͷ
    

    View Slide

16. CREATE TABLE Comments (
    comment_id SERIAL PRIMARY KEY,
    parent_id BIGINT UNSIGNED,
    comment TEXT NOT NULL,
    );
    親idが入る
    Ξϯνύλʔϯৗʹ਌ͷΈʹґଘ͢Δ
    

    View Slide

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階層目
    ΞϯνύλʔϯʹΑΓى͜Δ͜ͱ
    ૉ๿͗͢Δނʹ
    Ξϯνύλʔϯ
    

    View Slide

18. ໊લ
    ໨త
    Ξϯνύλʔϯ
    Ξϯνύλʔϯͷݟ͚ͭํ
    ΞϯνύλʔϯΛ༻͍ͯ΋ྑ͍৔߹
    ղܾࡦ
    ࣮ྫφΠʔϒπϦʔ ૉ๿ͳ໦
    
    

    View Slide

19. ௚໘͍ͯ͠Δ໰୊ͷछྨ΍ɺϝϯόʔؒͷձ
    ࿩ͰͷԿؾͳ͍ݴ༿͕ɺͦ͜ʹΞϯνύλʔ
    ϯ͕͋Δ͔΋͠Εͳ͍͜ͱʹؾͮ͘ώϯτʹ
    ͳΓ·͢ɻ
    ᴷ#JMM,BSXJO
    

    View Slide

20. Ξϯνύλʔϯͷݟ͚ͭํ
    ʮ͜ͷπϦʔͰ͸ɺਂ͞ΛԿ֊૚·Ͱαϙʔ
    τ͢Ε͹͍͍ʁʯ
    ʮπϦʔܕͷσʔλߏ଄Λѻ͏ίʔυͳΜͯ
    ೋ౓ͱॻ͖ͨ͘ͳ͍ͳʯ
    ʮπϦʔͷதͰݽࣇʹͳͬͨߦΛ͖Ε͍ʹ͢
    ΔͨΊʹɺఆظతʹεΫϦϓτΛ࣮ߦ͠ͳ͚
    Ε͹ʯ
    

    View Slide

21. ໊લ
    ໨త
    Ξϯνύλʔϯ
    Ξϯνύλʔϯͷݟ͚ͭํ
    ΞϯνύλʔϯΛ༻͍ͯ΋ྑ͍৔߹
    ղܾࡦ
    ࣮ྫφΠʔϒπϦʔ ૉ๿ͳ໦
    
    

    View Slide

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
    
    Λ࢖ͬͯ࠶ؼΫΤϦΛॻ͚Δ৔߹
    

    View Slide

23. ຊॻʹ͸ΞϯνύλʔϯΛద༻ͯ͠΋ྑ
    ͍ঢ়گͷઆ໌΋͋ͬͯ޷ײ͕࣋ͯ·͢ɻ
    ུ
    ͜ͷຊ͸୯ͳΔʮ΂͔ΒͣूʯͰ͸
    ͳ͘ʮύλʔϯຊʯ͔ͩΒͰ͢ɻίϯς
    Ωετ΍੍໿͕ҟͳΕ͹ಋ͔ΕΔղ๏΋
    ҟͳΔͱ͍͏Θ͚Ͱ͢ɻ
    ΞϯνύλʔϯΛ༻͍ͯ΋ྑ͍৔߹
    http://yojik.hatenablog.jp/entry/2013/02/13/235729
    

    View Slide

24. ໊લ
    ໨త
    Ξϯνύλʔϯ
    Ξϯνύλʔϯͷݟ͚ͭํ
    ΞϯνύλʔϯΛ༻͍ͯ΋ྑ͍৔߹
    ղܾࡦ
    ࣮ྫφΠʔϒπϦʔ ૉ๿ͳ໦
    
    

    View Slide

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
    
    

    View Slide

26. ೖΕࢠू߹
    /FTUFE4FU
    ดแςʔϒϧ
    $MPTVSF5BCMF
    ղܾࡦ୅ସπϦʔϞσϧΛ࢖༻͢Δ
    

    View Slide

27. ઃܭ
    ςʔϒ
    ϧ਺
    ࢠ΁ͷΫΤ
    Ϧ࣮ߦ
    πϦʔ΁ͷ
    ΫΤϦ࣮ߦ
    ૠೖ ࡟আ
    ࢀর੔߹ੑ
    ҡ࣋
    ྡ઀Ϧετ 1 ؆୯ ೉͍͠ ؆୯ ؆୯ Մೳ
    ࠶ؼΫΤϦ 1 ؆୯ ؆୯ ؆୯ ؆୯ Մೳ
    ܦ࿏ྻڍ 1 ؆୯ ؆୯ ؆୯ ؆୯ ෆՄ
    ೖΕࢠू߹ 1 ೉͍͠ ೉͍͠ ೉͍͠ ೉͍͠ ෆՄ
    ดแςʔϒϧ 2 ؆୯ ؆୯ ؆୯ ؆୯ Մೳ
    ղܾࡦ୅ସπϦʔϞσϧΛ࢖༻͢Δ
    ղܾࡦͷൺֱද
    

    View Slide

28. Agenda
    Ξϯνύλʔϯܗࣜͷ͓͞Β͍
    ೥ʹ͓͚Δຊॻʹ͍ͭͯ
    Ξϯνύλʔϯͷ͓ข্͖͛
    ຊॻͷҙٛʹ͍ͭͯ
    
    

    View Slide

29. ݪॻʹ͍ͭͯ
    IUUQTQSBHQSPHDPNCPPLCLTRMBTRMBOUJQBUUFSOT
    

    View Slide

30. ؂༁ʹ͍ͭͯ
    親子
    

    View Slide

31. IUUQTXXXZPVUVCFDPNXBUDI W7,(Q$G#X
    ZPVUVCFʮ࿨ా୎ਓº࿨ాলೋσʔλϕʔεΛ८Δੈ୅ؒಆ૪ʯ
    

    View Slide

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.
    * 値が同じ列に格納されていること（例: @{Bugs.date_reported}）
    #@# Values can be compared to one another so that GROUP BY can accurately
    group dates with equal values together.
    * 値を比較できること（@{GROUP BY}で同じ値の日付を正確にグループ化で
    きるように）
    ݪߘ͸3F7*&8Ͱࣥච
    

    View Slide

33. Ҏલͷ42-ຊͷঢ়گ
    ʻʻʻӽ͑ΒΕͳ͍นʻʻʻ
    ೖ໳ॻ ্ڃऀ޲͚
    

    View Slide

34. ݱࡏͰ͸
    ೖ໳ॻ ্ڃऀ޲͚
    தڃऀ޲͚
    

    View Slide

35. View Slide

36. ࠷ۙྑͦ͞͏ͳຊ͕૿͍͑ͯΔ݅
    

    View Slide

37. Agenda
    Ξϯνύλʔϯܗࣜͷ͓͞Β͍
    ೥ʹ͓͚Δຊॻʹ͍ͭͯ
    Ξϯνύλʔϯͷ͓ข্͖͛
    ຊॻͷҙٛʹ͍ͭͯ
    
    

    View Slide

38. ݪஶʰ42-"OUJQBUUFSOTʱͷग़൛͸೥
    Ͱ͢ɻग़൛࣌఺Ͱ͸Ξϯνύλʔϯͩͬͨ΋ͷ
    ΋ɺ೥ݱࡏɺ͓Αͼ༧૝Ͱ͖Δকདྷʹ͓
    ͍ͯ͸ΞϯνύλʔϯͰ͸ͳ͘ͳΓͦ͏ͳ΋ͷ
    ΍ɺҾ͖ଓ͖ΞϯνύλʔϯͰ͋Δ΋ͷͷɺղ
    ܾࡦ͕มΘ͖ͬͯͨ΋ͷ΋͋Γ·͢ɻ͔͜͜Β
    ͸ɺຊॻͷΞϯνύλʔϯͷ͍͔ͭ͘Λʮ͓ข
    ্͖͛ʯ͍͖ͯ͠·͢ɻ
    Ξϯνύλʔϯͷ͓ข্͖͛
    

    View Slide

39. δΣΠ΢ΥʔΫʢ৴߸ແࢹʣ
    φΠʔϒπϦʔʢૉ๿ͳ໦ʣ
    *%ϦΫϫΠΞυʢͱΓ͋͑ͣ*%ʣ
    ΩʔϨεΤϯτϦʢ֎෦Ωʔݏ͍ʣ
    &"7ʢΤϯςΟςΟɾΞτϦϏϡʔτɾόϦϡʔʣ
    ϙϦϞʔϑΟοΫؔ࿈
    ϚϧνΧϥϜΞτϦϏϡʔτʢෳ਺ྻଐੑʣ
    ϝλσʔλτϦϒϧʢϝλσʔλେ૿৩ʣ
    ୈ෦࿦ཧઃܭͷΞϯνύλʔϯ
    

    View Slide

40. CREATE TABLE Comments (
    comment_id SERIAL PRIMARY KEY,
    parent_id BIGINT UNSIGNED,
    comment TEXT NOT NULL,
    );
    親idが入る
    φΠʔϒπϦʔ ૉ๿ͳ໦
    
    

    View Slide

41. ೖΕࢠू߹
    /FTUFE4FU
    ดแςʔϒϧ
    $MPTVSF5BCMF
    ೥୅ସπϦʔϞσϧΛ࢖༻͢Δ
    

    View Slide

42. IUUQTZBLTUDPNKBQPTUT
    ೥
    .Z42-ʹ΋ ΍ͬͱ
    $5&͕དྷΔ
    

    View Slide

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”にしてしまう
    *%ϦΫϫΠΞυ ͱΓ͋͑ͣ*%
    
    

    View Slide

44. ೥ঢ়گʹԠͯ͡ద੾ʹௐ੔͢Δ
    Θ͔Γ΍͍͢ྻ໊(id より bug_id)
    SELECT * FROM Bugs INNER JOIN BugsProducts USING (bug_id);
    ن໿ʹറΒΕͳ͍
    ࣗવΩʔͱෳ߹Ωʔͷ׆༻
    同じ名前なら
    USINGが使える
    考えた結果の“id”ならそれで良し
    

    View Slide

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)
    );
    機械的な交差テーブルではなく
    関係性の発生ととらえる
    ݱࡏ୯ͳΔަࠩςʔϒϧͰ͸ͳ͘ɺؔ܎ੑͷൃੜ͔΋͠Εͳ͍
    

    View Slide

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,名前,値
    ……
    動的な項目を
    格納したい
    

    View Slide

47. ೥αϒλΠϓͷϞσϦϯάΛߦ͏
    γϯάϧςʔϒϧܧঝ
    ۩৅ςʔϒϧܧঝ
    Ϋϥεςʔϒϧܧঝ
    γϦΞϥΠζ-0#
    PofEAA 読むべし
    

    View Slide

48. IUUQNPEFSOTRMDPNCMPHXIBUTOFXJOTRM
    কདྷ+40/ؔ਺܈ 42-
    ͕དྷΔ
    EAV は引き続きアンチパターン
    解決策に JSON 関数群が加わる
    

    View Slide

49. ϥ΢ϯσΟϯάΤϥʔʢؙΊޡࠩʣ
    αʔςΟϫϯϑϨʔόʔʢͷϑϨʔόʔʣ
    ϑΝϯτϜϑΝΠϧʢݬͷϑΝΠϧʣ
    ΠϯσοΫεγϣοτΨϯʢҋӢΠϯσοΫεʣ
    ୈ෦෺ཧઃܭͷΞϯνύλʔϯ
    

    View Slide

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)
    );
    ϑΝϯτϜϑΝΠϧʢݬͷϑΝΠϧʣ
    物理ファイルの使用を必須と思い込む
    

    View Slide

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)
    );
    整合性、トランザクション、ロッ
    ク、権限管理がメリット
    

    View Slide

52. IUUQCBTIDIBUFOBCMPHDPNFOUSZ
    ೥"NB[PO4ͷํ͕৴པͰ͖Δ
    

    View Slide

53. ϑΟΞɾΦϒɾδɾΞϯϊ΢ϯʢڪාͷVOLOPXOʣ
    ΞϯϏΪϡΞεάϧʔϓʢᐆດͳάϧʔϓʣ
    ϥϯμϜηϨΫγϣϯ
    ϓΞϚϯζɾαʔνΤϯδϯʢශऀͷαʔνΤϯδϯʣ
    εύήοςΟΫΤϦ
    ΠϯϓϦγοτΧϥϜʢ҉໧ͷྻʣ
    ୈ෦ΫΤϦͷΞϯνύλʔϯ
    

    View Slide

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が
    返るとは限らない
    

    View Slide

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 の使用
    などの手段がある
    (例は導出テーブル)
    

    View Slide

56. IUUQXXXTPOHNVKQSJKJFOUSZLBNJQPUSBEJUJPOBMIUNM
    ೥LBNJQP53"%*5*0/"-Λ࢖͏
    

    View Slide

57. IUUQNPEFSOTRMDPNCMPHXIBUTOFXJOTRM
    কདྷ-JTUBHHؔ਺ 42-
    ΋࢖͑Δ
    

    View Slide

58. ϦʔμϒϧύεϫʔυʢಡΈऔΓՄೳύεϫʔυʣ
    42-ΠϯδΣΫγϣϯ
    γϡʔυΩʔɾχʔτϑϦʔΫʢٙࣅΩʔܿบ঱ʣ
    γʔɾϊʔɾΤϏϧʢष͍΋ͷʹ֖ʣ
    σΟϓϩϚςΟοΫɾΠϛϡχςΟʢ֎ަಛݖʣ
    ϚδοΫϏʔϯζʢຐ๏ͷ౾ʣ
    ࠭ͷ৓
    ୈ෦ΞϓϦέʔγϣϯͷΞϯνύλʔϯ
    

    View Slide

59. $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-ΠϯδΣΫγϣϯ
    未検証の入力をクエリにつなげて実行してしまう
    

    View Slide

60. ೥୭΋৴༻ͯ͠͸ͳΒͳ͍
    $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.ホワイトリスト
    

    View Slide

61. $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年にエスケープ/サニタイズはダメ
    プリペアドステートメントと
    ホワイトリストプログラミングを徹底
    ೥ʹΤεέʔϓ͕࠷ॳʹདྷͪΌμϝͩʜ
    

    View Slide

62. ͱ͜ΖͰ࿦ཧ࡟আͬͯͲ͏ͳͷ
    IUUQTXXXTMJEFTIBSFOFUU@XBEBSPOTBLVDBTVBM
    

    View Slide

63. Agenda
    Ξϯνύλʔϯܗࣜͷ͓͞Β͍
    ೥ʹ͓͚Δຊॻʹ͍ͭͯ
    Ξϯνύλʔϯͷ͓ข্͖͛
    ຊॻͷҙٛʹ͍ͭͯ
    
    

    View Slide

64. ࣾ಺ಡॻձʹ޲͍ͨຊͰ͢
    ֤ষ͕ಠཱ͍ͯ͠Δ Ͳ͔͜ΒͰ΋ࢀՃͰ͖Δ
    
    ࣾ಺ͳΒͰ͸ͷࣦഊͷڞ༗͕Ͱ͖Δ
    

    View Slide

65. ͜ͷຊͷૉ੖Β͍͠ͱ͜Ζ͸ɺ
    Α͘ݟΔʮѱ͍ʯํ๏Λʮѱ
    ͍͜ͱʯͱͯ͠·ͱΊͯ͘Ε
    ͨ͜ͱͰ͢ɻ
    http://bleis-tift.hatenablog.com/entry/2013/02/14/SQLΞϯνύλʔϯ
    ʮѱ͍͜ͱʯΛ·ͱΊͨҙٛ
    

    View Slide

66. ʮ͋ʔ͸͍͸͍ΠϯσοΫεγϣοτΨϯԵʯ
    &YQMBJOͷ݁Ռ΋ݟͳ͍ͰΠϯσοΫεషΓ·͘
    Δౕ͍ΔΑͶʔʔʔ
    ʮϚϧνΧϥϜΞτϦϏϡʔτͱ͔೥
    લʹ௨ͬͨΘʔʯ
    http://yoshiori.github.com/blog/2013/02/10/sql-antipatterns/
    Ξϯνύλʔϯ໊Ͱٞ࿦Ͱ͖ΔΑ͏ʹͳΔ
    

    View Slide

67. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠
    ͜ͷ໰୊ʂ
    ਐݚθϛʰ42-Ξϯνύλʔϯʱ
    Ͱ΍ͬͨͱ͜Ζͩʂ
    http://yojik.hatenablog.jp/entry/2013/02/13/235729
    

    View Slide