MySQL 양방향 암호화 복호화 insert 및 select 예제

MySQL 양방향 암호화 복호화 insert 및 select 예제

 

mysql 암호화와 복호화

 

 

안녕하세요.

 

오늘은 MySQL에서 테이블에 암호화된 데이터를 넣고, 다시 복호화하여 출력하는 작업 방법을 예제를 통해 실습해보도록 하겠습니다.

 

우선, 암호화와 복호화가 뭘까요?

 

 

암호화와 복호화

우선 암호화는 말 그대로 어떤 TEXT 데이터를 알아볼수 없게 암호처럼 만드는 것을 말합니다.

암호에는 단방향과 양방향이 있는데, 예를들어 'password'라는 누구나 알아볼 수 있는 <평문>을 암호화하면 <0D15222D3D8E40ECCE7AEDC3B8C4280D> 이런 식으로 변하게 되는데, 이걸 다시 'password'라는 <평문>으로 되돌릴 수 있냐 없냐의 차이입니다.

 

단방향 암호화는 주로 DB 사용자 비밀번호가 저장되어 있는 것처럼 저장 이후에 다시는 <평문>으로 볼 수 없는 케이스에서 사용을 주로 합니다. 일반적으로 MySQL 내에서 보면 대표적으로 md5(32 헥사) 또는 sha1(40 헥사) 등의 함수를 사용할 수 있습니다. 

 

MySQL에서 md5와 sha1을 사용하는 예제는 각각 아래와 같습니다.

select md5('test_password');
select sha1('test_password');

결과는 아래와 같이 나타납니다.

md5, sha1 사용예제

양방향 암호화는 이메일 주소나 전화번호, 전자서명 등과 같이 다시 사용할 가능성이 있어 암호화 이후 다시 복호화하여 <평문>으로 볼 필요가 있는 데이터를 저장할 때 사용을 합니다. 

 

양방향 암호화는 아래 실습에서 진행하도록 하겠습니다.

 

 

 

 

양방향 암호화 테이블 insert & select 실습

양방향 암호화 실습은 AES_ENCRYPT(암호화)와AES_DECRYPT(복호화) 함수를 이용해서 진행하겠습니다.

128비트 길이로 인코딩 되며, 수정을 통해 256비트까지 확장할 수 있습니다.

 

1. 테이블 생성

우선 암호화해서 데이터를 insert 하고 select 할 테이블을 생성합니다.

-- 암호화 test 테이블 생성
create table test_job.encrypt_test
(
    user_id varchar(100)
, password varchar(500)
) comment '암호화테스트' character set utf8 engine = InnoDB
;

2. 데이터를 암호화하여 insert

위와 같이 user_id와 password를 저장하는 테이블을 생성하고, 여기에 user_id와 password 중에 password를 암호화하여 저장하겠습니다.

 

암호화할 때는 AES_ENCRYPT 함수를 사용하게 되는데, 사용법은 아래와 같습니다.

hex(aes_encrypt('암호화 할 문자열','암호화키'))

암호화할 문자열에는 password 즉, 비밀번호 TEXT를 넣으면 되고, 암호화 키는 나중에 복호화할때 사용 할 key를 넣어주면 됩니다. 이 암호화키를 모르면 복호화 할 수 없습니다. 꼭 기억하고 있어야 합니다.

insert into test_job.encrypt_test values ('my_account', hex(aes_encrypt('test_password','a')));
insert into test_job.encrypt_test values ('my_account', hex(aes_encrypt('test_password','b')));
insert into test_job.encrypt_test values ('my_account', hex(aes_encrypt('test_password','c')));

자, 3개의 row를 저장하는데 똑같이 'test_password' 라는 TEXT를 paswword 컬럼에 insert 하지만, 각 행마다 뒤에 붙어 있는 'a', 'b', 'c' 가 다른걸 볼 수 있습니다. 암호화 키를 각각 상이하게 넣은것 입니다.

 

3. 데이터 확인

위와 같이 데이터를 넣었다면 잘 들어갔는지, 어떻게 암호화 되어 들어갔는지 확인을 해봐야 겠죠.

select * from test_job.encrypt_test;

결과는 아래와 같습니다.

암호화된 값

똑같이 'test_password'라는 TEXT를 암호화 하여 넣었지만 값이 다르게 출력 되는걸 확인 할 수 있습니다. 이는 저장하는 TEXT는 같지만 암호화 키가 달라서 그렇습니다. 암호화 키에 따라 값이 달라지는것을 확인 할 수 있습니다.

 

4. 복호화 select 테스트

자, 그럼 이젠 암호화된 TEXT를 복호화 하는 테스트를 해보겠습니다.

위에서도 말씀 드렸지만 복호화를 진행 할때는 AES_DECRYPT 함수를 사용 합니다. 사용법은 아래와 같습니다.

AES_DECRYPT(unhex(암호화된 값), '암호화키')

암호화를 할때 AES_ENCRYPT 함수를 먼저 사용하고 hex 함수를 사용 했었으니, 복호화를 할때는 unhex를 먼저하고 AES_DECRYPT 함수를 사용 하게 됩니다. 그리고 뒤에 암호화 할때 입력 했던 암호화 키를 넣어주면 됩니다.

 

위에서 암호화 할때 3개의 데이터에 각각 'a', 'b', 'c' 라는 암호화 키를 사용해서 암호화를 했는데, 우선 전 아래와 같이 'a' 라는 암호화 키를 사용해서 select를 해보겠습니다.

select user_id, AES_DECRYPT(unhex(password), 'a') from test_job.encrypt_test;

결과는 아래와 같습니다.

복호화 테스트

결과 처럼 하나의 행만 복호화가 된것을 확인 할 수 있습니다. 3개의 행의 암호화 키가 각각 다르기 때문에 'a' 라는 암호화 키를 사용한 하나의 행만 복호화가 되고, 나머지는 null이 반환 된것을 확인 할 수 있습니다.

 

다른 키를 넣어서 한번 더 테스트 해보겠습니다.

select user_id, AES_DECRYPT(unhex(password), 'b') from test_job.encrypt_test;

이번엔 'b' 암호화 키를 넣고 테스트를 합니다. 

결과는 아래와 같습니다.

복호화 테스트2

이번에는 두번째 행이 복호화 되어 나타났습니다.

 

생각보다 매우 간단하죠? 이렇게 이젠 우리는 어떤 데이터를 암호화 하여 입력 할 수도 있고 다시 복호화 하여 데이터를 확인 할 수도 있습니다.

 

필요하신 업무에 다양하게 사용 해보실 수 있길 바랍니다.

 

감사합니다.

 

 

 

 

by.sTricky