ȸ¿ø°¡ÀԡžÆÀ̵ð/ºñ¹øã±â
ȨÀ¸·Î

[php] SQL ÀÎÁ§¼Ç °ø°Ý
7³â Àü
SQL ÁúÀÇ °ø°ÝÀÌ Çö½ÇÀûÀ¸·Î ¸¹Àº ¹®Á¦Á¡À» °¡Á® ¿Â´Ù´Â Á¡¿¡ ÃֽŠ¹öÀü(MYSQL, MSSQL..)¿¡¼­ ÀÚüÀûÀ¸·Î ÇÊÅ͸µÇÏ°í ÀÖÁö¸¸, ±¸¹öÀü¿¡¼­ ±×·¸Áö ¸øÇÕ´Ï´Ù.

SQL ÁúÀǸ¦ ½Å·ÚÇÒ ¼ö ¾ø´Â ¸í·ÉÀ¸·Î ÀÎÇØ SQL ÁúÀÇ¿¡¼­ Á¢±Ù Á¦¾î¸¦ ¿ìȸÇÒ ¼ö ÀÖ¿©, ÀϹÝÀûÀÎ ÀÎÁõ°ú ÀÎÁõ È®ÀÎÀ» ¹«½ÃÇÏ°í, Á¾Á¾ SQL ÁúÀÇ°¡ »ç¿ëÀÚ°¡ °¡Áú ¼ö ¾ø´Â ±ÇÇÑÀ» °­Á¦ ÃëµæÇϱ⵵ ÇÕ´Ï´Ù.

SQL ¸í·É ÀÎÁ§¼ÇÀ̶õ? °ø°ÝÀÚ°¡ ¼û°ÜÁø µ¥ÀÌÅ͸¦ ³ëÃâÇϰųª, Ãë¾àÇÑ ºÎºÐÀ» µ¤¾î¾²°Å³ª, µ¥ÀÌÅͺ£À̽º¿¡ À§ÇèÇÑ ½Ã½ºÅÛ ´Ü°è ¸í·ÉÀ» ½ÇÇàÇÏ°Ô ÇÏ´Â SQL ¸í·ÉÀ» »ý¼ºÇϰųª ´ëüÇÏ´Â ±â¼ú¸¦ ¸»ÇÕ´Ï´Ù.

¾îÇø®ÄÉÀ̼ÇÀÌ »ç¿ëÀÚ ÀÔ·ÂÀ» ¹Þ¾Æ¼­, À̸¦ SQL ÁúÀǸ¦ ¸¸µé 떄 Á¤Àû Àμö·Î Á¶ÇÕÇÔÀ¸·Î½á ÀϾ´Ï´Ù. À¯°¨½º·´°Ôµµ, ¾Æ·¡ ¿¹Á¦µéÀº ½ÇÁ¦ÀÇ °ÍÀÔ´Ï´Ù.

Æнº¿öµå¸¦ ¾ò´Â ¹æ¹ý Áß Çϳª´Â °Ë»ö °á°ú ÆäÀÌÁö¸¦ ¿ìȸÇÏ´Â °ÍÀÔ´Ï´Ù. °ø°ÝÀÚ¿¡°Ô ÇÊ¿äÇÑ °ÍÀº º¯¼ö Áß Çϳª¶óµµ Á¦´ë·Î ´Ù·ïÁöÁö ¾ÊÀ¸¸é¼­ SQL ±¸¹®¿¡ »ç¿ëµÇ´Â °ÍÀÔ´Ï´Ù.

ÀÌ·¯ÇÑ ÇÊÅÍ´Â ÀϹÝÀûÀ¸·Î SELECT ±¸¹®¿¡¼­ WHERE, ORDER BY, LIMIT, OFFSET¿¡ »ç¿ëµË´Ï´Ù. µ¥ÀÌÅͺ£À̽º°¡ UNION ±¸Á¶¸¦ Áö¿øÇϸé, °ø°ÝÀÚ´Â ¿ø·¡ ÁúÀÇ¿¡ Àüü ÁúÀǸ¦ µ¡ºÙ¿©¼­ ÀÓÀÇÀÇ Å×ÀÌºí¿¡¼­ Æнº¿öµå¸¦ ¾òÀ» ¼ö ÀÖ½À´Ï´Ù. ¾ÏȣȭµÈ Æнº¿öµå Çʵ带 °­·ÂÈ÷ ±ÇÇÕ´Ï´Ù.


