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);
-- 결과: nullQUALIFIED_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 nameSCHEMA_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 nameSQL_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 nameLast updated