Console
    Contact Us Start free

    About client-side encryption

    This page describes how to implement client-side encryption on Cloud SQL.

    Overview

    Client-side encryption is the act of encrypting data before writing it to Cloud SQL. You can encrypt Cloud SQL data in a manner that only your application can decrypt.

    To enable client-side encryption you have the following options:

    1. Using an encryption key stored in the Cloud Key Management Service (Cloud KMS).
    2. Using an encryption key stored locally in your application.

    In this topic, we describe how to use the first option, which provides the most seamless key management option. We create an encryption key in Cloud KMS and implement envelope encryption using Tink , Google's open source cryptographic library.

    Why do you need client-side encryption?

    You need client-side encryption if you want to protect Cloud SQL data at the column level 1 . Imagine you have a table of names and credit card numbers. You want to grant a user access to this table, but you don't want them to view the credit card numbers. You can encrypt the numbers using client-side encryption. As long as the user is not granted access to the encryption key in Cloud KMS, they can't read the credit card information.

    Create keys using Cloud KMS

    Cloud KMS lets you create and manage keys on Google Cloud Platform.

    Cloud KMS supports many different key types. For client-side encryption, you need to create a symmetric key .

    To give your application access to the key in Cloud KMS, you need to grant the service account that your application uses with the cloudkms.cryptoKeyEncrypterDecrypter role. In gcloud, you use the following command to do this:

    gcloud  
kms  
keys  
add-iam-policy-binding  
key  
 \ 
--keyring = 
key-ring  
 \ 
--location = 
location  
 \ 
--member = 
serviceAccount:service-account-name@example.domain.com  
 \ 
