不正クエリを検知する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Λ࡞ͬͨ
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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
   ݕ஌͞ΕͨΫΤϦ
   ϗϫΠτϦετϑΝΠϧΛࢦఆ
   ݕ஌ର৅ͷΫΤϦ܈Λೖྗ
   

   View Slide

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

   View Slide

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

   View Slide

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

    View Slide

11. 11
    IUUQTTQFBLFSEFDLDPNLPNFJXFCBQVSJLFTJZPOUFTVUPXPZPOHJUBTRMLVFSJGBMTFIPXBJUPSJTVUP[JEPOH
    [VPDIFOHTIPVGBFCDGCDFBFBEDF
    

    View Slide

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

    View Slide

13. View Slide