모의해킹 스터디 2주차: Database

 <현재 환경 세팅>

1. Virtual Box 7.1.4 + 우분투 리눅스 20.04

2. 우분투 APM (Apache2 + PHP + MySQL)

3. 기본 웹 루트 경로 /var/www/html

 

 

 

◈ MySQL + phpMyAdmin 기본 세팅하기

 

phpmyadmin 대문

 

일단 이 페이지에 도달하는 것부터가 문제의 시작이었다. phpmyadmin에 들어가기 위해 세팅을 따로 해야하는 줄 몰랐기 때문이다. 

 

그래서 일단 MySQL의 root 계정부터 만들고 

($ sudo /usr/bin/mysql -u root -p   ->   비번 입력)

 

https://kkotkkio.tistory.com/40

[우분투] Phpmyadmin 설치 및 설정 그리고 접속 방법

 

phpmyadmin 설치는 이 방법을 따랐다. 

 

phpmyadmin 을 설치하고 -> 아파치와 연동하고 -> 수정한 아파치 재시작하고 -> ip주소/phpmyadmin으로 접속하면 저 대문이 뜬다.

 

 

이게 끝이 아니라 계정 설정을 손봐야 했다. MySQL 계정 인증 방식에 어떤 문제가 있어서

phpmyadmin 과 함께 쓸 수 없었기 때문이다.

 

 

찾아본 바로는 MySQL 인증 방식은 2가지로 나뉜다.

 

1.  mysql_native_password : 터미널이 아닌 외부에서 사용하는 경우에 주로 사용. 비밀번호를 입력해야 접근 가능.
2. auth_socket : 터미널 내부에서 sudo mysql로 사용. OS의 유닉스 소켓으로 인증하기 때문에 비밀번호 입력X                                                            (터미널에서 root 계정 <#>으로 사용중이면 안해도 되고 일반 사용자 계정 <$>으로 사용 중이면 한 번은 비밀번호를 입력해야하는 듯)

 

MySQL은 기본적으로 auth_socket 방식으로 시작하게 되어있는지 phpmyadmin에 접속하기 위해선 mysql_native_password 방식으로 변경해야 했다.

1. 인증 플러그 확인하기 -> 해당 정보를 담은 테이블이 뜸

    SELECT user, host, plugin FROM mysql.server WHERE user = 'root';

2. 인증 플러그인 변경하기 -> auth_socket을 mysql_native_password로 변경

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '내 비번';
    (혹은 반대를 원한다면 ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;)

3. 변경사항 적용하기

    FUSH PRIVILEGES;
    
4. MySQL 나가기
 
    EXIT; (소문자 됨)

 

 

여기서 선택지가 갈리는 데,

 

• 터미널과 phpmyadmin 모두 mysql_native_password 방식을 사용하여 터미널에서도 비번 치고 들어가기

터미널 접속 시 아래 코드 입력하고 비번 쳐서 들어가야함. mysql 접근할 때마다 매번.

$ sudo mysql -u root -p

 

• phpmyadmin 전용 계정 생성

터미널 용, phpmyadmin 용 계정을 분리해서 터미널에선 로그인 없는 auth_socket 방식으로 접속하고

phpmyadmin 에선 mysql_native_password 방식으로 접속.

1. 계정 만들기

    CREATE USER '계정명'@'localhost' IDENTIFIED BY '내 비번';
    
2. 권한 부여하기

    GRANT ALL PRIVILEGES ON *.* TO '계정명'@'localhost' WITH GRANT OPTION;
    
3. 변경 사항 적용

    FLUSH PRIVILEGES;

 

이 두 가지 방법 중에서 나는 후자를 선택했다. 뭔가... 보안 신경쓴 너낌 좀 들지 않나 :) (아님 말고)

 

아무튼 이제 터미널에서는 auth_socket 방식으로 인증하는 계정을 사용할 것이고

phpMyAdmin에서는 mysql_native_password 방식으로 인증하는 계정을 사용할 예정이다.

 

 

아래는 GRANT 쿼리에 대한 해설이다.

GRANT   ALL   PRIVILEGES   ON   *.*   TO   '계정명'@'localhost'   WITH    GRANT   OPTION;

 

1)  특수한 권한을 제외한 모든 권한을 부여한다.   

               ->  여기서 '모든 권한'이란 MySQL에 저장된 데이터를 오른손으로 비비고 왼손으로 비빌 수 있는 지위를 주겠다는 뜻이다. 데이터베이스나 테이블의 내용을 수정, 삭제, 생성하는 일이 여기에 포함된다.

                  또한 '특수한 권한'이란 MySQL의 중요한 설정이나 프로세스를 제어한다든가, 서버가 돌아가는 환경의 파일을 읽고 쓸 수 있게 한다든가 (심지어 웹 루트 경로의 상위 디렉토리에 있는 파일을 포함한다), MySQL을 복제하는 권한이라든가, 서버를 종료시키는 권한 등을 말하는 듯하다. 간단히 축약해도 얼마나 특수하게 다뤄야하는 권한인지 알 수 있다.

 

 