--role = 
roles/cloudkms.cryptoKeyEncrypterDecrypter

    While you can use the KMS key to directly encrypt data , here we use a more flexible solution called envelope encryption . This allows us to encrypt messages longer than 64KB, which is the maximum message size that the Cloud Key Management Service API can support.

    Cloud KMS envelope encryption

    In envelope encryption, the KMS key acts as a key encrypting key (KEK). That is, it is used to encrypt data encryption keys (DEK) which in turn are used to encrypt actual data.

    After creating a KEK in Cloud KMS, to encrypt each message you need to:

    • Generate a data encryption key (DEK) locally.
    • Use this DEK locally to encrypt the message.
    • Call Cloud KMS to encrypt (wrap) the DEK with the KEK.
    • Store the encrypted data and the wrapped DEK.

    Instead of implementing envelope encryption from scratch, in this topic we use Tink.

    Tink

    Tink is a multi-language, cross-platform library that provides high-level cryptographic APIs. To encrypt data with Tink's envelope encryption, you provide Tink with a key URI pointing to your KEK in Cloud KMS, and credentials that allow Tink to use the KEK. Tink generates the DEK, encrypts the data, wraps the DEK and returns a single ciphertext with the encrypted data and wrapped DEK.

    Tink supports envelope encryption in C++, Java, Go, and Python using the AEAD API:

      public 
  
 interface 
  
 Aead 
 { 
  
 byte 
 [] 
  
 encrypt 
 ( 
 final 
  
 byte 
 [] 
  
 plaintext 
 , 
  
 final 
  
 byte 
 [] 
  
 associatedData 
 ) 
  
 throws 
 … 
  
 byte 
 [] 
  
 decrypt 
 ( 
 final 
  
 byte 
 [] 
  
 ciphertext 
 , 
  
 final 
  
 byte 
 [] 
  
 associatedData 
 ) 
  
 throws 
 … 
 }

    Besides the normal message/ciphertext argument, the encrypt and decrypt methods support optional associated data. This argument can be used to tie the ciphertext to a piece of data. For example, suppose you have a database with a field user-id and a field encrypted-medical-history . In this case, the field user-id should probably be used as associated data when encrypting the medical history. This ensures that an attacker cannot move medical history from one user to another. It is also used to verify that you have the correct row of data when you run a query.

    Samples

    In this section, we'll walk through sample code for a voter information database that uses client-side encryption. The sample code shows how to:

    • Create a database table and connection pool
    • Set up Tink for envelope encryption
    • Encrypt and decrypt data using Tink's envelope encryption with a KEK in Cloud KMS

    Before you begin

    1. Create a Cloud SQL Instance by following these instructions . Note the connection string, database user, and database password that you create.

    2. Create a database for your application by following these instructions . Note the database name.

    3. Create a KMS key for your application by following these instructions . Copy the resource name of your created key.

    4. Create a service account with the 'Cloud SQL Client' permissions by following these instructions .

    5. Add the 'Cloud KMS CryptoKey Encrypter/Decrypter' permission for the key to your service account by following these instructions .

    Create a connection pool and create a new table in the database.

    Java

      import 
  
 com.zaxxer.hikari.HikariConfig 
 ; 
 import 
  
 com.zaxxer.hikari.HikariDataSource 
 ; 
 import 
  
 java.sql.Connection 
 ; 
 import 
  
 java.sql.PreparedStatement 
 ; 
 import 
  
 java.sql.SQLException 
 ; 
 import 
  
 javax.sql.DataSource 
 ; 
 public 
  
 class 
 CloudSqlConnectionPool 
  
 { 
  
 public 
  
 static 
  
 DataSource 
  
 createConnectionPool 
 ( 
 String 
  
 dbUser 
 , 
  
 String 
  
 dbPass 
 , 
  
 String 
  
 dbName 
 , 
  
 String 
  
 instanceConnectionName 
 ) 
  
 { 
  
 HikariConfig 
  
 config 
  
 = 
  
 new 
  
 HikariConfig 
 (); 
  
 config 
 . 
 setJdbcUrl 
 ( 
 String 
 . 
 format 
 ( 
 "jdbc:mysql:///%s" 
 , 
  
 dbName 
 )); 
  
 config 
 . 
 setUsername 
 ( 
 dbUser 
 ); 
  
 config 
 . 
 setPassword 
 ( 
 dbPass 
 ); 
  
 config 
 . 
 addDataSourceProperty 
 ( 
 "socketFactory" 
 , 
  
 "com.google.cloud.sql.mysql.SocketFactory" 
 ); 
  
 config 
 . 
 addDataSourceProperty 
 ( 
 "cloudSqlInstance" 
 , 
  
 instanceConnectionName 
 ); 
  
 DataSource 
  
 pool 
  
 = 
  
 new 
  
 HikariDataSource 
 ( 
 config 
 ); 
  
 return 
  
 pool 
 ; 
  
 } 
  
 public 
  
 static 
  
 void 
  
 createTable 
 ( 
 DataSource 
  
 pool 
 , 
  
 String 
  
 tableName 
 ) 
  
 throws 
  
 SQLException 
  
 { 
  
 // Safely attempt to create the table schema. 
  
 try 
  
 ( 
 Connection 
  
 conn 
  
 = 
  
 pool 
 . 
 getConnection 
 ()) 
  
 { 
  
 String 
  
 stmt 
  
 = 
  
 String 
 . 
 format 
 ( 
 "CREATE TABLE IF NOT EXISTS %s ( " 
  
 + 
  
 "vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, team CHAR(6) NOT NULL," 
  
 + 
  
 "voter_email VARBINARY(255), PRIMARY KEY (vote_id) );" 
 , 
  
 tableName 
 ); 
  
 try 
  
 ( 
 PreparedStatement 
  
 createTableStatement 
  
 = 
  
 conn 
 . 
 prepareStatement 
 ( 
 stmt 
 );) 
  
 { 
  
 createTableStatement 
 . 
 execute 
 (); 
  
 } 
  
 } 
  
 } 
 }

    Python

      import 
  
 sqlalchemy 
 def 
  
 init_tcp_connection_engine 
 ( 
 db_user 
 : 
 str 
 , 
 db_pass 
 : 
 str 
 , 
 db_name 
 : 
 str 
 , 
 db_host 
 : 
 str 
 ) 
 - 
> sqlalchemy 
 . 
 engine 
 . 
 base 
 . 
 Engine 
 : 
  
 """ 
 Creates a connection to the database using tcp socket. 
 """ 
 # Remember - storing secrets in plaintext is potentially unsafe. Consider using 
 # something like https://cloud.google.com/secret-manager/docs/overview to help keep 
 # secrets secret. 
 # Extract host and port from db_host 
 host_args 
 = 
 db_host 
 . 
 split 
 ( 
 ":" 
 ) 
 db_hostname 
 , 
 db_port 
 = 
 host_args 
 [ 
 0 
 ], 
 int 
 ( 
 host_args 
 [ 
 1 
 ]) 
 pool 
 = 
 sqlalchemy 
 . 
 create_engine 
 ( 
 # Equivalent URL: 
 # mysql+pymysql://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name> 
 sqlalchemy 
 . 
 engine 
 . 
 url 
 . 
 URL 
 . 
 create 
 ( 
 drivername 
 = 
 "mysql+pymysql" 
 , 
 username 
 = 
 db_user 
 , 
 # e.g. "my-database-user" 
 password 
 = 
 db_pass 
 , 
 # e.g. "my-database-password" 
 host 
 = 
 db_hostname 
 , 
 # e.g. "127.0.0.1" 
 port 
 = 
 db_port 
 , 
 # e.g. 3306 
 database 
 = 
 db_name 
 , 
 # e.g. "my-database-name" 
 ), 
 ) 
 print 
 ( 
 "Created TCP connection pool" 
 ) 
 return 
 pool 
 def 
  
 init_unix_connection_engine 
 ( 
 db_user 
 : 
 str 
 , 
 db_pass 
 : 
 str 
 , 
 db_name 
 : 
 str 
 , 
 instance_connection_name 
 : 
 str 
 , 
 db_socket_dir 
 : 
 str 
 , 
 ) 
 - 
