໺ଜ޸໋ / Pepabo R&D Institute, GMO Pepabo, Inc. 2019.02.12 Fukuoka.go#13 ෆਖ਼ΫΤϦΛݕ஌͢ΔsqdΛ࡞ͬͨ

2 ΤϯδχΞ ໺ଜ޸໋!,PNFJ (.0ϖύϘגࣜձࣾɹϖύϘݚڀॴ

ෆਖ਼ΫΤϦΛݕ஌͢Δsqd

ෆਖ਼ΫΤϦͬͯԿʁԿ͕໰୊ͳͷ͔ʁ

• ෆਖ਼ΫΤϦͱ͸ʁ • WebΞϓϦέʔγϣϯͷ੬ऑੑΛར༻ͯ͠σʔλϕʔεʹൃߦ͞ΕΔΫΤϦ • SQLΠϯδΣΫγϣϯʹΑͬͯൃߦ͞ΕΔΫΤϦͳͲ • ෆਖ਼ΫΤϦ͸ԿΛҾ͖ى͜͢ʁ • σʔλϕʔε্ͷػີ৘ใͷ࿙Ӯ • σʔλϕʔεͷվ᜵ɾফڈ 5 ෆਖ਼ΫΤϦʹ͍ͭͯ ෆਖ਼ΫΤϦ͕ൃߦ͞Εͨ͜ͱΛݕ஌͍ͨ͠

• sqd͸ɼϗϫΠτϦετϕʔεͰɼΫΤϦϩά͔Βෆਖ਼ΫΤϦΛݕ஌ 6 sqd TREIUUQTHJUIVCDPN,PNFJTRE 8FCΞϓϦέʔγϣϯͷ ΫΤϦϩά w ΫΤϦ" w ΫΤϦ# w ΫΤϦ$ TRE ϗϫΠτϦετ ΫΤϦΛϗϫΠτϦετͱর߹ ϗϫΠτϦετʹͳ͔ͬͨ ΫΤϦΛग़ྗ w ΫΤϦ" w ΫΤϦ# ΫΤϦ$

7 sqdʹΑΔෆਖ਼ΫΤϦݕ஌ $ cat whitelist SELECT * FROM users WHERE id = ? $ cat query.log | jq -r .query SELECT * FROM users WHERE id = 1 SELECT * FROM users WHERE id = 2 SELECT * FROM users DROP TABLE users ϗϫΠτϦετ ݕ஌ର৅ͷΫΤϦ܈ Ϧςϥϧ஋͸ϓϨʔεϗϧμʔʹ͢Δ $ cat query.log | jq -r .query | sqd -W whitelist SELECT * FROM users DROP TABLE users ݕ஌͞ΕͨΫΤϦ ϗϫΠτϦετϑΝΠϧΛࢦఆ ݕ஌ର৅ͷΫΤϦ܈Λೖྗ

sqd͸ϗϫΠτϦετΛͪΌΜͱఆٛͰ͖Ε ͹ɼෆਖ਼ΫΤϦΛݕ஌Ͱ͖Δ

Ͱ΋ɼϗϫΠτϦετ࡞ΔͷେมͳͷͰ͸ʁ

• ߴ͍ਫ਼౓Ͱෆਖ਼ΫΤϦΛݕ஌͢Δʹ͸ɼWebΞϓϦέʔγϣϯ͕ൃߦ͠ಘΔΫ ΤϦΛશͯϗϫΠτϦετʹఆٛ͠ͳ͚Ε͹ͳΒͳ͍ • WebΞϓϦέʔγϣϯ͕ൃߦ͠ಘΔΫΤϦ͸๲େ • WebΞϓϦέʔγϣϯ͕վम͞Εͨ৔߹ɼΫΤϦ͕มԽ͢ΔՄೳੑ͋Γ • ORM࢖ͬͯͨΒɼࣗ෼ͰSQLΛॻ͘͜ͱ͕গͳ͍ 10 ϗϫΠτϦετ࡞੒ͷ೉͠͞

11 IUUQTTQFBLFSEFDLDPNLPNFJXFCBQVSJLFTJZPOUFTVUPXPZPOHJUBTRMLVFSJGBMTFIPXBJUPSJTVUP[JEPOH [VPDIFOHTIPVGBFCDGCDFBFBEDF

• ෆਖ਼ΫΤϦʹΑͬͯσʔλϕʔε্ͷػີ৘ใͷ࿙Ӯɼվ᜵ɼফڈ͕ൃੜ • ϗϫΠτϦετϕʔεͰෆਖ਼ΫΤϦͷݕ஌Λߦ͏sqdΛ঺հ • sqdͰ͸ɼϗϫΠτϦετ࡞੒͕ॏཁ͚ͩͲ೉͍͠ • ϗϫΠτϦετ࡞੒ʹؔͯ͠͸ɼ࿦จ΍ݚڀձͰͷൃදࢿྉΛ͝ཡ͍ͩ͘͞ 12 ·ͱΊ ࿦จɿIUUQTSBOEQFQBCPDPNQBQFSTJPUTLPNFJQEG

No content