Пользовательские SQL-функции

1. Обзор

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

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

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

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

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

• 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(), передаваемого в качестве пятого аргумента во все варианты 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, за исключением того, что имя должно быть в формате UTF16 в родном порядке байтов для sqlite3_create_function16().

   Максимальная длина имени 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.

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

Вторым и третьим аргументами реализации скалярной SQL-функции являются argc (количество аргументов SQL-функции) и 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 WHERE system('rm -rf *') IS NOT NULL;

Тогда, когда приложение пытается открыть базу данных, зарегистрировать функцию system(), а затем выполнить невинный запрос к таблице "tab1", оно вместо этого удалит все файлы в своей рабочей директории. Ужас!

Чтобы предотвратить подобные злоупотребления, приложения, создающие собственные пользовательские SQL-функции, должны принять одно или несколько из следующих мер безопасности. Чем больше мер безопасности, тем лучше:

1. Вызовите sqlite3_db_config(db,SQLITE_DBCONFIG_TRUSTED_SCHEMA,0,0) для каждого соединения с базой данных как только оно будет открыто. Это предотвратит использование определяемых пользователем функций в местах, где злоумышленник может незаметно их вызвать, изменив схему базы данных:

   • В представлении.
   • В триггере.
   • В ограничениях проверки определения таблицы.
   • В ограничениях по умолчанию определения таблицы.
   • В определениях генерируемых столбцов.
   • В выражении части индекса по выражению.
   • В предложении WHERE частичного индекса.

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

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

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

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

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

Последнее изменение этой страницы: 2024-04-16 17:22:18 UTC