애플리케이션 정의 SQL 함수

애플리케이션 정의 SQL 함수

SQLite를 사용하는 애플리케이션은 결과를 계산하기 위해 애플리케이션 코드를 다시 호출(callback)하는 커스텀 SQL 함수를 정의할 수 있어요. 이런 커스텀 SQL 함수는 애플리케이션 코드 안에 직접 내장하거나, 로더블 확장(loadable extension)으로 만들 수 있어요.

출처: Application-Defined SQL Functions

본문

1. 요약 (Executive Summary)

SQLite를 사용하는 애플리케이션은 결과를 계산하기 위해 애플리케이션 코드로 다시 호출하는 커스텀 SQL 함수를 정의할 수 있어요. 커스텀 SQL 함수 구현은 애플리케이션 코드 자체에 내장할 수 있고, 로더블 확장으로도 만들 수 있어요.

애플리케이션 정의(커스텀) SQL 함수는 sqlite3_create_function() 인터페이스 계열로 만들어요. 커스텀 SQL 함수는 스칼라 함수, 집계 함수(aggregate), 윈도우 함수(window function)가 될 수 있어요. 커스텀 SQL 함수는 0개부터 SQLITE_MAX_FUNCTION_ARG까지 인자 개수를 가질 수 있어요. sqlite3_create_function() 인터페이스는 새 SQL 함수의 처리를 수행하기 위해 호출되는 콜백을 지정해요.

SQLite는 커스텀 테이블-값 함수(table-valued function)도 지원하지만, 이것은 이 문서에서 다루지 않는 다른 메커니즘으로 구현돼요.

2. 새 SQL 함수 정의하기

sqlite3_create_function() 인터페이스 계열은 새 커스텀 SQL 함수를 만드는 데 사용돼요. 이 계열의 각 멤버는 공통 코어를 감싼 래퍼예요. 모든 계열 멤버는 같은 일을 수행하며, 단지 호출 시그니처만 다를 뿐이에요.

• sqlite3_create_function() → 원래 버전의 sqlite3_create_function()은 애플리케이션이 스칼라 또는 집계가 될 수 있는 단일 새 SQL 함수를 만들 수 있게 해 줘요. 함수 이름은 UTF8로 지정해요.
• sqlite3_create_function16() → 이 변형은 함수 이름 자체가 UTF8 문자열 대신 UTF16 문자열로 지정된다는 점을 제외하고 sqlite3_create_function() 원본과 똑같이 동작해요.
• sqlite3_create_function_v2() → 이 변형은 원래 sqlite3_create_function()과 같지만, 모든 sqlite3_create_function() 변형의 5번째 인자로 전달되는 sqlite3_user_data() 포인터의 소멸자(destructor) 포인터인 추가 매개변수를 포함해요. 그 소멸자 함수(비NULL이라면)는 커스텀 함수가 삭제될 때 호출돼요 — 보통 데이터베이스 연결이 닫힐 때요.
• sqlite3_create_window_function() → 이 변형은 원래 sqlite3_create_function()과 같지만, 서로 다른 콜백 포인터 집합 — 윈도우 함수 정의에 사용되는 콜백 포인터 — 를 받아요.

2.1. 공통 매개변수

sqlite3_create_function() 인터페이스 계열에 전달되는 많은 매개변수는 계열 전체에 걸쳐 공통이에요.

1. db → 1번째 매개변수는 항상 커스텀 SQL 함수가 동작할 데이터베이스 연결에 대한 포인터예요. 커스텀 SQL 함수는 각 데이터베이스 연결에 대해 별도로 만들어져요. 모든 데이터베이스 연결에서 동작하는 SQL 함수를 만드는 축약 메커니즘은 없어요.
2. zFunctionName → 2번째 매개변수는 만들어지는 SQL 함수의 이름이에요. 이름은 보통 UTF8이지만, sqlite3_create_function16()의 경우에는 네이티브 바이트 순서의 UTF16이어야 해요.

SQL 함수 이름의 최대 길이는 UTF8 255바이트예요. 더 긴 이름으로 함수를 만들려는 시도는 SQLITE_MISUSE 오류를 발생시켜요.

SQL 함수 생성 인터페이스는 같은 함수 이름으로 여러 번 호출될 수 있어요. 예를 들어 두 호출이 같은 함수 번호를 가지지만 인자 수가 다르다면, 각기 다른 인자 수를 받는 SQL 함수의 두 변형이 등록돼요.

