2024.06.03.보안 day41

2024년 06월 03일 12시 30분 01초에 업로드 된 글입니다.

작성자: 202401to07

워크밴치

지난 수업 마무리 못한 admin 다시 실습

MariaDB [(none)]> grant all privileges on *.* to smaster@'%' identified by 'asd123' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all privileges on *.* to myadmin@'localhost' identified by 'asd123' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

https://www.phpmyadmin.net/downloads/

Downloads

Your download should start soon, if not please click here. Please verify the downloaded file Please take additional steps to verify that the file you have downloaded is not corrupted, you can verify it using the following methods: phpMyAdmin needs your con

www.phpmyadmin.net

rpm -qa |grep mariadb ---------5.5
rpm -qa |grep php --------- 5.4

wget https://files.phpmyadmin.net/phpMyAdmin/4.0.0/phpMyAdmin-4.0.0-all-languages.zip
240 ll
241 unzip phpMyAdmin-4.0.0-all-languages.zip

MariaDB [(none)]> grant all privileges on *.* to smaster@'localhost' identified by 'asd123' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

192.168.10.20 )

[root@sevas10 ~]# firewall-cmd --permanent --add-service mysql
success
[root@sevas10 ~]# firewall-cmd --reload

MariaDB [(none)]> grant all privileges on xe.* to xemaster@'http://www.sevas10.com' identified by 'asd123'
-> ;
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> grant all privileges on xe.* to xemaster@'192.168.10.10' identified by 'asd123'
-> ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;

웹서버 / 디비 실습 시작

1. 10에서 만든 DNS에 20이 접속할수있게

yum install bind-* httpd-* php-* --skip-broken
   40  vi /etc/named.conf
   41  vi /etc/named.rfc1912.zones
   42  cd /var/named/
   44  cp -a named.empty sevas10.zone
   45  vi sevas10.zone
   46  service named restart
   47  vi /etc/sysconfig/network-scripts/ifcfg-ens33 DNS주소 바꾸기
   48  vi /etc/resolv.conf DNS주소 바꾸기
   50  service network restart       적용
   51  nslookup db.sevas10.com       확인
   52  nslookup http://www.sevas10.com
   53  firewall-cmd --permanent --add-port 53/udp    DNS 서비스는 대부분UDP를 사용          그러나 , 대용량 쿼리, DNSSEC은 TCP를 사용

일반적으로 UDP 포트 53만 열어도 DNS 쿼리가 작동
firewall-cmd --permanent --add-port 80/tcp 53 : DNS / 80: HTTP / 3306: MYsql

54 firewall-cmd --reload

61  chmod 660 sevas10.zone
   62  chown .named sevas10.zone
   55  service named restart     실행
   56  systemctl enable named     자동실행
   57  systemctl enable httpd        자동실행
20) nslookup http://www.sevas10.com 되면 기본설정 완료

2. DB만들고

dnf install mariadb-* -y mysql 다운받으면 충돌됨
service mariadb restart

