Does we plan to support sqlite3 encryption module

[davygeek]

Some scenarios need to support db encryption, so we need encryption support.

[mattn]

Did you try pragma?

PRAGMA key='your-secret-key';

[imkos]

like wxsqlite3

[gjrtimmer]

@davygeek @imkos I would really like to support that, especially with the new european GDPR legislation, when using go-sqlite3 in a microservice. However its not easy, can you guys help with the implementation ? What would be really nice if we could write a CGO wrapper which implements the required C calls as show in case #200 and we redirect those to use native golang AES encryption which means no additional dependency on an external library like OpenSSL. Can you guys help ?

[davygeek]

@gjrtimmer Is it possible to use cgo to include ‘https://github.com/sqlcipher/sqlcipher’ for encryption purposes?

[gjrtimmer]

@davygeek That would definitly be possible, however I was thinking more along the following. Provide custom implementation of the required functions. And use CGO to use native Go to encrypt en decrypt.

So basically write some Go functions to encrypt en decrypt, export them to make them available to CGO and that implement the required SQLite functions and use the exported functions.

Are you willing to help to implement SEE ?

[davygeek]

@gjrtimmer I'm happy to implement part of the great project

[gjrtimmer]

@davygeek That would be great, currently working on a version 2.0.0 which will also implement several features from golang:1.10 after this I want to add SEE.

[ctp52]

Here is suggested code to permit use of SEE. This change uses a new DSN parameter.
The SQLITE_HAS_CODEC flag is handled in a new file sqlite3_see.go:

package sqlite3
/*
#cgo CFLAGS: -DSQLITE_HAS_CODEC=1
*/
import "C"

Only code changes are in sqlite3.go:

--- begin diff ---
968a969,971
> //   _see_key=XXX
> //     Specify symmetric crypto key for use by SEE.  X must be text key without quotes.
> //
995a999,1000
> 	seeKey := ""
> 
1017a1023,1027
> 		// SEE decryption key
> 		if val := params.Get("_see_key"); val != "" {
> 			seeKey = val
> 		}
> 
1369a1380,1387
> 	// SEE encryption key
> 	if len(seeKey) > 0 {
> 		if err := exec(fmt.Sprintf("PRAGMA key = '%s';", seeKey)); err != nil {
> 			C.sqlite3_close_v2(db)
> 			return nil, err
> 		}
> 	}
> 
--- end diff ---

[fabiante]

Hi, have any advances in this direction been made? I found https://github.com/CovenantSQL/go-sqlite3-encrypt but am having trouble using it and since the issues tab has been disabled there is no real way to find any help with that repo.

[ctp52]

To expand on my instructions of 2 Sep 2019:
You will need to buy a license for SEE from

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

The C language instructions for SEE require that the see-prefix.c file be prepended and that the see.c file be appended to the current amalgamated sqlite3.c source. These instructions are on the cited web page. That concatenated file is then copied into the mattn/go-sqlite3 directory as a replacement for sqlite3-binding.c and you should copy in sqlite3.h for sqlite3-binding.h in case any definitions have changed.

It is best to fork a copy of mattn's work into a project directory that is not "vendor". The vendor directory is assumed to contain unchanged copies of third party code. These instructions require a changed copy. I use "golang/forked" within the project's parent directory, followed by "github.com/mattn/go-sqlite3". My projects have source code in a number of other languages, plus documentation, and the standard golang directory structure does not work so well.

Changes to mattn/sqlite.go are listed in my previous instructions. The insertion points shifted a couple of lines in the most recent release of mattn/sqlite.go but a quick read of the code will suggest where to put them.

The sqlite3_see.go file mentioned in my previous post must be created in the mattn/go-sqlite3 directory, consistent with other tag files in that directory.

Since Go now uses the module approach and not GOPATH, the correct environment variable definitions for Go when used in a project specific workflow are critical. This has nothing to do with mattn's work directly but it took a long time to figure it out. Here is a sample go.env file referenced in the make instruction which comes after this:

GO111MODULE=on
GOBIN=/home/yourname/xxxx/golang/bin
unset GOPATH
GOPRIVATE=xxxx
CGO_ENABLED=1

You will need to change yourname and project name (xxxx) to suit. GOBIN must be a non-relative path. This file should be executed each time you switch projects otherwise the Go compiler will look in the wrong directories for the current one. I put it alongside the go.mod and go.sum files in the "xxxx/golang" project directory.

To create the desired go.env file, I use a small shell script (makegoenv.sh):

echo "GO111MODULE=on" >go.env
echo "GOBIN=${PWD}/bin" >>go.env
echo "unset GOPATH" >>go.env
echo "GOPRIVATE=xxxx" >>go.env
echo "CGO_ENABLED=1" >>go.env

It is run as part of the makefile instruction shown below. Note the "xxxx" project name which is required and might not match the directory name.

I split the golang code into "public" and "internal" sub-directories so my project directory structure includes:

xxxx
├── golang
│   ├── bin
│   ├── forked
│   │   ├── github.com
│   │   │   └── mattn
│   │   │       └── go-sqlite3
│   ├── go.env
│   ├── go.mod
│   ├── go.sum
│   ├── internal
│   │   ├── cmds
│   │   └── makefile
│   ├── makegoenv.sh
│   ├── public
│   │   ├── cmds
│   │   └── makefile
│   ├── shared
│   │   ├── config
│   │   │   └── config.go
│   └── vendor
│       ├── github.com
│       └── modules.txt
├ plus others

Each of "public" and "internal" has its own makefile. That is the reason for the "../" prefix on go.env in the make instruction.

The make instruction for executables using this forked copy of go-sqlite3 is:

PROJECT = xxxx/public
cd ..; ./makegoenv.sh
export GOENV=../go.env; go install --tags "see" ${PROJECT}/cmds/$@

Note the required "--tags" parameter which matches the name in sqlite3_see.go

The Go compiler puts the executables in GOBIN and I need to move/rename them for final use. So the makefile includes other instructions after the compile that can be automated.

Your Go code that imports the forked module must reference it as:

import _  "xxxx/forked/github.com/mattn/go-sqlite3"

where "xxxx" is your project name as defined in go.env.

I've used this method on three unrelated projects and it works well.

[jgiannuzzi]

If you want to use SQLCipher instead of SEE, I have created the following PR: #1109