不正クエリを検知するsqdを作った

Fukuoka.go#13+Okayama.go
https://fukuokago.connpass.com/event/112073/

Transcript

1. ໺ଜ޸໋ / Pepabo R&D Institute, GMO Pepabo, Inc. 2019.02.12 Fukuoka.go#13 ෆਖ਼ΫΤϦΛݕ஌͢ΔsqdΛ࡞ͬͨ
2. 2 ΤϯδχΞ ໺ଜ޸໋
   
   !,PNFJ (.0ϖύϘגࣜձࣾɹϖύϘݚڀॴ
3. ෆਖ਼ΫΤϦΛݕ஌͢Δsqd
4. ෆਖ਼ΫΤϦͬͯԿʁԿ͕໰୊ͳͷ͔ʁ
5. • ෆਖ਼ΫΤϦͱ͸ʁ • WebΞϓϦέʔγϣϯͷ੬ऑੑΛར༻ͯ͠σʔλϕʔεʹൃߦ͞ΕΔΫΤϦ • SQLΠϯδΣΫγϣϯʹΑͬͯൃߦ͞ΕΔΫΤϦͳͲ • ෆਖ਼ΫΤϦ͸ԿΛҾ͖ى͜͢ʁ • σʔλϕʔε্ͷػີ৘ใͷ࿙Ӯ • σʔλϕʔεͷվ᜵ɾফڈ 5 ෆਖ਼ΫΤϦʹ͍ͭͯ ෆਖ਼ΫΤϦ͕ൃߦ͞Εͨ͜ͱΛݕ஌͍ͨ͠
6. • sqd͸ɼϗϫΠτϦετϕʔεͰɼΫΤϦϩά͔Βෆਖ਼ΫΤϦΛݕ஌ 6 sqd TRE
   
   IUUQTHJUIVCDPN,PNFJTRE 8FCΞϓϦέʔγϣϯͷ
   ΫΤϦϩά w ΫΤϦ"
   w ΫΤϦ#
   w ΫΤϦ$ TRE ϗϫΠτϦετ ΫΤϦΛϗϫΠτϦετͱর߹ ϗϫΠτϦετʹͳ͔ͬͨ
   ΫΤϦΛग़ྗ w ΫΤϦ"
   w ΫΤϦ# ΫΤϦ$
7. 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 ݕ஌͞ΕͨΫΤϦ ϗϫΠτϦετϑΝΠϧΛࢦఆ ݕ஌ର৅ͷΫΤϦ܈Λೖྗ
8. sqd͸ϗϫΠτϦετΛͪΌΜͱఆٛͰ͖Ε ͹ɼෆਖ਼ΫΤϦΛݕ஌Ͱ͖Δ
9. Ͱ΋ɼϗϫΠτϦετ࡞ΔͷେมͳͷͰ͸ʁ
10. • ߴ͍ਫ਼౓Ͱෆਖ਼ΫΤϦΛݕ஌͢Δʹ͸ɼWebΞϓϦέʔγϣϯ͕ൃߦ͠ಘΔΫ ΤϦΛશͯϗϫΠτϦετʹఆٛ͠ͳ͚Ε͹ͳΒͳ͍ • WebΞϓϦέʔγϣϯ͕ൃߦ͠ಘΔΫΤϦ͸๲େ • WebΞϓϦέʔγϣϯ͕վम͞Εͨ৔߹ɼΫΤϦ͕มԽ͢ΔՄೳੑ͋Γ • ORM࢖ͬͯͨΒɼࣗ෼ͰSQLΛॻ͘͜ͱ͕গͳ͍ 10 ϗϫΠτϦετ࡞੒ͷ೉͠͞
11. 11 IUUQTTQFBLFSEFDLDPNLPNFJXFCBQVSJLFTJZPOUFTVUPXPZPOHJUBTRMLVFSJGBMTFIPXBJUPSJTVUP[JEPOH [VPDIFOHTIPVGBFCDGCDFBFBEDF
12. • ෆਖ਼ΫΤϦʹΑͬͯσʔλϕʔε্ͷػີ৘ใͷ࿙Ӯɼվ᜵ɼফڈ͕ൃੜ • ϗϫΠτϦετϕʔεͰෆਖ਼ΫΤϦͷݕ஌Λߦ͏sqdΛ঺հ • sqdͰ͸ɼϗϫΠτϦετ࡞੒͕ॏཁ͚ͩͲ೉͍͠ • ϗϫΠτϦετ࡞੒ʹؔͯ͠͸ɼ࿦จ΍ݚڀձͰͷൃදࢿྉΛ͝ཡ͍ͩ͘͞ 12 ·ͱΊ ࿦จɿIUUQTSBOEQFQBCPDPNQBQFSTJPUTLPNFJQEG