Using Two-way Encryption Functions with Java SP in CUBRID

posted Jul 17, 2020


Author: Youngjin Joo


CUBRID DBMS ('CUBRID') supports only one-way (MD5, SHA1, SHA2) encryption functions, and does not support two-way encryption functions. One-way encryption functions can be used when the encrypted values such as passwords are not decrypted and used. However, as with personal information, encryption is essential, and if decryption is required, a two-way encryption function should be used.


Currently, when a database receives data, it can receive encrypted data using API methods provided by an encryption solution provider. By implementing a Java Stored Function/Procedure (hereinafter referred to as'Java SP') that uses an external library, the database receives plain text data and encrypts it.


CUBRID supports Java SP, so if you can implement it with Java, you can create and add new functions. Therefore, the two-way encryption function can be used as a function of CUBRID as provided by the encryption solution provider.


If you search for 'Implementing a Java two-way encryption function', many people have already created a two-way encryption function using the Java native library. This blog will summarize these and cover the use of the two-way encryption functions from CUBRID to Java SP.


We will use AES256, a symmetric key block encryption algorithm, to implement the two-way encryption function.


First, we need to build a development environment:

  • Server OS: CentOS Version 7.6.
Shell>cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)
Shell> uname -a
Linux localhost.localdomain 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux



Secondly, we need to install OPENJDK.

We will check the OpenJDK version one more time in a while, but now we have installed version 1.8.0_232.

Shell> sudo yum install -y java-1.8.0-openjdk-devel.x86_64
Shell> java -version
openjdk version "1.8.0_232"
OpenJDK Runtime Environment (build 1.8.0_232-b09)
OpenJDK 64-Bit Server VM (build 25.232-b09, mixed mode)
Shell> javac -version
javac 1.8.0_232


