小巧、快速、可靠。
三选一。
应用程序定义的 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 函数。该系列中的每个成员都是一个围绕公共核心进行包装的成员。所有系列成员都完成相同的事情；它们只是具有不同的调用签名。

2.1. 通用参数

传递给 sqlite3_create_function() 接口系列的许多参数在整个系列中是通用的。

  1. db → 第 1 个参数始终是指向 数据库连接 的指针，该连接将使用自定义 SQL 函数。自定义 SQL 函数是为每个数据库连接单独创建的。没有创建跨所有数据库连接工作的 SQL 函数的简写机制。

  2. zFunctionName → 第 2 个参数是正在创建的 SQL 函数的名称。该名称通常为 UTF8，但对于 sqlite3_create_function16()，该名称应为本机字节序的 UTF16。

    SQL 函数名称的最大长度为 255 个字节的 UTF8。任何尝试创建具有更长名称的函数都将导致 SQLITE_MISUSE 错误。

    SQL 函数创建接口可以多次使用相同的函数名称进行调用。例如，如果两次调用具有相同的函数编号，但参数数量不同，那么将注册 SQL 函数的两个变体，每个变体接受不同的参数数量。

  3. nArg → 第 3 个参数始终是函数接受的参数数量。该值必须是 -1 到 SQLITE_MAX_FUNCTION_ARG（默认值为 127）之间的整数。值为 -1 表示 SQL 函数是可变参数函数，可以接受 0 到 SQLITE_MAX_FUNCTION_ARG 之间的任意数量的参数。

  4. eTextRep → 第 4 个参数是一个 32 位整数标志，其位表示关于新函数的各种属性。该参数的最初目的是使用以下常量之一来指定函数的首选文本编码

    所有自定义 SQL 函数都将接受任何编码的文本。编码转换将自动发生。首选编码仅指定函数实现针对其优化的编码。可以使用相同的名称和相同数量的参数，但不同的首选编码和不同的回调来实现函数，SQLite 将为输入编码最接近首选编码的回调集选择回调集。

    最近，第 4 个参数已被扩展，其中添加了其他标志位以表示有关函数的更多信息。其他位包括

    SQLite 的未来版本可能会添加更多位。

  5. 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. 回调

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 函数的内容。该上下文点将成为函数实现可能调用的许多其他例程的第一个参数，包括

该 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 对象的实例中。可以使用 sqlite3_value() 函数系列 从该对象中提取特定的 C 语言值。

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 保证 thta xFinal() 方法将被调用一次，即使查询中止也是如此。

2.3.3. 窗口函数回调

窗口函数 使用与聚合函数相同的 xStep() 和 xFinal() 回调，另外还有两个：xValue 和 xInverse。有关详细信息，请参见关于 应用程序定义的窗口函数 的文档。

2.3.4. 示例

SQLite 源代码中散布着数十个 SQL 函数实现，可作为示例应用程序。内置的 SQL 函数使用与应用程序定义的 SQL 函数相同的接口，因此内置函数也可以用作示例。在 SQLite 源代码中搜索“sqlite3_context”以查找示例。

3. 安全隐患

如果管理不当，应用程序定义的 SQL 函数可能会成为安全漏洞。例如，假设一个应用程序定义了一个新的“system(X)”SQL 函数，它将它的参数 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 子句中。

    换句话说，此设置要求仅从应用程序本身直接运行应用程序定义的函数，而不是作为执行其他无害查询的结果。

  2. 使用 PRAGMA trusted_schema=OFF SQL 语句禁用受信任的架构。这与前面的项目具有相同的效果，但不需要使用 C 代码，因此可以在用其他编程语言编写的程序中执行，这些程序无法访问 SQLite C 语言 API。

  3. 使用 -DSQLITE_TRUSTED_SCHEMA=0 编译时选项编译 SQLite。这将使 SQLite 默认情况下不信任架构内部的应用程序定义的函数。

  4. 如果任何应用程序定义的 SQL 函数具有潜在的危险副作用，或者如果在滥用时它们可能会将敏感信息泄露给攻击者，那么请使用“enc”参数上的 SQLITE_DIRECTONLY 选项标记这些函数。这意味着该函数永远无法从架构代码中运行，即使打开了受信任的架构选项。

  5. 除非您真的需要，并且已经仔细检查了实现并且确信即使它落入攻击者的控制下也不会造成任何伤害，否则永远不要用 SQLITE_INNOCUOUS 标记应用程序定义的 SQL 函数。

此页面上次修改于 2024-04-16 17:22:18 UTC