SQL Injection Basics

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

@kentaro ΤϯδχΞ 1FSMFS ϧϏʔετ ʮ΃ͪ΃ʯʮ΃ͪ ͺʔʯ૑࢝ऀ /FX ܀ྛ݈ଠ࿠ paperboy&co.

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

42-ΠϯδΣΫγϣϯ͸ɺ42-ͷݺͼग़͠ํʹෆඋ͕͋Δ৔߹ʹൃੜ͢Δ੬ ऑੑͰ͢ɻΞϓϦέʔγϣϯʹ42-ΠϯδΣΫγϣϯ੬ऑੑ͕͋Δ৔߹ɺҎԼ ͷΑ͏ͳӨڹΛड͚ΔՄೳੑ͕͋Γ·͢ɻ͢΂ͯɺ߈ܸऀ͕ೳಈతʹ ར༻ऀͷ ؔ༩ͳ͠Ͱ αʔόʔΛ߈ܸͰ͖·͢ɻ ‣σʔλϕʔε಺ͷ͢΂ͯͷ৘ใ͕֎෦͔Β౪·ΕΔ ‣σʔλϕʔεͷ಺༰͕ॻ͖׵͑ΒΕΔ ‣ೝূΛճආ͞ΕΔ *%ͱύεϫʔυΛ༻͍ͣʹϩάΠϯ͞ΕΔ
‣ͦͷଞɺσʔλϕʔεαʔόʔ্ͷϑΝΠϧͷಡΈग़͠ɺॻ͖ࠐΈɺϓϩά ϥϜͷ࣮ߦͳͲΛߦΘΕΔ 42-ΠϯδΣΫγϣϯ ʰମܥతʹֶͿ҆શͳ8FCΞϓϦέʔγϣϯͷ࡞ΓํʱQ ҎԼʰಙؙຊʱ

$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? ʗ

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

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

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

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

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

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

αχλΠζʁ ‣͍͏ͳ CZͻΖΈͪΎઌੜ ‣Ͳ͏ͤ࿙ΕΔ ‣લड़ͷ௨Γɺҧ͏ϧʔϧͷจࣈྻΛ ࡞੒͠Α͏ͱ͍ͯ͠Δͷ͔ͩΒʮԚ છʯ͞ΕͯΑ͏͕ͳΜͩΖ͏͕ɺશͯ ద੾ʹॲஔ͢Δ΂͖

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

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

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

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

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);

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

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();

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));

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

·ͱΊ ‣ม਺͕Ͳ͏͍͏஋Ͱ͋Δ͔ʹؔΘΒͣɺม਺ ͷ஋ΛؚΉ42-Λ૊ΈཱͯΔ৔߹͸ɺඞͣϓ ϨʔεϗϧμʔΛ࢖͏ͳΓɺΤεέʔϓ͢Δͳ Γ͢Δ લऀɺಛʹ੩తϓϨʔεϗϧμΛ࢖͏ ‣҉໧ͷલఏʹͨΑΒͣɺ҆શੑΛ௚ۙͰ֬ೝ Ͱ͖ΔΑ͏ʹ͢Δ ‣42-ΠϯδΣΫγϣϯʹݶΒͣɺಉ༷ͷΠϯ δΣΫγϣϯܥ੬ऑੑ
લड़ Ͱಉ༷

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

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

ࢀߟจݙ ‣ʮαχλΠζݴ͏ͳΩϟϯϖʔϯʯͱ͸Կ͔ ‣IUUQUBLBHJIJSPNJUTVKQEJBSZIUNMQ ‣ʰମܥతʹֶͿ҆શͳ8FCΞϓϦέʔγϣϯ ͷ࡞Γํʱ ಙؙߒɾஶ ‣IUUQXXXTCDSKQQSPEVDUTIUNM ‣ʰ҆શͳ΢ΣϒαΠτͷ࡞Γํผ࡭ʮ҆શͳ 42-ͷݺͼग़͠ํʯʱ ‣IUUQXXXJQBHPKQTFDVSJUZWVMOEPDVNFOUT
XFCTJUF@TFDVSJUZ@TRMQEG

SQL Injection Basics

SQL Injection Basics

Kentaro Kuribayashi

More Decks by Kentaro Kuribayashi

Other Decks in Technology

Featured

Transcript

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

@kentaro ΤϯδχΞ 1FSMFS ϧϏʔετ ʮ΃ͪ΃ʯʮ΃ͪ ͺʔʯ૑࢝ऀ /FX ܀ྛ݈ଠ࿠ paperboy&co.

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

$sql = "SELECT * FROM user WHERE uid = "

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

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

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

αχλΠζʁ ‣͍͏ͳ CZͻΖΈͪΎઌੜ ‣Ͳ͏ͤ࿙ΕΔ ‣લड़ͷ௨Γɺҧ͏ϧʔϧͷจࣈྻΛ ࡞੒͠Α͏ͱ͍ͯ͠Δͷ͔ͩΒʮԚ છʯ͞ΕͯΑ͏͕ͳΜͩΖ͏͕ɺશͯ ద੾ʹॲஔ͢Δ΂͖

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

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

mysql_queryؔ਺ ‣ඇਪ঑Ͱ͢ ‣࢖Θͳ͍Α͏ʹ $sql = vsprintf( "SELECT * FROM user

1&"3%# $result = $db->query( "SELECT * FROM user WHERE uid

1%0 ‣ʮ1%0ʹ͓͚ΔҰԠͷ҆શએݴͱ࢒Δ໰୊ ఺ʯIUUQCMPHUPLVNBSVPSH QEPIUNMΑΓվมͷ্ܝࡌ $dbh = new PDO('mysql:host=hostname;dbname=dbname;charset=utf8', “user”, “pass”);

1&"3.%# ‣ʰಙؙຊʱQΑΓɺվมͷ্ܝࡌ header('Content-Type: text/html; charset=UTF-8'); $mdb2 = MDB2::connect('mysql://user:pass@hostname/dbname? charset=utf8'); $sql

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

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

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