systemctl enable mariadb
netstat -lntup |grep mysql 포트번호확인 (3306/tcp

vi /etc/my.cnf 한글 설정

[mysqld]
character-set-server=utf8

[client]
default_character_set=utf8

service mariadb restart
mysql -u root -p

create database sevas; DB만들기

use sevas;

create table class (
c_no int unsigned auto_increment,
id char(40) not null unique,
pw char(40) not null,
nick varchar(40) not null,
date datetime not null,
primary key(c_no)
);

vi index.php

<html>
<head>
<meta charset="utf-8">
<title>SEVAS_WEB</title>
</head>
<body>
<br>
<marquee width=600 bgcolor="red">
<font size=10 color=black>
SEVAS_TEST페이지에 오신것을 환영합니다.
</font>
</marquee>
<br>

<?php
session_start();
mysql_connect("db.sevas10.com","master","asd123") or die ("Network Fail<br>");
echo "Connect Success<br>";
mysql_select_db(sevas) or die ("DB Fail<br>");
echo "Connect DB<br>";
$sql="select nick from class where id='$_SESSION[LoginID]'";
$exec=mysql_query($sql);
$result=mysql_fetch_array($exec);
if ($result[nick] != "" )
{
echo "$result[nick] 님 환영합니다.<br>";
}
?>
<table border="1" style=float:left>
<tr><th><font size=5 color="red">
LIST
</font></th></tr>
<br>
<tr><td><a href="/php/phpinfo.php"> php정보확인 </a> <br></td></tr>
<tr><td><a href="login.html"> 로그인 </a> <br></td></tr>
<tr><td><a href="join.html"> 회원가입 </a> <br></td></tr>
<tr><td><a href="http://www.naver.com">
<img src="https://search.pstatic.net/common/?src=http%3A%2F%2Fimgnews.naver.net%2Fimage%2F003%2F2020%2F12%2F13%2FNISI20201211_0000654683_web_20201211162103_20201213152506274.jpg&type=sc960_832" width="100" height="50"></img> </a> <br></td></tr>
</table>
</body>
</html>

========================================================================================

vi login.html

<html>
<head>
<title>
SEVAS_LOGIN
</title>
</head>
<body>
<font size=3 color=blue> ID와 PW를 입력해주세요 </font><br><br>
<table border="1">
<form action=proc/login_proc.php method=POST>
<tr>
<th colspan="3"> LOGIN </th>
</tr>
<tr>
<td> ID </td>
<td> <input type=text name=id></td>
<td rowspan="2"> <input type=submit value=로그인 style='height:50'> </td>
</tr>
<tr>
<td> PW </td>
<td> <input type=password name=pw> </td>
</tr>
</form>
</table>
</body>
</html>

====================================================================================

vi proc/login_proc.php

<?php
$id=$_POST['id'];
$pw=$_POST['pw'];
$hidden=$_POST['hidden'];
#echo "$id<br>";
#echo "$pw<br>";
#echo "$hidden<br>";
session_start();
mysql_connect("db.sevas10.com","master","asd123") or die ("NET 연결오류");
echo "connect success<br>";
mysql_select_DB(sevas) or die ("DB 연결실패");
echo "Connect DB<br>";
$sql="select id,pw from class where id='$id' and pw='$pw'";
$exec=mysql_query($sql);
$result=mysql_fetch_array($exec) or die ("Login Fail<br>
<form method=POST action=/login.html>
<input type=submit value='다시로그인'>
</form>"
);
echo "Login Success<br>";

print_r($result);
echo "<br>";
echo $result["0"];
echo "<br>";
echo $result["id"];
echo "<br>";
echo $result["1"];
echo "<br>";
echo $result["pw"];
echo "<br>";

$_SESSION['LoginID'] = $id;
#echo $_SESSION['LoginID'];
header ("location:/index.php");
?>

===================================================================================

vi join.html

<html>
<head>
<title> 회원가입 </title>
</head>
<body>
<font size=3 color=blue> <b>회원가입신청서</b></font>
<br><br>

<form method=POST action=proc/join_proc.php>

<table border="1">
<tr>  <td> 아이디 </td>  <td> <input type=text name=id> </td>
<tr>  <td> 비밀번호 </td>  <td> <input type=password name=pw> </td>
<tr>  <td> NICKNAME </td>  <td> <input type=text name=nick>  </td>
<tr>  <td> </td> <td> <input type=submit value="회원가입완료">
<input type=reset value="입력   취소"> </td>
</table>
</body>
</html>

=======================================================================================
vi proc/join_proc.php)

<?php

$id = $_POST['id'];
$pw = $_POST['pw'];
$nick = $_POST['nick'];

mysql_connect ("db.sevas10.com","master","asd123") or die ("mysql Fail");
echo "Connect Mysql<br>";
mysql_select_db(sevas) or die ("DB Fail");
echo "Connect DB<br>";

$sql = "insert into class values (null,'$id','$pw','$nick',now() )";
$exec = mysql_query($sql) or die ("회원가입실패<br>
<form method=POST action=/join.html>
<input type=submit value='회원가입페이지로'>
</form>"
);
echo "회원가입성공";
echo "<form method=POST action=/index.php>";
echo "<input type=submit value='메인화면'>";
echo "</form>";
?>

ㄴ 로딩 안되면 쿠키 삭제 확인하기

이제 암호화 복호화 ~

char 40이상

ID: master  PW: *12ya~   key:monster(md5)
ID: admin   PW: asd123 key:root(sha1)
ID: sevas   PW: Zhangbo key:mygirl(sha2-256)
ID: myclass PW: iloveyou     key:real(sha2-512)

문제1. 암호화 복호화

[암호화]

insert into testuser values('myclass',hex(aes_encrypt('iloveyou',sha2('real',512))));
insert into testuser values('sevas',hex(aes_encrypt('Zhangbo',sha2('mygirl',256))));
insert into testuser values('master',hex(aes_encrypt('*12ya~',md5('monster'))));
insert into testuser values('admin',hex(aes_encrypt('asd123',sha1('root'))));

[복호화]

select id,aes_decrypt(unhex(pw), sha1('root')) from testuser;

select id,aes_decrypt(unhex(pw),sha2('mygirl',256)) from testuser;

select id,aes_decrypt(unhex(pw),md5('monster')) from testuser;

select id,aes_decrypt(unhex(pw), sha2('real',512)) from testuser;

문제 2.

1. 웹사이트 가입시 sha2(512) 로 암호화 되면서 DB에 입력되도록 설정 (key: sevas )
2. 로그인시 평문이 sha2로 암호화가 되면서 로그인

[정답]
1.vi proc/join_proc.php
#$sql = "insert into class values ('','$id','$pw','$nick',now())";
$sql ="insert into class values (null,'$id',hex(aes_encrypt('$pw',sha2('sevas',512))),'$nick',now())";

