$30 off During Our Annual Pro Sale. View Details »

State of SQL Antipatterns in 2017

State of SQL Antipatterns in 2017

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

Takuto Wada
PRO

July 10, 2017
Tweet

More Decks by Takuto Wada

Other Decks in Programming

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