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

[jmcdonagh]

Is this still an issue/not supported? Came here seeing if it would be simple to feed in the custom SEE amalgam that you get when you have a license to this project, since my intuition and research shows this project is a lot faster than the other SQLite package. I am assuming I can tweak that other one to link to the see .so (might end up being the route I take if this is too strange/difficult). That's how I have SEE working with my Ruby projects actually...

I have my own little build automation for SEE that creates deb packages for my boxen that I use in various ways. I don't recall doing what @ctp52 said about file concatenation but i am not on the box/network that has that code at the moment. IIRC you get an archive that just has the whole amalgams depending on which algorithm you want to use. So if my memory is indeed correct, it seems like those instructions aren't correct anymore.

The thing with the SEE amalgam is, it's a drop-in replacement for the regular one. Unless I am misunderstanding the scope and functionality of this project's code, all it should take is adding a go build tag that specifies the path to the custom amalgam. Then you can interact with the encryption via PRAGMAs. Your code wouldn't really have to know anything about the encryption functionality per se. All that happens is, if you don't execute the PRAGMA before other things you get the not a database error.

[ctp52]

The changes I identified on 2019-08-19 still apply to sqlite3.go and are repeated below with current line numbers plus a preceding/following line to provide context for later versions of this file on github. There has been no changes in this region of sqlite3.go since 2018.

I wrote additional comment on 2020-05-13 that still applies in my code over many more projects. You will likely need to adjust it to suit your needs.

There are now several amalgamation files provided as part of the SEE source code. Each includes a current copy of sqlite3 source plus prefix/suffix code that I mentioned five years ago. Each amalgamation file works with a specific encode/decode algorithm, so you need to choose one to select the correct file. The following instructions do not care which encryption algorithm is used.

All code changes are in sqlite3.go and the line numbers have shifted due to other edits to the current source on github. I do not use github for version control, preferring instead to use fossil-scm.org so the best I can do for a unique file reference is that the current github commit is 7658c06 as of 2024-12-08. (Fossil uses a file specific hash identifier which can be searched in fossil to find the exact file version).

The purpose of these changes is to provide an extra pragma for:

func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error)

where the pragma is appended to the dsn input string. And then to execute that pragma immediately after opening the database file.

File: sqlite3.go:

starting at line 1069:
//     https://www.sqlite.org/pragma.html#pragma_secure_delete
//
++ //   _see_key=XXX
++ /     Specify symmetric crypto key for use by SEE.  X must be text key without quotes.
++ //
//   _synchronous=X | _sync=X

starting at line 1094:
    txlock := "BEGIN"

++     seeKey := ""

    // PRAGMA's

starting at line 1118:
        }

++         // SEE decryption key
++         if val := params.Get("_see_key"); val != "" {
++             seeKey = val
++         }

        // Authentication

starting at line 1494:
    }

++     // 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
++         }
++     }

    // USER AUTHENTICATION

--- end of changes to sqlite3.go ---

[ctp52]

There is still need to create the small extra file sqlite3_see.go to be included in the mattn/go-sqlite3 source directory:

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

and to reference the tag in the go install instruction: go install --tags "see" ...

[jmcdonagh]

@ctp52 thanks for your work on this, I should be able to generate a patch based on that to keep in the project and integrate that however I see fit. I'll be ready to start hacking on this portion of the Go project I am working on sometime in February if everything else goes according to plan.

It would be nice if this got integrated into the project in a more general sense, as SEE is totally official (distributed by SQLite themselves).

[coreybutler]

There is a wrapper using sqlcipher available at https://github.com/mutecomm/go-sqlcipher.

[jmcdonagh]

@ctp52 alright cool, finally got around to doing this and it's working swimmingly. thanks again!

One question for you, in my Ruby apps I would pre-create the encrypted DBs with a rake task using the shell binary with SEE in it. What is your move for creating encrypted DBs with go-sqlite3? All I can think of right now is bundle the command with the go app and use exec.Command to call it to pre-create the DB file if it doesn't exist. Not sure if there is a better way I am missing.

[ctp52]