2) 모든 데이터베이스의 모든 테이블에 대한. 

               -> on은 어떤 데이터에 해당 권한을 부여할지를 특정하는 표현이다. 여기서 문제는 저 뽕맞은 표정의 기호인데

(*) 얘는 '모든'이라는 뜻이고 (.) 얘는 테이블과 데이터베이스를 구분하는 역할을 하는 듯하다. 예를 들어 내가 '성적'이라는 데이터베이스에 모든 테이블에 대한 권한을 부여하고자 한다면 ON 성적.* 이라고 표현할 수 있다. 성적 데이터베이스의 1학기 테이블이라면 ON 성적.1학기 라고 표현하면 될 것이다.

 

 

3) 'localhost'로 접속하는 '계정명'에게

 

 

4) 다른 계정에도 권한을 부여할 수 있는 권한까지 합해서.

               -> 이 권한을 받은 '계정명'은 다른 사용자에 대해 자신이 갖고 있는 권한을 부여할 수 있는 능력을 갖게 되는 듯하다.

 

 

 

        즉, 'localhost'로 접속하는 '계정명'에게 모든 데이터베이스의 모든 테이블에 대한 거의 모든 권한을 부여하며

               거기다 다른 계정에도 권한을 부여할 수 있는 권한까지 주겠다.

 

는 뜻이다. 대충 개쩌는 능력을 너에게도 물려주마~ 로 받아들이면 될 것 같다.

 

mysql_native_password 인증 방식을 적용한 계정으로 phpMyAdmin에 접속해서

좌측 목록 상단의 '새로운'을 클릭한 결과이다.

 

이제 데이터베이스의 이름과 그 옆 정렬방식을 선택하고 '만들기'를 누르면 내가 실습할 데이터베이스가 만들어질 것이다.   

 

아래는 어쩌다 발견한 caching_sha2_password 인증 방식에 대한 짧은 글이다.

 지금 보니까 mysql_native_password가 아니라 caching_sha2_password 방식으로 되어있다. 

허둥지둥하지 않았던 이유는 여전히 로그인은 잘 되었기 때문이다.

 

 찾아보니 MySQL의 버전에 따라 어떤 인증 방식을 default로 둘 것인지가 정해져 있는 모양이다.

 

 

1) mysql_native_password는 MySQL 5.x 버전의 기본 인증이고 호환성을 장점으로 가지고 있는 듯하다.

                                              암호화 방식은 SHA1 해시 기반 방식을 따른다고 한다. (일단 그렇게 알고 넘어가자)

 

2) caching_sha2_password는 MySQL 8.x 이상 버전의 기본 인증 방식이고 암호화를 SHA-256 기반 해시 방식을

따른다고 한다. 정확히는 몰라도 앞선 방식에 나온 숫자를 x256 했으니 짱짱한 보안을 장점으로 가지고 있을 것이다.

 

 

 

MySQL의 버전은 아래 쿼리를 통해 알 수 있고 (mysql 8.0.39 나옴)

SELECT VERSION();

 

기본 인증 방식은 아래 쿼리를 통해 알 수 있었다. (caching_sha2_password)

SHOW VARIABLES LIKE 'default_authentication_plugin';

       -> 이거 치면 변수 이름이랑 안에 든 value랑 같이 나옴. 상수가 아니라 변수라는 건 수정 가능하다는 뜻이겟지. 하지만 아직은 수정할 필요를 못 느끼겠으니 냅두겠다.

 

 

 

 

 

◈ 기억나는 거 실습해보기

 

 

데이터베이스의 이름은 test 이고

위 테이블의 이름은 test_table로 설정하였다.

 

컬럼명을 설정하였다. 식별 번호와 이름, 점수, 비밀번호를 열이름으로 갖는다. 필드명, 속성명이라고도 하는 듯.

 

들어갈 데이터의 자료형을 정해주고 데이터의 길이도 설정한다.

 

키값이 될 idx 컬럼의 A_I 설정을 선택해주면 자동으로 기본키로 설정된다. 이제 해당 열은 자동으로 1부터 순차적으로 배당될 것이다. 

 

                ***기본키: 각 행을 고유하게 구분짓는 값. 안 겹침.

 

 

 

