SimpleSQL and SQLCipher

[trueh]

Hello:

I am interested in purchasing SimpleSQL but I have the requirement of encrypting the database which not supported by the standard SQLite. We had this same requirement in a previous project made with Unreal and we ended up using SQLCipher instead of SQLite to avoid paying 2000$ for SSE. After reading the documentation and FAQs my questions are the following:

• I would have to modify SimpleSQL to perform authentication just after connecting to the database. To do that I will need to call a couple of additional functions in the SQLCipher DLL as well as executing a PRAGMA when the database is created. The rest of the API is the same as SQLite's. Is it possible to do it?
• Are SQLite DLLs included in the package? If the answer is yes, how are they managed in runtime? Are they located in a resources folder in the project and then extracted somewhere else? Can I change those DLLs by the ones I will have to compile with SQLCipher?
• Does the ORM support nested objects? I mean, something like having an User class which contains and array of Permission objects?
• Which compilation options do the SQLite DLLs you provide use? (if you provide them). I'm trying to figure out the features that I should have available in a SQLCipher DLL compiled by me.

Thank you in advance and best regards.

[echo17]

I'm not sure I can answer all of your questions because I have not tried encryption through SQLCipher. I'll answer the best I can, though.

The SQLite dlls included with the package are downloaded already compiled from sqlite.org. They are embedded in the SimpleSQL runtime dll and extracted when the Plugin starts (in its Awake method). The source to the SimpleSQL code is included, so if you want to include your own dlls, you'll just need to replace the ones in the SimpleSQL_Runtime/Resources folder (being sure to rename them the same and making them an embedded resource).



You can add all the fields and methods you want to the ORM classes. The only requirement is that the fields that you want to store from a query need to have a generic get; set; combo for reflection to register the mapping. Like:


public class MyQuery
{
   public int MyID { get; set; } // will be mapped
   public string MyValue { get; set; } // will be mapped

   public float someOtherValue; // will not be mapped

   // ignored by the ORM mapper
   public void Process()
   {
       // do something
   }
}


I do not know what options the SQLite dlls used since I just downloaded fully compiled ones from sqlite.org.

SimpleSQL comes with a demo of one way you could encrypt your fields manually (instead of the entire database). Here are the scripts that are used in the plugin's encryption demo:

Encryption.cs (2.1 KB)
EncryptedData.cs (3.26 KB)

[echo17]

Here is the Cipher.cs script that is used as the backend encryption for these demos:

Cipher.cs (5.58 KB)

Any cipher could be used in place, and this is just one way to handle encrypting at the field level. I know this isn't exactly what your post was asking, but I wanted to show all the options available.

[trueh]

Thank you for the answer. It helps me a lot. I had not though about encrypting the fields instead of the whole database, but it is an option I can consider which is easier to implement. I will purchase SimpleSQL to give it a try

[echo17]

Great! Here is a link to the FAQ explaining how to modify the source code for SimpleSQL if you decide to compile and use different SQLite dlls (or make any changes to SimpleSQL): link