You have the right idea to use the sqlite shell binary. I keep a text file with all the table definitions and use the .read function to create the database structure and populate reference tables. I think the open function can be used to create an encrypted database. You can also issue the data definition statements using go-sqlite3.

[jmcdonagh]

@ctp52 word.

i just had another idea come to me when i woke up (i must have been dreaming of Go!) that might be slightly better IMO than packaging a whole other binary with the software... with the new(ish) embed.FS stuff I could theoretically embed an empty secure database with a default key like 'secret' or whatever, then copy that into place and use the rekey mechanism. I think I'm gonna give that a shot later when I've got a minute to explore the embed.FS stuff. I've used it before but I don't remember how it works.

[jmcdonagh]

Whipped this up when I had a few minutes before an appointment I'm about to run to. Need to write tests but I think this will work out. I basically created a test db with key 'secret' and embedded it via embed.FS in the startup for this service, which I will pass downstream to NewSeeDb. I'll post the tests later. Note that this is bolted on to a slightly modified version of Bill Kennedy's sqldb package from his Ultimate Service repo/course.

package sqldb

import (
	"embed"
	"fmt"
	"os"
	"strings"
)

func NewSeeDb(fs embed.FS, src string, dst string, key string, schema string) error {
	// Logic here is if the file exists just keep it moving in the caller,
	// or if there is an ACTUAL error (dst not existing is good to move
	// on with db creation) return that as well. Up to the caller to
	// do whatever.
	//
	// You may have other thoughts about what behavior is correct here.
	if _, err := os.Stat(dst); err == nil || (err != nil && !os.IsNotExist(err)) {
		return err
	}

	if err := installEmbeddedDb(fs, src, dst); err != nil {
		return err
	}

	if err := RekeySeeDb(dst, "secret", key); err != nil {
		return err
	}

	return CleanAndSeedFreshSeeDb(dst, key, schema)
}

func CleanAndSeedFreshSeeDb(dbPath string, key string, schema string) error {
	tmpCfg := SqliteConfig{
		AbsolutePath: dbPath,
		Key:          key,
		MaxIdleConns: 1,
		MaxOpenConns: 1,
	}

	db, err := Open(tmpCfg)
	if err != nil {
		return fmt.Errorf("connecting to db: %w", err)
	}
	defer db.Close()

	cleanStmt := "DROP TABLE delete_me;"
	if _, err := db.Exec(cleanStmt); err != nil {
		return err
	}

	// Passed in schema takes precedence over potential .seed file
	if schema != "" {
		if _, err := db.Exec(schema); err != nil {
			return err
		}

		return nil
	}

	seedPath := strings.ReplaceAll(dbPath, ".db", ".seed")
	if _, err := os.Stat(seedPath); err == nil {
		schema, err := os.ReadFile(seedPath)
		if err != nil {
			return fmt.Errorf("error reading schema seed file %s: %v", seedPath, err)
		}

		if _, err := db.Exec(string(schema)); err != nil {
			return err
		}
	}

	return nil
}

func RekeySeeDb(dbPath string, currentKey string, newKey string) error {
	tmpCfg := SqliteConfig{
		AbsolutePath: dbPath,
		Key:          currentKey,
		MaxIdleConns: 1,
		MaxOpenConns: 1,
	}

	db, err := Open(tmpCfg)
	if err != nil {
		return fmt.Errorf("connecting to db: %w", err)
	}
	defer db.Close()

	rekeyStmt := fmt.Sprintf("PRAGMA rekey='%s';", newKey)
	if _, err := db.Exec(rekeyStmt); err != nil {
		return err
	}

	return nil
}

func installEmbeddedDb(fs embed.FS, src string, dst string) error {
	data, err := fs.ReadFile(src)
	if err != nil {
		return fmt.Errorf("reading embedded db: %w", err)
	}

	err = os.WriteFile(dst, data, 0600)
	if err != nil {
		return fmt.Errorf("writing db file: %w", err)
	}

	return nil
}

[jmcdonagh]

Tests are almost done, uncovered a small boolean issue at the beginning. The test file is long so I'm not going to clog up this thread with it. If anyone wants to see it ping me I guess. thanks to all developers involved!