Securing Information in Database using Data Encryption (written in Go)

Storing customer personal information in plain text is considered illegal by regulation in most countries.

Photo by Jose Fontano on Unsplash

Background

Personally-identifying information (PII) is personal information that customers tell the service provider (e-commerce, financial service, etc…). As a service provider, it’s their responsibility to safekeeping the information. The attack vector can be from outside or inside the organization.

Objective

Encrypt PII stored in the Database so information cannot be read by an employee within the organization, and the external attacker cannot read the information when they manage to steal the Database.

Strategy

Data Writing

1. Read input (plaintext)
2. Encrypt plaintext to ciphertext
3. Write ciphertext to DB

Data Reading

1. Read ciphertext from DB
2. Decrypt ciphertext to plaintext
3. Send plaintext

Example

kingsman ==> sLR9ctALjY0rtAi8IvosScCtBE21gyMOBl3xHzi52Hbo+H3O

Encryption Algorithm

Symmetric-key is an appropriate algorithm in this scenario because:

1. Encryption process happens in one party (the same service to be precise). So no need to concerning key exchange with another party.
2. Symmetric encryption is faster compare to asymmetric encryption. Extra speed in data interaction service is always welcomed.
3. Content size in each field of data can be huge. Symmetric encryption has a better capability for encrypting big sized data.

Sample Case

Financial Service Provider: Registration Module

Create New User Function

func createData(id, name, nationalID, createTimeUnix string) (err error) {
    _, err = DB.Exec(`
    INSERT INTO 
        user (id, name, national_id, create_time_unix)
    VALUES 
        ("?", "?", "?", "?")
    `, id, name, nationalID, createTimeUnix)
    return
}

The function above is the minimum function to write data into the Database in Go.

To limit the scope in the example case, every request contains 4 info:

• id
• name
• nationalID (in USA this known as SSN, in Indonesia known by KTP)
• createTimeUnix

We will come back to this function to integrate encryption function.

Read User Data Function

type User struct {
    ID             string
    Name           string
    NationalID     string
    CreateTimeUnix string
}
func readData(id string) (user User, err error) {
    row := DB.QueryRow(`
    SELECT
        id, name, national_id, create_time_unix
    FROM 
        user
    WHERE 
        id = "?"`, id)
    err = row.Scan(
        &user.ID,
        &user.Name,
        &user.NationalID,
        &user.CreateTimeUnix)
    
    return
}

The code above explains two things, struct of User and function to read data from the Database. The function is the minimum code to read data and parse into an object without any data handling. The function will work fine given the provided with the correct table in the Database. We will come back to this function again to decrypt data from the Database.

Data Encryption Function

func encrypt(plaintext, passphrase string) (chipertext string, err error) {
    block, _ := aes.NewCipher([]byte(passphrase))
    gcm, err := cipher.NewGCM(block)
    if err != nil {
        return
    }
nonce := make([]byte, gcm.NonceSize())
    if _, err = io.ReadFull(rand.Reader, nonce); err != nil {
        return
    }
ciphertextByte := gcm.Seal(
        nonce,
        nonce,
        []byte(plaintext),
        nil)
    chipertext = base64.StdEncoding.EncodeToString(ciphertextByte)
return
}

The function above (encrypt) is a function to encrypt plain text. AES algorithm is used as a symmetrical key algorithm. Most of the modern language (Go, Node JS, Python, PHP) already have a library for AES. In essence, what’s written in code above are;

1. Create AES Cipher (encryptor) using NewCipher function. Creating an AES Cipher required a passphrase. The passphrase is the human-readable format of the key.
2. Ciphering plain text using Seal function. The output of Seal function is ciphertext in byte-formated which not a human-readable format. It is required to encode the ciphertext into base64 so it can be stored in the Database.

We will use encrypt function to encrypt nationalID. The process will happen at createData function.

Data Decryption Function

We need to create function to decrypt data stored in the Database. Decryption using the same key used during encryption.

func decrypt(cipherText, key string) (plainText string, err error) {
    // prepare cipher
    keyByte := []byte(key)
    block, err := aes.NewCipher(keyByte)
    if err != nil {
        return
    }
    gcm, err := cipher.NewGCM(block)
    if err != nil {
        return
    }
    nonceSize := gcm.NonceSize()
    //
// process ciphertext
    ciphertextByte, _ := base64.StdEncoding.DecodeString(cipherText)
    nonce, ciphertextByteClean := ciphertextByte[:nonceSize], ciphertextByte[nonceSize:]
    plaintextByte, err := gcm.Open(
        nil,
        nonce,
        ciphertextByteClean,
        nil)
    if err != nil {
        log.Println(err)
        return
    }
    plainText = string(plaintextByte)
    //
    return
}

