DBMS_ASSERT

DBMS_ASSERT

DBMS_ASSERT 패키지는 사용자 입력 문자열을 SQL에서 안전하게 사용하기 위해 유효성을 검증하거나 리터럴, 식별자, 객체명을 안전하게 감싸는 유틸리티 함수들을 제공합니다. 이 패키지는 SQL Injection 방지를 위해 자주 사용되며, Oracle과의 호환성을 고려하여 PostgreSQL 확장으로 구현되었습니다.

함수	반환 타입	설명
ENQUOTE_LITERAL	TEXT	입력 문자열을 작은따옴표로 감싸는 함수입니다.
ENQUOTE_NAME	TEXT	입력 문자열을 큰따옴표로 감싸는 함수입니다.
NOOP	TEXT	입력 문자열을 그대로 반환하는 함수입니다.
QUALIFIED_SQL_NAME	TEXT	입력 문자열이 유효한 정규 SQL 식별자인지 검증하는 함수입니다.
SCHEMA_NAME	TEXT	입력 문자열이 현재 데이터베이스에 존재하는 스키마 이름인지 검증하는 함수입니다.
SIMPLE_SQL_NAME	TEXT	입력 문자열이 단순 SQL 식별자인지 검증하는 함수입니다.
SQL_OBJECT_NAME	TEXT	입력 문자열이 유효한 객체 이름인지 검증하는 함수입니다.

권한

설치는 super user에 이루어져야 하고 관리자가 DBMS_ASSERT 스키마 접근 권한 부여한 유저만 접근 가능합니다.

CREATE EXTENSION dbms_assert;

---

ENQUOTE_LITERAL

개요

