State of SQL Antipatterns in 2017

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

࿨ా୎ਓ JEUXBEB !U@XBEB HJUIVCUXBEB

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

͓͔͛͞·Ͱ޷ධՁΛ௖͍͓ͯΓ·͢

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ղܾࡦ୅ସπϦʔϞσϧΛ࢖༻͢Δ 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

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

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

ݪॻʹ͍ͭͯ IUUQTQSBHQSPHDPNCPPLCLTRMBTRMBOUJQBUUFSOT

؂༁ʹ͍ͭͯ 親子

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

#@# 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Ͱࣥච

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

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

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

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

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

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

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

IUUQTZBLTUDPNKBQPTUT ೥ .Z42-ʹ΋ ΍ͬͱ $5&͕དྷΔ

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

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

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

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

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

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

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

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

೥ඞཁʹԠͯ͡#-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) ); 整合性、トランザクション、ロック、権限管理がメリット

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

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

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

೥ᐆດͰͳ͍ྻΛ࢖͏ 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 の使用などの手段がある (例は導出テーブル)

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

IUUQNPEFSOTRMDPNCMPHXIBUTOFXJOTRM কདྷ-JTUBHHؔ਺ 42- ΋࢖͑Δ

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

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

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

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

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

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

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

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

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

State of SQL Antipatterns in 2017

State of SQL Antipatterns in 2017

More Decks by Takuto Wada

Other Decks in Programming

Featured

Transcript