Now, install CUBRID (you can download the latest version of CUBRID from I have created a CUBRID account to manage CUBRID independently.

Shell> sudo useradd cubrid
Shell> sudo su - cubrid


You can also install it with a shell script installation file, but here we will download and install the Tarball installation file.

Shell> curl -O
Shell> tar -zxvf CUBRID-


* The tarball installation file does not contain a file that sets the environment variables required to use CUBRID. So you have to create a separate file.

Shell> cat << EOF >
export CUBRID=/home/cubrid/CUBRID
export CUBRID_DATABASES=\$CUBRID/databases
if [ ! -z \$LD_LIBRARY_PATH ]; then
export PATH=\$CUBRID/bin:\$PATH
export TMPDIR=\$CUBRID/tmp
if [ ! -d \$TMPDIR ]; then
    mkdir -p \$TMPDIR
if [ ! -d \$CUBRID_TMP ]; then
    mkdir -p \$CUBRID_TMP
export JAVA_HOME=/usr/lib/jvm/java
export PATH=\$JAVA_HOME/bin:\$PATH
export CLASSPATH=.
export LD_LIBRARY_PATH=\$JAVA_HOME/jre/lib/amd64:\$JAVA_HOME/jre/lib/amd64/server:\$LD_LIBRARY_PATH


After you have finished setting up the environment variables by running the file, you can run the cbrid_rel utility to see the current version of CUBRID.

Shell> cat << EOF >> $HOME/.bash_profile
. /home/cubrid/
Shell> . $HOME/.bash_profile
Shell> cubrid_rel
CUBRID 10.2 ( (64bit release build for Linux) (Dec  5 2019 21:42:17)


Since the environment variables related to Java are also set in the file, you can check the JRE and JDK versions with the following command.

Shell> java -version
openjdk version "1.8.0_232"
OpenJDK Runtime Environment (build 1.8.0_232-b09)
OpenJDK 64-Bit Server VM (build 25.232-b09, mixed mode)
Shell> javac -version
javac 1.8.0_232


A database must be created to test the Java SP. We will create the directory to create the database first, and then create the database with the createdb utility. And I will set it to start automatically when the CUBRID service starts.

Shell> mkdir -p $HOME/databases/demodb/java
Shell> ln -s $HOME/databases $CUBRID/databases
Shell> cubrid createdb -F $HOME/databases/demodb demodb ko_KR.utf8
Shell> sed s/#server=foo,bar/server=demodb/g -i $CUBRID/conf/cubrid.conf

Note that the java directory is automatically created when the Java Class file is loaded with the loadjava utility. In this case, while creating a directory to create a database, I also created a java directory.


Java SP is not used in CUBRID default setting. To use Java SP, set java_stored_procedure=y in $CUBRID/conf/cubrid.conf configuration file.

Shell> cat << EOF >> $CUBRID/conf/cubrid.conf
> java_stored_procedure=y


Now that we have completed the installation and all settings, we will start the CUBRID service. If the CUBRID service starts normally, check the java_stored_procedure setting of the demodb database (hereinafter'demodb') created above. Since java_stored_procedure=y, you can use Java SP normally.

Shell> cubrid service start
Shell> cubrid paramdump demodb | grep java


The jps utility and the ps command allow you to see the JVM process used by demodb.

Shell> jps -v
17085  -Djava.util.logging.config.file=/home/cubrid/CUBRID/java/ -Xrs
Shell> ps -ef | grep 17085
cubrid    17085      1  2 13:25 ?        00:06:04 cub_server demodb


We have finished building the development environment so far. Now let's look at a Java program that implements a two-way encryption function!



The Java SP can only invoke the static method. So we declared the variables to be used in the Static Method as Static variables, and we reset them in the Static Initialization Block.

public class CryptoTest_AES256 {
       private static String keyFileName = "crypto-test_aes256.jck";
       private static String keyPath = "/home/cubrid/keystore/";
       private static String keyAlias = "crypto-test_aes256";
       private static String keystorePassword = "keystorePassword";
       private static String keyPassword = "keyPassword";
       private static Key secretKey = null;
       private static Cipher encryptCipher = null;
       private static Cipher decryptCipher = null;
       static {
              try {
                     File keyFile = new File(keyPath + keyFileName);
                     InputStream inputStream = new FileInputStream(keyFile);
                     KeyStore keystore = KeyStore.getInstance("JCEKS");
                     keystore.load(inputStream, keystorePassword.toCharArray());
                     secretKey = keystore.getKey(keyAlias, keyPassword.toCharArray());
                     encryptCipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
                     decryptCipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
              } catch ...


For encryption/decryption, an encryption key is required, and in a symmetric key block encryption algorithm, the encryption key used for encryption/decryption must be the same.


The JDK includes a utility called keytool for managing cryptographic keys and certificates. Here, we will use the keytool utility to generate an encryption key and store it in the encryption keystore file.

  • Using the -genseckey option, create a cryptographic keystore (hereinafter referred to as the'keystore') named crypto-test_aes256.jck in the $HOME/keystore location.
  • You can check the list of cryptographic keys stored in the crypto-test_aes256.jck file with the -list option.
Shell> mkdir -p $HOME/keystore
Shell> cd $HOME/keystore
Shell> keytool -genseckey -keystore crypto-test_aes256.jck -storetype jceks -storepass keystorepassword -keyalg AES -keysize 256 -alias crypto-test_aes256 -keypass keypassword
Shell> keytool -v -list -keystore crypto-test_aes256.jck -storetype jceks


Let's go back to the Java code and take a look at the keystore that we just created. The keystore password is required when importing the keystore. In the key store, when importing an encryption key, an encryption key name and an encryption key password are required.

File keyFile = new File(keyPath + keyFileName);
InputStream inputStream = new FileInputStream(keyFile);
KeyStore keystore = KeyStore.getInstance("JCEKS");
keystore.load(inputStream, keystorePassword.toCharArray());
secretKey = keystore.getKey(keyAlias, keyPassword.toCharArray());


When an instance used for encryption/decryption is obtained from the Cipher class, a string containing an encryption algorithm is passed as a parameter. The transfer factor specifies the encryption algorithm, operation method, and padding method.

  • The encryption algorithm:  it specifies the AES method that was originally used.​
encryptCipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
decryptCipher = Cipher.getInstance("AES/CBC/PKCS5Padding");


  • Block Encryption Operation Method:

It might be difficult to explain full detail of encryption/decryption, but according to wikipedia- Block Encryption Operation Method


‘In cryptography, block cipher modes of operation refer to the procedure of repeatedly and safely using block ciphers under one key. Since block ciphers operate in blocks of a specific length, variable length data In order to encrypt, you must first divide them into unit blocks and decide how to encrypt the blocks. At this time, the encryption method of blocks is called the operation method.’


Many modes of operation have been defined. And in this article we will talk about two mode of them: Electronic codebook (ECB) and  Ciper block chaining (CBC). Electronic codebook (ECB) method is said to be vulnerable to security, so we will use CBC method, which is widely used here.


Wikipedia- Block Encryption Operation Method:

‘The electronic codebook is vulnerable to security because all blocks use the same encryption key. If two blocks have the same value when the encryption message is divided into multiple parts, the encryption result is the same. It is also vulnerable to repeated attacks of repeatedly encrypting. (Wikipedia-Block cipher operation method)’


‘Each block is XORed with the previous block's encryption result before encryption, and in the first block, an initialization vector is used. If the initialization vector is the same, the output result is always the same, so a different initialization vector must be used for each encryption. CBC The method is one of the widely used operation methods.’



  • The Padding Method:

The padding method is to keep the length of the blocks to be encrypted constant. When using AES/CBC, the padding method is set by the JDK to use PKCS5Padding.



‘Padding" refers to filling empty areas to keep the block size constant in the block encryption algorithm. Block encryption algorithms such as AES and Triple DES in Electronic Code Book (ECB) and Cipher Block Chaining (CBC) modes The input must be an exact multiple of the block size (64-bit or 128-bit), if the original text size is not a multiple of 16 bytes (64-bit or 128-bit), the last block will be less than 16 bytes. The way to fill in the empty part of the last block is called padding.’


When I go back to Java code and encrypt/decrypt, the methods I call are executeEncrypt and executeDecrypt. However, in fact, encryption/decryption is performed by the encrypt and decrypt methods.


When encrypting/decrypting, I had to send and receive values as a byte[] array, but if I just stored the byte[] array in a String type, an encoding problem occurred. So, execute the functions [executeEncrypt] and [executeDecrypt] to encode and decode the byte[] array in Base64 and pass it to encrypt and decrypt.


public static String executeEncrypt(String plainString) {
    byte[] ivEncryptByteArray = encrypt(plainString.getBytes(StandardCharsets.UTF_8));
    return Base64.getEncoder().encodeToString(ivEncryptByteArray);
public static String executeDecrypt(String encryptString) {
    byte[] ivEncryptByteArray = Base64.getDecoder().decode(encryptString.getBytes(StandardCharsets.UTF_8));
    byte[] decryptByteArray = decrypt(ivEncryptByteArray);
    return new String(decryptByteArray, StandardCharsets.UTF_8);


Encryption/decryption is done by calling the doFinal() method on the encryptCipher and decryptCipher instances. Even so, the reason the code looks complicated is because of the initialization vector used by the CBC method when dealing with the block cipher operation method above.


When using the CBC method as the operation method, it is recommended to use a different initialization vector for each encryption. So, we need to change the initialization vector every encryption, but we can't decrypt it without knowing the initialization vector used to encrypt it.


Wikipedia- Block Encryption Operation Method:

"An initialization vector has different security requirements than a key, so the IV usually does not need to be secret. However, in most cases, it is important that an initialization vector is never reused under the same key. (Wikipedia - Block cipher mode of operation - Initialization vector (IV))"


Fortunately, there is no problem in publishing the initialization vector, so we decided to prepend the initialization vector used for encryption before the encrypted value. Then, even if the initialization vector is different for each encryption, the initialization vector can be used separately from the encrypted value, so it can be decrypted normally.


public static byte[] encrypt(byte[] plainByteArray) {
    SecureRandom secureRandom = new SecureRandom();
    byte[] iv = new byte[16];
    IvParameterSpec ivParameterSpec = new IvParameterSpec(iv);
    byte[] encryptByteArray = null;
    try {
        encryptCipher.init(Cipher.ENCRYPT_MODE, secretKey, ivParameterSpec);
        encryptByteArray = encryptCipher.doFinal(plainByteArray);
    } catch ...
    byte[] ivEncryptByteArray = new byte[iv.length + encryptByteArray.length];
    System.arraycopy(iv, 0, ivEncryptByteArray, 0, iv.length);
    System.arraycopy(encryptByteArray, 0, ivEncryptByteArray, iv.length, encryptByteArray.length);
    return ivEncryptByteArray;
public static byte[] decrypt(byte[] ivEncryptByteArray) {
    byte[] iv = new byte[16];
    byte[] encryptByteArray = new byte[ivEncryptByteArray.length - iv.length];
    System.arraycopy(ivEncryptByteArray, 0, iv, 0, iv.length);
    System.arraycopy(ivEncryptByteArray, iv.length, encryptByteArray, 0, encryptByteArray.length);
    IvParameterSpec ivParameterSpec = new IvParameterSpec(iv);
    byte[] decryptByteArray = null;
    try {
        decryptCipher.init(Cipher.DECRYPT_MODE, secretKey, ivParameterSpec);
        decryptByteArray = decryptCipher.doFinal(encryptByteArray);
    } catch ...
    return decryptByteArray;

The Java program code ends here.


Go to demodb's java directory, save the code, and compile it is the same as running the loadjava utility.

Shell> cd $HOME/databases/demodb/java
Shell> vi
Shell> javac


Let's connect to demodb with CUBRID's csql utility to create a Java SP and test it.

Shell> csql -u dba demodb
csql> CREATE FUNCTION encrypt (plain_string VARCHAR) RETURN VARCHAR AS LANGUAGE JAVA NAME 'CryptoTest_AES256.executeEncrypt(java.lang.String) return java.lang.String';
csql> CREATE FUNCTION decrypt (encrypt_string VARCHAR) RETURN VARCHAR AS LANGUAGE JAVA NAME 'CryptoTest_AES256.executeDecrypt(java.lang.String) return java.lang.String';
csql> create table t1 (c1 varchar);
csql> insert into t1 values (encrypt('CUBRID is an object-relational database management system and consists of a database server, a broker, and a CUBRID manager.'));
1 row affected. (0.012436 sec) Committed.
csql> select decrypt(c1) from t1;
  'CUBRID is an object-relational database management system and consists of a database server, a broker, and a CUBRID manager.'
1 row selected. (0.015956 sec) Committed.


In the example, the encryption/decryption works well!


If you are using it as a reference, please keep in mind that a lot of testing is needed to see if there are any values that can't be encrypted or if there are no performance issues. And the keytool utility provided by the JDK is also required to verify the safety of storing the encryption key.