입력된 문자열을 SQL 리터럴로 안전하게 감싸기 위해 작은따옴표(')로 감싸고, 내부에 존재하는 모든 작은따옴표를 이중 작은따옴표('')로 escape 합니다.

빈 문자열이나 NULL을 입력하면 빈 문자열을 작은 따옴표로 감싼 문자열’’을 반환합니다.

프로토타입

dbms_assert.enquote_literal(str TEXT) RETURNS TEXT

파라미터

파라미터	설명
str	작은따옴표로 감쌀 입력 문자열입니다.

예제

SELECT dbms_assert.enquote_literal('O''''Reilly'); -- [O''Reilly] 라는 문자열을 입력
-- 결과: 'O''''Reilly'

SELECT dbms_assert.enquote_literal('');
-- 결과: ''

SELECT dbms_assert.enquote_literal(NULL);
-- 결과: ''

---

ENQUOTE_NAME

개요

입력된 식별자(name)를 SQL 식별자 형식(큰따옴표”)으로 감싸는 함수입니다. 대문자로 일괄 변환할지 여부를 선택할 수 있으며, 기본값은 변환(true)이다. false를 입력하면 입력받은 문자열의 대소문자를 그대로 보존합니다.

빈 문자열이나 NULL을 입력하면 빈 문자열을 큰 따옴표로 감싼 문자열””을 반환합니다.

프로토타입

dbms_assert.enquote_name(str TEXT, capitalize BOOLEAN DEFAULT TRUE) RETURNS TEXT

파라미터

파라미터	설명
str	큰따옴표로 감쌀 입력 문자열입니다.
capitalize	대문자로 일괄 변환할 지 여부입니다; false 입력시 입력 문자열의 대소문자 그대로 유지합니다.

예제

SELECT dbms_assert.enquote_name('TableName');
-- 결과: "TABLENAME"

SELECT dbms_assert.enquote_name('TableName', false);
-- 결과: "TableName"

SELECT dbms_assert.enquote_name('');
-- 결과: ""

참고

• 작은 따옴표 '는 큰 따옴표 " 와 달리 문자열에 나타날 수 있습니다. 연속된 두 개의 작은 따옴표에서 첫 번째 작은 따옴표는 두 번째 작은 따옴표를 escape한다. POSIX에서의 백슬래시 \ 와 같은 방식입니다.

• 대문자 변환 수행 시, 영어 알파벳(a to z, A to Z)과 로컬 로케일 기반의 8비트 확장 문자셋(라틴어 문자 등)에 대해서만 대문자변경을 수행합니다.

---

NOOP

개요

입력 문자열을 아무런 검증 없이 그대로 반환하는 함수입니다. 단순히 DBMS_ASSERT 인터페이스의 형태를 맞추기 위한 pass-through 함수로 사용됩니다.

프로토타입

dbms_assert.noop(str TEXT) RETURNS TEXT

파라미터

파라미터	설명
str	입력 문자열입니다.

예제

SELECT dbms_assert.noop('test_input');
-- 결과: 'test_input'

SELECT dbms_assert.noop('');
-- 결과: ''

SELECT dbms_assert.noop(null);
-- 결과: null

---

QUALIFIED_SQL_NAME

개요

입력된 문자열이 유효한 정규 SQL 식별자(schema.object, pkg.proc, a.b.c, ...) 형식인지 검증합니다. Oracle에서는 큰따옴표를 포함한 식별자가 허용되지 않지만, PostgreSQL에서는 큰따옴표(")로 감싼 유효 식별자도 허용합니다.

• 각 이름은 일반 이름 또는 큰따옴표로 감싼 이름 가능합니다.

• 이름 내에는 _, $, # 허용합니다.

• 큰따옴표 내의 "는 ""로 escape 되어야 합니다.

• 빈문자열‘‘ 및 NULL 값은 허용되지 않으며 예외 발생합니다.

프로토타입

dbms_assert.qualified_sql_name(str TEXT) RETURNS TEXT

파라미터

파라미터	설명
str	입력 문자열입니다.

동작 규칙 요약

유형	허용	예시
점(.)으로 구분된 다단계 이름	✅	aaa.bbb.ccc.ddd
큰따옴표 식별자 포함	✅	aaa.bbb."ccc.dd"
특수문자 _, $, #	✅	a.b$.c_."d#d"
잘못된 문자 포함	❌	%, !, &, ...
숫자로 시작	❌	1broken
NULL 입력	❌	NULL

예제

-- 정상 케이스
SELECT dbms_assert.qualified_sql_name('aaa.bbb.ccc."aaaa""aaa"');
-- 결과: aaa.bbb.ccc."aaaa""aaa"

-- 정상 케이스
SELECT dbms_assert.qualified_sql_name('aaa.e$.s_."aa##aa""aaa"');
-- 결과: aaa.e$.s_."aa##aa""aaa"

-- 예외: 특수문자 %
SELECT dbms_assert.qualified_sql_name('aaa.bbb.cc%c."aaaa""aaa"');
-- ERROR: string is not qualified SQL name

-- 예외: 숫자로 시작
SELECT dbms_assert.qualified_sql_name('1broken');
-- ERROR: string is not qualified SQL name

-- 예외: NULL 입력
SELECT dbms_assert.qualified_sql_name(NULL);
-- ERROR: string is not qualified SQL name

---

SCHEMA_NAME

개요

입력된 문자열이 현재 데이터베이스 내에 존재하는 스키마 이름인지 검증합니다. 존재하지 않을 경우 예외를 발생시킵니다.

프로토타입

dbms_assert.schema_name(str TEXT) RETURNS TEXT

파라미터

파라미터	설명
str	입력 문자열입니다.

예제

SELECT dbms_assert.schema_name('pg_catalog'); -- OK

SELECT dbms_assert.schema_name('not_exist_schema'); -- 예외 발생

---

SIMPLE_SQL_NAME

개요

입력 문자열이 SQL의 단순 식별자(SIMPLE SQL NAME) 규칙에 부합하는지 검증합니다. Oracle에서는 알파벳, 숫자, _, $, # 문자만 허용되며, PostgreSQL에서는 큰따옴표로 감싼 식별자도 지원합니다.

• 일반 식별자: ASCII 알파벳으로 시작하고, 나머지 문자는 [a-zA-Z0-9_$#]만 허용합니다.

• 큰따옴표로 감싸인 식별자도 허용하며, 이 경우 내부의 "는 ""로 이스케이프된 형태여야 합니다.

• 빈문자열‘‘ 및 NULL 값은 허용되지 않으며 예외 발생합니다.

프로토타입

dbms_assert.simple_sql_name(str TEXT) RETURNS TEXT

파라미터

파라미터	설명
str	입력 문자열입니다.

동작 규칙 요약

유형	허용	예시
영문자로 시작하는 이름	✅	valid_name
큰따옴표 감싼 식별자	✅	"Aaa dg""hh shsh"
숫자로 시작	❌	1invalid
특수문자 시작	❌	-badname
NULL 입력	❌	NULL
내부 공백 및 허용 특수문자	✅	"Aaa d$g#h_h shsh"

예제

-- 정상 케이스
SELECT dbms_assert.simple_sql_name('"Aaa d$g#h_h shsh"');
-- 결과: "Aaa d$g#h_h shsh"

-- 정상 케이스
SELECT dbms_assert.simple_sql_name('valid_name');
-- 결과: valid_name

-- 정상 케이스
SELECT dbms_assert.simple_sql_name('"as""df"');
-- 결과: "as""df"

-- 예외 발생: 비허용 문자 포함
SELECT dbms_assert.simple_sql_name('ajajaj -- ajaj');
-- ERROR: string is not simple SQL name

-- 예외 발생: 숫자로 시작
SELECT dbms_assert.simple_sql_name('1broken');
-- ERROR: string is not simple SQL name

-- 예외 발생: NULL 입력
SELECT dbms_assert.simple_sql_name(NULL);
-- ERROR: string is not simple SQL name

---

SQL_OBJECT_NAME

개요

입력된 문자열이 현재 데이터베이스에 존재하는 유효한 SQL 객체 이름(예: 테이블, 뷰, 시퀀스, 함수 등)인지 확인합니다.

• "스키마"."객체" 형태 또는 스키마.객체 형태 모두 지원합니다.

• 존재하지 않는 객체명이나 큰 따옴표로 감쌌을 경우 대소문자 일치 실패 시 예외를 발생시킵니다.

• 빈 문자열, NULL 값 허용되지 않습니다.

입력 문자열이 데이터베이스 내 존재하는 객체 이름인지 검증합니다.어떤 종류의 객체(function, table, view, 등)에 대해 어떤 기준으로 확인하는지는 구현 기반 설명이 필요합니다.

프로토타입

dbms_assert.sql_object_name(str TEXT) RETURNS TEXT

파라미터

파라미터	설명
str	입력 문자열입니다.

동작 규칙 요약

유형	허용 여부	설명
존재하는 객체 이름	✅	pg_catalog.pg_class
대소문자 무시 식별자	✅	pg_CAtalog.pG_CLaSS
큰따옴표 감싼 정확한 식별자	✅	"pg_catalog"."pg_class"
대소문자 틀린 큰따옴표 식별자	❌	"pg_CAtaLOg"."PG_class"
존재하지 않는 객체	❌	dbms_assert.fooo
빈 문자열 / NULL	❌	'', NULL

예제

-- 존재하는 객체 (대소문자 무관)
SELECT dbms_assert.sql_object_name('pg_catalog.pg_class');
-- 결과: pg_catalog.pg_class

-- 존재하는 객체 (대소문자 무관)
SELECT dbms_assert.sql_object_name('pg_CAtalog.pG_CLaSS');
-- 결과: pg_CAtalog.pG_CLaSS

-- 대소문자 구분 명시 (큰따옴표 사용)
SELECT dbms_assert.sql_object_name('"pg_catalog"."pg_class"');
-- 결과: "pg_catalog"."pg_class"

-- ❌ 예외: 대소문자 틀림
SELECT dbms_assert.sql_object_name('"pg_CAtaLOg"."PG_class"');
-- ERROR: invalid object name

-- ❌ 예외: 존재하지 않는 객체
SELECT dbms_assert.sql_object_name('dbms_assert.fooo');
-- ERROR: invalid object name

-- ❌ 예외: 빈 문자열
SELECT dbms_assert.sql_object_name('');
-- ERROR: invalid object name

-- ❌ 예외: NULL 입력
SELECT dbms_assert.sql_object_name(NULL);
-- ERROR: invalid object name