SQL Injection Basics

社内勉強会用資料です。

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