1. nArg → 3번째 매개변수는 항상 함수가 받는 인자의 수예요. 값은 -1과 SQLITE_MAX_FUNCTION_ARG(기본값: 127) 사이의 정수여야 해요. -1은 SQL 함수가 0에서 SQLITE_MAX_FUNCTION_ARG 사이의 어떤 인자 수도 받을 수 있는 가변 인자(variadic) 함수임을 의미해요.
2. eTextRep → 4번째 매개변수는 비트가 새 함수의 다양한 속성을 전달하는 32-bit 정수 플래그예요. 이 매개변수의 원래 목적은 다음 상수 중 하나를 사용해 함수의 선호 텍스트 인코딩을 지정하는 것이었어요:

• SQLITE_UTF8
• SQLITE_UTF16BE
• SQLITE_UTF16LE

모든 커스텀 SQL 함수는 어떤 인코딩의 텍스트도 받아들여요. 인코딩 변환은 자동으로 일어나요. 선호 인코딩은 단지 함수 구현이 최적화된 인코딩을 지정할 뿐이에요. 같은 이름과 같은 인자 수를 가지지만 선호 인코딩과 함수 구현에 사용되는 콜백이 다른 여러 함수를 지정할 수 있고, SQLite는 입력 인코딩이 선호 인코딩과 가장 가깝게 일치하는 콜백 집합을 선택해요.

선호 텍스트 인코딩 플래그는 다음 속성 플래그 중 0개 이상과 OR-결합될 수 있어요:

• SQLITE_DETERMINISTIC
• SQLITE_DIRECTONLY
• SQLITE_INNOCUOUS
• SQLITE_SUBTYPE

추가 함수 속성 플래그 비트는 SQLite의 향후 버전에서 추가될 수 있어요.

1. pApp → 5번째 매개변수는 콜백 루틴으로 전달되는 임의의 포인터예요. SQLite 자체는 이 포인터로 아무것도 하지 않아요. 콜백에 사용 가능하게 하는 것과, 함수가 등록 해제될 때 소멸자로 전달하는 것 외에는요.

2.2. 같은 함수에 대한 sqlite3_create_function() 의 여러 호출

애플리케이션이 같은 SQL 함수에 대해 sqlite3_create_function()을 여러 번 호출하는 것은 흔해요. 예를 들어 SQL 함수가 2개 또는 3개의 인자를 받을 수 있다면, sqlite3_create_function()은 2-인자 버전에 대해 한 번, 3-인자 버전에 대해 두 번째로 호출돼요. 두 변형의 기본 구현(콜백)은 서로 다를 수 있어요.

애플리케이션은 같은 이름과 같은 인자 수를 가지지만 선호 텍스트 인코딩이 다른 여러 SQL 함수를 등록할 수도 있어요. 그런 경우 SQLite는 선호 텍스트 인코딩이 데이터베이스 텍스트 인코딩과 가장 가깝게 일치하는 버전의 콜백을 사용해 함수를 호출해요. 이렇게 하면 UTF8 또는 UTF16에 최적화된 같은 함수의 여러 구현을 제공할 수 있어요.

sqlite3_create_function()에 대한 여러 호출이 같은 함수 이름과 같은 인자 수, 같은 선호 텍스트 인코딩을 지정하면, 두 번째 호출의 콜백과 다른 매개변수가 첫 번째 것을 덮어쓰고, 첫 번째 호출의 소멸자 콜백(존재한다면)이 호출돼요.

2.3. 콜백 (Callbacks)

SQLite는 콜백 루틴을 호출하여 SQL 함수를 평가해요.

2.3.1. 스칼라 함수 콜백

스칼라 SQL 함수는 sqlite3_create_function()의 xFunc 매개변수에 있는 단일 콜백으로 구현돼요. 다음 코드는 인자를 그대로 반환하는 "noop(X)" 스칼라 SQL 함수의 구현을 보여줘요:

static void noopfunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  assert( argc==1 );
  sqlite3_result_value(context, argv[0]);
}

1번째 매개변수인 context는 SQL 함수가 호출된 콘텍스트를 설명하는 불투명한 객체에 대한 포인터예요. 이 context 포인터는 함수 구현이 호출하고 싶어할 많은 다른 루틴들의 첫 번째 매개변수가 돼요. 예를 들면:

• sqlite3_aggregate_context
• sqlite3_context_db_handle
• sqlite3_get_auxdata
• sqlite3_result_blob
• sqlite3_result_blob64
• sqlite3_result_double
• sqlite3_result_error
• sqlite3_result_error16
• sqlite3_result_error_code
• sqlite3_result_error_nomem
• sqlite3_result_error_toobig
• sqlite3_result_int
• sqlite3_result_int64
• sqlite3_result_null
• sqlite3_result_pointer
• sqlite3_result_subtype
• sqlite3_result_text
• sqlite3_result_text16
• sqlite3_result_text16be
• sqlite3_result_text16le
• sqlite3_result_text64
• sqlite3_result_value
• sqlite3_result_zeroblob
• sqlite3_result_zeroblob64
• sqlite3_set_auxdata
• sqlite3_user_data

