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:

sqlite-jdbc/Makefile

Lines 58 to 62 in 5105677

	perl -p -e "s/sqlite3_api;/sqlite3_api = 0;/g" \
	$(SQLITE_SOURCE)/sqlite3ext.h > $(SQLITE_OUT)/sqlite3ext.h
	# insert a code for loading extension functions
	perl -p -e "s/^opendb_out:/ if(!db->mallocFailed && rc==SQLITE_OK){ rc = RegisterExtensionFunctions(db); }\nopendb_out:/;" \
	$(SQLITE_SOURCE)/sqlite3.c > $(SQLITE_OUT)/sqlite3.c

[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;
    }

[jdev-2020]

The downcast is not needed.

final Connection connection = DriverManager.getConnection(":memory:");
if (connection.isWrapperFor(SQLiteConnection.class)) {
	DB db = connection.unwrap(SQLiteConnection.class).getDatabase();

	try {
		db.enable_load_extension(true);
		// sql queries to load extensions
	} finally {
		db.enable_load_extension(false);
	}
}