> sqlalchemy 
 . 
 engine 
 . 
 base 
 . 
 Engine 
 : 
  
 """ 
 Creates a connection to the database using unix socket. 
 """ 
 # Remember - storing secrets in plaintext is potentially unsafe. Consider using 
 # something like https://cloud.google.com/secret-manager/docs/overview to help keep 
 # secrets secret. 
 pool 
 = 
 sqlalchemy 
 . 
 create_engine 
 ( 
 # Equivalent URL: 
 # mysql+pymysql://<db_user>:<db_pass>@/<db_name>?unix_socket=<socket_path>/<cloud_sql_instance_name> 
 sqlalchemy 
 . 
 engine 
 . 
 url 
 . 
 URL 
 . 
 create 
 ( 
 drivername 
 = 
 "mysql+pymysql" 
 , 
 username 
 = 
 db_user 
 , 
 # e.g. "my-database-user" 
 password 
 = 
 db_pass 
 , 
 # e.g. "my-database-password" 
 database 
 = 
 db_name 
 , 
 # e.g. "my-database-name" 
 query 
 = 
 { 
 "unix_socket" 
 : 
 f 
 " 
 { 
 db_socket_dir 
 } 
 / 
 { 
 instance_connection_name 
 } 
 " 
 }, 
 ), 
 ) 
 print 
 ( 
 "Created Unix socket connection pool" 
 ) 
 return 
 pool 
 def 
  
 init_db 
 ( 
 db_user 
 : 
 str 
 , 
 db_pass 
 : 
 str 
 , 
 db_name 
 : 
 str 
 , 
 table_name 
 : 
 str 
 , 
 instance_connection_name 
 : 
 str 
 = 
 None 
 , 
 db_socket_dir 
 : 
 str 
 = 
 None 
 , 
 db_host 
 : 
 str 
 = 
 None 
 , 
 ) 
 - 
> sqlalchemy 
 . 
 engine 
 . 
 base 
 . 
 Engine 
 : 
  
 """Starts a connection to the database and creates voting table if it doesn't exist.""" 
 if 
 db_host 
 : 
 db 
 = 
 init_tcp_connection_engine 
 ( 
 db_user 
 , 
 db_pass 
 , 
 db_name 
 , 
 db_host 
 ) 
 else 
 : 
 db 
 = 
 init_unix_connection_engine 
 ( 
 db_user 
 , 
 db_pass 
 , 
 db_name 
 , 
 instance_connection_name 
 , 
 db_socket_dir 
 ) 
 # Create tables (if they don't already exist) 
 with 
 db 
 . 
 connect 
 () 
 as 
 conn 
 : 
 conn 
 . 
 execute 
 ( 
 f 
 "CREATE TABLE IF NOT EXISTS 
 { 
 table_name 
 } 
 " 
 "( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, " 
 "team CHAR(6) NOT NULL, voter_email VARBINARY(255), " 
 "PRIMARY KEY (vote_id) );" 
 ) 
 print 
 ( 
 f 
 "Created table 
 { 
 table_name 
 } 
 in db 
 { 
 db_name 
 } 
 " 
 ) 
 return 
 db

    Initialize an envelope AEAD primitive with Tink.

    Java

      import 
  
 com.google.crypto.tink.Aead 
 ; 
 import 
  
 com.google.crypto.tink.KmsClient 
 ; 
 import 
  
 com.google.crypto.tink.aead.AeadConfig 
 ; 
 import 
  
 com.google.crypto.tink.aead.AeadKeyTemplates 
 ; 
 import 
  
 com.google.crypto.tink.aead.KmsEnvelopeAead 
 ; 
 import 
  
 com.google.crypto.tink.integration.gcpkms.GcpKmsClient 
 ; 
 import 
  
 java.security.GeneralSecurityException 
 ; 
 public 
  
 class 
 CloudKmsEnvelopeAead 
  
 { 
  
 public 
  
 static 
  
 Aead 
  
 get 
 ( 
 String 
  
 kmsUri 
 ) 
  
 throws 
  
 GeneralSecurityException 
  
 { 
  
 AeadConfig 
 . 
 register 
 (); 
  
 // Create a new KMS Client 
  
 KmsClient 
  
 client 
  
 = 
  
 new 
  
 GcpKmsClient 
 (). 
 withDefaultCredentials 
 (); 
  
 // Create an AEAD primitive using the Cloud KMS key 
  
 Aead 
  
 gcpAead 
  
 = 
  
 client 
 . 
 getAead 
 ( 
 kmsUri 
 ); 
  
 // Create an envelope AEAD primitive. 
  
 // This key should only be used for client-side encryption to ensure authenticity and integrity 
  
 // of data. 
  
 return 
  
 new 
  
 KmsEnvelopeAead 
 ( 
 AeadKeyTemplates 
 . 
 AES128_GCM 
 , 
  
 gcpAead 
 ); 
  
 } 
 }

    Python

      import 
  
 logging 
 import 
  
 tink 
 from 
  
 tink 
  
 import 
 aead 
 from 
  
 tink.integration 
  
 import 
 gcpkms 
 logger 
 = 
 logging 
 . 
 getLogger 
 ( 
 __name__ 
 ) 
 def 
  
 init_tink_env_aead 
 ( 
 key_uri 
 : 
 str 
 , 
 credentials 
 : 
 str 
 ) 
 - 