sqlite3_result() 함수 계열은 스칼라 SQL 함수의 결과를 지정하는 데 사용돼요. 이 중 하나 이상이 함수 반환값을 설정하기 위해 콜백에서 호출되어야 해요. 특정 콜백에 대해 이 루틴 중 어느 것도 호출되지 않으면 반환값은 NULL이 돼요.

sqlite3_user_data() 루틴은 SQL 함수가 생성될 때 sqlite3_create_function()에 주어진 pArg 포인터의 복사본을 반환해요.

sqlite3_context_db_handle() 루틴은 데이터베이스 연결 객체에 대한 포인터를 반환해요.

sqlite3_aggregate_context() 루틴은 집계 함수와 윈도우 함수의 구현에서만 사용돼요. 스칼라 함수는 sqlite3_aggregate_context()를 사용할 수 없어요. sqlite3_aggregate_context() 함수는 완전성을 위해 인터페이스 목록에 포함된 것뿐이에요.

스칼라 SQL 함수 구현의 2번째와 3번째 인자인 argc와 argv는 SQL 함수 자체의 인자 수와 SQL 함수의 각 인자 값이에요. 인자 값은 어떤 데이터 타입도 될 수 있으므로 sqlite3_value 객체의 인스턴스에 저장돼요. 이 객체에서 특정 C 언어 값을 추출하려면 sqlite3_value() 인터페이스 계열을 사용해요.

2.3.2. 집계 함수 콜백

집계 SQL 함수는 xStep과 xFinal이라는 두 콜백 함수를 사용해 구현돼요. xStep() 함수는 집계의 각 행에 대해 호출되고, xFinal() 함수는 마지막에 최종 답을 계산하기 위해 호출돼요. 다음 (약간 단순화된) 내장 count() 함수 버전이 이를 보여줘요:

typedef struct CountCtx CountCtx;
struct CountCtx {
  i64 n;
};
static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
  CountCtx *p;
  p = sqlite3_aggregate_context(context, sizeof(*p));
  if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
    p->n++;
  }
}   
static void countFinalize(sqlite3_context *context){
  CountCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  sqlite3_result_int64(context, p ? p->n : 0);
}

count() 집계에는 두 버전이 있다는 것을 기억하세요. 인자가 없으면 count()는 행의 수를 반환해요. 인자가 하나면 count()는 그 인자가 non-NULL이었던 횟수를 반환해요.

countStep() 콜백은 집계의 각 행에 대해 한 번씩 호출돼요. 보시다시피, 인자가 없거나 하나의 인자가 NULL이 아니면 카운트가 증가해요.

집계의 step 함수는 항상 sqlite3_aggregate_context() 루틴을 호출해서 집계 함수의 영속 상태(persistent state)를 가져오는 것으로 시작해야 해요. step() 함수의 첫 호출에서 집계 콘텍스트는 N바이트 크기의 메모리 블록으로 초기화되는데, N은 sqlite3_aggregate_context()의 두 번째 매개변수이고 그 메모리는 0으로 설정돼요. 이후 모든 step() 호출에서 같은 메모리 블록이 반환돼요. 단, 메모리 부족 오류의 경우 sqlite3_aggregate_context()가 NULL을 반환할 수 있으므로, 집계 함수는 그 경우를 처리할 준비가 되어 있어야 해요.

모든 행이 처리된 후 countFinalize() 루틴이 정확히 한 번 호출돼요. 이 루틴은 최종 결과를 계산하고 sqlite3_result() 함수 계열 중 하나를 호출해 최종 결과를 설정해요. 집계 콘텍스트는 SQLite가 자동으로 해제하지만, xFinalize() 루틴은 반환하기 전에 집계 콘텍스트와 연관된 하위 구조를 정리해야 해요. xStep() 메서드가 한 번 이상 호출되면, 쿼리가 중단(abort)되더라도 SQLite는 xFinal() 메서드가 한 번은 호출될 것을 보장해요.

2.3.3. 윈도우 함수 콜백

윈도우 함수는 집계 함수가 사용하는 것과 같은 xStep()과 xFinal() 콜백에 더해 xValue와 xInverse라는 두 개를 더 사용해요. 자세한 내용은 애플리케이션 정의 윈도우 함수 문서를 참고해 주세요.

2.3.4. 예제 (Examples)

