Has anyone compiled this gem with SEE (SQLite Encryption Extension)?

[slithernix]

Hi, I'm developing a local (non-internet) rails app and I'd like to use the SEE with SQLite. It looks like a pretty attractive solution to me for a number of reasons. I'd like to hear if anybody has done this successfully or otherwise. It looks like just a matter of dropping in the SEE .c code to the ext/libsqlite directory and compiling. I'm not sure that's enough though as I have never used this software and as a small-time solo operator I don't want to shell out a couple Gs just to end up in a quagmire.

Thoughts?

[flavorjones]

@slithernix I wrote up some instructions for loading extensions at #383 which I think should probably be helpful for you. If it is, I'd love to hear feedback on it so that I can add it to the README.

[slithernix]

So, I pulled the trigger on a license for it cause I just know it is the best solution for this very unique set of circumstances. I will be trying to get it together this week with the gem and give you feedback.

[slithernix]

alright so i compiled SEE with :

gcc -fPIC -shared -o libsee.so sqlite3-see-aes256-ofb.c

on my mac, then tried gem install sqlite3, then the instructions in that issue you linked to, and i got:

[5] pry(main)> db.load_extension("/Users/theguy/Downloads/see-sources/libsee.so")
RuntimeError: dlsym(0x7ff910e92470, sqlite3_see_init): symbol not found

I'm gonna try on a Linux box in a minute and see if I get better results. Linux is the target deployment platform anyways so I am less concerned with macOS.

[slithernix]

similar thing on Linux:

[6] pry(main)> db.load_extension("/home/jguy/working/see-sources/libsee.so")
RuntimeError: /home/guy/working/see-sources/libsee.so: undefined symbol: sqlite3_see_init
from (pry):6:in `load_extension'

[slithernix]

this extension is designed to a drop in replacement for sqlite.c. wouldn't it be better if i just replace sqlite.c in the gem's source in /ext? the thing is, when i tried that earlier, it still downloaded the sqlite distro from sqlite.org when I ran the gem build. did i miss something to force it to look at the local ext?

[flavorjones]

Ah, I have no idea what SEE is, I assumed it was an extension because, well, the name says "extension". Sorry if that ended up being misleading.

If it's a drop-in replacement then, well, you should check the documentation and see how to compile and install it onto your system somewhere. Let's imagine that directory is "/opt/see" (so you should see directories like "/opt/see/lib" and "/opt/see/include").

Then you should install this gem like this:

gem install sqlite3 --platform=ruby -- --enable-system-libraries --with-opt-dir=/opt/see

Explanation:

• use --platform=ruby to avoid the precompiled native gems (see the README)
• the -- separates arguments passed to "gem install" from arguments passed to the C extension builder
• use --enable-system-libraries to avoid the vendored sqlite3 source
• use --with-opt-dir=/path/to/installation to point the build process at the desired header files and shared object files

Let me know if that works?

[slithernix]

SEE is the official encryption add on for SQLite put out by the actual SQLite org. it is licensed software that one pays a one time fee for a lifetime source code license. this is the wiki:

https://sqlite.org/see/doc/release/www/readme.wiki

What is supposed to be in /opt/see in your example? I tried it with the amalgamation in there, the shell with added crypto functionality, as well as a libsee shared object, and no dice. no errors during compilation or anything like that but, i create the db with the upgraded sqlite3 shell:

⮕  ./sqlite3 -key testing123 /tmp/bullshit.db
SQLite version 3.42.0 2023-05-16 13:13:42 with the Encryption (see-aes256-ofb)
Copyright 2016 Hipp, Wyrick & Company, Inc.
Enter ".help" for usage hints.

sqlite> create table numbers (
    name varchar(30),
    val int
  );(x1...> (x1...> (x1...>
sqlite> insert into numbers values (1, 3);
sqlite> select * from numbers;
1|3
sqlite> .exit

then confirm encryption works as intended:

⮕  ./sqlite3 /tmp/bullshit.db
SQLite version 3.42.0 2023-05-16 13:13:42 with the Encryption (see-aes256-ofb)
Copyright 2016 Hipp, Wyrick & Company, Inc.
Enter ".help" for usage hints.
sqlite> select * from numbers;
Parse error: file is not a database (26)
sqlite> .exit

⮕  ./sqlite3 -key testing123 /tmp/bullshit.db
SQLite version 3.42.0 2023-05-16 13:13:42 with the Encryption (see-aes256-ofb)
Copyright 2016 Hipp, Wyrick & Company, Inc.
Enter ".help" for usage hints.
sqlite> select * from numbers;
1|3
sqlite> .exit

So then I try the gem I installed with the arguments you suggested (using PRAGMA to set the key like the wiki says for libs that aren't necessarily aware of the key function API):

[1] pry(main)> require 'sqlite3'
true
[2] pry(main)> db = SQLite3::Database.open('/tmp/bullshit.db')
#<SQLite3::Database:0x00007fa4b7708268 @tracefunc=nil, @authorizer=nil, @encoding=nil, @busy_handler=nil, @collations={}, @functions={}, @results_as_hash=nil, @type_translation=nil, @type_translator=#<Proc:0x00007fa4b76d53e0 /usr/local/rvm/gems/ruby-3.0.3/gems/sqlite3-1.6.3/lib/sqlite3/database.rb:727 (lambda)>, @readonly=false>
[3] pry(main)> db.execute("PRAGMA key='testing123';")
[]
[4] pry(main)> db.execute("select * from numbers;")
SQLite3::NotADatabaseException: file is not a database
from /usr/local/rvm/gems/ruby-3.0.3/gems/sqlite3-1.6.3/lib/sqlite3/database.rb:152:in `initialize'

