Encryption Functions

Encryption Functions#

Altibase provides functions for encrypting strings and decrypting encrypted strings. The encryption algorithms that are available in Altibase are Data Encryption Standard (DES) and Advanced Encryption Standard (AES).

The encryption functions of Altibase perform block encryption in Cipher Block Chaining (CBC) mode. The DES algorithm is used to encrypt blocks in 8 bytes, and the more advanced AES algorithm is used to encrypt blocks in 16 bytes. If a string to encrypt is not a multiple of 8 or 16 bytes in length, it can be padded with characters to fill the required length by using the PKCS7PAD16 function.

The encryption functions using these algorithms are listed below.

AES Algorithms#

AESDECRYPT, AESENCRYPT

DES Algorithms#

DESDECRYPT, DESENCRYPT, TDESDECRYPT, TDESENCRYPT

AESDECRYPT#

Syntax#

AESDECRYPT (VARCHAR  encrypted_string,
            VARCHAR  key_string)

Description#

encrypted_string

This is the string to decrypt. The length of this string must be a multiple of 16.
key_string

This is the string that was used as the encryption key. The minimum length of this string is 16 characters.

Note:

Outputting an encrypted string to the screen can cause a terminal emulator error

Examples#

Save an encrypted string to a table and output the decryption.

1) Insert an encrypted text to the table.

CREATE TABLE t1( encrypted_string VARCHAR(40) );
INSERT INTO t1 
VALUES(AESENCRYPT(PKCS7PAD16('ABC AES TEST'), 'WORRAPS1WORRAPS2'));

2) Decrypt the encrypted text by using the same key that was used to encrypt it, and output the decryption.

SELECT PKCS7UNPAD16(AESDECRYPT(encrypted_string, 'WORRAPS1WORRAPS2')) 
  FROM t1;

PKCS7UNPAD16(AESDECRYPT(encrypted_string, 'WORRAPS1WORRA 
-----------------------------------------------------
ABC AES TEST 
1 row selected.

AESENCRYPT#

Syntax#

AESENCRYPT (VARCHAR  expr,
            VARCHAR  key_string)

Description#

expr

This is the string to encrypt. The length of this string must be a multiple of 16.
key_string

This is the string that will be used as the encryption key. The minimum length of this string is 16 characters. Depending on the input key_string length, the following portion of the string is used as the encryption key, and the other characters are ignored.
- 16 <= *key_string length < 24: 16
- 24 <= *key_string length < 32: 24
- *key_string length >= 32: 32

Example#

Please refer to the examples for AESDECRYPT

DESDECRYPT#

Syntax#

DESDECRYPT (VARCHAR  encrypted_string,
            VARCHAR  key_string)

Description#

encrypted_string

This is the string to encrypt. The length of the string must be a multiple of 8.
key_string

This is the string that will be used as the encryption key. The minimum length of key_string is 8 characters. The 9th and subsequent characters are ignored.

Note:

Outputting encrypted strings to the screen can cause terminal emulator errors.

Example 1#

Store, decrypt, and display the encrypted text in a table.

1) Insert encrypted text into a table

CREATE TABLE t1( encrypted_string VARCHAR(40) );
INSERT INTO t1 
VALUES( DESENCRYPT( 'A4 ALTIBASE Corporation.', 'altibase') );

2) If the encrypted text is looked up, it won't be recognized it.

SELECT * FROM t1;

ENCRYPTED_STRING 
-------------------------------------------- 
Z\uf900\u5b87\ub94c]\uffff\uffffu\uffffxE\uffffIXek 
                     \uffff 
1 row selected.

3) Decrypt and display the encrypted text using the same key to encrypt it.

SELECT DESDECRYPT(encrypted_string, 'altibase') FROM t1;

DESDECRYPT(ENCRYPTED_STRING, 'altibase') 
-------------------------------------------- 
A4 ALTIBASE Corporation. 
1 row selected.

Example 2#

