수업소개
여기서는 PHP와 MySQL을 연동할 때 발생할 수 있는 보안적인 문제를 해결하고, 이를 완화하는 방법을 소개합니다.
입력 공격의 차단
index.php
<?php
$conn = mysqli_connect(
'localhost',
'root',
'111111',
'opentutorials');
$sql = "SELECT * FROM topic";
$result = mysqli_query($conn, $sql);
$list = '';
while($row = mysqli_fetch_array($result)) {
$list = $list."<li><a href=\"index.php?id={$row['id']}\">{$row['title']}</a></li>";
}
$article = array(
'title'=>'Welcome',
'description'=>'Hello, web'
);
if(isset($_GET['id'])) {
$filtered_id = mysqli_real_escape_string($conn, $_GET['id']);
$sql = "SELECT * FROM topic WHERE id={$filtered_id}";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($result);
$article['title'] = $row['title'];
$article['description'] = $row['description'];
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>WEB</title>
</head>
<body>
<h1><a href="index.php">WEB</a></h1>
<ol>
<?=$list?>
</ol>
<a href="create.php">create</a>
<h2><?=$article['title']?></h2>
<?=$article['description']?>
</body>
</html>
create.php
<?php
$conn = mysqli_connect(
'localhost',
'root',
'111111',
'opentutorials');
$sql = "SELECT * FROM topic";
$result = mysqli_query($conn, $sql);
$list = '';
while($row = mysqli_fetch_array($result)) {
$list = $list."<li><a href=\"index.php?id={$row['id']}\">{$row['title']}</a></li>";
}
$article = array(
'title'=>'Welcome',
'description'=>'Hello, web'
);
if(isset($_GET['id'])) {
$filtered_id = mysqli_real_escape_string($conn, $_GET['id']);
$sql = "SELECT * FROM topic WHERE id={$filtered_id}";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($result);
$article['title'] = $row['title'];
$article['description'] = $row['description'];
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>WEB</title>
</head>
<body>
<h1><a href="index.php">WEB</a></h1>
<ol>
<?=$list?>
</ol>
<form action="process_create.php" method="POST">
<p><input type="text" name="title" placeholder="title"></p>
<p><textarea name="description" placeholder="description"></textarea></p>
<p><input type="submit"></p>
</form>
</body>
</html>
process_create.php
<?php
$conn = mysqli_connect(
'localhost',
'root',
'111111',
'opentutorials');
$filtered = array(
'title'=>mysqli_real_escape_string($conn, $_POST['title']),
'description'=>mysqli_real_escape_string($conn, $_POST['description'])
);
$sql = "
INSERT INTO topic
(title, description, created)
VALUES(
'{$filtered['title']}',
'{$filtered['description']}',
NOW()
)
";
$result = mysqli_query($conn, $sql);
if($result === false){
echo '저장하는 과정에서 문제가 생겼습니다. 관리자에게 문의해주세요';
error_log(mysqli_error($conn));
} else {
echo '성공했습니다. <a href="index.php">돌아가기</a>';
}
?>
SQL 주입(injection)의 차단
출력 공격(Cross site scripting)의 차단
index.php
<?php
$conn = mysqli_connect(
'localhost',
'root',
'111111',
'opentutorials');
$sql = "SELECT * FROM topic";
$result = mysqli_query($conn, $sql);
$list = '';
while($row = mysqli_fetch_array($result)) {
$escaped_title = htmlspecialchars($row['title']);
$list = $list."<li><a href=\"index.php?id={$row['id']}\">{$escaped_title}</a></li>";
}
$article = array(
'title'=>'Welcome',
'description'=>'Hello, web'
);
if(isset($_GET['id'])) {
$filtered_id = mysqli_real_escape_string($conn, $_GET['id']);
$sql = "SELECT * FROM topic WHERE id={$filtered_id}";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($result);
$article['title'] = htmlspecialchars($row['title']);
$article['description'] = htmlspecialchars($row['description']);
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>WEB</title>
</head>
<body>
<h1><a href="index.php">WEB</a></h1>
<ol>
<?=$list?>
</ol>
<a href="create.php">create</a>
<h2><?=$article['title']?></h2>
<?=$article['description']?>
</body>
</html>