This is what /opt/see looks like:

⮕  ls -l /opt/see
total 25232
-rwxr-xr-x  1 root  wheel  1159864 May 22 11:33 libsee.so
-rw-r--r--@ 1 root  wheel   876064 May 22 11:02 shell.c
-rwxr-xr-x  1 root  wheel  1439960 May 22 11:26 sqlite3
-rw-r--r--@ 1 root  wheel  8803129 May 22 11:02 sqlite3.c
-rw-r--r--@ 1 root  wheel   626932 May 22 11:02 sqlite3.h

This was the output of my installation command for the gem:

⮕  gem install sqlite3 --platform=ruby -- --enable-system-libraries --with-opt-dir=/opt/see
Fetching sqlite3-1.6.3.gem
Building native extensions with: '--enable-system-libraries --with-opt-dir=/opt/see'
This could take a while...
Successfully installed sqlite3-1.6.3
Parsing documentation for sqlite3-1.6.3
Installing ri documentation for sqlite3-1.6.3
Done installing documentation for sqlite3 after 1 seconds
1 gem installed

I feel like I am missing something stupid here. Any ideas? Also, should I file a feature request possibly to "officially" support this since it is the sqlite org's official encryption solution?

[flavorjones]

My advice above assumed the structure of /opt/see was similar to the directories that the autoconf package installs with ./configure --prefix=/opt/see ; make install:

/opt/see
├── bin
│   └── sqlite3
├── include
│   ├── sqlite3.h
│   └── sqlite3ext.h
├── lib
│   ├── libsqlite3.a
│   ├── libsqlite3.la
│   ├── libsqlite3.so -> libsqlite3.so.0.8.6
│   ├── libsqlite3.so.0 -> libsqlite3.so.0.8.6
│   ├── libsqlite3.so.0.8.6
│   └── pkgconfig
│       └── sqlite3.pc
└── share
    └── man
        └── man1
            └── sqlite3.1

Now that I understand what's in your directory, here's an updated suggestion:

gem install sqlite3 --platform=ruby -- \
  --enable-system-libraries \
  --with-opt-include=/opt/see \
  --with-opt-lib=/opt/see

The --with-opt-dir flag assumes the fine subdirectory structure I posted above; but the individual include and lib flags allow you to specify the exact directory to find the header files and the shared object file, respectively.

[slithernix]

i also tried with PRAGMA textkey, just in case, same behavior

[slithernix]

Ok, so I actually did miss a line in the wiki (the PRAGMA to activate the extension), so I tried again to no avail:

[3] pry(main)> db = SQLite3::Database.open('/tmp/bullshit.db')
#<SQLite3::Database:0x00007ff1a178bb90 @tracefunc=nil, @authorizer=nil, @encoding=nil, @busy_handler=nil, @collations={}, @functions={}, @results_as_hash=nil, @type_translation=nil, @type_translator=#<Proc:0x00007ff1a1753718 /usr/local/rvm/gems/ruby-3.0.3/gems/sqlite3-1.6.3/lib/sqlite3/database.rb:727 (lambda)>, @readonly=false>
[4] pry(main)> db.execute("PRAGMA activate_extensions='see-7bb07b8d471d642e';")
[]
[5] pry(main)> db.execute("PRAGMA key='testing123';")
[]
[6] pry(main)> db.execute("select * from numbers;")
SQLite3::NotADatabaseException: file is not a database

I suspect that I just don't have the right file(s)/file structure in /opt/see.

[slithernix]

OK thanks!!! you gave me a hint by mentioning the autoconf layout... I was going about this all wrong.

I got this working by downloading the autoconf package, rsyncing the see-sources over to it (and copying the appropriate encryption type file over sqlite3.c), then doing a ./configure && make && make install. so actually way easier than what I was trying to do earlier. now all I have to do is look at how to execute custom PRAGMAs at the beginning of a rails start and I will be in business. thank you so much!!!!

[flavorjones]

I'm glad you got it working!

[flavorjones]

Note: I've extracted some of what we talked about here into INSTALLATION.md in #390