About client-side encryption

importcom.zaxxer.hikari.HikariConfig;
importcom.zaxxer.hikari.HikariDataSource;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.SQLException;
importjavax.sql.DataSource;
publicclass CloudSqlConnectionPool{
publicstaticDataSourcecreateConnectionPool(StringdbUser,StringdbPass,StringdbName,
StringinstanceConnectionName){
HikariConfigconfig=newHikariConfig();
config.setDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerDataSource");
config.setUsername(dbUser);// e.g. "root", "sqlserver"
config.setPassword(dbPass);// e.g. "my-password"
config.addDataSourceProperty("databaseName",dbName);
// The Cloud SQL Java Connector provides SSL encryption so 
// it should be disabled at the driver level
config.addDataSourceProperty("encrypt","false");
config.addDataSourceProperty("socketFactoryClass",
"com.google.cloud.sql.sqlserver.SocketFactory");
config.addDataSourceProperty("socketFactoryConstructorArg",instanceConnectionName);
DataSourcepool=newHikariDataSource(config);
returnpool;
}
publicstaticvoidcreateTable(DataSourcepool,StringtableName)throwsSQLException{
// Safely attempt to create the table schema.
try(Connectionconn=pool.getConnection()){
Stringstmt=String.format("IF NOT EXISTS("
+"SELECT * FROM sysobjects WHERE name='%s' and xtype='U')"
+"CREATE TABLE %s ("
+"vote_id INT NOT NULL IDENTITY,"
+"time_cast DATETIME NOT NULL,"
+"team VARCHAR(6) NOT NULL,"
+"voter_email VARBINARY(255)"
+"PRIMARY KEY (vote_id));",tableName,tableName);
try(PreparedStatementcreateTableStatement=conn.prepareStatement(stmt);){
createTableStatement.execute();
}
}
}
}

importpytds
importsqlalchemy
fromsqlalchemyimport Column
fromsqlalchemyimport DateTime
fromsqlalchemyimport Integer
fromsqlalchemyimport Table
definit_tcp_connection_engine(
 db_user: str, db_pass: str, db_name: str, db_host: str
) -> sqlalchemy.engine.base.Engine:
 # 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])
 defconnect_with_pytds() -> pytds.Connection:
 return pytds.connect(
 db_hostname, # e.g. "127.0.0.1"
 user=db_user, # e.g. "my-database-user"
 password=db_pass, # e.g. "my-database-password"
 database=db_name, # e.g. "my-database-name"
 port=db_port, # e.g. 1433
 bytes_to_unicode=False, # disables automatic decoding of bytes
 )
 pool = sqlalchemy.create_engine(
 # This allows us to use the pytds sqlalchemy dialect, but also set the
 # bytes_to_unicode flag to False, which is not supported by the dialect
 "mssql+pytds://",
 creator=connect_with_pytds,
 )
 print("Created TCP connection pool")
 return pool
definit_db(
 db_user: str,
 db_pass: str,
 db_name: str,
 db_host: str,
 table_name: str,
) -> sqlalchemy.engine.base.Engine:
 db = init_tcp_connection_engine(db_user, db_pass, db_name, db_host)
 # Create tables (if they don't already exist)
 if not db.has_table(table_name):
 metadata = sqlalchemy.MetaData(db)
 Table(
 table_name,
 metadata,
 Column("vote_id", Integer, primary_key=True, nullable=False),
 Column("voter_email", sqlalchemy.types.VARBINARY, nullable=False),
 Column("time_cast", DateTime, nullable=False),
 Column("team", sqlalchemy.types.VARCHAR(6), nullable=False),
 )
 metadata.create_all()
 print(f"Created table {table_name} in db {db_name}")
 return db

importcom.google.crypto.tink.Aead;
importcom.google.crypto.tink.KmsClient;
importcom.google.crypto.tink.aead.AeadConfig;
importcom.google.crypto.tink.aead.AeadKeyTemplates;
importcom.google.crypto.tink.aead.KmsEnvelopeAead;
importcom.google.crypto.tink.integration.gcpkms.GcpKmsClient;
importjava.security.GeneralSecurityException;
publicclass CloudKmsEnvelopeAead{
publicstaticAeadget(StringkmsUri)throwsGeneralSecurityException{
AeadConfig.register();
// Create a new KMS Client
KmsClientclient=newGcpKmsClient().withDefaultCredentials();
// Create an AEAD primitive using the Cloud KMS key
AeadgcpAead=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.
returnnewKmsEnvelopeAead(AeadKeyTemplates.AES128_GCM,gcpAead);
}
}

importlogging
importtink
fromtinkimport aead
fromtink.integrationimport gcpkms
logger = logging.getLogger(__name__)
definit_tink_env_aead(key_uri: str, credentials: str) -> tink.aead.KmsEnvelopeAead:
 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

