Database Autonomy Service:Integrate the Go driver for column encryption

Prerequisites

• You have run a sensitive data detection scan to find columns that require encryption.

• You have configured column encryption for the target database and set the permission for the database account to Ciphertext Permission (JDBC Decryption). For detailed instructions, see column encryption.

• Connection details for the instance: hostname, port, database name, username, and password.

Configure MEK

The key is transmitted from the client to the database server through a secure asymmetric encryption protocol, so that the server and the client share the same key, thereby enabling data to be transmitted securely through symmetric encryption.

Value range: A 16-byte hexadecimal string, exactly 32 characters long.

Important warning: If you lose your MEK, all historical query results encrypted under this policy become permanently undecryptable.

You can use key generation tools or the random functions in various programming languages to generate the MEK. For example:

• Linux: Run openssl rand -hex 16.

• Windows: Install and use OpenSSL tool.

Usage notes

• You must store the MEK and keep it confidential.

• The Go version must be 1.18 or later.

Client integration

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

// Replace the placeholders with your actual endpoint (hostname), port number (port), database name (dbname), username, and password.
db, err := sql.Open("encmysql", "<username>:<password>@tcp(<hostname>:<port>)/<dbname>?MEK=00112233445566778899aabbccddeeff")
if err != nil {
    panic(err)
}

// Send a query.	
rows, err := db.Query("SELECT * FROM sddp_test_mask")
if err != nil {
    log.Fatalf("Failed to query data: %v", err)
}
// Make sure to close the result set after use.
defer rows.Close() 
// Define variables to store the data for each row.
var id int
var name string
var password string
var age int
// Iterate over each row of data.
for rows.Next() {
    // Scan the data of the current row into variables.
    err := rows.Scan(&id, &name, &password, &age)
    if err != nil {
        log.Fatalf("Failed to scan row: %v", err)
    }
    // Print the data of the current row.
    fmt.Printf("read data: id=%d, name=%s, password=%s, age=%d\n", id, name, password, age)
}

Complete code example

The following example uses a database account with the Ciphertext Permission (JDBC Decryption) to query plaintext data from encrypted columns in a PolarDB for MySQL database.

The database configuration used in this example is detailed in the Column encryption example for a PolarDB for MySQL database section of the Column encryption topic.

package main
import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/aliyun/alibabacloud-encdb-mysql-go-client"
)
func main() {
    // Replace the placeholders with your actual endpoint (hostname), port number (port), database name (dbname), username, and password.
    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 to close the result set after use.
    defer rows.Close() 
    // Define variables to store the data for each row.
    var id int
    var name string
    var password string
    var age int
    // Iterate over each row of data.
    for rows.Next() {
        // Scan the data of the current row into variables.
        err := rows.Scan(&id, &name, &password, &age)
        if err != nil {
            log.Fatalf("Failed to scan row: %v", err)
        }
        // Print the data of the current row.
        fmt.Printf("read data: id=%d, name=%s, password=%s, age=%d\n", id, name, password, age)
    }
}

After running the code, the decrypted output is similar to the following:

read data: id=1, name=2clxxx, password=xxx, age=3
read data: id=2, name=RZlxxx6Z=, password=)xxxv9>, age=4
read data: id=3, name=!xxxrm, password=kxxxS, age=5