> tink 
 . 
 aead 
 . 
 KmsEnvelopeAead 
 : 
  
 """ 
 Initiates the Envelope AEAD object using the KMS credentials. 
 """ 
 aead 
 . 
 register 
 () 
 try 
 : 
 gcp_client 
 = 
 gcpkms 
 . 
 GcpKmsClient 
 ( 
 key_uri 
 , 
 credentials 
 ) 
 gcp_aead 
 = 
 gcp_client 
 . 
 get_aead 
 ( 
 key_uri 
 ) 
 except 
 tink 
 . 
 TinkError 
 as 
 e 
 : 
 logger 
 . 
 error 
 ( 
 "Error initializing GCP client: 
 %s 
 " 
 , 
 e 
 ) 
 raise 
 e 
 # Create envelope AEAD primitive using AES256 GCM for encrypting the data 
 # This key should only be used for client-side encryption to ensure authenticity and integrity 
 # of data. 
 key_template 
 = 
 aead 
 . 
 aead_key_templates 
 . 
 AES256_GCM 
 env_aead 
 = 
 aead 
 . 
 KmsEnvelopeAead 
 ( 
 key_template 
 , 
 gcp_aead 
 ) 
 print 
 ( 
 f 
 "Created envelope AEAD Primitive using KMS URI: 
 { 
 key_uri 
 } 
 " 
 ) 
 return 
 env_aead

    Encrypt data and insert it into the database.

    Java

      import 
  
 com.google.crypto.tink.Aead 
 ; 
 import 
  
 java.security.GeneralSecurityException 
 ; 
 import 
  
 java.sql.Connection 
 ; 
 import 
  
 java.sql.PreparedStatement 
 ; 
 import 
  
 java.sql.SQLException 
 ; 
 import 
  
 java.sql.Timestamp 
 ; 
 import 
  
 java.util.Date 
 ; 
 import 
  
 javax.sql.DataSource 
 ; 
 public 
  
 class 
 EncryptAndInsertData 
  
 { 
  
 public 
  
 static 
  
 void 
  
 main 
 ( 
 String 
 [] 
  
 args 
 ) 
  
 throws 
  
 GeneralSecurityException 
 , 
  
 SQLException 
  
 { 
  
 // Saving credentials in environment variables is convenient, but not secure - consider a more 
  
 // secure solution such as Cloud Secret Manager to help keep secrets safe. 
  
 String 
  
 dbUser 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "DB_USER" 
 ); 
  
 // e.g. "root", "mysql" 
  
 String 
  
 dbPass 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "DB_PASS" 
 ); 
  
 // e.g. "mysupersecretpassword" 
  
 String 
  
 dbName 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "DB_NAME" 
 ); 
  
 // e.g. "votes_db" 
  
 String 
  
 instanceConnectionName 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "INSTANCE_CONNECTION_NAME" 
 ); 
  
 // e.g. "project-name:region:instance-name" 
  
 String 
  
 kmsUri 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "CLOUD_KMS_URI" 
 ); 
  
 // e.g. "gcp-kms://projects/...path/to/key 
  
 // Tink uses the "gcp-kms://" prefix for paths to keys stored in Google Cloud KMS. For more 
  
 // info on creating a KMS key and getting its path, see 
  
 // https://cloud.google.com/kms/docs/quickstart 
  
 String 
  
 team 
  
 = 
  
 "TABS" 
 ; 
  
 String 
  
 tableName 
  
 = 
  
 "votes" 
 ; 
  
 String 
  
 email 
  
 = 
  
 "hello@example.com" 
 ; 
  
 // Initialize database connection pool and create table if it does not exist 
  
 // See CloudSqlConnectionPool.java for setup details 
  
 DataSource 
  
 pool 
  
 = 
  
 CloudSqlConnectionPool 
 . 
 createConnectionPool 
 ( 
 dbUser 
 , 
  
 dbPass 
 , 
  
 dbName 
 , 
  
 instanceConnectionName 
 ); 
  
 CloudSqlConnectionPool 
 . 
 createTable 
 ( 
 pool 
 , 
  
 tableName 
 ); 
  
 // Initialize envelope AEAD 
  
 // See CloudKmsEnvelopeAead.java for setup details 
  
 Aead 
  
 envAead 
  
 = 
  
 CloudKmsEnvelopeAead 
 . 
 get 
 ( 
 kmsUri 
 ); 
  
 encryptAndInsertData 
 ( 
 pool 
 , 
  
 envAead 
 , 
  
 tableName 
 , 
  
 team 
 , 
  
 email 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 encryptAndInsertData 
 ( 
  
 DataSource 
  
 pool 
 , 
  
 Aead 
  
 envAead 
 , 
  
 String 
  
 tableName 
 , 
  
 String 
  
 team 
 , 
  
 String 
  
 email 
 ) 
  
 throws 
  
 GeneralSecurityException 
 , 
  
 SQLException 
  
 { 
  
 try 
  
 ( 
 Connection 
  
 conn 
  
 = 
  
 pool 
 . 
 getConnection 
 ()) 
  
 { 
  
 String 
  
 stmt 
  
 = 
  
 String 
 . 
 format 
 ( 
  
 "INSERT INTO %s (team, time_cast, voter_email) VALUES (?, ?, ?);" 
 , 
  
 tableName 
 ); 
  
 try 
  
 ( 
 PreparedStatement 
  
 voteStmt 
  
 = 
  
 conn 
 . 
 prepareStatement 
 ( 
 stmt 
 ); 
  
 ) 
  
 { 
  
 voteStmt 
 . 
 setString 
 ( 
 1 
 , 
  
 team 
 ); 
  
 voteStmt 
 . 
 setTimestamp 
 ( 
 2 
 , 
  
 new 
  
 Timestamp 
 ( 
 new 
  
 Date 
 (). 
 getTime 
 ())); 
  
 // Use the envelope AEAD primitive to encrypt the email, using the team name as 
  
 // associated data. This binds the encryption of the email to the team name, preventing 
  
 // associating an encrypted email in one row with a team name in another row. 
  
 byte 
 [] 
  
 encryptedEmail 
  
 = 
  
 envAead 
 . 
 encrypt 
 ( 
 email 
 . 
 getBytes 
 (), 
  
 team 
 . 
 getBytes 
 ()); 
  
 voteStmt 
 . 
 setBytes 
 ( 
 3 
 , 
  
 encryptedEmail 
 ); 
  
 // Finally, execute the statement. If it fails, an error will be thrown. 
  
 voteStmt 
 . 
 execute 
 (); 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 String 
 . 
 format 
 ( 
 "Successfully inserted row into table %s" 
 , 
  
 tableName 
 )); 
  
 } 
  
 } 
  
 } 
 }

    Python

      import 
  
 datetime 
 import 
  
 logging 
 import 
  
 os 
 import 
  
 sqlalchemy 
 import 
  
 tink 
 from 
  
 .cloud_kms_env_aead 
  
 import 
 init_tink_env_aead 
 from 
  
 .cloud_sql_connection_pool 
  
 import 
 init_db 
 logger 
 = 
 logging 
 . 
 getLogger 
 ( 
 __name__ 
 ) 
 def 
  
 main 
 () 
 - 
