Database Autonomy Service:Integrate the Go driver for column encryption

Prerequisites

Before you begin, ensure the following setup is complete:

• You have run a sensitive data detection scan to identify the columns you want to encrypt. For more information, see Sensitive data detection.

• You have enabled column encryption for the destination database and granted the Ciphertext Permission (JDBC Decryption) for the destination database account. For more information, see Column encryption.

• The connection information about the RDS instance for which the always-confidential database feature is enabled is obtained. The connection information includes the domain name (host), port number (port), instance name (dbname), username (username), and password (password).

Master Encryption Key (MEK)

MEK is the root credential that authorizes your client application to access encrypted data. Here's how it works:

The client transmits the key to the database server over a secure asymmetric key encryption protocol. This process allows the server and the client to share the same key so data can be securely transmitted using symmetric encryption.

The value must be a 32-character hexadecimal string, representing 16 bytes of data.

You are responsible for generating and securely storing your MEK. A common method is to use a cryptographic tool like OpenSSL.

For example:

• On Linux or macOS, run the this command with the built-in OpenSSL tool:

  openssl rand -hex 16

  This will generate a random 32-character hex string, for example: 00112233445566778899aabbccddeeff

• On Windows, install the OpenSSL package to use the same command.

Usage notes

• You must store the MEK and keep it confidential.

• The Go version must be 1.18 or later.

Procedure

go get github.com/aliyun/alibabacloud-encdb-mysql-go-client@latest

// Update the connection information such as the endpoint (hostname), port number (port), instance name (dbname), username (username), and password (password) based on the actual scenario. 
db, err := sql.Open("encmysql", "<username>:<password>@tcp(<hostname>:<port>)/<dbname>?MEK=00112233445566778899aabbccddeeff")
if err != nil {
    panic(err)
}

// Initiate a query.	
rows, err := db.Query("SELECT * FROM sddp_test_mask")
if err != nil {
    log.Fatalf("Failed to query data: %v", err)
}
// Make sure that the query result set is closed after it is used.
defer rows.Close() 

// Define variables to store data for each row.
var id int
var name string
var password string
var age int

// Traverse each row.
for rows.Next() {
    // Extract data from the current row and assign the data to the specified variables.
    err := rows.Scan(&id, &name, &password, &age)
    if err != nil {
        log.Fatalf("Failed to scan row: %v", err)
    }

    // Display the data of the current row.
    fmt.Printf("read data: id=%d, name=%s, password=%s, age=%d\n", id, name, password, age)
}

Complete sample code

For example, you use a database account that has the ciphertext permission (JDBC decryption) to view the plaintext data of the encrypted columns in a PolarDB for MySQL database.

For more information about the database configurations in the following sample code, see Column encryption in a PolarDB for MySQL database in the Column encryption section.

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/aliyun/alibabacloud-encdb-mysql-go-client"
)

func main() {
    // Update the connection information such as the endpoint (hostname), port number (port), instance name (dbname), username (username), and password (password) based on the actual scenario.

    db, err := sql.Open("encmysql", "sddp_02:He******4@tcp(polar***.rwlb.rds.aliyuncs.com:3306)/sddp_test?MEK=00112233445566778899aabbccddeeff")
    if err != nil {
        panic(err)
    }

    rows, err := db.Query("SELECT * FROM user3 LIMIT 3")
    if err != nil {
        log.Fatalf("Failed to query data: %v", err)
    }
	
    // Make sure that the query result set is closed after it is used.
    defer rows.Close() 

    // Define variables to store data for each row.
    var id int
    var name string
    var password string
    var age int

    // Traverse each row.
    for rows.Next() {
        // Extract data from the current row and assign the data to the specified variables.
        err := rows.Scan(&id, &name, &password, &age)
        if err != nil {
            log.Fatalf("Failed to scan row: %v", err)
        }

        // Display the data of the current row.
        fmt.Printf("read data: id=%d, name=%s, password=%s, age=%d\n", id, name, password, age)
    }
}

After the preceding code is called, the decrypted result similar to the following information is returned.