Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Komei Nomura
February 12, 2019

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

Komei Nomura

February 12, 2019
Tweet

More Decks by Komei Nomura

Other Decks in Technology

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