> None 
 : 
  
 """ 
 Connects to the database, encrypts and inserts some data. 
 """ 
 db_user 
 = 
 os 
 . 
 environ 
 [ 
 "DB_USER" 
 ] 
 # e.g. "root", "mysql" 
 db_pass 
 = 
 os 
 . 
 environ 
 [ 
 "DB_PASS" 
 ] 
 # e.g. "mysupersecretpassword" 
 db_name 
 = 
 os 
 . 
 environ 
 [ 
 "DB_NAME" 
 ] 
 # e.g. "votes_db" 
 # Set if connecting using TCP: 
 db_host 
 = 
 os 
 . 
 environ 
 [ 
 "DB_HOST" 
 ] 
 # e.g. "127.0.0.1" 
 # Set if connecting using Unix sockets: 
 db_socket_dir 
 = 
 os 
 . 
 environ 
 . 
 get 
 ( 
 "DB_SOCKET_DIR" 
 , 
 "/cloudsql" 
 ) 
 instance_connection_name 
 = 
 os 
 . 
 environ 
 [ 
 "INSTANCE_CONNECTION_NAME" 
 ] 
 # e.g. "project-name:region:instance-name" 
 credentials 
 = 
 os 
 . 
 environ 
 . 
 get 
 ( 
 "GOOGLE_APPLICATION_CREDENTIALS" 
 , 
 "" 
 ) 
 key_uri 
 = 
 "gcp-kms://" 
 + 
 os 
 . 
 environ 
 [ 
 "GCP_KMS_URI" 
 ] 
 # e.g. "gcp-kms://projects/...path/to/key 
 # Tink uses the "gcp-kms://" prefix for paths to keys stored in Google 
 # Cloud KMS. For more info on creating a KMS key and getting its path, see 
 # https://cloud.google.com/kms/docs/quickstart 
 table_name 
 = 
 "votes" 
 team 
 = 
 "TABS" 
 email 
 = 
 "hello@example.com" 
 env_aead 
 = 
 init_tink_env_aead 
 ( 
 key_uri 
 , 
 credentials 
 ) 
 db 
 = 
 init_db 
 ( 
 db_user 
 , 
 db_pass 
 , 
 db_name 
 , 
 table_name 
 , 
 instance_connection_name 
 , 
 db_socket_dir 
 , 
 db_host 
 , 
 ) 
 encrypt_and_insert_data 
 ( 
 db 
 , 
 env_aead 
 , 
 table_name 
 , 
 team 
 , 
 email 
 ) 
 def 
  
 encrypt_and_insert_data 
 ( 
 db 
 : 
 sqlalchemy 
 . 
 engine 
 . 
 base 
 . 
 Engine 
 , 
 env_aead 
 : 
 tink 
 . 
 aead 
 . 
 KmsEnvelopeAead 
 , 
 table_name 
 : 
 str 
 , 
 team 
 : 
 str 
 , 
 email 
 : 
 str 
 , 
 ) 
 - 
