Fukuoka.go#13+Okayama.go https://fukuokago.connpass.com/event/112073/
ଜ໋ / Pepabo R&D Institute, GMO Pepabo, Inc.2019.02.12 Fukuoka.go#13ෆਖ਼ΫΤϦΛݕ͢ΔsqdΛ࡞ͬͨ
View Slide
2ΤϯδχΞଜ໋!,PNFJ(.0ϖύϘגࣜձࣾɹϖύϘݚڀॴ
ෆਖ਼ΫΤϦΛݕ͢Δsqd
ෆਖ਼ΫΤϦͬͯԿʁԿ͕ͳͷ͔ʁ
• ෆਖ਼ΫΤϦͱʁ• WebΞϓϦέʔγϣϯͷ੬ऑੑΛར༻ͯ͠σʔλϕʔεʹൃߦ͞ΕΔΫΤϦ• SQLΠϯδΣΫγϣϯʹΑͬͯൃߦ͞ΕΔΫΤϦͳͲ• ෆਖ਼ΫΤϦԿΛҾ͖ى͜͢ʁ• σʔλϕʔε্ͷػີใͷ࿙Ӯ• σʔλϕʔεͷվ᜵ɾফڈ5ෆਖ਼ΫΤϦʹ͍ͭͯෆਖ਼ΫΤϦ͕ൃߦ͞Εͨ͜ͱΛݕ͍ͨ͠
• sqdɼϗϫΠτϦετϕʔεͰɼΫΤϦϩά͔Βෆਖ਼ΫΤϦΛݕ6sqdTREIUUQTHJUIVCDPN,PNFJTRE8FCΞϓϦέʔγϣϯͷΫΤϦϩάw ΫΤϦ"w ΫΤϦ#w ΫΤϦ$TREϗϫΠτϦετΫΤϦΛϗϫΠτϦετͱর߹ϗϫΠτϦετʹͳ͔ͬͨΫΤϦΛग़ྗw ΫΤϦ"w ΫΤϦ#ΫΤϦ$
7sqdʹΑΔෆਖ਼ΫΤϦݕ$ cat whitelistSELECT * FROM users WHERE id = ?$ cat query.log | jq -r .querySELECT * FROM users WHERE id = 1SELECT * FROM users WHERE id = 2SELECT * FROM usersDROP TABLE usersϗϫΠτϦετݕରͷΫΤϦ܈ϦςϥϧϓϨʔεϗϧμʔʹ͢Δ$ cat query.log | jq -r .query | sqd -W whitelistSELECT * FROM usersDROP TABLE usersݕ͞ΕͨΫΤϦϗϫΠτϦετϑΝΠϧΛࢦఆݕରͷΫΤϦ܈Λೖྗ
sqdϗϫΠτϦετΛͪΌΜͱఆٛͰ͖Εɼෆਖ਼ΫΤϦΛݕͰ͖Δ
ͰɼϗϫΠτϦετ࡞ΔͷେมͳͷͰʁ
• ߴ͍ਫ਼Ͱෆਖ਼ΫΤϦΛݕ͢ΔʹɼWebΞϓϦέʔγϣϯ͕ൃߦ͠ಘΔΫΤϦΛશͯϗϫΠτϦετʹఆٛ͠ͳ͚ΕͳΒͳ͍• WebΞϓϦέʔγϣϯ͕ൃߦ͠ಘΔΫΤϦେ• WebΞϓϦέʔγϣϯ͕վम͞Εͨ߹ɼΫΤϦ͕มԽ͢ΔՄೳੑ͋Γ• ORMͬͯͨΒɼࣗͰSQLΛॻ͘͜ͱ͕গͳ͍10ϗϫΠτϦετ࡞ͷ͠͞
11IUUQTTQFBLFSEFDLDPNLPNFJXFCBQVSJLFTJZPOUFTVUPXPZPOHJUBTRMLVFSJGBMTFIPXBJUPSJTVUP[JEPOH[VPDIFOHTIPVGBFCDGCDFBFBEDF
• ෆਖ਼ΫΤϦʹΑͬͯσʔλϕʔε্ͷػີใͷ࿙Ӯɼվ᜵ɼফڈ͕ൃੜ• ϗϫΠτϦετϕʔεͰෆਖ਼ΫΤϦͷݕΛߦ͏sqdΛհ• sqdͰɼϗϫΠτϦετ࡞͕ॏཁ͚ͩͲ͍͠• ϗϫΠτϦετ࡞ʹؔͯ͠ɼจݚڀձͰͷൃදࢿྉΛ͝ཡ͍ͩ͘͞12·ͱΊจɿIUUQTSBOEQFQBCPDPNQBQFSTJPUTLPNFJQEG