Encryption and Decryption Functions

AES_DECRYPT

AES_DECRYPT(crypt_str, key_str[, init_vector][, kdf_name][, salt][, info | iterations])

Description: Based on AES algorithm, the encrypted string crypt_str is decrypted using the decryption password key_str, initialization vector init_vector, and KDF-related options (kdf_name, salt, and info/iterations).

Return value type: LONGBLOB

Examples:

m_db=# SELECT AES_DECRYPT(AES_ENCRYPT('huwei123','123456vfhex4dyu','vdaladhjsadadabcdef','hkdf'),'123456vfhex4dyu','vdaladhjsadadabcdef','hkdf');
 aes_decrypt 
-------------
 huwei123
(1 row)

• A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the SQL statements containing the function name in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.
• Do not call this function during operations related to stored procedures, preventing the risk of sensitive information disclosure. When using the stored procedure that contains the function, you are advised to filter the parameter information of the function before providing the information for external maintenance personnel to locate the fault. After you finish using the logs, delete them as soon as possible.
• Do not call the function when debug_print_plan is set to on, preventing the risk of sensitive information disclosure. You are advised to filter parameter information of the function in the log files generated when debug_print_plan is set to on before providing the log files to external maintenance engineers for fault locating. After you finish using the logs, delete them as soon as possible.
• Ensure that the values of block_encryption_mode, key_str, and iv are the same as those during encryption.
• Due to encoding differences, encrypted data cannot be directly copied from the gsql client for decryption. In this scenario, the decryption result may be different from the character string before encryption.
• The SQL_ASCII setting performs quite differently from other settings. If the character set of the server is SQL_ASCII, the server interprets the byte values 0 to 127 according to the ASCII standard. The byte values 128 to 255 are regarded as the characters that cannot be parsed. If the setting is SQL_ASCII, no code conversion occurs. When this function calls the third-party OpenSSL library, the returned data is non-ASCII data. Therefore, when the character set of the database server is set to SQL_ASCII, the encoding of the client must also be set to SQL_ASCII. Otherwise, an error is reported. The database cannot automatically convert or verify non-ASCII characters.

AES_ENCRYPT

AES_ENCRYPT(str, key_str[, init_vector][, kdf_name][, salt][, info | iterations])

Description: Based on AES algorithm, the string str is encrypted using the encryption password key_str, initialization vector init_vector, and KDF-related options (kdf_name, salt, and info/iterations).

Return value type: LONGBLOB

Examples:

m_db=# SELECT AES_ENCRYPT('huwei123','123456vfhex4dyu,vdaladhjsadad','1234567890123456');
    aes_encrypt    
-------------------
 Z_Z\x7F\\x7Fy\x1D
(1 row)

• An encryption password is required during the execution of this function. For security purposes, the gsql tool does not record the SQL statements containing the function name in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.
• Do not call this function during operations related to stored procedures, preventing the risk of sensitive information disclosure. When using the stored procedure that contains the function, you are advised to filter the parameter information of the function before providing the information for external maintenance personnel to locate the fault. After you finish using the logs, delete them as soon as possible.
• Do not call the function when debug_print_plan is set to on, preventing the risk of sensitive information disclosure. You are advised to filter parameter information of the function in the log files generated when debug_print_plan is set to on before providing the log files to external maintenance engineers for fault locating. After you finish using the logs, delete them as soon as possible.
• The SQL_ASCII setting performs quite differently from other settings. If the character set of the server is SQL_ASCII, the server interprets the byte values 0 to 127 according to the ASCII standard. The byte values 128 to 255 are regarded as the characters that cannot be parsed. If the setting is SQL_ASCII, no code conversion occurs. When this function calls the third-party OpenSSL library, the returned data is non-ASCII data. Therefore, when the character set of the database server is set to SQL_ASCII, the encoding of the client must also be set to SQL_ASCII. Otherwise, an error is reported. The database cannot automatically convert or verify non-ASCII characters.

SHA

SHA(str)

Description: Calculates the SHA-1 160-bit checksum of string str.

Parameter: str, which is a string or number.

Return value: string. The value is a string of 40 hexadecimal digits.

Examples:

m_db=# SELECT SHA('abc');
                   sha                    
------------------------------------------
 a9993e364706816aba3e25717850c26c9cd0d89d
(1 row)

SHA1

SHA1(str)

Description: SHA1 is the alias of the SHA function and has the same function and usage.

Examples:

m_db=# SELECT SHA1('abc');
                   sha1                   
------------------------------------------
 a9993e364706816aba3e25717850c26c9cd0d89d
(1 row)

SHA2

SHA2(str, hash_length)

Description: Calculates the SHA-2 checksum of string str.

Parameters:

str: character string or number.

hash_length: corresponds to an SHA2 algorithm. The value can be 0 (SHA-256), 224 (SHA-224), 256 (SHA-256), 384 (SHA-384), or 512 (SHA-512). For other values, NULL is returned.

Return value: string. The value is a string of 40 hexadecimal digits.

Examples:

m_db=# SELECT SHA2('abc', 224);
                           sha2                           
----------------------------------------------------------
 23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7
(1 row)