만들어진 테이블의 구조

여기서 데이터를 삽입할 수 있다. idx 열은 알아서 값을 주기 때문에 쓰지 않는다.

 

데이터 삽입한 test_table의 레알 테이블 모습

 

 

데이터는 물론 삽입 탭에서 손쉽게 넣을 수도 있다.

하지만 데이터베이스라 하면 쿼리를 빼놓을 수 없지 않은가.

 

 

• INSERT문

 

삽입 탭이 있는 곳 왼쪽에 SQL 탭에서 원하는 쿼리를 직접 실습해볼 수 있다. 쿼리를 써넣을 박스 아래에 보면 어떤 종류의 쿼리를 쓸지 선택할 수 있고 해당 쿼리 (예를 들어 INSERT)를 누르면 어떤 값을 넣어야하는지 알려주기까지 한다. 친절해서 눈물이 다 난다. 

 

test_table의 idx, name, score, pass 열에 null, rara, 55, 7777라는 값을 추가하라.

 

'라라'라는 녀석의 데이터를 넣었다. idx의 값은 마찬가지로 알아서 부여해주기 때문에 null 상태로 둔다. 나머지는 열 이름에 맞게 값을 넣는다.

 

여기서 주의할 점은 idx를 제외한 다른 열들의 자료형이 varchar, 문자열이기 때문에 '작은따옴표'를 꼭 표시해줘야한다는 거다.

내가 오류 나봐서 안다.

 

 

• UPDATE 문 (WHERE 조건문 사용)

 

test_table의 테이블에서 이름이 라라인 녀석의 이름을 가가로 바꿔라.

 

 

'라라' 녀석이 이름을 '가가'로 바꿨다며 날 찾아왔다. 라라 녀석은 레이디 가가의 팬이었던 것이다.

 

update 연산의 결과. 라라였던 것이 점수와 비번으로나마 그 흔적을 남기고 있다.

 

 

다른 쿼리도 실행해보았다.

 

 

 

• SELECT문 (WHERE 조건 사용)

test_table 테이블에서 이름이 doldol <<<이고!!!!>>> 점수가 98점인 행의 모든 열을 검색하여 보여주셈.

 

 

어떤 결과도 반환하지 못한다.

당연하다 돌돌이는 98점이 아니고 98점인 사람 중에 돌돌이는 없다. AND란 그런 것이다.

 

AND가 아니라 OR를 사용한다면?

test_table 테이블에서 이름이 doldol이<<<거나!!!!!!>>> 98점인 행의 모든 열을 검색하여 보여주셈.

 

 

2행의 결과가 표시되었다. 두 조건 모두 충족해야하는 AND 연산자와 달리

OR 연산자는 둘 중 하나라도 충족하면 검출해내기 때문이다.

그래서 이름이 돌돌인 행과 점수가 98점인 행 모두 반환된 것을 볼 수 있다.

 

 

 

× PHP와 DB연동하기 ×

 

사실 여기서부터는 기억에 의존한 부분이 거의 없다...

엄밀히 말해 "기억나는대로"가 아니지만 카테고리가 애매해지니까 그냥 쓸 생각이다.

 

PHP와 DB가 서로 소통을 하기 위해서는 PHP가 먼저 밝혀둬야 하는 정보가 있다.

 

DB 서버의 아이피 주소와 DB 사용자 이름, 그에 따른 비밀번호, 그리고 사용할 데이터베이스의 이름이다.

 

그니까 DB는 PHP의 마음의 문을 열기 위해서는 

내가 어디로 갈거고 누구이고 내 민증은 무엇입니다만 이 정보 좀 사용하여도 되겠읍니까? 하고 정중히 다가가야 한다는 것이다.

이 둘은 그렇게 말문을 트게 된다. 

(그래서 웹 서버의 shell이 털렸다는 건 DB도 털렸다는 뜻과 같다고 한다. 웹 쉘을 따서 이제 DB를 털려면 php 코드를 잘 살펴야 한다.)

 

 

php로 DB를 연동하는 과정

 

 

1) 우선 필요한 정보를 상수로 저장해둔다.

 

앞서 언급했듯이 DB서버의 아이피 주소, DB 사용자 이름, DB 비번, 사용할 DB 이름 정보가 필요하다.

그 정보를 define함수의 오른쪽에 있는 상수에 담는다. 변하지 않을 값이라 '상수'로 담는 것이고 지금은 값들이 그렇게 길지 않지만 어떤 환경에서는 길 수 있으니 여기 저기 사용할 때 불편함을 줄이려고 굳이 '담는' 것일 테다. 그리고 저 기밀 정보를 날 것의 형태로 이곳 저곳에서 사용하는 것보다는 한 곳에서만 명시하는 게 보안적으로도... 좋지 않을까..? (아님 말고)

 

 

 

