Using JDBC to Operate an Encrypted Database

Obtain the JDBC driver package.

1. Obtain the JDBC driver package.

   The encrypted database supports the gsjdbc4.jar, opengaussjdbc.jar, and gscejdbc.jar driver packages.

   • gsjdbc4.jar: The main class name is org.postgresql.Driver, and the URL prefix of the database connection is jdbc:postgresql.
   • opengaussjdbc.jar: The main class name is com.huawei.opengauss.jdbc.Driver, and the URL prefix of the database connection is jdbc:opengauss.
   • gscejdbc.jar (supported only in some OSs): The main class name is com.huawei.gaussdb.jdbc.Driver. The URL prefix of the database connection is jdbc:gaussdb. The driver package is recommended in encrypted scenarios. If the driver package does not contain gscejdbc.jar, you can also use the opengaussjdbc.jar or gsjdbc4.jar package.
2. Configure LD_LIBRARY_PATH.

   Before using the JDBC driver package in encrypted scenarios, you need to set the environment variable LD_LIBRARY_PATH.

   • When the gscejdbc.jar driver package is used, the dependent library required by the encrypted database in the gscejdbc.jar driver package is automatically copied to the path and loaded when the encrypted function is enabled to connect to the database.
   • When using opengaussjdbc.jar or gsjdbc4.jar, you need to decompress GaussDB-Kernel_Database version number_OS version number_64bit_libpq.tar.gz to a specified directory, and add the path of the lib folder to the LD_LIBRARY_PATH environment variable.
     

     To use the JDBC driver package in the full-encryption scenario, you must have the System.loadLibrary permission as well as the read and write permissions on files in the first-priority path of the environment variable LD_LIBRARY_PATH. You are advised to use an independent directory to store the full-encryption dependent library. If java.library.path is specified during execution, the value must be the same as the first-priority path of LD_LIBRARY_PATH.

When gscejdbc.jar is used, JVM loading class files depends on the libstdc++ library of the system. If the encryption mode is enabled, gscejdbc.jar automatically copies the dynamic libraries (including the libstdc++ library) on which the encryption database depends to the LD_LIBRARY_PATH path set by the user. If the version of a dependent library does not match that of the existing system library, only the dependent library is deployed during the first running. After the dependent library is invoked again, it can be used normally.

Executing SQL Statements

Before running the SQL statements in this section, ensure that the preparation and configuration phases are complete.

This section uses a complete execution process as an example to describe how to use the encrypted database syntax, including three phases: DDL statement execution, DML statement execution, and cleanup.

For details about JDBC development operations that are the same as those in non-encrypted scenarios, see "Application Development Guide > Development Based on JDBC" in Developer Guide.

• Connection parameters of an encrypted database

  enable_ce: string type. If enable_ce is set to 0, the full encryption function is disabled. If enable_ce is set to 1, the basic capability of encrypted equality query is supported. If enable_ce is set to 2, client sorting is supported based on the encrypted equality query capability (lab feature). If enable_ce is set to 3, software and hardware integration is supported based on the encrypted equality query capability. (The current feature is a lab feature. Contact Huawei technical support before using it.)

  // The following uses gs_ktool as an example. Before executing the test case, run the gs_ktool -g command on the client to generate a key file.
  
  // The following uses the gscejdbc.jar driver as an example. If other driver packages are used, you only need to change the driver class name and the URL prefix of the database connection.
  // gsjdbc4.jar: The main class name is org.postgresql.Driver, and the URL prefix of the database connection is jdbc:postgresql.
  // opengaussjdbc.jar: The main class name is com.huawei.opengauss.jdbc.Driver, and the URL prefix of the database connection is jdbc:opengauss.
  // gscejdbc.jar: The main class name is com.huawei.gaussdb.jdbc.Driver, and the URL prefix of the database connection is jdbc:gaussdb.
  
  public static void main(String[] args) {
      // Driver class.
      String driver = "com.huawei.gaussdb.jdbc.Driver";
      // Database connection descriptor. If enable_ce is set to 1, the basic capability of encrypted equality query is supported.
      String sourceURL = "jdbc:gaussdb://10.10.0.13:8000/postgres?enable_ce=1";
      String username = "admin";
      String passwd = "Gauss_234";
      Connection conn = null;
      try {
          // Load the driver.
          Class.forName(driver);
          // Create a connection.
          conn = DriverManager.getConnection(sourceURL, username, passwd);
          System.out.println("Connection succeed!");
          // Create a statement object.
          Statement stmt = conn.createStatement();
  
          // Create a CMK.
          // The following describes how to create a master key in multiple scenarios. Select one of the following methods as required: key management tool gs_ktool, Huawei Cloud key management service (huawei_kms).
          // For details about the KEY_PATH format, see "SQL Reference > SQL Syntax > CREATE CLIENT MASTER KEY" in Developer Guide.
          int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = gs_ktool , KEY_PATH = \"gs_ktool/1\" , ALGORITHM = AES_256_CBC);");
          // In the Huawei Cloud scenario, the project ID and key ID are required in KEY_PATH. For details about how to obtain the key ID, see the preparation phase. For details about how to obtain the project ID, see the configuration phase.
          // int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = huawei_kms , KEY_PATH = 'https://kms.cn-north-4.myhuaweicloud.com/v1.0/00000000000000000000000000000000/kms/00000000-0000-0000-0000-00000000000', ALGORITHM = AES_256);");
  
          // Create a CEK.
          int rc2 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AES_256_GCM);");
          // Create an encrypted table.
          int rc3 = stmt.executeUpdate("CREATE TABLE creditcard_info (id_number int, name varchar(50) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC),credit_card varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));");
          // Insert data.
          int rc4 = stmt.executeUpdate("INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');");
          // Query the encrypted table.
          ResultSet rs = null;
          rs = stmt.executeQuery("select * from creditcard_info where name = 'joe';");
          // Delete the encrypted table.
          int rc5 = stmt.executeUpdate("DROP TABLE IF EXISTS creditcard_info;");
          // Delete a CEK.
          int rc6 = stmt.executeUpdate("DROP COLUMN ENCRYPTION KEY IF EXISTS ImgCEK1;");
          // Delete the CMK.
          int rc7 = stmt.executeUpdate("DROP CLIENT MASTER KEY IF EXISTS ImgCMK1;");
          // Close the statement object.
          stmt.close();
          // Close the connection.
          conn.close();
      } catch (Exception e) {
          e.printStackTrace();
          return;
      }
  }

