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

Ec3fcf78fa2ad21dcc48478df80e0dc4?s=47 Komei Nomura
February 12, 2019

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

Ec3fcf78fa2ad21dcc48478df80e0dc4?s=128

Komei Nomura

February 12, 2019
Tweet

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 TREIUUQTHJUIVCDPN,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
  13. None