Пользовательские функции SQL, определяемые приложением

1. Краткое изложение

Приложения, использующие SQLite, могут определять пользовательские функции SQL, которые вызывают код приложения для вычисления результатов. Реализации пользовательских функций SQL могут быть встроены непосредственно в код приложения или предоставляться в виде загружаемых расширений.

Пользовательские функции SQL создаются с помощью семейства интерфейсов sqlite3_create_function(). Пользовательские функции SQL могут быть скалярными, агрегатными или оконными функциями. Пользовательские функции SQL могут принимать любое количество аргументов от 0 до SQLITE_MAX_FUNCTION_ARG. Интерфейс sqlite3_create_function() задаёт обратные вызовы, которые выполняют обработку для новой функции SQL.

SQLite также поддерживает пользовательские табличные функции, но они реализованы с помощью другого механизма, который в этом документе не рассматривается.

2. Определение новых функций SQL

Семейство интерфейсов sqlite3_create_function() используется для создания новых пользовательских функций SQL. Каждый элемент этого семейства является обёрткой над общей базовой реализацией. Все элементы семейства выполняют одну и ту же задачу, но имеют разные сигнатуры вызова.

• sqlite3_create_function() → Исходная версия sqlite3_create_function() позволяет приложению создать одну новую функцию SQL, которая может быть скалярной или агрегатной. Имя функции задаётся в кодировке UTF8.

• sqlite3_create_function16() → Этот вариант работает точно так же, как исходная версия sqlite3_create_function(), за исключением того, что имя самой функции задаётся строкой UTF16, а не строкой UTF8.

• sqlite3_create_function_v2() → Этот вариант работает так же, как исходная версия sqlite3_create_function(), но принимает дополнительный параметр — указатель на деструктор для указателя sqlite3_user_data(), передаваемого в качестве 5-го аргумента всем вариантам sqlite3_create_function(). Эта функция-деструктор (если она не равна NULL) вызывается при удалении пользовательской функции — обычно при закрытии соединения с базой данных.

• sqlite3_create_window_function() → Этот вариант работает так же, как исходная версия sqlite3_create_function(), но принимает другой набор указателей на обратные вызовы — указатели, используемые в определениях оконных функций.

2.1. Общие параметры

Многие параметры, передаваемые семейству интерфейсов sqlite3_create_function(), являются общими для всего семейства.

1. db → Первый параметр всегда является указателем на соединение с базой данных, в котором будет работать пользовательская функция SQL. Пользовательские функции SQL создаются отдельно для каждого соединения с базой данных. Сокращённого способа создания функций SQL, работающих во всех соединениях с базами данных, не существует.

2. zFunctionName → Второй параметр — имя создаваемой функции SQL. Обычно имя задаётся в кодировке UTF8, за исключением sqlite3_create_function16(), для которого имя должно быть в кодировке UTF16 с порядком байтов, принятым в системе.

   Максимальная длина имени функции SQL — 255 байт в кодировке UTF8. Любая попытка создать функцию с более длинным именем приведёт к ошибке SQLITE_MISUSE.

   Интерфейсы создания функций SQL можно вызывать несколько раз с одним и тем же именем функции. Например, если два вызова имеют одинаковый номер функции, но разное количество аргументов, будут зарегистрированы два варианта функции SQL, каждый из которых принимает своё количество аргументов.

3. nArg → Третий параметр всегда указывает количество аргументов, принимаемых функцией. Значение должно быть целым числом от -1 до SQLITE_MAX_FUNCTION_ARG (значение по умолчанию: 127). Значение -1 означает, что функция SQL является функцией с переменным числом аргументов и может принимать любое количество аргументов от 0 до SQLITE_MAX_FUNCTION_ARG.

