[MariaDB] VARBINARY 형 비밀번호 처리에 관하여

문제점 및 증상

• 비밀번호 사용을 위하여 내장된 password() 함수를 이용하다가, 보안 강화를 위하여 sha2-256을 적용
• 실제 비밀번호를 아무런 암호화 없이 HTTP를 통하여 주고 받기 보다는 클라이언트에서 비밀번호 자체를 sha2-256을 적용한 해쉬값을 전달하여 처리하는 방식으로 개선하는 도중에 클라이언트에서 받은 해쉬값을 MariaDB의 해쉬값과 비교하는 과정에서 비밀번호가 일치하지 않는 문제 발생

• 사용자 테이블
  

  CREATE TABLE `customer`
  (
      `customer_seq` INT(11) NOT NULL AUTO_INCREMENT COMMENT '고객번호',
      `login_id` VARCHAR(200) NOT NULL COMMENT '아이디' COLLATE 'utf8_unicode_ci',
      `password` VARBINARY(64) NULL DEFAULT NULL COMMENT '비밀번호. SHA2-256bit 해쉬 함수 결과값',
      `username` VARCHAR(200) NULL DEFAULT NULL COMMENT '고객이름' COLLATE 'utf8_unicode_ci',
  );
  

원인 분석

• 사용자 비밀번호를 업데이트 하는 과정에서 다음과 같은 쿼리를 사용을
  

      UPDATE `TIMING`.`CUSTOMER` SET
          password                = SHA2('password', 256)
      WHERE 1 = 1
          AND customer_seq = 18;
  

• 결과를 볼 때, 올바른 내용이 들어간 것 같았으나, MariaDB 연결 도구마다 BINARY 데이터의 표시 결과가 달랐음(HeidiSQL, dbForge Studio Express for MySQL)
• 그로 인하여 헷갈렸었는데, SHA2 함수의 결과가 실제로 BINARY 형태가 아니라, 문자열 형태여서 위와 같이 업데이트를 한 경우 문제가 됨

해결 방안

• 결과적으로 위 업데이트를 다음과 같이 변경하면, SHA2 함수의 호출 결과를 BINARY 형태로 저장하게 됨
  

      UPDATE `TIMING`.`CUSTOMER` SET
          password                = UNHEX(SHA2('password', 256))
      WHERE 1 = 1
          AND customer_seq = 18;
  

참고자료

• 12.13 Encryption and Compression Functions
• Insert hex values into MySql
• 12.4.2 The BINARY and VARBINARY Types
• PHP convert string to hex and hex to string