Not logged in
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 filename ?entrypoint?
The new API can also be accessed from SQL using the load_extension()
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:
sqlite3 *db, /* The database connection */
char **pzErrMsg, /* Write error messages here */
const sqlite3_api_routines *pApi /* API methods */
Some programs allow users to enter SQL statements then check those
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.
The following code is an example of how to build a loadable extension:
** The half() SQL function returns half of its input value.
static void halfFunc(
/* 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.
const sqlite3_api_routines *pApi
sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 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
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.
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
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> .load half.so testloadext_init
sqlite> select half(7);
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.