암호화, 복호화 - mysql

:)·2024년 6월 4일

보안

목록 보기

17/28

Hash

$1 md5
$5 sha 256
$6 sha 512

암호

단방향 암호 = hash 암호 암호화 가능, 복호화X → 무결성 O

select md5('sevas');
select sha1('sevas');
select sha2('sevas',256);
select sha2('sevas',512);
양방향 암호화 (키, 이름 ,아이디, 주민번호, 패스워드.. )
- 스트림 암호화: RC4, RC5....
- 블럭 암호화: SEED , DES , 3DES , AES
- mysql = aes
- 암호화에서 16 진수를 쓰는 이유: 비트 연산을 하기 때문. shr- shl 때문

암호화

select hex(암호화종류 ('비밀번호', 해쉬암호종류('키값'));

select hex(aes_encrypt('asd123',md5('sevas')));
select hex(aes_encrypt('asd123',sha1('sevas')));
select hex(aes_encrypt('asd123',sha2('sevas',256)));
hex(aes_encrypt('asd123',sha2('sevas',512)));
양방향 암호화는 aes_encrypt로 주로 진행 + HEX로 진행

복호화

select aes_decrypt(unhex(’암호화된 값'),md5('키값') );

select aes_decrypt(unhex('B688F3861568B112D5D60C48C8E987C8'),md5('sevas') );
select aes_decrypt(unhex('F22AD8472D863D4443DFEDEB66D42EED'),sha1('sevas'));
select aes_decrypt(unhex('09F01FF638796435B6531E912CCF324A'),sha2('sevas',256));
select aes_decrypt(unhex('A12E8C1C4F090802237FE9C8BB9DC016'),sha2('sevas',512));

실습

예제 - class 데이터 베이스에 패스워드를 각각 키 값을 통해 aes로 암호화하고 복호화하여 내용 출력(외우기)

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)

암호화 (평문입력 → 암호화 (암호화문) → hex 처리)
insert into class values (null,'master',hex(aes_encrypt('*12ya~',md5('monster'))),'마스터',now());
insert into class values (null,'admin',hex(aes_encrypt('asd123',sha1('root'))),'관리자',now());
insert into class values (null,'sevas2',hex(aes_encrypt('Zhangbo',sha2('mygirl',256))),'세바스',now());
insert into class values (null,'myclass',hex(aes_encrypt('iloveyou',sha2('real',512))),'우리반',now());
복호화 (암호화 → unhex 처리 → 복호화 → 평문)
- select c_no,id,aes_decrypt(unhex(pw),md5('monster')),nick,date from class;
select c_no,id,aes_decrypt(unhex(pw),sha1('root')),nick,date from class;
select c_no,id,aes_decrypt(unhex(pw),sha2('mygirl',256)),nick,date from class;
select c_no,id,aes_decrypt(unhex(pw),sha2('real',512)),nick,date from class;
```
  
```

예제 - 웹사이트 가입 시 암호화, 로그인 시 평문 암호화(sha2(512))
- 보통 로그인 시 암호화를 하여 이루어지도록 함 → 두 번의 암호화가 이루어짐
- 복호화도 가능은 함
1. 가입 시 암호화(삽입 시)
  vi proc/join_proc.php
  $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=hex(aes_encrypt('$pw',sha2('sevas',512)))";
3. 복호화로 할 경우
  $sql=" select id,aes_decrypt(unhex(pw),sha2('sevas',512)) from class where id='$id' and aes_decrypt(unhex(pw),sha2('sevas',512))='$pw'";

웹 실습 환경 만들기

서버 (dns, 웹)
www.sevas10.com 192.168.10.10
db.sevas10.com 192.168.10.20
클라이언트 (db) dns는 192.168.10.10에 연결
계정 master 비번 asd123
db=sevas
Tb=class

서버(10)에 yum install httpd-* bind-* php-* --skip-broken
20에 dnf install maria*
설치 완료후 start , systemctl enable [서비스명]
/etc/named.conf 와 etc/named.rfc 수정 -> seavs10.com 만들기 (ns, www, db(20))
서버 - 방화벽 53/udp 열어주기 -> nslookup 으로 확인 , 방화벽 80/tcp 열어주기 -> 인터넷에서 접속으로 확인
서버 - var/www/html 이동 - index.php, login.html, join.html, proc/login_proc.php, proc/join_proc.php 생성
20 - vi etc/my.cnf

[mysqld]
character-set-server=utf8
[client]
default_character_set=utf8

20 - 방화벽 3306/tcp 추가
20 - mysql 접속
grant all privileges on sevas.* to master@'www.sevas10.com' identified by 'asd123';
grant all privileges on sevas.* to master@'192.168.10.10' identified by 'asd123';
flush privileges;
20 - create database sevas -> use sevas -> table class 생성

vi 코드들

  [db]
  
  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](http://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](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 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](http://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>
  
  =======================================================================================
  
  회원가입프로세스 ( proc/join_proc.php)
  
  <?php
  
  $id = $_POST['id'];
  $pw = $_POST['pw'];
  $nick = $_POST['nick'];
  
  mysql_connect ("[db.sevas10.com](http://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>";
  ?>

:) GITHUB: https://github.com/YJ2123412

이전 포스트

Zero Trust(제로 트러스트)

다음 포스트

암호화, 복호화 - mysql

보안

암호

암호화

복호화

실습

웹 실습 환경 만들기

Zero Trust(제로 트러스트)

공격 -XSS, Session Hijacking, CSRF

0개의 댓글