4. eTextRep → Четвёртый параметр — 32-разрядный целочисленный флаг, биты которого задают различные свойства новой функции. Изначально этот параметр предназначался для указания предпочтительной кодировки текста функции с помощью одной из следующих констант:

   • SQLITE_UTF8
   • SQLITE_UTF16BE
   • SQLITE_UTF16LE
   Все пользовательские функции SQL принимают текст в любой кодировке. Преобразования кодировки выполняются автоматически. Предпочтительная кодировка лишь указывает кодировку, для которой оптимизирована реализация функции. Можно задать несколько функций с одинаковыми именем и количеством аргументов, но с разными предпочтительными кодировками и разными обратными вызовами. В этом случае SQLite выберет набор обратных вызовов, для которого кодировки входных данных наиболее близки к предпочтительной кодировке.

   Флаг предпочтительной кодировки текста можно объединить с помощью побитового ИЛИ с одним или несколькими из следующих флагов свойств:

   • SQLITE_DETERMINISTIC
   • SQLITE_DIRECTONLY
   • SQLITE_INNOCUOUS
   • SQLITE_SUBTYPE

   В будущих версиях SQLite могут появиться дополнительные биты флагов свойств функций.

5. pApp → Пятый параметр — произвольный указатель, передаваемый процедурам обратного вызова. Сам SQLite ничего не делает с этим указателем, кроме как предоставляет его обратным вызовам и передаёт деструктору при отмене регистрации функции.

2.2. Несколько вызовов sqlite3_create_function() для одной функции

Приложения часто вызывают sqlite3_create_function() несколько раз для одной и той же функции SQL. Например, если функция SQL может принимать 2 или 3 аргумента, sqlite3_create_function() вызывается один раз для версии с 2 аргументами и второй раз для версии с 3 аргументами. Базовая реализация (обратные вызовы) для этих вариантов может различаться.

Приложение также может зарегистрировать несколько функций SQL с одинаковыми именем и количеством аргументов, но разной предпочтительной кодировкой текста. В этом случае SQLite вызовет функцию, используя обратные вызовы той версии, чья предпочтительная кодировка текста наиболее близка к кодировке текста базы данных. Таким образом можно предоставить несколько реализаций одной функции, оптимизированных для UTF8 или UTF16.

Если несколько вызовов sqlite3_create_function() задают одинаковые имя функции, количество аргументов и предпочтительную кодировку текста, обратные вызовы и другие параметры второго вызова заменяют параметры первого, а обратный вызов-деструктор первого вызова (если он существует) выполняется.

2.3. Обратные вызовы

SQLite вычисляет функцию SQL, вызывая процедуры обратного вызова.

2.3.1. Обратный вызов скалярной функции

Скалярные функции SQL реализуются одним обратным вызовом в параметре xFunc функции sqlite3_create_function(). Следующий код демонстрирует реализацию скалярной функции SQL "noop(X)", которая просто возвращает свой аргумент:

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

Первый параметр, context, — это указатель на непрозрачный объект, описывающий контекст вызова функции SQL. Этот указатель контекста становится первым параметром многих других процедур, которые могут понадобиться реализации функции, в том числе:

Семейство функций sqlite3_result() используется для задания результата скалярной функции SQL. Чтобы задать возвращаемое функцией значение, обратный вызов должен вызвать одну или несколько таких функций. Если в конкретном обратном вызове не вызывается ни одна из этих процедур, возвращаемым значением будет NULL.

Процедура sqlite3_user_data() возвращает копию указателя pArg, переданного в sqlite3_create_function() при создании функции SQL.

Процедура sqlite3_context_db_handle() возвращает указатель на объект соединения с базой данных.

Процедура sqlite3_aggregate_context() используется только при реализации агрегатных и оконных функций. Скалярные функции не могут использовать sqlite3_aggregate_context(). Функция sqlite3_aggregate_context() включена в список интерфейсов только для полноты.

Второй и третий аргументы реализации скалярной функции SQL, argc и argv, — это количество аргументов самой функции 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() возвращает количество строк, в которых этот аргумент не равен NULL.

Обратный вызов countStep() выполняется для каждой строки агрегата. Как видно, счётчик увеличивается, если аргументов нет или единственный аргумент не равен NULL.