The function above (decrypt) is processing ciphertext and key into plaintext. There are 2 parts in the decrypt function.

1. The first part is preparing the cipher using AES and cipher library from Go. It required a key used during encryption.
2. The second part is processing decryption. Data from the Database is string-type in base64 format. We need to format it into byte-type before running Open function. The output of Open is byte type so we need to format it into string-type to make it human-readable.

Unit Test

Before we integrate encrypt and decrypt function into CRUD DB function. we must create the unit test to verify the output of decrypt function is the same as plaintext.

func Test_encrypt(t *testing.T) {
    type args struct {
        plaintext string
        key       string
    }
    tests := []struct {
        name string
        args args
    }{
        {
            name: "happy test",
            args: args{
                plaintext: "kingsman",
                key:       "04076d64bdb6fcf31706eea85ec98431"},
        },
    }
    for _, tt := range tests {
        t.Run(tt.name, func(t *testing.T) {
            // encrypt the plaintext
            ciphertext, err := encrypt(tt.args.plaintext,   tt.args.key)
            if err != nil {
                t.Errorf("encrypt() error = %v", err)
                return
            }
            t.Logf("ciphertext = %s", ciphertext)
            //
// decrypt the ciphertext from previous encrypt function
            plaintext, err := decrypt(ciphertext, tt.args.key)
            if err != nil {
                t.Errorf("encrypt() error = %v", err)
                return
            }
            t.Logf("plaintext = %s", plaintext)
            //
// compare the initial plaintext with output of previous decrypt function
            if plaintext != tt.args.plaintext {
                t.Errorf("plaintext = %v, want %v", plaintext, tt.args.plaintext)
            }
            //
        })
    }
}

We are running encrypt function to encrypt plaintext (“kingsman”). The output is ciphertext which we will pass into decrypt function. The expected output is the string-type value equal to the plaintext. Lastly, we compare the output with plaintext as verification. The test result is the following;

db-encryption go test -v -timeout 30s
=== RUN   Test_encrypt
=== RUN   Test_encrypt/happy_test
--- PASS: Test_encrypt (0.00s)
    --- PASS: Test_encrypt/happy_test (0.00s)
        main_test.go:78: ciphertext = sLR9ctALjY0rtAi8IvosScCtBE21gyMOBl3xHzi52Hbo+H3O
        main_test.go:87: plaintext = kingsman
PASS
ok      github.com/purnaresa/secureme/db-encryption     0.005s

Integration

It is time to integrate to CRUD DB Function once we are confident that both encrypt and decrypt function working as expected. Basically we must encrypt the data right before we insert it into the Database.

func createData(id, name, nationalID, createTimeUnix string) (err error) {
    // encryption
    nationalID, _ = encrypt(nationalID, masterKey)
    //
_, err = DB.Exec(`
    INSERT INTO 
        user (id, name, national_id, create_time_unix)
    VALUES 
        ("?", "?", "?", "?")
    `, id, name, nationalID, createTimeUnix)
return
}

In the above code, we are running encryption for nationalID. To simplify the example we are ignoring the error output.

And for decryption, we must run decrypt function right after we read data from the Database.

func readData(id string) (user User, err error) {
    row := DB.QueryRow(`
    SELECT
        id, name, national_id, create_time_unix
    FROM 
        user
    WHERE 
        id = "?"`, id)
err = row.Scan(
        &user.ID,
        &user.Name,
        &user.NationalID,
        &user.CreateTimeUnix)
// decryption
    user.NationalID, _ = decrypt(user.NationalID, masterKey)
    //
    return
}

Summary

The sample provided in this project is the encryption of a single value. But it can be used to encrypt several values. AES mechanism is safe to use as long as the key is well guarded. This project is considered as a minimum requirement for Securing Information in Database because there are only two factors implemented (algorithm and key).

Full code can be downloaded from Github: https://github.com/purnaresa/secureme/blob/master/db-encryption/main.go

We can improve more security factors such as salt so even if the key is stolen, an attacker couldn’t utilize the key to decrypt the ciphertext.
What else do you think we can add to the next post?

If you found this article helpful for you, don’t forget to follow my account.
Xoxo