importcom.google.crypto.tink.Aead;
importjava.security.GeneralSecurityException;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.SQLException;
importjava.sql.Timestamp;
importjava.util.Date;
importjavax.sql.DataSource;
publicclass EncryptAndInsertData{
publicstaticvoidmain(String[]args)throwsGeneralSecurityException,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.
StringdbUser=System.getenv("DB_USER");// e.g. "root", "mysql"
StringdbPass=System.getenv("DB_PASS");// e.g. "mysupersecretpassword"
StringdbName=System.getenv("DB_NAME");// e.g. "votes_db"
StringinstanceConnectionName=
System.getenv("INSTANCE_CONNECTION_NAME");// e.g. "project-name:region:instance-name"
StringkmsUri=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
Stringteam="TABS";
StringtableName="votes";
Stringemail="hello@example.com";
// Initialize database connection pool and create table if it does not exist
// See CloudSqlConnectionPool.java for setup details
DataSourcepool=
CloudSqlConnectionPool.createConnectionPool(dbUser,dbPass,dbName,instanceConnectionName);
CloudSqlConnectionPool.createTable(pool,tableName);
// Initialize envelope AEAD
// See CloudKmsEnvelopeAead.java for setup details
AeadenvAead=CloudKmsEnvelopeAead.get(kmsUri);
encryptAndInsertData(pool,envAead,tableName,team,email);
}
publicstaticvoidencryptAndInsertData(
DataSourcepool,AeadenvAead,StringtableName,Stringteam,Stringemail)
throwsGeneralSecurityException,SQLException{
try(Connectionconn=pool.getConnection()){
Stringstmt=
String.format(
"INSERT INTO %s (team, time_cast, voter_email) VALUES (?, ?, ?);",tableName);
try(PreparedStatementvoteStmt=conn.prepareStatement(stmt);){
voteStmt.setString(1,team);
voteStmt.setTimestamp(2,newTimestamp(newDate().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));
}
}
}
}

importdatetime
importlogging
importos
importsqlalchemy
importtink
from.cloud_kms_env_aeadimport init_tink_env_aead
from.cloud_sql_connection_poolimport init_db
logger = logging.getLogger(__name__)
defmain() -> None:
 db_user = os.environ["DB_USER"] # e.g. "root", "sqlserver"
 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)
defencrypt_and_insert_data(
 db: sqlalchemy.engine.base.Engine,
 env_aead: tink.aead.KmsEnvelopeAead,
 table_name: str,
 team: str,
 email: str,
) -> None:
 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, CONVERT(varbinary(max), :voter_email, 0))"
 )
 # 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}!")

importcom.google.crypto.tink.Aead;
importjava.security.GeneralSecurityException;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Timestamp;
importjavax.sql.DataSource;
publicclass QueryAndDecryptData{
publicstaticvoidmain(String[]args)throwsGeneralSecurityException,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.
StringdbUser=System.getenv("DB_USER");// e.g. "root", "mysql"
StringdbPass=System.getenv("DB_PASS");// e.g. "mysupersecretpassword"
StringdbName=System.getenv("DB_NAME");// e.g. "votes_db"
StringinstanceConnectionName=
System.getenv("INSTANCE_CONNECTION_NAME");// e.g. "project-name:region:instance-name"
StringkmsUri=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
StringtableName="votes123";
// Initialize database connection pool and create table if it does not exist
// See CloudSqlConnectionPool.java for setup details
DataSourcepool=
CloudSqlConnectionPool.createConnectionPool(dbUser,dbPass,dbName,instanceConnectionName);
CloudSqlConnectionPool.createTable(pool,tableName);
// Initialize envelope AEAD
// See CloudKmsEnvelopeAead.java for setup details
AeadenvAead=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);
}
publicstaticvoidqueryAndDecryptData(DataSourcepool,AeadenvAead,StringtableName)
throwsGeneralSecurityException,SQLException{
try(Connectionconn=pool.getConnection()){
Stringstmt=
String.format(
"SELECT TOP(5) team, time_cast, voter_email FROM %s ORDER BY time_cast DESC;",
tableName);
try(PreparedStatementvoteStmt=conn.prepareStatement(stmt);){
ResultSetvoteResults=voteStmt.executeQuery();
System.out.println("Team\tTime Cast\tEmail");
while(voteResults.next()){
Stringteam=voteResults.getString(1);
TimestamptimeCast=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.
Stringemail=newString(envAead.decrypt(voteResults.getBytes(3),team.getBytes()));
System.out.println(String.format("%s\t%s\t%s",team,timeCast,email));
}
}
}
}
}

importos
importsqlalchemy
importtink
from.cloud_kms_env_aeadimport init_tink_env_aead
from.cloud_sql_connection_poolimport init_db
from.encrypt_and_insert_dataimport encrypt_and_insert_data
defmain() -> None:
 db_user = os.environ["DB_USER"] # e.g. "root", "sqlserver"
 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)
defquery_and_decrypt_data(
 db: sqlalchemy.engine.base.Engine,
 env_aead: tink.aead.KmsEnvelopeAead,
 table_name: str,
) -> None:
 with db.connect() as conn:
 # Execute the query and fetch all results
 recent_votes = conn.execute(
 f"SELECT TOP(5) team, time_cast, voter_email FROM {table_name} "
 "ORDER BY time_cast DESC"
 ).fetchall()
 print("Team\tEmail\tTime Cast")
 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).decode()
 time_cast = row[1]
 # Print recent votes
 print(f"{team}\t{email}\t{time_cast}")