728x90
회원 정보 데이터베이스 구축하기
mysql> create database login;
Query OK, 1 row affected (0.01 sec)
mysql> use login
Database changed
mysql> create table user(id varchar(20), pw varchar(50));
Query OK, 0 rows affected (0.00 sec)
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(20) | YES | | NULL | |
| pw | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
테이블에 데이터 입력하기
mysql> insert into user(id, pw) values('guest', 'guest123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user(id, pw) values('admin', 'admin123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+-------+----------+
| id | pw |
+-------+----------+
| guest | guest123 |
| admin | admin123 |
+-------+----------+
2 rows in set (0.00 sec)
검색 데이터베이스 구축하기
mysql> create database portal;
Query OK, 1 row affected (0.01 sec)
mysql> use portal;
Database changed
mysql> create table search(content varchar(100));
Query OK, 0 rows affected (0.01 sec)
mysql> desc search;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| content | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
테이블에 데이터 입력하기
mysql> insert into search(content) values('test data 1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into search(content) values('test data 2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into search(content) values('search data');
Query OK, 1 row affected (0.00 sec)
mysql> insert into search(content) values('search data 2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into search(content) values('how to search things?');
Query OK, 1 row affected (0.00 sec)
mysql> select * from search;
+-----------------------+
| content |
+-----------------------+
| test data 1 |
| test data 2 |
| search data |
| search data 2 |
| how to search things? |
+-----------------------+
5 rows in set (0.01 sec)
login.html
<html>
<head>
</head>
<body>
<form method="GET" action="login.php">
<input type="text" name="id_param"> <br>
<input type="password" name="pw_param"> <br>
<input type="submit">
</form>
</body>
</html>
search.html
<html>
<head>
</head>
<body>
This is Search page <br>
<form method="GET" action="search.php">
<input type="text" name="search_data">
<input type="submit">
</form>
</body>
</html>
DB 사용자 생성 후 권한 할당하기
student@security:/var/www/html$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.35-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user 'admin_db'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on login.* to 'admin_db'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on portal.* to 'admin_db'@'localhost';
Query OK, 0 rows affected (0.00 sec)
student@security:/var/www/html$ mysql -u admin_db -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.35-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| login |
| performance_schema |
| portal |
+--------------------+
4 rows in set (0.00 sec)
로그인 기능 구현
login.php
<?php
$id = $_GET['id_param'];
$pw = $_GET['pw_param'];
$db_conn = mysqli_connect("127.0.0.1", "admin_db", "password", "login");
if($db_conn == false){
echo mysqli_connect_error();
}
else {
$query = "select * from user where id='{$id}' and pw='{$pw}'";
$result = mysqli_query($db_conn, $query);
echo "query : {$query}<br>";
if($result == false) {
echo mysqli_error($db_conn);
}
else {
$row = mysqli_fetch_array($result);
if($row) {
echo "Hello {$row['id']}, login success!";
}
else {
echo "login failed";
}
}
mysqli_close($db_conn);
}
?>
검색 기능 구현
search.php
<?php
$search_data = htmlentities($_GET['search_data']);
echo "<b>{$search_data}</b> Search Result <br>";
$data = addslashes($_GET['search_data']);
$db_conn = mysqli_connect("127.0.0.1", "admin_db", "password", "portal");
if($db_conn == false){
echo mysqli_connect_error();
}
else {
$query = "select * from search where content like '%{$data}%'";
$result = mysqli_query($db_conn, $query);
echo "<table style='border:1px solid; border-collapse:collapse'>";
echo "<th style='border:1px solid'>Search Result Contents</th>";
if($result == false) {
echo mysqli_error($db_conn);
}
else {
while($row = mysqli_fetch_array($result)) {
echo "<tr><td style='border:1px solid'>{$row['content']}</td></tr>";
}
}
mysqli_close($db_conn);
}
?>728x90
'Security > 보안기초' 카테고리의 다른 글
| 통합 보안 (0) | 2023.11.17 |
|---|---|
| 웹 보안 기초(03) (0) | 2023.11.15 |
| 웹보안 기초(01) (0) | 2023.11.15 |
| Memory Theory(02) (0) | 2023.11.14 |
| Memory Theory(01) (0) | 2023.11.14 |