SQLite 소스 코드 곳곳에는 예제 애플리케이션으로 사용할 수 있는 수십, 수백 개의 SQL 함수 구현이 흩어져 있어요. 내장 SQL 함수는 애플리케이션 정의 SQL 함수와 같은 인터페이스를 사용하므로, 내장 함수도 예제로 사용할 수 있어요. SQLite 소스 코드에서 "sqlite3_context"를 검색하면 예제를 찾을 수 있어요.

3. 보안 영향 (Security Implications)

애플리케이션 정의 SQL 함수는 신중하게 관리하지 않으면 보안 취약점이 될 수 있어요. 예를 들어, 애플리케이션이 인자 X를 명령으로 실행하고 정수 결과 코드를 반환하는 새 "system(X)" SQL 함수를 정의한다고 가정해 보세요. 아마 구현은 이럴 거예요:

static void systemFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const char *zCmd = (const char*)sqlite3_value_text(argv[0]);
  if( zCmd!=0 ){
    int rc = system(zCmd);
    sqlite3_result_int(context, rc);
  }
}

이것은 강력한 부작용을 가진 함수예요. 대부분의 프로그래머는 사용에 대해 자연스럽게 조심하겠지만, 그저 사용 가능하게 해 두는 것의 해로움은 보지 못할 거예요. 하지만 그런 함수를 정의만 해도 큰 위험이 있어요. 애플리케이션 자체가 결코 호출하지 않더라도요!

애플리케이션이 시작할 때 보통 TAB1 테이블에 대해 쿼리를 실행한다고 가정해 보세요. 공격자가 데이터베이스 파일에 접근해 스키마를 다음과 같이 수정할 수 있다면:

ALTER TABLE tab1 RENAME TO tab1_real;
CREATE VIEW tab1 AS
   SELECT * FROM tab1_real
    WHERE system('rm -rf *') IS NOT NULL;

그러면 애플리케이션이 데이터베이스를 열고, system() 함수를 등록하고, "tab1" 테이블에 대해 무해한 쿼리를 실행하려고 할 때, 대신 작업 디렉토리의 모든 파일을 삭제하게 돼요. 아찔하죠!

이런 종류의 악행을 방지하기 위해, 자신만의 커스텀 SQL 함수를 만드는 애플리케이션은 다음 안전 예방 조치 중 하나 이상을 취해야 해요. 예방 조치를 많이 취할수록 좋아요:

1. 각 데이터베이스 연결이 열리자마자 sqlite3_db_config(db, SQLITE_DBCONFIG_TRUSTED_SCHEMA, 0, 0)를 호출해요. 이는 공격자가 데이터베이스 스키마를 수정해 은밀하게 호출할 수 있는 곳에서 애플리케이션 정의 함수가 사용되는 것을 막아요:

• VIEWs에서.
• TRIGGERs에서.
• 테이블 정의의 CHECK 제약 조건에서.
• 테이블 정의의 DEFAULT 제약 조건에서.
• 생성 컬럼(generated column)의 정의에서.
• 표현식에 대한 인덱스의 표현식 부분에서.
• 부분 인덱스(partial index)의 WHERE 절에서.

다시 말해, 이 설정은 애플리케이션 정의 함수가 다른 무해해 보이는 쿼리를 수행한 결과로가 아니라, 애플리케이션 자체에서 최상위로 실행하는 SQL에 의해서만 직접 실행되도록 요구해요.

1. PRAGMA trusted_schema=OFF SQL 문을 사용해 trusted schema를 비활성화해요. 이것은 이전 항목과 같은 효과가 있지만 C 코드를 사용할 필요가 없으므로, SQLite C 언어 API에 접근할 수 없는 다른 프로그래밍 언어로 작성된 프로그램에서 수행할 수 있어요.
2. -DSQLITE_TRUSTED_SCHEMA=0 컴파일 타임 옵션으로 SQLite를 컴파일해요. 이는 SQLite가 기본적으로 스키마 안의 애플리케이션 정의 함수를 신뢰하지 않게 만들어요.
3. 애플리케이션 정의 SQL 함수 중 부작용이 잠재적으로 위험한 것이 있거나, 오용될 경우 공격자에게 민감한 정보를 누출할 가능성이 있다면, "enc" 매개변수의 SQLITE_DIRECTONLY 옵션으로 그 함수들을 태그해요. 이는 trusted-schema 옵션이 켜져 있어도 그 함수가 스키마 코드에서 절대 실행될 수 없음을 의미해요.
4. 정말 필요하고 구현을 면밀히 확인해서 공격자의 통제 아래에 놓여도 해를 끼칠 수 없다고 확신하지 않는 한, 애플리케이션 정의 SQL 함수를 SQLITE_INNOCUOUS로 태그하지 마세요.

더 알아보기 (Learn more)

• sqlite3_create_function() C API
• Loadable Extensions
• Window Functions