SQL ÁúÀÇ °ø°Ý


°ËÁõµÇÁö ¾Ê´Â º¯¼ö¸¦ ÀüÀû »ç¿ëÀÚÀÇ ½Å·Ú¸¦ ¹Ï°í ÇÊÅ͸µÇÏÁö ¾Ê´Â´Ù¸é, ¹®Á¦´Â Ä¿Áú ¼ö ¹Û¿¡ ¾ø½À´Ï´Ù. ´ÙÀ½ º¯¼ö¿¡ ÀÌ ÁúÀÇ('¿Í --·Î..)°¡ $query ¿¡¼­ »ç¿ëÇÏ´Â º¯¼ö Áß Çϳª¿¡ ÇÒ´çµÇ¸é, ¹®Á¦´Â Ä¿Áú ¼ö ¹Û¿¡ ¾ø½À´Ï´Ù.

¿¹Á¦ (ex #1

<?php
$query  = "SELECT id, name, inserted, size FROM products
                  WHERE size = '$size'
                  ORDER BY $order LIMIT $limit, $offset;";
$result = mysql_query($query);
?>

´ÙÀ½ ÁúÀÇ·Î ºñ¹Ð¹øÈ£¾øÀÌ ´©±¸³ª Á¢¼ÓÀÌ °¡´ÉÇÏ°Ô º¯ÁúµÇ¾î ¹ö¸³´Ï´Ù.

¿¹Á¦ (ex #2
<?php
$_POST['username'] = 'aidan';
$_POST['password'] = "' OR ''='";

$query = "SELECT * FROM users WHERE user='{$_POST['username']}'
            AND password='{$_POST['password']}'";
mysql_query($query);

echo $query;
// °á°ú: SELECT * FROM users WHERE user='aidan' AND password='' OR ''=''
?>

SQL UPDATEµµ °ø°Ý¹ÞÀ» ¼ö Àִµ¥, ÀÌ·± ÁúÀǸ¦ ¿ÏÀüÇÑ »õ ÁúÀǸ¦ µ¡ºÙÀÏ ¼ö ÀÖ½À´Ï´Ù. ¶ÇÇÑ °ø°ÝÀÚ°¡ SET ÀýÀ» ´Ù·ê ¼öµµ ÀÖ½À´Ï´Ù. ÀÌ °æ¿ì ÁúÀǸ¦ ¼º°øÀûÀ¸·Î º¯°æÇϱâ À§ÇÏ¿© ÀϺΠ½ºÅ°¸¶ Á¤º¸¸¦ °¡Áö°í ÀÖ¾î¾ß ÇÕ´Ï´Ù.

¿¹Á¦ (ex #3
<?php
$query = "UPDATE usertable SET pwd='$pwd' WHERE uid='$uid';";
?>

¾ÇÀÇÀûÀÎ »ç¿ëÀÚ°¡ $uid ¿¡ ' or uid like'%admin'; -- °ªÀ» ³Ö¾î¼­ °ü¸®ÀÚ Æнº¿öµå¸¦ º¯°æÇϰųª, $pwd ¿¡ "hehehe', admin='yes', trusted=100 "(¸¶Áö¸· °ø¹é Æ÷ÇÔ)À» ¼³Á¤ÇÏ¿© ±ÇÇÑÀ» ¾òÀ» ¼öµµ ÀÖ½À´Ï´Ù.

¿¹Á¦ (ex #4
<?php
// $uid == ' or uid like'%admin%'; --
$query = "UPDATE usertable SET pwd='...' WHERE uid='' or uid like
'%admin%'; --";

// $pwd == "hehehe', admin='yes', trusted=100 "
$query = "UPDATE usertable SET pwd='hehehe', admin='yes', trusted=100
WHERE ...;";
?>

µ¥ÀÌÅͺ£À̽º È£½ºÆ®ÀÇ OS µî±Þ ¸í·É¿¡ Á¢±ÙÇÏ´Â ¹®Á¦½Ã µÉ ¿¹Á¦ÀÔ´Ï´Ù.

¿¹Á¦ (ex #5
<?php
$query  = "SELECT * FROM products WHERE id LIKE '%$prod%'";
$result = mssql_query($query);
?>

°ø°ÝÀÚ°¡ $prod¿¡ a%' exec master..xp_cmdshell 'net user test testpass /ADD' -- °ªÀ» Á¦ÃâÇϸé, $query´Â:

¿¹Á¦ (ex #6
<?php
$query  = "SELECT * FROM products
             WHERE id LIKE '%a%'
             exec master..xp_cmdshell 'net user test testpass /ADD'--";
$result = mssql_query($query);
?>

ÀÌ·¯ÇÑ °ø°ÝÀº ÁÖ·Î º¸¾ÈÀ» ¿°µÎ¿¡ µÎÁö ¾Ê°í, ¾²¿©Áø ÄÚµå Ãë¾àÁ¡¿¡¼­ ¹ß»ýÇÕ´Ï´Ù. ¾î¶°ÇÑ ÀԷµµ ¹Ï¾î¼­´Â ¾ÈµÇ¸ç, ÃֽŠ¹öÀüÀÌ¶óµµ Çѹø´õ ÇÊÅ͸µÇØ ÁÖ¾î¾ß ÇÕ´Ï´Ù.

ƯÈ÷ Ŭ¶óÀ̾ðÆ®Ãø¿¡¼­ ¿À´Â ÀÔ·ÂÀº ¹Ï¾î¼­´Â ¾ÈµË´Ï´Ù. select, hidden input Çʵå, ÄíÅ°µµ ¸¶Âù°¡ÁöÀÔ´Ï´Ù. ù ¹ø°, µÎ ¹ø° ¿¹Á¦¿¡¼­ ±×·¯ÇÑ ÁúÀÇ°¡ Å« ¹®Á¦¸¦ ÀÏÀ¸Å³ ¼ö ÀÖÀ½À» º¸¿©ÁÖ°í ÀÖ½À´Ï´Ù.
SQL ÀÎÁ§¼Ç ȸÇÇ


´ÙÀ½Àº ¾ÈÀüÇÑ ÁúÀÇ ¿¹Á¦°¡ µË´Ï´Ù.

¿¹Á¦ (ex #7

<?php
settype($offset, 'integer');
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET
               $offset;";

$query = sprintf("SELECT id, name FROM products ORDER BY
                    name LIMIT 20 OFFSET %d;", $offset);
?>

ÀÌ·¯ÇÑ ¹®Á¦¸¦ ȸÇÇÇϱâ À§ÇØ ¾ÈÀüÇÑ ¹æ¹ýÀ¸·Î ¹®ÀÚ¿­ ȸÇÇ ÇÔ¼ö¸¦ »ç¿ëÇÏ´Â °ÍÀε¥, mysql_real_escape_string() °¡ ±×°ÍÀÔ´Ï´Ù. mysql_real_escape_string ´Â mysql_query ¿¡¼­ Ư¼ö ¹®ÀÚ¿­À» À̽ºÄÉÀÌÇÁÇϱâ À§ÇØ »ç¿ëµÇ¸ç, ±×·¯¹Ç·Î SQL ÀÎÁ§¼Ç °ø°ÝÀÌ µ¿ÀÛÇÏÁö ¾Ê°í ÁúÀÇ°¡ Á¤È®ÇÏ°Ô ½ÇÇàµÉ °ÍÀÔ´Ï´Ù.

¿¹Á¦ (ex #8
<?php
if (isset($_POST['product_name']) &&
       isset($_POST['product_description']) &&
       isset($_POST['user_id'])) {
    // Á¢¼Ó
    $link = mysql_connect(
      'mysql_host',
      'mysql_user',
      'mysql_password'
    );
    if(!is_resource($link)) {
        echo "¼­¹ö Á¢¼Ó ½ÇÆÐ\n";
        // ... ¿À·ù¸¦ ÀûÀýÈ÷ ±â·Ï
    } else {
        // ONÀÏ °æ¿ì magic_quotes_gpc/magic_quotes_sybase È¿°ú Á¦°Å
        if(get_magic_quotes_gpc()) {
            $product_name        =
                stripslashes($_POST['product_name']);
            $product_description =
                stripslashes($_POST['product_description']);
        } else {
            $product_name        = $_POST['product_name'];
            $product_description = $_POST['product_description'];
        }
        // ¾ÈÀüÇÑ ÁúÀÇ ¸¸µé±â
        $query = sprintf("INSERT INTO products (
                          `name`, `description`, `user_id`)
                           VALUES ('%s', '%s', %d)",
               mysql_real_escape_string($product_name, $link),
               mysql_real_escape_string($product_description, $link),
               $_POST['user_id']);

        mysql_query($query, $link);

        if (mysql_affected_rows($link) > 0) {
            echo "Product inserted\n";
        }
    }
} else {
    echo "Fill the form property\n";
}
?>


¾Æ´Ï¸é addslashes() ¿Í str_replace() ÇÔ¼ö¸¦ »ç¿ëÇÒ ¼öµµ ÀÖ½À´Ï´Ù. addslashes() ´Â µ¥ÀÌÅͺ£À̽º ÁúÀÇ µî¿¡¼­ ó¸®ÇÒ ÇÊ¿ä°¡ ÀÖ´Â ¹®ÀÚ ¾Õ¿¡ ¹é½½·¡½Ã¸¦ ºÙÀÎ ¹®ÀÚ¿­À» ¹ÝȯÇÕ´Ï´Ù. ÀÌ ¹®ÀÚµéÀº ÀÛÀº µû¿ÈÇ¥('), Å« µû¿ÈÇ¥("), ¹é½½·¡½Ã(\), NUL(NULL ¹ÙÀÌÆ®)ÀÔ´Ï´Ù.

addslashes()¸¦ »ç¿ëÇÏ´Â ´ëÇ¥ÀûÀÎ ¿¹´Â µ¥ÀÌÅͺ£À̽º¿¡ µ¥ÀÌÅ͸¦ ³ÖÀ» ¶§ ÀÔ´Ï´Ù. ¿¹¸¦ µé¾î, µ¥ÀÌÅͺ£À̽º¿¡ O'reilly ¶ó´Â À̸§À» ³ÖÀ¸·Á°í ÇÒ¶§, À̽ºÄÉÀÌÇÁÇÒ ÇÊ¿ä°¡ ÀÖ½À´Ï´Ù. ´ëºÎºÐÀÇ µ¥ÀÌÅͺ£À̽º´Â \À» »ç¿ëÇϱ⿡ O\'reilly°¡ µÇ¾î¾ß ÇÕ´Ï´Ù. ÀÌ µ¥ÀÌÅ͸¦ µ¥ÀÌÅͺ£À̽º¿¡ ³ÖÀ¸¸é Ãß°¡ÇÑ \Àº ÀúÀåµÇÁö ¾Ê½À´Ï´Ù.

¿¹Á¦ (ex #9
<?php
$str = "Is your name O'reilly?";

// Ãâ·Â: Is your name O\'reilly?
echo addslashes($str);
?>
ÃßõÃßõ : 257 Ãßõ ¸ñ·Ï
¹øÈ£ Á¦¸ñ
2,825
»ç¿ëÀÚ°¡ À¥ºê¶ó¿ìÀú¿¡¼­ µÚ·Î°¡±â¸¦ ÇßÀ»¶§ °¨ÁöÇÏ´Â ¹æ¹ý
2,824
[jQuery]¹öÆ° È°¼ºÈ­, ºñÈ°¼ºÈ­
2,823
jQuery show() / hide() / toggle() »ç¿ë¹ý
2,822
jquery ¿©·¯°¡Áö À̺¥Æ®
2,821
border-radius ¼Ó¼º
2,820
³×À̹ö ¿ÀÇÂAPI À½¼ºÇÕ¼º API »ç¿ëÇÏ´Â PHP »ùÇÃÄÚµå
2,819
UTF8 ÇÑ±Û ÀÚ¸£±â..
2,818
iconv ¿¡·¯ ¹ß»ý½Ã °è¼Ó ó¸®Çϱ⠿ɼÇ
2,817
[PHP] ÇöÀç ÆäÀÌÁöÀÇ µµ¸ÞÀÎ , URL Á¤º¸ ¾Ë¾Æ³»±â.
2,816
[PHP] ¸·°­ ±â´É ¹è¿­..
2,815
[CSS] - Input clear `X ` ¹öÆ° Á¦°Å ( IE, Chrome, Firefox )
2,814
[Mobile] - ¸ð¹ÙÀÏÀ¥ Href ű׼Ӽºµé
2,813
[JqueryMobile] - ÇöÀçÈ­¸éÀÇ °¡·Î¼¼·Î »çÀÌÁî ±¸Çϱâ
2,812
[JqueryMobile] - È­¸éÀÇ °¡·Î, ¼¼·Î »çÀÌÁî ±¸ÇÏ´Â ¹æ¹ý
2,811
jquery·Î °¡·Î ³ÐÀÌ(width), ¼¼·Î ³ôÀÌ(height) ÀÚµ¿ Á¶Àý
2,810
iframe ³ôÀÌ jquery·Î ÀÚµ¿Á¶ÀýÇϱâ
2,809
jQuery ¿À¸¥ÂÊ ¿µ¿ªÀÇ ³ôÀ̸¦ ¿ÞÂÊ ¿µ¿ªÀÇ ³ôÀÌ¿Í µ¿ÀÏÇÏ°Ô Çϱâ
2,808
jquery¿¡¼­ Å×À̺í ¦¼ö, Ȧ¼ö ¹ø° TR ¹è°æ»ö º¯°æÇϱâ
2,807
jquery¿¡¼­ Å×ÀÌºí¿¡ ¸¶¿ì½º ¿À¹ö½Ã ÇØ´ç ÇàÀÇ ¹è°æ»ö»ó º¯°æÇϱâ
2,806
jquery ½ºÅ©¸³Æ®³» ƯÁ¤°ª È®ÀÎÇϱâ (µð¹ö±ë)
2,805
jquery cookie (jquery.cookie.js)
2,804
jquery div ±âº» ³ÐÀÌ, ³ôÀÌ °è»ê ¹× padding, border Æ÷ÇÔ Çϱâ
2,803
jquery ´Ù¸¥¹öÀü Ãß°¡ »ç¿ë½Ã Ãæµ¹ ¹æÁö (Ä«Æä24 ½º¸¶Æ®µðÀÚÀÎ ±âº»³»Àå jquery 1.4.4 ¹öÀü°ú Ãæµ¹½Ã ÇØ°á¹æ¹ý)
2,802
ƯÁ¤ ÆäÀÌÁö Á¾·á½Ã È®ÀÎ °æ°íâ Ãâ·Â
2,801
jquery ¸¶¿ì½º ¿À¸¥ÂÊ ¹öÆ° Ŭ¸¯ ±ÝÁö (¿ìŬ¸¯ Á¦ÇÑ)
2,800
Á¤±Ô½Ä ƯÁ¤±¸°£ÀÇ ³»¿ë¸¸ °¡Á®¿À±â
2,799
°ýÈ£() ¾ÈÀÇ ³»¿ë¸¸ ÃßÃâ
2,798
4±â°¡ ÀÌ»óÀÇ Å×À̺íÀ» ¸¸µé°í ½Í´Ù¸é(Å×À̺íÀÇ AVG_ROW_LENGTH, MAX_ROWS)
2,797
Á¤±Ô Ç¥Çö½Ä(Regular Expression)
2,796
PHP¿¡¼­ À¯¿ëÇÏ°Ô ¾²ÀÌ´Â ¹®ÀÚ¿­ ó¸® ÇÔ¼ö
¸ñ·Ï
¹ÂÁ÷Æ®·ÎÆ® ºÎ»ê±¤¿ª½Ã ºÎ»êÁø±¸ °¡¾ßµ¿ ¤Ó °³ÀÎÁ¤º¸Ãë±Þ¹æħ
Copyright ¨Ï musictrot All rights reserved.