Load SQLite extensions via SQLite C-API

[Andy-2639]

This allows to load SQLite extensions without enabling the SQL function load_extension.

[bradh]

Is there a way forward on this PR?

[StevenLooman]

I am interested in this as well. What is the way forward?

[alun]

I found it's relatively easy to do without this change:

1. Enable load extension in the connection properties like here https://github.com/xerial/sqlite-jdbc/blob/f948552fa06d475c4f1e72fdc2f72cbb18269217/src/test/java/org/sqlite/JDBCTest.java#L26

Also possible in DBeaver:

2. Use SQL to load the extension e.g. SELECT load_extension('/path/to/extension/md5', 'sqlite3_extension_init');

Note the shared library extension must be omitted.

P. S. I've compiled md5 from this source https://github.com/moisseev/sqlite-md5/blob/master/md5.c on macOS using clang CLI:

cc -c md5.c
cc -dynamiclib md5.o -o md5.dylib

[Andy-2639]

I found it's relatively easy to do without this change:

Yes, it is. It just allows extensions - arbitrary code - to be loaded into your process by normal SQL queries or SQL injection attacks. I mean, you have to take care of SQL injections anyway (prepared statements, ...) but humans make errors and enabling enable_load_extension increases the ability of an attacker in case of an possible injection attack IMO.

[alun]

@Andy-2639 cool, I see you point. I'd say those parts of a program calling load_extension stuff shouldn't be exposed to a non-authorised user's input data. And adding more native calls perhaps enlarges the attacking surface by itself. What about this function here https://github.com/xerial/sqlite-jdbc/blob/d4b19c11e1b569cdabce82823a2fad5927df983a/src/main/java/org/sqlite/core/NativeDB.java#L109 can it be used to disable the feature once all required extensions are loaded? Although it might be added after your PR.

[Andy-2639]

@alun Yeah, https://github.com/xerial/sqlite-jdbc/blob/d4b19c11e1b569cdabce82823a2fad5927df983a/src/main/java/org/sqlite/core/NativeDB.java#L109 should work. However, I don't see it exposed.

So I guess when https://github.com/xerial/sqlite-jdbc/blob/d4b19c11e1b569cdabce82823a2fad5927df983a/src/main/java/org/sqlite/SQLiteConnection.java#L25 exposes enable_load_extension and https://github.com/xerial/sqlite-jdbc/blob/d4b19c11e1b569cdabce82823a2fad5927df983a/src/main/java/org/sqlite/SQLiteConfig.java#L100 ~~would return a SQLiteConnection~~, the following would be possible:

1. Create SQLite connection
2. Enable extension loading
3. Load extension
4. Disable extension loading
5. Run queries with user provided data (still properly escaping/quoting it - prepared statements(!) - to protect from sql injections)

Edit: Changing the return type is not an option because it breaks API compatibility: https://wiki.eclipse.org/Evolving_Java-based_APIs_2#Evolving_API_interfaces_-_API_methods

[Andy-2639]

I sketched a possible solution to disable extension loading after loading them with the enable_load_extension method: https://github.com/xerial/sqlite-jdbc/issues/317#issuecomment-922339859

[gotson]

@Andy-2639 given this is 4 years old, it needs to be updated to the latest version. If you can do that i can review the code, i think it would be a good addition to this project.

[Andy-2639]

Sorry @gotson, as this PR needs the native libs rebuilt and I currently have no Linux system available to do this, caring about this PR takes too much time for me.

I'm fine with the workaround described in https://github.com/xerial/sqlite-jdbc/issues/317#issuecomment-922339859 despite the nasty downcast. In this PR, I really don't like the serialization I needed to store the extensions to load in the properties ...

[gotson]

this PR needs the native libs rebuilt and I currently have no Linux system available to do this,

Almost everything is built via Dockcross now, should work well on WSL on Windows too.

---

I will mark this as needing rework, if someone wants to pickup your work