2) 상수를 이용하여 DB에 접속할 수 있는 티켓을 변수에 저장한다.

(이 티켓 받아서 저장 안하면 땅에 버리는 거라는 노말틱 님의 말씀이 기억에 씨게 박혔다.)

 

mysqli_connect(저장한 상수들)로 입장권을 받고 $db_conn이라는 주머니에 잘 담아둔다.

 

 

 

3) (if문 생략) DB에 사용할 sql쿼리문을 변수에 저장한다.

 

(test라는 데이터베이스의)test_table 테이블에서 name이 doldol인 행의 모든 데이터를 가지고 온다.

 

 

 

4) 티켓과 쿼리문을 가지고 수행한 쿼리의 결과를 변수에 저장한다.

(땅바닥에 버릴 거에요? 아니잖아요~)

 

mysqli_query 변수에 DB 입장 티켓을 넣은 주머니와 쿼리문을 제출하여 쿼리 결과를 얻고 그 값을 $result 변수에 담는다.

 

 

 

5) (if문 생략) 쿼리 결과 중 한 행을 또 다시 변수에 저장한다.

 

이때 주의해야하는 포인트가 mysqli_fetch_array 메서드를 통해 행을 뽑지 않으면 원하는 결과가 나오지 않는다는 점이다.

var_dump($result)의 결과이다. 처리 결과가 mysqli_result 객체로 반환되어 속성이 출력된다. (아 근데 이건 미니 과제를 수행한 php코드 파일에서 실험한 결과이다. 다른 기능도 추가했던 터라 뭔가 다른 점이 있을 수 있다.)

 

 

 

6) echo 문을 통해 결과 행 중 score에 해당하는 데이터를 해당 서식에 맞게 출력한다.

결과 확인

 

 

 

아래는 echo문이 아니라 var_dump 메서드를 통해 $row 값을 출력한 결과이다.

 

var_dump($row);로 결과 출력해보기

var_dump($row);로 출력된 결과 화면

 

 

+ var_dump($row['name'])로 해봤는데 string[2] "65"로 출력된다. 

 여기서 string[2]는 "문자열, 길이 2" 라는 의미라고 한다.

 

 

사이사이 추가된 if문에 대한 글이다.

결론부터 말하자면 if문 두개는 디버깅 코드이다.

 

php 코드를 다 완성해놨으나 "SCORE : "라는 문구만 나오고 쿼리 결과는 나오지 않는 문제를 겪었고 GPT에 내가 쓴 코드와 내가 예상했던 결과, 그에 반하는 오류 상황을 설명했더니 두 디버깅 코드를 추가하여 어떤 단계에서 오류가 유발된 건지 확인하라는 답변이 왔기 때문에 써넣었었다.

if (!$db_conn) {
   die("Database connection failed : ". mysqli_connect_error());
}

주머니에 티켓이 없으면 (데이터베이스 연결이 문제라면) 오류문 출력하셈. 이라는 의미이다.

 

이 코드를 넣고 실행하자 HTTP 500 error가 떴고 이것은 서버에 문제가 있음을 알리는 오류 메세지라고 하였다.

이건 그리 큰 문제는 아니었다. 내 오타가 원인이 된 것이었다.

 

if (!$result) {
    die("Query Failed : ".mysqli_error($db_conn));
}

쿼리 연산 결과 넣은 주머니가 비어있으면 오류문 출력하셈. 이라는 의미이다.

die() 는 php 코드의 실행을 거기서 중지시키는 역할을 한다.

 

위 코드를 추가하였더니 "Access denied for user 'root'@'localhost'"라는 오류 메시지가 떴었다. 

위 메세지는 root 계정이 DB접근 권한이 없다는 뜻이다.

 

 

 

어떻게 어떻게 했었을 때는 결과값으로 NULL이 출력되었다. DB에 접속은 되었으나 가져올 데이터가 없다는 뜻으로 해석되었다. 나는 분명 phpMyAdmin으로 테이블을 만들었는데 말이지!!!!!

 

결국 어떻게 해결했냐면 DB_USER 에 root가 아니라

phpMyAdmin 용으로 만든 계정명을 넣었더니 해결되었다.

 

아무튼 저건 디버깅 코드라고 한다.

 

 

◈ 미니 미션

 

< doldol 학생의 점수는 80점입니다.> 를 출력시키기.

*이때 GET 방식으로 URL에 name=doldol이 떠야함.*

 

 

