Share
Embed
State of SQL Antipatterns in 2017
2017/07/10
SQLアンチパターンNight Part2
https://connpass.com/event/59946/
Takuto WADA PRO
July 10, 2017
Transcript
67 slides
-
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階層目 ΞϯνύλʔϯʹΑΓى͜Δ͜ͱ ૉ͗͢Δނʹ Ξϯνύλʔϯ -
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 Λͬͯ࠶ؼΫΤϦΛॻ͚Δ߹ -
ղܾࡦସπϦʔϞσϧΛ༻͢Δ 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 -
ઃܭ ςʔϒ ϧ ࢠͷΫΤ Ϧ࣮ߦ πϦʔͷ ΫΤϦ࣮ߦ ૠೖ আ ࢀর߹ੑ
ҡ࣋ ྡϦετ 1 ؆୯ ͍͠ ؆୯ ؆୯ Մೳ ࠶ؼΫΤϦ 1 ؆୯ ؆୯ ؆୯ ؆୯ Մೳ ܦ࿏ྻڍ 1 ؆୯ ؆୯ ؆୯ ؆୯ ෆՄ ೖΕࢠू߹ 1 ͍͠ ͍͠ ͍͠ ͍͠ ෆՄ ดแςʔϒϧ 2 ؆୯ ؆୯ ؆୯ ؆୯ Մೳ ղܾࡦସπϦʔϞσϧΛ༻͢Δ ղܾࡦͷൺֱද -
#@# 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Ͱࣥච -
CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT
UNSIGNED, comment TEXT NOT NULL, ); 親idが入る φΠʔϒπϦʔ ૉͳ -
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,名前,値 …… 動的な項目を 格納したい -
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) ); 整合性、トランザクション、ロッ ク、権限管理がメリット -
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 の使用 などの手段がある (例は導出テーブル) -
<?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年にエスケープ/サニタイズはダメ プリペアドステートメントと ホワイトリストプログラミングを徹底 ʹΤεέʔϓ͕࠷ॳʹདྷͪΌμϝͩʜ