> None 
 : 
  
 """ 
 Inserts a vote into the database with email address previously encrypted using 
 a KmsEnvelopeAead object. 
 """ 
 time_cast 
 = 
 datetime 
 . 
 datetime 
 . 
 now 
 ( 
 tz 
 = 
 datetime 
 . 
 timezone 
 . 
 utc 
 ) 
 # Use the envelope AEAD primitive to encrypt the email, using the team name as 
 # associated data. Encryption with associated data ensures authenticity 
 # (who the sender is) and integrity (the data has not been tampered with) of that 
 # data, but not its secrecy. (see RFC 5116 for more info) 
 encrypted_email 
 = 
 env_aead 
 . 
 encrypt 
 ( 
 email 
 . 
 encode 
 (), 
 team 
 . 
 encode 
 ()) 
 # Verify that the team is one of the allowed options 
 if 
 team 
 != 
 "TABS" 
 and 
 team 
 != 
 "SPACES" 
 : 
 logger 
 . 
 error 
 ( 
 f 
 "Invalid team specified: 
 { 
 team 
 } 
 " 
 ) 
 return 
 # Preparing a statement before hand can help protect against injections. 
 stmt 
 = 
 sqlalchemy 
 . 
 text 
 ( 
 f 
 "INSERT INTO 
 { 
 table_name 
 } 
 (time_cast, team, voter_email)" 
 " VALUES (:time_cast, :team, :voter_email)" 
 ) 
 # Using a with statement ensures that the connection is always released 
 # back into the pool at the end of statement (even if an error occurs) 
 with 
 db 
 . 
 connect 
 () 
 as 
 conn 
 : 
 conn 
 . 
 execute 
 ( 
 stmt 
 , 
 time_cast 
 = 
 time_cast 
 , 
 team 
 = 
 team 
 , 
 voter_email 
 = 
 encrypted_email 
 ) 
 print 
 ( 
 f 
 "Vote successfully cast for ' 
 { 
 team 
 } 
 ' at time 
 { 
 time_cast 
 } 
 !" 
 )

    Query the database and decrypt the stored data.

    Java

      import 
  
 com.google.crypto.tink.Aead 
 ; 
 import 
  
 java.security.GeneralSecurityException 
 ; 
 import 
  
 java.sql.Connection 
 ; 
 import 
  
 java.sql.PreparedStatement 
 ; 
 import 
  
 java.sql.ResultSet 
 ; 
 import 
  
 java.sql.SQLException 
 ; 
 import 
  
 java.sql.Timestamp 
 ; 
 import 
  
 javax.sql.DataSource 
 ; 
 public 
  
 class 
 QueryAndDecryptData 
  
 { 
  
 public 
  
 static 
  
 void 
  
 main 
 ( 
 String 
 [] 
  
 args 
 ) 
  
 throws 
  
 GeneralSecurityException 
 , 
  
 SQLException 
  
 { 
  
 // Saving credentials in environment variables is convenient, but not secure - consider a more 
  
 // secure solution such as Cloud Secret Manager to help keep secrets safe. 
  
 String 
  
 dbUser 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "DB_USER" 
 ); 
  
 // e.g. "root", "mysql" 
  
 String 
  
 dbPass 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "DB_PASS" 
 ); 
  
 // e.g. "mysupersecretpassword" 
  
 String 
  
 dbName 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "DB_NAME" 
 ); 
  
 // e.g. "votes_db" 
  
 String 
  
 instanceConnectionName 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "INSTANCE_CONNECTION_NAME" 
 ); 
  
 // e.g. "project-name:region:instance-name" 
  
 String 
  
 kmsUri 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "CLOUD_KMS_URI" 
 ); 
  
 // e.g. "gcp-kms://projects/...path/to/key 
  
 // Tink uses the "gcp-kms://" prefix for paths to keys stored in Google Cloud KMS. For more 
  
 // info on creating a KMS key and getting its path, see 
  
 // https://cloud.google.com/kms/docs/quickstart 
  
 String 
  
 tableName 
  
 = 
  
 "votes123" 
 ; 
  
 // Initialize database connection pool and create table if it does not exist 
  
 // See CloudSqlConnectionPool.java for setup details 
  
 DataSource 
  
 pool 
  
 = 
  
 CloudSqlConnectionPool 
 . 
 createConnectionPool 
 ( 
 dbUser 
 , 
  
 dbPass 
 , 
  
 dbName 
 , 
  
 instanceConnectionName 
 ); 
  
 CloudSqlConnectionPool 
 . 
 createTable 
 ( 
 pool 
 , 
  
 tableName 
 ); 
  
 // Initialize envelope AEAD 
  
 // See CloudKmsEnvelopeAead.java for setup details 
  
 Aead 
  
 envAead 
  
 = 
  
 CloudKmsEnvelopeAead 
 . 
 get 
 ( 
 kmsUri 
 ); 
  
 // Insert row into table to test 
  
 // See EncryptAndInsert.java for setup details 
  
 EncryptAndInsertData 
 . 
 encryptAndInsertData 
 ( 
  
 pool 
 , 
  
 envAead 
 , 
  
 tableName 
 , 
  
 "SPACES" 
 , 
  
 "hello@example.com" 
 ); 
  
 queryAndDecryptData 
 ( 
 pool 
 , 
  
 envAead 
 , 
  
 tableName 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 queryAndDecryptData 
 ( 
 DataSource 
  
 pool 
 , 
  
 Aead 
  
 envAead 
 , 
  
 String 
  
 tableName 
 ) 
  
 throws 
  
 GeneralSecurityException 
 , 
  
 SQLException 
  
 { 
  
 try 
  
 ( 
 Connection 
  
 conn 
  
 = 
  
 pool 
 . 
 getConnection 
 ()) 
  
 { 
  
 String 
  
 stmt 
  
 = 
  
 String 
 . 
 format 
 ( 
  
 "SELECT team, time_cast, voter_email FROM %s ORDER BY time_cast DESC LIMIT 5" 
 , 
  
 tableName 
 ); 
  
 try 
  
 ( 
 PreparedStatement 
  
 voteStmt 
  
 = 
  
 conn 
 . 
 prepareStatement 
 ( 
 stmt 
 ); 
  
 ) 
  
 { 
  
 ResultSet 
  
 voteResults 
  
 = 
  
 voteStmt 
 . 
 executeQuery 
 (); 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Team\tTime Cast\tEmail" 
 ); 
  
 while 
  
 ( 
 voteResults 
 . 
 next 
 ()) 
  
 { 
  
 String 
  
 team 
  
 = 
  
 voteResults 
 . 
 getString 
 ( 
 1 
 ); 
  
 Timestamp 
  
 timeCast 
  
 = 
  
 voteResults 
 . 
 getTimestamp 
 ( 
 2 
 ); 
  
 // Use the envelope AEAD primitive to encrypt the email, using the team name as 
  
 // associated data. This binds the encryption of the email to the team name, preventing 
  
 // associating an encrypted email in one row with a team name in another row. 
  
 String 
  
 email 
  
 = 
  
 new 
  
 String 
 ( 
 envAead 
 . 
 decrypt 
 ( 
 voteResults 
 . 
 getBytes 
 ( 
 3 
 ), 
  
 team 
 . 
 getBytes 
 ())); 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 String 
 . 
 format 
 ( 
 "%s\t%s\t%s" 
 , 
  
 team 
 , 
  
 timeCast 
 , 
  
 email 
 )); 
  
 } 
  
 } 
  
 } 
  
 } 
 }

    Python

      import 
  
 os 
 import 
  
 sqlalchemy 
 import 
  
 tink 
 from 
  
 .cloud_kms_env_aead 
  
 import 
 init_tink_env_aead 
 from 
  
 .cloud_sql_connection_pool 
  
 import 
 init_db 
 from 
  
 .encrypt_and_insert_data 
  
 import 
 encrypt_and_insert_data 
 def 
  
 main 
 () 
 - 