• GET 방식으로 데이터를 받으려면 URL에 직접 입력하는 방식이 있고, input를 통해 전달하는 방식이 있다. 난 인풋박스에 이름 받아서 돌돌이만이 아니라 솔솔이 시시, 가가의 성적도 받아보려고 한다.
• doldol이의 이름을 이용해 DB에 접근해서 해당하는 score 값을 가지고 와야한다. 그러려면 get으로 받은 돌돌이 이름을 변수에 저장해야 한다. 쿼리에는 $_GET['name'] 못 쓰는 듯. (해봤더니 구문 오류 떴다.)
• echo로 문장을 조합해 출력한다. 

 

 

저 조건들을 그때그때 적용시켜서 만든 코드가 이것이다.

<!DOCTYPE html>
<html>
    <form method ="GET">
        <input type="text" name="name" placeholder="이름"/>
        <input type="submit"/>
    </form>


<?php 

    define('DB_SERVER', 'localhost');
    define('DB_USER', '안알랴줌');
    define('DB_PASSWORD', '안알랴줌22');
    define('DB_NAME', 'test');

    $name = $_GET['name'];


    $db_conn = mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);

    if (!$db_conn) {
        die("Database connection failed : ". mysqli_connect_error());
    }

    $sql = "SELECT * FROM test_table WHERE name ='$name'";
    
    $result = mysqli_query($db_conn, $sql);

  
   if (!$result) {
        die("Query Failed : ".mysqli_error($db_conn));
    }
    
    $row= mysqli_fetch_array($result);
 
     if (!$row) {
        echo "이름을 입력하세요.";
      } else {

        echo $name. "학생의 점수는 ".$row['score']."점 입니다.";
      }
?>


</html>

 

 

그리고 아래가 결과 화면이다.

이름을 입력하기 전 화면이다.

 

doldol이의 이름을 입력하고 난 후의 화면이다. url에 get방식의 흔적이 보인다.

 

solsol이를 입력한 후의 화면이다.

 

sisi를 입력한 후의 화면이다.

 

gaga의 이름을 입력한 후의 화면이다. 가가야 분발해라.

 

 

◈ 놓친 부분 복습하기

 

Database 란 무엇인가

데이터베이스는 데이터를 저장하고 was와 소통함으로써 저장된 데이터의 활용을 가능하게 하는 데이터 묶음이라고 할 수 있다.

 

데이터베이스의 구조는 대략 이렇다

 

• Database : 데이터를 보관하는 큰 하나의 단위. 엑셀로 치자면 엑셀 파일 하나.
• Table : 데이터베이스에 속한 정보 묶음으로 로그인 정보나 게시판 용 데이터처럼 묶어서 관리하고 싶은 정보들을 분류하기에 용이하게 해준다. 데이터베이스가 엑셀 파일 하나를 의미한다면 테이블은 그 파일에 속한 sheet들 중 하나와 비슷하다.
• Column : 테이블의 열. 데이터 카테고리이자 종류를 구분할 수 있게 한다. 필드라고도 하고 속성이라고도 하는 듯하다.
• Row : 테이블의 행. 뭔가.. 서로 겹치지 않는 느낌이다. 튜플이나 레코드라고 표현하기도 하는 듯하다. 

 

수업에선 모두 다루지 않았지만 조금은 정리해두는 게 좋을 것 같아서 추가한다.

 

데이터베이스의 데이터를 다루기 위해서 필요한 언어를 SQL 이라고 한다.

이러한 SQL의 분류는 세 가지로 나뉘는데 그 종류는 아래와 같다.

 

1. DCL
2. DDL
3. DML

 

1. DCL은 데이터 제어 언어로 권한을 주거나 SQL 연산 결과를 저장 혹은 취소 등을 담당한다

앞서 다룬 GRANT 문이 그러하고 COMMIT, ROLLBACK, REVOKE 등이 포함된다.

 

2. DML은 데이터 조작 언어로 앞서 테이블에 돌돌, 솔솔, 시시, 가가의 정보를 조작할 때 사용한 언어들이 포함된다.

INSERT, UPDATE, SELECT, DELETE 등이 있다.

 

2. DDL은 데이터 정의 언어로 테이블을 생성하거나 삭제할 수 있고 테이블의 컬럼을 추가, 삭제, 변경하는 등에 쓰일 수 있다.

원래는 테이블 자체에 많이 사용하나보다 싶었는데 앞서 ALTER로 인증 방식을 변경한 걸 보면 더 넓은 용도로 사용되기도 하는 듯하다.

CREATE, ALTER, DROP 

 

 

 

데이터베이스 부분의 복습은 이 정도에서 마무리 해야겠다.