There is a new experimental API call sqlite3_load_extension() that does the loading. This API is experimental meaning that it is not guaranteed to be supported in future releases in a backwards compatible way. We reserve the right to make changes to the interface. After gaining some experience with the interface, we will probably remove the "experimental" label.
The new API is accessed from the shell using the ".load" command:
.load filename ?entrypoint?
The new API can also be accessed from SQL using the load_extension() function:
SELECT load_extension('filename');
SELECT load_extension('filename','entrypoint');
The filename is the name of the shared library or DLL. The entrypoint is the name of an initialization function within the shared library. If the entry point is omitted then a default entry point function named sqlite3_extension_init is called. Use of the default entry point name is recommended.
The entry point must be a function with the following prototype:
The db parameter is the database connection pointer returned from sqlite3_open(). The extension will likely want to pass this argument through to routines like sqlite3_create_function() and sqlite3_create_collation(). If an error occurs and pzErrMsg is not NULL, then the extension should use sqlite3_mprintf() to generate an error message and store that message at *pzErrMsg. The pApi argument contains pointers back to all of the APIs in the calling library. Extensions should reference the SQLite API through these pointers.int sqlite3_extension_init( sqlite3 *db, /* The database connection */ char **pzErrMsg, /* Write error messages here */ const sqlite3_api_routines *pApi /* API methods */ );
Security Considerations
Some programs allow users to enter SQL statements then check those statements using sqlite3_set_authorizer() to prevent attacks against the program. The new load_extension() SQL function described above could circumvent this protection and open holes in legacy applications. To avoid this, the entire extension loading mechanism is turned off by default. To enable the extension loading mechanism, first invoke this API:
int sqlite3_enable_load_extension(sqlite3 *db, int onoff);
The onoff parameter is true to enable extension loading and false to disable it. This allows programs that want to run user-entered SQL to do so safely by first turning off extension loading. Extension loading is off by default so that if an older program links against a newer version of SQLite it will not open a potential exploit.
Example Extension
The following code is an example of how to build a loadable extension:
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1
/*
** The half() SQL function returns half of its input value.
*/
static void halfFunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}
/* SQLite invokes this routine once when it loads the extension.
** Create new functions, collating sequences, and virtual table
** modules here. This is usually the only exported symbol in
** the shared library.
*/
int sqlite3_extension_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
){
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
return 0;
}
In the example above, the green text is boiler-plate that should appear in every loadable extension. The blue text is code you add to implement your extension. In the example, a single SQL function that multiplies its input by 0.5 is shown. A real loadable extension would probably do something more useful.
A extension is not limited to creating a single function or collating sequence. We expect that typically extensions will create multiple user functions, collating sequences, and/or virtual-table modules.
Note that the extension uses the header file "sqlite3ext.h" instead of "sqlite3.h". This is an imporant difference. Dynamically loaded extensions should always use "sqlite3ext.h" and statically linked additions to the library should use "sqlite3.h". If you want your code to work as either a statically linked or a dynamically loaded module, the you will need to use #ifdefs to #include the appropriate header file.
The SQLITE_EXTENSION_INIT1 and SQLITE3_EXTENSION_INIT2 symbols are C preprocessor macros that deal with redirecting the API routines through the function pointers in sqlite3_api_routines structure. You can look at the definitions of these macros in the sqlite3ext.h header file to find out exactly what they do, if you are curious. The simplest approach is just to use them as shown above.
How To Build a Loadable Extension Shared Library on Linux
0. untar latest sqlite3 source code in a new directory
1. cd to the newly untarred sqlite directory
2. Comment out the line in Makefile.in to enable loadable extensions:
# TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1
3. ./configure LIBS=-ldl && make sqlite3
4. export LD_LIBRARY_PATH="`pwd`:$LD_LIBRARY_PATH"
5. gcc -I`pwd` -shared src/test_loadext.c -o half.so
6. ./sqlite3
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> .load half.so testloadext_init
sqlite> select half(7);
3.5
Wish List
create table sqlite_extensions (
load INT, -- the order in which the extension
-- is to be loaded/initialized.
-- lowest number loads first.
-- if tied, order is alphabetical by name.
name TEXT, -- name of the extension shared library.
-- Note: both the path and suffix is NOT to be included in name.
init TEXT, -- name of the shared library entry point init function.
-- optional, may be null.
-- if null, the entry point function will be constructed
-- from name.
fini TEXT -- name of the shared library entry point unload function.
-- shared library unloading will be in the reverse order
-- of loading.
-- optional, may be null.
);