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

SQL Injection Basics

SQL Injection Basics

社内勉強会用資料です。

Kentaro Kuribayashi

October 02, 2012
Tweet

More Decks by Kentaro Kuribayashi

Other Decks in Technology

Transcript

  1. 42-ΠϯδΣΫγϣϯͷ
    ݪཧɾݪଇ
    @kentaro
    ܀ྛ݈ଠ࿠
    paperboy&co.
    http://www.flickr.com/photos/vissago/3270115155/

    View Slide

  2. @kentaro
    ΤϯδχΞ
    1FSMFS ϧϏʔετ
    ʮ΃ͪ΃ʯʮ΃ͪ
    ͺʔʯ૑࢝ऀ /FX

    ܀ྛ݈ଠ࿠
    paperboy&co.

    View Slide

  3. ‣42-ΠϯδΣΫγϣϯࣗମͷৄࡉ͸
    ޙड़ͷࢀߟจݙΛࢀরͷ͜ͱ
    ‣͜͜Ͱ͸42-ΠϯδΣΫγϣϯΛ
    ࢝Ίͱ͢Δɺ޿͘8FCηΩϡϦςΟ
    ʹؔ͢Δݪཧɾݪଇʹ͍ͭͯड़΂·͢
    લఏ

    View Slide

  4. 42-ΠϯδΣΫγϣϯ͸ɺ42-ͷݺͼग़͠ํʹෆඋ͕͋Δ৔߹ʹൃੜ͢Δ੬
    ऑੑͰ͢ɻΞϓϦέʔγϣϯʹ42-ΠϯδΣΫγϣϯ੬ऑੑ͕͋Δ৔߹ɺҎԼ
    ͷΑ͏ͳӨڹΛड͚ΔՄೳੑ͕͋Γ·͢ɻ͢΂ͯɺ߈ܸऀ͕ೳಈతʹ ར༻ऀͷ
    ؔ༩ͳ͠Ͱ
    αʔόʔΛ߈ܸͰ͖·͢ɻ
    ‣σʔλϕʔε಺ͷ͢΂ͯͷ৘ใ͕֎෦͔Β౪·ΕΔ
    ‣σʔλϕʔεͷ಺༰͕ॻ͖׵͑ΒΕΔ
    ‣ೝূΛճආ͞ΕΔ *%ͱύεϫʔυΛ༻͍ͣʹϩάΠϯ͞ΕΔ

    ‣ͦͷଞɺσʔλϕʔεαʔόʔ্ͷϑΝΠϧͷಡΈग़͠ɺॻ͖ࠐΈɺϓϩά
    ϥϜͷ࣮ߦͳͲΛߦΘΕΔ
    42-ΠϯδΣΫγϣϯ
    ʰମܥతʹֶͿ҆શͳ8FCΞϓϦέʔγϣϯͷ࡞ΓํʱQ
    ҎԼʰಙؙຊʱ

    View Slide

  5. $sql = "SELECT * FROM user WHERE uid = " . $uid . " AND
    provider = '" . $provider . "'";
    ΠϯδΣΫγϣϯͷྫ
    SELECT * FROM user WHERE uid = 9999 AND provider = '';
    DELETE FROM user; --
    ΋͠$provider͕͜͏ͩͬͨΒ
    '; DELETE FROM user; --
    ʘ ?P?
    ʗ

    View Slide

  6. ΋͠੬ऑੑ͕͋ͬͨΒ
    ৘ใ͕ྲྀग़ͨ͠৔߹ʹ͸اۀଘଓͷةػʹͭͳ͕Γ͔Ͷͳ͍ɻ৘
    ใॲཧਪਐػߏʢ*1"ʣ͸42-ΠϯδΣΫγϣϯʹΑΔඃ֐͔Β
    ͷ෮چίετ͸ԯԁΛ௒͑͏Δͱ͓ͯ͠Γɺ࣮ࡍʹα΢ϯυϋ
    ΢εͷࣄྫͰ͸ิঈͷΈͰ΋໊ʹԁ૬౰ͷظݶ
    ෇͖ΫϨδοτΛෛ୲͍ͯ͠Δɻิঈͷ΄͔ʹ΋ઐ໳ՈʹΑΔௐ
    ࠪɺγεςϜͷೖΕସ͑ɺސ٬ରԠɺҰ࣌ด࠯ʹΑΔӦۀػձͷ
    ҳࣦɺ෩ධඃ֐ͱ͍ͬͨෛ୲͕͋Γɺ42-ΠϯδΣΫγϣϯ΋ؚ
    ΊηΩϡϦςΟରࡦ͸ݫີʹߦ͏΂͖Ͱ͋Δɻ
    IUUQKBXJLJQFEJBPSHXJLJ42-ΠϯδΣΫγϣϯ

    View Slide

  7. ੬ऑੑͷछྨ
    ʰಙؙຊʱQ

    View Slide

  8. ΠϯδΣΫγϣϯܥ
    ʰಙؙຊʱQ
    42-ΠϯδΣΫγϣϯ੬ऑੑ͕ൃੜ͢ΔݪҼ͸ɺ΋ͱ΋ͱʮσʔλʯΛ૝ఆ͠
    ͍ͯΔͱ͜ΖʹγϯάϧΫΥʔτʮʯΛ࢖ͬͯσʔλ෦෼ΛऴΘΒͤɺ42-
    จͷߏ଄ΛมԽͤͨ͞ͱ͜Ζʹ͋Γ·͢ɻ͜ͷݪཧ͸ଞͷΠϯδΣΫγϣϯܥ
    ੬ऑੑͰ΋ಉ͡Ͱ͢ɻσʔλͷதʹҾ༻ූ΍σϦϛλͳͲʮσʔλͷऴ୺ʯΛ
    ࣔ͢ϚʔΫΛࠞೖͤͯ͞ɺͦͷޙͷจࣈྻͷߏ଄ΛมԽͤ͞ΔͷͰ͢ɻ

    View Slide

  9. ݪཧ
    ‣944΋42-ΠϯδΣΫγϣϯ΋ɺ
    ى͜Δݪཧ͸ಉ͡
    ‣)5.-ʹͤΑ42-ʹͤΑɺ8FCΞ
    ϓϦ಺ͷม਺ͱ͸·ͬͨ͘ผͷϧʔϧ
    ʹΑͬͯߏங͞ΕΔจࣈྻ
    ‣ͦΕΒͷੈքͷϧʔϧΛཚ͞ͳ͍Α
    ͏ʹ஫ҙ͢Δඞཁ͕͋Δͱ͍͏఺Ͱಉ
    ͡

    View Slide

  10. ݩʑɺ)5.-Λग़ྗ͢Δͱ͖͸ɺͦͷग़ྗશମʹରͯ͠ʮʯʮʯʮʯͷ
    Τεέʔϓॲཧͷݕ౼͕ཁٻ͞Ε͍ͯΔͷͰ͋ͬͯɺ$(*ೖྗʹґଘ͍ͯ͠Δ
    ͔Ͳ͏͔͸ແؔ܎Ͱ͋Δɻͦ͜ͷߟ͑ํʹʮແಟԽʯͩͷʮແ֐Խʯͩͷʮফ
    ಟʯͩͷʮαχλΠζʯͩͷʮαχλΠδϯάʯͩͷͱ͍͏ʢ࠷ۙྲྀߦͷʣൃ
    ૝͸ग़ͯ͜ͳ͍ɻ
    ʮαχλΠζݴ͏ͳΩϟϯϖʔϯʯͱ͸Կ͔
    http://takagi-hiromitsu.jp/diary/20051227.html#p02
    ݪཧ

    View Slide

  11. ೖྗ஋ͷݕূʁ
    ‣όϦσʔγϣϯ͸ΞϓϦతʹ͸ॏཁ
    ‣ͨͩ͠ɺͦΕ͸ΞϓϦͷ࢓༷ͷ໰୊
    Ͱ͋ͬͯɺ42-ΠϯδΣΫγϣϯͱ͸
    ௚઀͸ؔ܎ͳ͍
    ‣݁Ռతʹ໾ཱͭ͜ͱ΋͋Δ͚ͩ

    View Slide

  12. αχλΠζʁ
    ‣͍͏ͳ CZͻΖΈͪΎઌੜ

    ‣Ͳ͏ͤ࿙ΕΔ
    ‣લड़ͷ௨Γɺҧ͏ϧʔϧͷจࣈྻΛ
    ࡞੒͠Α͏ͱ͍ͯ͠Δͷ͔ͩΒʮԚ
    છʯ͞ΕͯΑ͏͕ͳΜͩΖ͏͕ɺશͯ
    ద੾ʹॲஔ͢Δ΂͖

    View Slide

  13. ҉໧ͷલఏΛආ͚Δ
    ‣$entry->user_idʹ͸਺஋͔͋͠Γಘͳ͍
    ͱ͍͏લఏʹཔΒͳ͍
    ‣ͦΕ͕ຊ౰ʹͦ͏͔ͳΜͯΘ͔Βͳ͍͠ɺ஌
    Βͳ͍ؒʹલఏ͕มΘΔ͔΋͠Εͳ͍
    ‣ੲ͋ͬͨmagic_quotes_gpc=onͱ͔࿦֎
    "select * from user where user_id = " . $entry->user_id

    View Slide

  14. είʔϓΛڱΊΔ
    ‣ม਺ͷείʔϓ͸Ͱ͖Δ͚ͩڱ͍ํ͕Α͍ͱ
    ͍͏ϓϩάϥϛϯάͷݪଇΛద༻
    ‣άϩʔόϧม਺ΛͰ͖Δ͚ͩආ͚Δ΂͖Ͱ͋
    Δͷͱಉ༷
    ‣௚ۙͷίʔυͷΈͰ҆શੑΛ֬ೝͰ͖Δํ͕
    Α͍
    ‣ίʔυ͔Βԕ͘཭Εͨ৔ॴͰอূ͞Ε͍ͯΔ
    ͔΋͠Εͳ͍
    ͜ͱʹͨΑͬͯॲཧΛม͑Δ΂
    ͖Ͱ͸ͳ͍

    View Slide

  15. ग़ྗͷ௚ۙͰॲஔ
    ‣ϓϩάϥϜ಺ͷม਺ ϢʔβೖྗͰ΋%#͔Β
    ͖ͨ΋ͷͰ΋ͳΜͰ΋
    ͱ)5.-΍42-Λ݁߹
    ͢Δࡍʹ͸ɺͦͷ݁߹ͷ௚ۙͰɺ)5.-42-
    ͷϧʔϧʹ߹ΘͤͨͳΜΒ͔ͷॲஔΛࢪ͢
    ‣)5.-ͳΒhtmlspecialchars($str,
    ENT_QUOTES)͢Δ͠ɺ42-ͳΒϓϨʔεϗϧ
    μΛ࢖͏ͳͲ

    View Slide

  16. 42-ΠϯδΣΫγϣϯͷࠜຊతղܾͷجຊ͸όΠϯυػߏΛ࢖༻͢Δ͜ͱͰ͢
    ͕ɺԿΒ͔ͷཧ༝ʹΑΓόΠϯυػߏͰ࣮૷Ͱ͖ͳ͍৔߹͸ɺΤεέʔϓॲཧ
    ʹΑΔରࡦ΋ࠜຊతղܾͷҰͭʹͳΔͱ͍ͯ͠·͢ɻ͔͠͠ɺ্ͷҾ༻෦ʹ΋
    ͋ΔΑ͏ʹɺ42-ʹͱͬͯಛผͳҙຯΛ࣋ͭه߸ʢϝλจࣈʣ͸ɺσʔλϕʔ
    εΤϯδϯʹΑͬͯҟͳΔ΋ͷͰ͋Γɺ؀ڥʹԠͯ͡ରࡦ͢Δඞཁ͕͋Γ·
    ͢ɻ
    ʰ҆શͳ΢ΣϒαΠτͷ࡞Γํผ࡭ɿʮ҆શͳ42-ͷݺͼग़͠ํʯʱQ
    ݪଇ

    View Slide

  17. mysql_queryؔ਺
    ‣ඇਪ঑Ͱ͢
    ‣࢖Θͳ͍Α͏ʹ
    $sql = vsprintf(
    "SELECT * FROM user WHERE uid = %d AND provider = '%s'",
    array_map('mysql_real_escape_string', array($uid, $provider))
    );
    $result = mysql_query($sql);

    View Slide

  18. 1&"3%#
    $result = $db->query(
    "SELECT * FROM user WHERE uid = ? AND provider = ?",
    array($uid, $provider)
    );
    ‣։ൃఀࢭ͍ͯ͠·͢
    ‣࢖Θͳ͍Α͏ʹ

    View Slide

  19. 1%0
    ‣ʮ1%0ʹ͓͚ΔҰԠͷ҆શએݴͱ࢒Δ໰୊
    ఺ʯIUUQCMPHUPLVNBSVPSH
    QEPIUNMΑΓվมͷ্ܝࡌ
    $dbh = new PDO('mysql:host=hostname;dbname=dbname;charset=utf8',
    “user”, “pass”);
    // ੩తϓϨʔεϗϧμΛࢦఆ
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $sth = $dbh->prepare("SELECT * FROM user WHERE uid =? AND provider
    = ?");
    $sth->setFetchMode(PDO::FETCH_NUM);
    $sth->bindParam(1, $uid, PDO::PARAM_INT);
    $sth->bindParam(2, $provider, PDO::PARAM_STR);
    $sth->execute();

    View Slide

  20. 1&"3.%#
    ‣ʰಙؙຊʱQΑΓɺվมͷ্ܝࡌ
    header('Content-Type: text/html; charset=UTF-8');
    $mdb2 = MDB2::connect('mysql://user:pass@hostname/dbname?
    charset=utf8');
    $sql = "SELECT * FROM user WHERE id = ? AND provider = ?";
    $stmt = $mdb2->prepare($sql, array($uid, $provider));
    $rs = $stmt->execute(array($author));

    View Slide

  21. ԿΛ࢖͑͹͍͍ͷʁ
    ‣1)1Ҏલͷ1%0͸จࣈίʔυ·ΘΓ
    Ͱ͍Ζ͍Ζ໘౗
    ‣1&"3.%#Λ࢖͏ͷ͕ແ೉ͦ͏
    ‣͍ͣΕʹͤΑɺͪΌΜͱจࣈίʔυΛࢦఆ͠
    ·͠ΐ͏
    ‣ৄ͘͠͸ʮ1%0ʹ͓͚ΔҰԠͷ҆શએݴͱ
    ࢒Δ໰୊఺ʯIUUQCMPHUPLVNBSVPSH
    QEPIUNMࢀরͷ͜ͱ

    View Slide

  22. ·ͱΊ
    ‣ม਺͕Ͳ͏͍͏஋Ͱ͋Δ͔ʹؔΘΒͣɺม਺
    ͷ஋ΛؚΉ42-Λ૊ΈཱͯΔ৔߹͸ɺඞͣϓ
    ϨʔεϗϧμʔΛ࢖͏ͳΓɺΤεέʔϓ͢Δͳ
    Γ͢Δ લऀɺಛʹ੩తϓϨʔεϗϧμΛ࢖͏

    ‣҉໧ͷલఏʹͨΑΒͣɺ҆શੑΛ௚ۙͰ֬ೝ
    Ͱ͖ΔΑ͏ʹ͢Δ
    ‣42-ΠϯδΣΫγϣϯʹݶΒͣɺಉ༷ͷΠϯ
    δΣΫγϣϯܥ੬ऑੑ લड़
    Ͱಉ༷

    View Slide

  23. ੩తϓϨʔεϗϧμʹؔ͢Δิ଍
    https://twitter.com/tokuhirom/status/253499819136520193
    https://twitter.com/tokuhirom/status/253501614747439104
    ‣ϝϞϦΛແବʹফඅ
    ͢Δͱ͍͏໰୊ʁ
    ‣ΫϥΠΞϯτ͕ͪΌ
    Μͱεςʔτϝϯτϋ
    ϯυϥΛ։์ͯ͠΍Ε
    ͹໰୊ͳ͍ʁ
    ‣IUUQCMPHFWFSRVFVFDPN
    DIJCB

    View Slide

  24. ༨ஊ
    ಙؙઌੜʹ͓๽෇͖͍͖ͨͩ·ͨ͠
    https://twitter.com/ockeghem/status/253128283795886080

    View Slide

  25. ࢀߟจݙ
    ‣ʮαχλΠζݴ͏ͳΩϟϯϖʔϯʯͱ͸Կ͔
    ‣IUUQUBLBHJIJSPNJUTVKQEJBSZIUNMQ
    ‣ʰମܥతʹֶͿ҆શͳ8FCΞϓϦέʔγϣϯ
    ͷ࡞Γํʱ ಙؙߒɾஶ

    ‣IUUQXXXTCDSKQQSPEVDUTIUNM
    ‣ʰ҆શͳ΢ΣϒαΠτͷ࡞Γํผ࡭ʮ҆શͳ
    42-ͷݺͼग़͠ํʯʱ
    ‣IUUQXXXJQBHPKQTFDVSJUZWVMOEPDVNFOUT
    XFCTJUF@TFDVSJUZ@TRMQEG

    View Slide