go-sqlcipher

You can install the go-sqlcipher package using the standard go get command.

go get github.com/mutecomm/go-sqlcipher

SQLCipher 4.x is incompatible with SQLCipher 3.x. If you are upgrading a major version, you are responsible for migrating existing database files. To upgrade your Go code to the 4.x series, update your import path to:

"github.com/mutecomm/go-sqlcipher/v4"

import "github.com/mutecomm/go-sqlcipher/v4"

The built-in sqlite_crypt(X, Y) function used by the UserAuthentication module uses a simple Caesar-cypher which is insecure. To improve security, you can provide a stronger implementation (e.g., using SHA hashes) by using the RegisterFunc method on your connection to overload sqlite_crypt.

When sqlite_crypt(X, Y) is called:

• X is the plaintext password.
• Y is the existing stored hash (used to extract the salt).
• The result is the new encoded blob.

To verify credentials, SQLite performs: sqlite_user.pw == sqlite_crypt(X, sqlite_user.pw). To generate a new hash with a random salt, call sqlite_crypt(X, NULL).

To use this driver in your Go project, install it using go get:

go get github.com/mattn/go-sqlite3

To open an encrypted database using a 32-byte hex-encoded key, include _pragma_key and optionally _pragma_cipher_page_size in your Data Source Name (DSN). The _pragma_key must be a 64-character hex string wrapped in x'' literals.

key := "2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99"
dbname := fmt.Sprintf("db?_pragma_key=x'%s'&_pragma_cipher_page_size=4096", key)
db, _ := sql.Open("sqlite3", dbname)

To use a plain-text passphrase instead of a hex key, pass the passphrase to _pragma_key. You must use url.QueryEscape() on the passphrase to ensure the DSN is valid.

key := url.QueryEscape("secret")
dbname := fmt.Sprintf("db?_pragma_key=%s&_pragma_cipher_page_size=4096", key)
db, _ := sql.Open("sqlite3", dbname)

The TraceConfig struct defines how the trace callback behaves and which events are captured.

type TraceConfig struct {
	Callback        TraceUserCallback
	EventMask       uint32
	WantExpandedSQL bool
}

If you attempt to use go-sqlcipher in an environment where CGO_ENABLED=0 was used during compilation, the driver will fail to open connections and return the following error: Binary was compiled with 'CGO_ENABLED=0', go-sqlite3 requires cgo to work. This is a stub.

To use this library, you must ensure that CGO is enabled during the build process.

errors.New("Binary was compiled with 'CGO_ENABLED=0', go-sqlite3 requires cgo to work. This is a stub")

You can load external C extensions (compiled as .so or .dll shared libraries) by registering a custom driver with the Extensions field in the sqlite3.SQLiteDriver struct. The strings in the Extensions slice should correspond to the names of the shared libraries.

sql.Register("sqlite3_with_extensions",
	&sqlite3.SQLiteDriver{
		Extensions: []string{
			"sqlite3_mod_regexp",
		},
	})

You can register native Go functions as SQLite extension functions by using the ConnectHook to call conn.RegisterFunc. This allows you to use Go logic directly within your SQL queries.

// Define your Go function
regex = func(re, s string) (bool, error) {
	return regexp.MatchString(re, s)
}

// Register a custom driver that injects the function
sql.Register("sqlite3_extended",
	&sqlite3.SQLiteDriver{
		ConnectHook: func(conn *sqlite3.SQLiteConn) error {
			return conn.RegisterFunc("regexp", regex, true)
		},
	})

// Use the custom driver
var i int
conn, err := sql.Open("sqlite3_extended", "./foo.db")
// ... handle err

err = conn.QueryRow(`SELECT regexp("foo.*", "seafood")`).Scan(&i)

Use AuthUserDelete(username string) to remove a user from the database.

Permissions and Safety:

• Admin Only: This operation requires administrator privileges.
• Preventing Lockout: The currently logged-in user cannot be deleted. This guarantee ensures that there is always at least one administrator, preventing the database from being reverted to a no-authentication state accidentally.

err := db.AuthUserDelete("user_to_remove")
if err != nil {
    if errors.Is(err, sqlite3.ErrAdminRequired) {
        // Handle permission error
    }
}