2. vi proc/login_proc.php
기존내용 주석
#$sql="select id,pw from class where id='$id' and pw='$pw'";
$sql="select id,pw from class where id='$id' and pw=hex(aes_encrypt('$pw',sha2('sevas',512)))";

위에 잘 안되서 아래로 함

insert into class values (1,'test',hex(aes_encrypt('test',sha2('sevas',512))),'testnick',now());
insert into class values (2,'test2',hex(aes_encrypt('test2',sha2('sevas',512))),'test2nick',now());

이제 group by , having

create database SEVAS_ELECTRONIC;
use SEVAS_ELECTRONIC;
create table 판매 (
no int auto_increment,primary key(no),
id char (20) not null,
가전제품 varchar(20) not null,
가격 int (50) unsigned not null,
구매수량 varchar(10),
구매날짜 datetime not null);

insert into 판매 values (null,'sevas','티비','2560000','1',now());
insert into 판매 values (null,'sm','컴퓨터','2180000',' 2',now());
insert into 판매 values (null,'sevas','티비','3000000','3',now());
insert into 판매 values (null,'lg','컴퓨터','800000','1',now());
insert into 판매 values (null,'sk','다리미','100000','4',now());
insert into 판매 values (null,'sevas','티비','3400000','1',now());
insert into 판매 values (null,'sk','헤드셋','150000','3',now());
insert into 판매 values (null,'sm','안마기','500000','2',now());
insert into 판매 values (null,'sevas','전자렌지','170000','1',now());
insert into 판매 values (null,'dw','냉장고','3000000','1',now());

group by :

1. 중복값 처리

2. 칼럼에 집중

COUNT: 숫자
AVG : 평균
SUM : 합
MAX : 최대값
MIN : 최소갑
STDEV : 표준편차
반올림 : round
버림 : truncate

[평균 avg ]
select 가전제품,avg(가격) as 평균가격 from 판매 group by 가전제품;
[소수점 정리]
select 가전제품,round(avg(가격),2) as 평군가격 from 판매 group by 가전제품;
select 가전제품,truncate(avg(가격),2) as 평군가격 from 판매 group by 가전제품;

**가전제품별 판린 총 판매량  ( + , - , * , / )
select  가전제품,(sum(가격*구매수량))as 총판매량   from 판매 group by 가전제품;

**회사별 팔린 판매량**
select id,sum(가격*구매수량) as "회사별 총판매"  from 판매 group by id;

[MAX 최대값]
가전제품별 가장 높은 가격
select 가전제품,max(가격) from 판매 group by 가전제품;

[MIN 최소값]
select 가전제품,min(가격) from 판매 group by 가전제품;

[ 남,여  숫자 확인 ]
select 성별,count(성별) from sevas group by 성별;

[ 남,여  나이 총합 ]
select 성별,sum(age) as 나이총합 from sevas group by 성별;

[성별 평균나이 ]
select 성별,avg(age)  as 나이평균 from sevas group by 성별;

[반올림 round]  ( 소숫점 1자리)
select 성별,round(avg(age),1)  as 나이평균 from sevas group by 성별;

[버림 runcate]  ( 소숫점 X )
select 성별,truncate(avg(age),0)  as 나이평균 from sevas group by 성별;

조건 : where

group by 조건 : having

UNION : 자신테이블 + 외부테이블

중복된 레코드를 제거 /두 개 이상의 SELECT 문의 결과를 결합

조인 : 자신테이블 과 외부테이블 비교 후 자신테이블만

*mysql은 full join이 없다 * -> left join + right join + union(중복처리)로 해결

[union]
select * from [A]  union select [칼럼수를 맞춰야됨] from [B]

[inner join  교집합]
select * from [A]  inner join [B] on <조건>

[left outer join  왼쪽집합]
select * from [A]  left join [B] on <조건>

[right outerjoin 오른쪽 집합]
select * from [A]  right join [B] on <조건>

[Full outer join]
select * from [Atb] left join [Btb] on <조건구문>
union
select * from [Atb] right join [Btb] on <조건구문>;

[단일행 서브쿼리 ]    == 실행결과가 1건
select * from sword where s_name = (select m_name from market where m_seller='evernick');

[다중행 서브쿼리]   == 실행결과가 여러건인 서브 쿼리   ( in 칼럼 1개만 비교 )

select * from sword where s_name in (select m_name from market where m_category='검');

[다중열 서브쿼리 ]  (in   칼럼 2개이상 비교 )
select * from sword where (s_name,s_level) in (select m_name, 50 from market where m_category='검');

뷰 테이블

'보안' 카테고리의 다른 글

2024.06.05.보안 day43 (0)	2024.06.09
2024.06.04.보안 day42 (0)	2024.06.04
2024.05.30.목.보안 day40 (0)	2024.05.30
2024.05.29.수.보안 day39 (0)	2024.05.29
2024.05.28.화.보안 day38 (0)	2024.05.28

다음글이 없습니다.

이전글이 없습니다.

'보안' 카테고리의 다른 글

티스토리툴바