Encrypt and decrypt a string whose length is not a multiple of 8, using the PKCS7PAD16 and PKCS7UNPAD16 functions.

1) Use the PKCS7PAD16 function to encrypt a string whose length is not a multiple of 8, and save it.

CREATE TABLE t1( encrypted_string VARCHAR(40) );
INSERT INTO t1 
VALUES( DESENCRYPT( PKCS7PAD16( 'Altibase Client Query utility.'), 'altibase' ) );

2) Use the PKCS7UNPAD1 function to decrypt the string encrypted above.

SELECT PKCS7PAD16( DESDECRYPT( encrypted_string, 'altibase' ) ) desdecrypt_str 
  FROM t1;

DESDECRYPT_STR
---------------------------------------------------- 
Altibase Client Query utility. 
1 row selected.

DESENCRYPT#

Syntax#

DESENCRYPT (VARCHAR  expr,
            VARCHAR  key_string)

Description#

expr

This is the string to encrypt. The length of the string must be a multiple of 8.
key_string

This is the string that will be used as the encryption key. The minimum length of key_string is 8 characters. The 9th and subsequent characters are ignored.

Example#

Please refer to the DESDECRYPT example below.

TDESDECRYPT/TRIPLE_DESDECRYPT#

Syntax#

TRIPLE_DESDECRYPT (VARCHAR  input_string,
                   VARCHAR  key_string,
                   [, SMALLINT keying_option
                   [, VARCHAR initial_vector]])

Description#

This function decrypts the input character string encrypted by the previous TDESENCRYPT or TRIPLE_DESENCRYPT operation and returns the decrypted data.

input_string

This is the character string to be decrypted, and its length must be a multiple of 8 bytes.
key_string

This is the character string which was used as the encryption key.
keying_option

This specifies the key mode which was used for encryption.
initial_vector

This is the initialization vector which was used for encryption

Note:

If an encrypted character string is printed on the screen, a terminal emulator error can occur.

Example#

Store the encrypted text in the table and print it in decrypted form.

1) The encrypted text is inserted into the table.

CREATE TABLE t1( encrypted_string VARCHAR(40) );
INSERT INTO t1 VALUES( TDESENCRYPT( 'A4 ALTIBASE Corporation.', 'altibaselocation'));

2) When queried, the encrypted text is illegible.

SELECT * FROM t1;

ENCRYPTED_STRING
--------------------------------------------
-m
y???????/o??
1 row selected.

3) The encrypted text is printed in decrypted form, using the same key which was used for encryption.

SELECT TDESDECRYPT(encrypted_string, 'altibaselocation') FROM t1;

TDESDECRYPT(ENCRYPTED_STRING, 'altibaseloc
----------------------------------------------
A4 ALTIBASE Corporation.
1 row selected.

TDESENCRYPT/TRIPLE_DESENCRYPT#

Syntax#

TRIPLE_DESENCRYPT (VARCHAR  input_string,
                   VARCHAR  key_string,
                [, SMALLINT keying_option
                [, VARCHAR initial_vector]]))

Description#

The TDESENCRYPT and TRIPLE_DESENCRYPT functions encrypt the input character string by passing it through the 3DES encryption algorithm and return the encrypted data. Triple-DES encryption(3DES) passes the character string through the DES algorithm three times for encryption.

input_string

This is the character string to be encrypted, and its length must be a multiple of 8 bytes.
key_string

This is the character string to be used as the encryption key.
keying_option

This specifies the key mode to be used for encryption. The key mode value is either 0 or 1; on omission, the default value is 0. 0 indicates two-key mode, and the length of the key_string must be larger than 16 bytes; 1 indicates three-key mode, and the length of the key_string must be larger than 24 bytes.
initial_vector

This is the initialization vector, a random number used during encryption. It is a character string with a length larger than 8 bytes. On omission, the default value is '00000000'.

Example#

Refer to the TDESDECRYPT example.

Administrator

Reference

Developer

Tools

External Tools

Encryption Functions