How can I make the modified code work in sqlite-jdbc-crypt?

[iimsoftadmin]

I modified some code in the SQLite3MultipleCiphers , then successfully compiled and installed it (make install). Then I recompiled sqlite-jdbc-crypt (make native), executed database operations through jdbc, but the modified code did not take effect. How can I make the modified code work in sqlite-jdbc-crypt?

OS： ubuntu 22.04
OpenJDK 64-Bit Server VM (build 11.0.20.1+1-post-Ubuntu-0ubuntu122.04, mixed mode, sharing)

[utelle]

I modified some code in the SQLite3MultipleCiphers,

What sort of modification did you apply?

then successfully compiled and installed it (make install).

This only tells you that you didn't make any syntactical mistakes. It doesn't tell you that your modifications were semantically correct.

Then I recompiled sqlite-jdbc-crypt (make native), executed database operations through jdbc, but the modified code did not take effect. How can I make the modified code work in sqlite-jdbc-crypt?

Without a crystal ball I can't answer your question. Without knowing which modifications you applied in which parts of the code it is impossible to tell why it doesn't work as expected.

Only if you show us your modifications and describe the expected behaviour, it may be possible to answer your question.

[Willena]

FYI, @iimsoftadmin created the same question at Willena/sqlite-jdbc-crypt/issues/103;
I gave an answer on how to integrate and build the SQLite JDBC Driver from an external shared library, of course assuming that all modification in the C source are correct.

[iimsoftadmin]

Thanks for your quick reply. I modified the sqlite3_key_v2 method to check the format of the key. I tested it with sqlite3shell, and the logic was correct.
`
./sqlite3shell test.db

sqlite> PRAGMA KEY=d00283889696e195ead52f7746cf4dbd38e496ab5ad860982808e8fc19efc7bfa82a8d45;
key is ok!ok

`

When I opened the database by setting the key through the URL, the corresponding check logic did not take effect. I checked the JDBC code and found that the URL parameter was executed through the SQL API.

stat.execute(String.format("pragma %s=%s", key, value));

So I would like to ask the following questions:

1. When setting PRAGMA KEY through the SQL API, will sqlite3_key_v2 be called in the end?
2. If sqlite3_key_v2 is not called, which function is more appropriate to add logic?
3. In order to compile sqlite-jdbc-crypt/libsqlitejdbc.so, how to generate files such as sqlite3mc_amalgamation.c and .h?
   

[Willena]

I modified the sqlite3_key_v2 method to check the format of the key.

Instead of creating a modified version of the native library, can't this format check be done in your java application (since you are using the jdbc ? )

sqlite> PRAGMA KEY=[...]
[...]
When setting PRAGMA KEY through the SQL API, will sqlite3_key_v2 be called in the end?

I'm pretty sure running PRAGMA statement in the shell or as SQL statement in through the jdbc has the same effect.

But , if your only way to check that call is the printf, I would flush the output to be sure.
Also, if you don't see the expected behaviour at all, you might not be using your .so

how to generate files such as sqlite3mc_amalgamation.c and .h?

There is a amalgamate.py script: https://github.com/utelle/SQLite3MultipleCiphers/blob/main/scripts/amalgamate.py

[iimsoftadmin]

@Willena Thanks.

But , if your only way to check that call is the printf, I would flush the output to be sure.

printf is not original code, it was temporarily added for shell testing to check the logic. Whether jdbc is called correctly can be determined by whether data can still be encrypted normally with an incorrectly formatted key.

Instead of creating a modified version of the native library, can't this format check be done in your java application (since you are using the jdbc ? )

My purpose is to prevent others from knowing the actual key of SQLite. The key input from Jdbc is encrypted, and the key passed to SQLite is decrypted data. Java can be easily decompiled and then viewed the decryption key and algorithm; decrypting in C language increases the difficulty of cracking. Of course, this method is not convenient for obtaining the latest code and version upgrades in the future.

[utelle]

I modified the sqlite3_key_v2 method to check the format of the key. I tested it with sqlite3shell, and the logic was correct. ` ./sqlite3shell test.db

As @Willena already pointed out the better approach would be to add such logic in your own application. Patching the SQLite sources is cumbersome and you have to redo it for every new version.

In your code you added a printf() to report the result of your check. That is really a bad idea, because your application may or may not have access to a console. If you really need to do such checks within the SQLite library, you could write a user defined function (UDF) that could then be invoked via an SQL SELECT statement.

When I opened the database by setting the key through the URL, the corresponding check logic did not take effect. I checked the JDBC code and found that the URL parameter was executed through the SQL API.

stat.execute(String.format("pragma %s=%s", key, value));

How do you know that your logic was not executed? PRAGMA key= calls sqlite3_key internally. That is, if your modified library version was used at all, the check took place. But probably you simply didn't see the result of your printf() statement, because it is unclear where to the output is actually sent (maybe to a Java log file, maybe not).

So I would like to ask the following questions:

1. When setting PRAGMA KEY through the SQL API, will sqlite3_key_v2 be called in the end?

Yes.

2. If sqlite3_key_v2 is not called, which function is more appropriate to add logic?

None. Do it in your application or in a UDF.

3. In order to compile sqlite-jdbc-crypt/libsqlitejdbc.so, how to generate files such as sqlite3mc_amalgamation.c and .h?

As @Willena already explained this project contains a Python script that you can use to generate the amalgamated source code.

[iimsoftadmin]

if you really need to do such checks within the SQLite library, you could write a user defined function (UDF) that could then be invoked via an SQL SELECT statement.

I don’t know much about SQLite, but UDFs should check each select, which will reduce performance. In addition, can UDFs change the select statement to be executed, such as decrypting the key and letting ’PRAGMA KEY=new value‘? Can you provide a UDF example as a reference? Thank you very much.

In your code you added a printf() to report the result of your check. That is really a bad idea, because your application may or may not have access to a console.

The printf was temporarily added for shell testing to check the logic. Whether jdbc is called correctly can be determined by whether data can still be encrypted normally with an incorrectly formatted key. And if the key is incorrect, SQLITE_ERROR will be returned.

[utelle]

My purpose is to prevent others from knowing the actual key of SQLite. The key input from Jdbc is encrypted, and the key passed to SQLite is decrypted data. Java can be easily decompiled and then viewed the decryption key and algorithm; decrypting in C language increases the difficulty of cracking.

Usually it's not a good idea to have the key somewhere hard coded in the sources of an application - at least from a security standpoint of view. Whether it is encrypted or not doesn't really matter.

I don’t know much about SQLite, but UDFs should check each select, which will reduce performance.

Why? Just like a call to PRAGMA key or sqlite3_key it would occur just once.

In addition, can UDFs change the select statement to be executed, such as decrypting the key and letting ’PRAGMA KEY=new value‘?

In principle, the UDF could call sqlite3_key internally.

Can you provide a UDF example as a reference?

UDFs are coded in C. Samples can be found in the source distribution of SQLite (see section Alternative Source Code Formats).

[iimsoftadmin]

Thanks @Willena & @utelle

This issue was solved through the following steps. Although there were some issues with specific programming principles, I understood how to modify SQLite3MultipleCiphers code and compile JDBC.

1. Use amalgamate.py to generate sqlite3mc_amalgamation.c and. h
2. Use the file generated above when compiling libsqlitejdbc.so

how to generate files such as sqlite3mc_amalgamation.c and .h?

There is a amalgamate.py script: https://github.com/utelle/SQLite3MultipleCiphers/blob/main/scripts/amalgamate.py