Функция шага агрегата всегда должна начинаться с вызова процедуры sqlite3_aggregate_context(), чтобы получить постоянное состояние агрегатной функции. При первом вызове функции step() контекст агрегата инициализируется блоком памяти размером N байт, где N — второй параметр sqlite3_aggregate_context(), а память обнуляется. При всех последующих вызовах функции step() возвращается тот же блок памяти. Однако sqlite3_aggregate_context() может вернуть NULL при нехватке памяти, поэтому агрегатные функции должны быть готовы обработать этот случай.

После обработки всех строк процедура countFinalize() вызывается ровно один раз. Она вычисляет окончательный результат и вызывает одну из функций семейства sqlite3_result(), чтобы задать его. SQLite автоматически освободит контекст агрегата, однако процедура xFinalize() должна очистить все вложенные структуры, связанные с контекстом агрегата, прежде чем завершить работу. Если метод xStep() был вызван один или несколько раз, SQLite гарантирует, что метод xFinal() будет вызван в любом случае, даже если выполнение запроса прервётся.

2.3.3. Обратные вызовы оконной функции

Оконные функции используют те же обратные вызовы xStep() и xFinal(), что и агрегатные функции, а также ещё два: xValue и xInverse. Дополнительные сведения см. в документации по оконным функциям, определяемым приложением.

2.3.4. Примеры

В исходном коде SQLite разбросаны десятки реализаций функций SQL, которые можно использовать в качестве примеров. Встроенные функции SQL используют тот же интерфейс, что и функции SQL, определяемые приложением, поэтому они тоже могут служить примерами. Чтобы найти примеры, выполните поиск по строке "sqlite3_context" в исходном коде SQLite.

3. Последствия для безопасности

Пользовательские функции SQL могут стать уязвимостями безопасности, если не управлять ими должным образом. Предположим, например, что приложение определяет новую функцию SQL "system(X)", которая запускает аргумент X как команду и возвращает целочисленный код результата. Возможно, её реализация выглядит так:

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) для каждого соединения с базой данных сразу после его открытия. Это не позволит использовать функции, определяемые приложением, в местах, где злоумышленник может незаметно вызвать их, изменив схему базы данных:

   • В представлениях (VIEW).
   • В триггерах (TRIGGER).
   • В ограничениях CHECK определения таблицы.
   • В ограничениях DEFAULT определения таблицы.
   • В определениях генерируемых столбцов.
   • В выражениях индексов по выражениям.
   • В предложении WHERE частичного индекса.

   Иными словами, этот параметр требует, чтобы функции, определяемые приложением, запускались только непосредственно из SQL верхнего уровня, выполняемого самим приложением, а не в результате выполнения какого-либо другого, на первый взгляд безобидного запроса.

2. Используйте оператор SQL PRAGMA trusted_schema=OFF, чтобы отключить доверенную схему. Он даёт тот же эффект, что и предыдущий пункт, но не требует использования кода на C, поэтому может применяться в программах, написанных на других языках программирования, которые не имеют доступа к API SQLite на языке C.

3. Скомпилируйте SQLite с параметром компиляции -DSQLITE_TRUSTED_SCHEMA=0. Это заставит SQLite по умолчанию не доверять функциям, определяемым приложением и используемым в схеме.

4. Если пользовательские функции SQL могут иметь опасные побочные эффекты или потенциально раскрыть злоумышленнику конфиденциальную информацию при неправильном использовании, пометьте эти функции параметром SQLITE_DIRECTONLY в параметре "enc". Это означает, что функцию нельзя будет вызвать из кода схемы, даже если включён параметр доверенной схемы.

5. Не помечайте пользовательскую функцию SQL флагом SQLITE_INNOCUOUS, если в этом нет действительной необходимости и вы внимательно не проверили реализацию, удостоверившись, что она не может причинить вред даже в случае, если окажется под контролем злоумышленника.