> None 
 : 
  
 """ 
 Connects to the database, inserts encrypted data and retrieves encrypted data. 
 """ 
 db_user 
 = 
 os 
 . 
 environ 
 [ 
 "DB_USER" 
 ] 
 # e.g. "root", "mysql" 
 db_pass 
 = 
 os 
 . 
 environ 
 [ 
 "DB_PASS" 
 ] 
 # e.g. "mysupersecretpassword" 
 db_name 
 = 
 os 
 . 
 environ 
 [ 
 "DB_NAME" 
 ] 
 # e.g. "votes_db" 
 # Set if connecting using TCP: 
 db_host 
 = 
 os 
 . 
 environ 
 [ 
 "DB_HOST" 
 ] 
 # e.g. "127.0.0.1" 
 # Set if connecting using Unix sockets: 
 db_socket_dir 
 = 
 os 
 . 
 environ 
 . 
 get 
 ( 
 "DB_SOCKET_DIR" 
 , 
 "/cloudsql" 
 ) 
 instance_connection_name 
 = 
 os 
 . 
 environ 
 [ 
 "INSTANCE_CONNECTION_NAME" 
 ] 
 # e.g. "project-name:region:instance-name" 
 credentials 
 = 
 os 
 . 
 environ 
 . 
 get 
 ( 
 "GOOGLE_APPLICATION_CREDENTIALS" 
 , 
 "" 
 ) 
 key_uri 
 = 
 "gcp-kms://" 
 + 
 os 
 . 
 environ 
 [ 
 "GCP_KMS_URI" 
 ] 
 # e.g. "gcp-kms://projects/...path/to/key 
 # Tink uses the "gcp-kms://" prefix for paths to keys stored in Google 
 # Cloud KMS. For more info on creating a KMS key and getting its path, see 
 # https://cloud.google.com/kms/docs/quickstart 
 table_name 
 = 
 "votes" 
 team 
 = 
 "TABS" 
 email 
 = 
 "hello@example.com" 
 env_aead 
 = 
 init_tink_env_aead 
 ( 
 key_uri 
 , 
 credentials 
 ) 
 db 
 = 
 init_db 
 ( 
 db_user 
 , 
 db_pass 
 , 
 db_name 
 , 
 table_name 
 , 
 instance_connection_name 
 , 
 db_socket_dir 
 , 
 db_host 
 , 
 ) 
 encrypt_and_insert_data 
 ( 
 db 
 , 
 env_aead 
 , 
 table_name 
 , 
 team 
 , 
 email 
 ) 
 query_and_decrypt_data 
 ( 
 db 
 , 
 env_aead 
 , 
 table_name 
 ) 
 def 
  
 query_and_decrypt_data 
 ( 
 db 
 : 
 sqlalchemy 
 . 
 engine 
 . 
 base 
 . 
 Engine 
 , 
 env_aead 
 : 
 tink 
 . 
 aead 
 . 
 KmsEnvelopeAead 
 , 
 table_name 
 : 
 str 
 , 
 ) 
 - 