Example of Calling the IsValid Method to Refresh the Cache

// Create a connection conn1.
Connection conn1 = DriverManager.getConnection("url","user","password");
// Create a CMK in another connection conn2.
...
// conn1 calls the IsValid method to refresh the cache.
try {
	if (!conn1.isValid(60)) {
		System.out.println("isValid Failed for connection 1");
	}
} catch (SQLException e) {
	e.printStackTrace();
        return null;
}

Decrypting the Encrypted Equality Ciphertext

The decryption interface is added to the database connection interfaces of the PgConnection class. The decryption interface can be used to decrypt the encrypted equality ciphertext of the fully-encrypted database. After decryption, the plaintext value is returned. The ciphertext column corresponding to the decryption is found based on schema.table.column and the original data type is returned.

// After the ciphertext is obtained through non-encrypted connection or logical decoding, this interface can be used to decrypt the ciphertext.
import org.postgresql.jdbc.PgConnection;
import org.postgresql.jdbc.clientlogic.ClientLogicDecryptResult;

// conn is an encrypted connection.
// Call the decryptData method of PgConnection to decrypt the ciphertext, locate the encrypted column to which the ciphertext belongs based on the column name, and return the original data type.
ClientLogicDecryptResult decrypt_res = null;
decrypt_res = ((PgConnection)conn).decryptData(ciphertext, ciphertext.length(), schemaname_str,
        tablename_str, colname_str);
// Check whether the decryption of the returned result class is successful. If the decryption fails, obtain the error information. If the decryption is successful, obtain the plaintext, length, and original data type.
if (decrypt_res.isFailed()) {
    System.out.println(String.format("%s\n", decrypt_res.getErrMsg()));
} else {
    System.out.println(String.format("decrypted plaintext: %s size: %d type: %s\n", decrypt_res.getPlaintext(),
        decrypt_res.getPlaintextSize(), decrypt_res.getOriginalType()));
}

Precompiling an Encrypted Table

// Create a prepared statement object by calling the prepareStatement method in Connection.
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO creditcard_info VALUES (?, ?, ?);");
// Set parameters by triggering the setShort method in PreparedStatement.
pstmt.setInt(1, 2);
pstmt.setString(2, "joy");
pstmt.setString(3, "6219985678349800033");
// Execute the precompiled SQL statement by triggering the executeUpdate method in PreparedStatement.
int rowcount = pstmt.executeUpdate();
// Close the precompiled statement object by calling the close method in PreparedStatement.
pstmt.close();

Batch Processing on an Encrypted Table

// Create a prepared statement object by calling the prepareStatement method in Connection.
Connection conn = DriverManager.getConnection("url","user","password");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO batch_table (id, name, address) VALUES (?,?,?)");
// Call the setShort method for each piece of data, and call addBatch to confirm that the setting is complete.
int loopCount = 20;
 for (int i = 1; i < loopCount + 1; ++i) {
      statemnet.setInt(1, i);
      statemnet.setString(2, "Name " + i);
      statemnet.setString(3, "Address " + i);
      // Add row to the batch.
      statemnet.addBatch();
}
// Execute batch processing by calling the executeBatch method in PreparedStatement.
int[] rowcount = pstmt.executeBatch();
// Close the precompiled statement object by calling the close method in PreparedStatement.
pstmt.close();