Feature Request: Exposing C function sqlite3_load_extension to Java

[Andy-2639]

I need to load a SQLite extension. With sqlite-jdbc, I can enable the SQL function load_extension to achieve this. However, this is not recommended as it increases the attack surface when a SQL injection is possible. (SQL injections should be avoided anyway but humans make mistakes ...)

SQLite provides the C function int sqlite3_load_extension(sqlite3 *db, const char *zFile, const char *zProc, char **pzErrMsg); https://sqlite.org/c3ref/load_extension.html to load extensions. This could be used to load extensions from Java but not from a SQL statement.

Loading of extensions can be enabled by C API and at the same time disabled by SQL queries. https://www.sqlite.org/c3ref/enable_load_extension.html https://www.sqlite.org/c3ref/c_dbconfig_enable_fkey.html (SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION)

[xerial]

Although the direct call of sqlite3_load_extension is not supported, you can register some UDFs as shown in the examples in https://github.com/xerial/sqlite-jdbc/blob/master/src/test/java/org/sqlite/UDFTest.java#L309

And also we are pre-loading some extension functions (math related stuffs) around here: https://github.com/xerial/sqlite-jdbc/blob/5105677bb66e0245bf76fc1175d0be3f30833c8e/Makefile#L58-L62

[xerial]

I think we have two (or three) options:

• If what you need to load is a commonly usable one, we can embed these extension
• Add your extension code loading step in the above Makefile, then build your own sqlite-jdbc
• Adding a JNI interface to call sqlite3_load_extension.

Anyway I don't have much interest in adding these options by myself, so PR based contribution is better for me.

[Andy-2639]

I'm going to look into adding a JNI interface.

The Extension I want to load is a slightly modified version of sqlite3_icu. (I renamed the function names so that the original functions are not overridden.) I don't think that it should be liked statically with sqlite-jdbc because it needs ICU which has a data library which is several MB big.

[xerial]

OK. I once tried to link ICU statically, but gave it up because of that reason you mentioned and thedifficulty of building ICU for various architectures. If load_extention works through JNI, it would be good for your purpose.

[Andy-2639]

I created PR #319 . I wrote test cases for loading SQLite extensions. I don't know how to integrate the building of the extension native libraries the best way. This is the reason why Travis build #491.1 failed.

[Andy-2639]

A possible solution is to use the enable_load_extension method to disable extension loading after loading them. The downcast however isn't ideal.

import java.sql.Connection;
import java.sql.SQLException;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteConnection;
import org.sqlite.core.DB;

    private Connection connect() throws SQLException {
        final Connection connection = (new SQLiteConfig()).createConnection(":memory:");
        final DB db = ((SQLiteConnection)connection).getDatabase();
        try {
            db.enable_load_extension(true);
            // sql queries to load extensions
        } finally {
            db.enable_load_extension(false);
        }
        return connection;
    }