> list 
 [ 
 tuple 
 [ 
 str 
 ]]: 
  
 """ 
 Retrieves data from the database and decrypts it using the KmsEnvelopeAead object. 
 """ 
 with 
 db 
 . 
 connect 
 () 
 as 
 conn 
 : 
 # Execute the query and fetch all results 
 recent_votes 
 = 
 conn 
 . 
 execute 
 ( 
 f 
 "SELECT team, time_cast, voter_email FROM 
 { 
 table_name 
 } 
 " 
 "ORDER BY time_cast DESC LIMIT 5" 
 ) 
 . 
 fetchall 
 () 
 print 
 ( 
 "Team 
 \t 
 Email 
 \t 
 Time Cast" 
 ) 
 output 
 = 
 [] 
 for 
 row 
 in 
 recent_votes 
 : 
 team 
 = 
 row 
 [ 
 0 
 ] 
 # Use the envelope AEAD primitive to decrypt the email, using the team name as 
 # associated data. Encryption with associated data ensures authenticity 
 # (who the sender is) and integrity (the data has not been tampered with) of that 
 # data, but not its secrecy. (see RFC 5116 for more info) 
 email 
 = 
 env_aead 
 . 
 decrypt 
 ( 
 row 
 [ 
 2 
 ], 
 team 
 . 
 encode 
 ()) 
 . 
 decode 
 () 
 time_cast 
 = 
 row 
 [ 
 1 
 ] 
 # Print recent votes 
 print 
 ( 
 f 
 " 
 { 
 team 
 } 
 \t 
 { 
 email 
 } 
 \t 
 { 
 time_cast 
 } 
 " 
 ) 
 output 
 . 
 append 
 (( 
 team 
 , 
 email 
 , 
 time_cast 
 )) 
 return 
 output

    1. You can also restrict access at the instance or database level.  ↩

    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: