[Oracle] 컬럼 이미지 파일 저장 여부 확인하기

반응형

데이터베이스에 이미지를 저장할 때 일반적으로 BLOB(Binary Large Object) 데이터 타입을 사용하여 바이너리 데이터를 직접 저장하거나 이미지를 Base64 인코딩하여 CLOB(Character Large Object) 타입에 문자열로 저장한다.

이러한 방식으로 이미지를 저장하기 때문에 데이터베이스에 저장된 특정 컬럼의 데이터가 이미지인지 여부를 확인하려면 특별한 방법이 필요하다. 이 글에서는 Oracle 데이터베이스를 기준으로 이미지 파일의 저장 여부를 확인하는 방법과 그 과정에서 사용하는 시그니처(매직 넘버) 에 대해 정리한다.

---

이미지 파일의 시그니처란?

이미지 파일은 파일의 종류를 식별하기 위해 파일의 시작 부분에 고유한 시그니처(매직 넘버) 를 가지고 있다. 이를 활용하여 파일의 형식을 판별할 수 있다.

 

주요 이미지 파일 시그니처

• JPEG: FFD8FF
• PNG: 89504E47
• GIF: 47494638
• BMP: 424D

---

BLOB 컬럼에서 이미지 여부 확인하기

BLOB 컬럼에 저장된 데이터가 이미지인지 확인하려면 시그니처를 추출하여 비교하는 방법이 가장 정확하다.

 

DBMS_LOB.SUBSTR 함수

Oracle에서 DBMS_LOB.SUBSTR 함수를 사용하여 BLOB 데이터의 특정 부분을 추출할 수 있다.

 

DBMS_LOB.SUBSTR(blob_컬럼, 추출할_바이트_수, 추출을_시작할_위치)

 

 

RAWTOHEX 함수

RAWTOHEX 함수를 사용하여 추출한 RAW 데이터를 16진수 문자열로 변환할 수 있다.

SELECT CASE
           -- JPEG 이미지 시그니처 검사
           WHEN RAWTOHEX(DBMS_LOB.SUBSTR(IMAGE_COLUMN, 3, 1)) = 'FFD8FF' THEN 'JPEG 이미지'

           -- PNG 이미지 시그니처 검사
           WHEN RAWTOHEX(DBMS_LOB.SUBSTR(IMAGE_COLUMN, 4, 1)) = '89504E47' THEN 'PNG 이미지'

           -- GIF 이미지 시그니처 검사
           WHEN RAWTOHEX(DBMS_LOB.SUBSTR(IMAGE_COLUMN, 4, 1)) = '47494638' THEN 'GIF 이미지'

           -- BMP 이미지 시그니처 검사
           WHEN RAWTOHEX(DBMS_LOB.SUBSTR(IMAGE_COLUMN, 2, 1)) = '424D' THEN 'BMP 이미지'

           ELSE '이미지 아님'
       END AS IMAGE_TYPE
  FROM MY_TABLE;

• RAWTOHEX(DBMS_LOB.SUBSTR(IMAGE_COLUMN, N, 1)): IMAGE_COLUMN 컬럼에서 처음 N 바이트를 추출하여 16진수 문자열로 변환한다.
• 각 이미지 형식의 시그니처와 비교하여 이미지 유형을 판별한다.
• 일치하는 시그니처가 없으면 '이미지 아님'으로 표시한다.

각 이미지 형식별 시그니처 추출 방법

시그니처는 단순 영문, 숫자가 아닌 바이너리 데이터를 16진수로 변환하기 때문 두자리의 16진수 하나가 1바이트가 된다.

• JPEG 시그니처
  • 바이트 시퀀스: FF, D8, FF
  • 바이트 수: 3바이트
  • 16진수 문자열: FFD8FF (각 바이트를 두 자리 16진수로 표현)
• PNG 시그니처
  • 바이트 시퀀스: 89, 50, 4E, 47
  • 바이트 수: 4바이트
  • 16진수 문자열: 89504E47
• GIF 시그니처
  • 바이트 시퀀스: 47, 49, 46, 38
  • 바이트 수: 4바이트
  • 16진수 문자열: 47494638
• BMP 시그니처
  • 바이트 시퀀스: 42, 4D
  • 바이트 수: 2바이트
  • 16진수 문자열: 424D

---

CLOB 컬럼에서 Base64 인코딩된 이미지 여부 확인하기

CLOB 컬럼에는 일반적으로 텍스트 데이터가 저장되지만 이미지를 Base64 인코딩하여 문자열로 저장할 수 있다.

 

Base64 인코딩 패턴 검사

Base64 인코딩된 문자열은 특정한 문자 집합과 패딩 문자를 사용하므로 정규 표현식을 통해 검사가 가능하다.

'^[A-Za-z0-9+/]+={0,2}$'

 

• [A-Za-z0-9+/]+: Base64 인코딩에 사용되는 문자들이 하나 이상 연속적으로 나타남.
• ={0,2}: 패딩 문자 =가 0개에서 2개까지 나타날 수 있음.
• ^, $: 문자열의 시작과 끝을 의미하여, 전체 문자열이 패턴과 일치해야 함을 나타냄.

REGEXP_LIKE 함수

정규 표현식(Regular Expression) 패턴과 일치하는지 여부를 확인하는 데 사용되는 함수이다.

REGEXP_LIKE(검사할_문자열, 정규표현식_패턴)

 

SELECT CASE
           WHEN REGEXP_LIKE(DBMS_LOB.SUBSTR(CLOB_COLUMN, 20, 1), '^[A-Za-z0-9+/]+={0,2}$')
           THEN 'Base64 인코딩된 이미지'
           ELSE '이미지 아님'
       END AS DATA_TYPE
FROM MY_TABLE;

 

 

• DBMS_LOB.SUBSTR(DESCRIPTION, 20, 1): DESCRIPTION 컬럼의 처음 20자를 추출한다.
• REGEXP_LIKE: 추출한 문자열이 Base64 패턴과 일치하는지 검사한다.