8.11 Cryptographic Functions

8.11.1 DECRYPT()

Available inDSQL, PSQL

Result typeVARBINARY or BLOB

Syntax

1. DECRYPT ( encrypted_input
2. USING <algorithm> [MODE <mode>]
3. KEY key
4. [IV iv] [<ctr_type>] [CTR_LENGTH ctr_length]
5. [COUNTER initial_counter] )

7. !! See syntax of <<fblangref40-scalarfuncs-encrypt,ENCRYPT>> for further rules !!

Table 8.11.1.1 DECRYPT Function Parameters

DECRYPT decrypts data using a symmetric cipher.

Note

• Sizes of data strings (like encrypted_input, key and iv) must meet the requirements of the selected algorithm and mode.
• This function returns BLOB SUB_TYPE BINARY when the first argument is a BLOB, and VARBINARY for all other text and binary types.
• When the encrypted data was text, it must be explicitly cast to a string type of appropriate character set.
• The ins and outs of the various algorithms are considered beyond the scope of this language reference. We recommend searching the internet for further details on the algorithms.

8.11.1.1 DECRYPT Examples

1. select decrypt(x'0154090759DF' using sober128 key 'AbcdAbcdAbcdAbcd' iv '01234567')
2. from rdb$database;
3. select decrypt(secret_field using aes mode ofb key '0123456701234567' iv init_vector)
4. from secure_table;

See alsoSection 8.11.2, ENCRYPT(), Section 8.11.3, RSA_DECRYPT()

8.11.2 ENCRYPT()

Available inDSQL, PSQL

Result typeVARBINARY or BLOB

Syntax

1. ENCRYPT ( input
2. USING <algorithm> [MODE <mode>]
3. KEY key
4. [IV iv] [<ctr_type>] [CTR_LENGTH ctr_length]
5. [COUNTER initial_counter] )

7. <algorithm> ::= <block_cipher> | <stream_cipher>

9. <block_cipher> ::=
10. AES | ANUBIS | BLOWFISH | KHAZAD | RC5
11. | RC6 | SAFER+ | TWOFISH | XTEA

13. <stream_cipher> ::= CHACHA20 | RC4 | SOBER128

15. <mode> ::= CBC | CFB | CTR | ECB | OFB

17. <ctr_type> ::= CTR_BIG_ENDIAN | CTR_LITTLE_ENDIAN

Table 8.11.2.1 ENCRYPT Function Parameters

ENCRYPT can encrypt data using a symmetric cipher.

Note

• This function returns BLOB SUB_TYPE BINARY when the first argument is a BLOB, and VARBINARY for all other text and binary types.

• Sizes of data strings (like key and iv) must meet the requirements of the selected algorithm and mode, see table Table 8.11.2.2, Encryption Algorithm Requirements.

• In general, the size of iv must match the block size of the algorithm

• For ECB and CBC mode, input must be multiples of the block size, you will need to manually pad with zeroes or spaces as appropriate.
• The ins and outs of the various algorithms and modes are considered beyond the scope of this language reference. We recommend searching the internet for further details on the algorithms.
• Although specified as separate options in this Language Reference, in the actual Firebird 4.0 syntax CTR_LENGTH and COUNTER are aliases.

Table 8.11.2.2 Encryption Algorithm Requirements

8.11.2.1 ENCRYPT Examples

1. select encrypt('897897' using sober128 key 'AbcdAbcdAbcdAbcd' iv '01234567')
2. from rdb$database;

See alsoSection 8.11.1, DECRYPT(), Section 8.11.4, RSA_ENCRYPT()

8.11.3 RSA_DECRYPT()

Available inDSQL, PSQL

Result typeVARBINARY

Syntax

1. RSA_DECRYPT (encrypted_input KEY private_key
2. [LPARAM tag_string] [HASH <hash>] [PKCS_1_5])

4. <hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 8.11.3.1 RSA_DECRYPT Function Parameters

RSA_DECRYPT decrypts encrypted_input using the RSA private key and then OAEP de-pads the resulting data.

By default, OAEP padding is used. The PKCS_1_5 option will switch to the less secure PKCS 1.5 padding. This option was introduced in Firebird 4.0.1.

Warning

The PKCS_1_5 option is only for backward compatibility with systems applying PKCS 1.5 padding. For security reasons, it should not be used in new projects.

Note

• This function returns VARBINARY`.
• When the encrypted data was text, it must be explicitly cast to a string type of appropriate character set.

8.11.3.1 RSA_DECRYPT Examples

Tip

Run the examples of the RSA_PRIVATE and RSA_PUBLIC, RSA_ENCRYPT functions first.

1. select cast(rsa_decrypt(rdb$get_context('USER_SESSION', 'msg')
2. key rdb$get_context('USER_SESSION', 'private_key')) as varchar(128))
3. from rdb$database;

See alsoSection 8.11.4, RSA_ENCRYPT(), Section 8.11.5, RSA_PRIVATE(), Section 8.11.1, DECRYPT()

8.11.4 RSA_ENCRYPT()

Available inDSQL, PSQL

Result typeVARBINARY

Syntax

1. RSA_ENCRYPT (input KEY public_key
2. [LPARAM tag_string] [HASH <hash>] [PKCS_1_5])

4. <hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 8.11.4.1 RSA_ENCRYPT Function Parameters

RSA_ENCRYPT pads input using the OAEP padding scheme and then encrypts it using the specified RSA public key. This function is normally used to encrypt short symmetric keys which are then used in block ciphers to encrypt a message.

By default, OAEP padding is used. The PKCS_1_5 option will switch to the less secure PKCS 1.5 padding. This option was introduced in Firebird 4.0.1.

Warning

The PKCS_1_5 option is only for backward compatibility with systems applying PKCS 1.5 padding. For security reasons, it should not be used in new projects.

8.11.4.1 RSA_ENCRYPT Examples

Tip

Run the examples of the RSA_PRIVATE and RSA_PUBLIC functions first.

1. select rdb$set_context('USER_SESSION', 'msg', rsa_encrypt('Some message'
2. key rdb$get_context('USER_SESSION', 'public_key'))) from rdb$database;

See alsoSection 8.11.3, RSA_DECRYPT(), Section 8.11.6, RSA_PUBLIC(), Section 8.11.2, ENCRYPT()

8.11.5 RSA_PRIVATE()

Available inDSQL, PSQL

Result typeVARBINARY

Syntax

1. RSA_PRIVATE (key_length)

Table 8.11.5.1 RSA_PRIVATE Function Parameters

RSA_PRIVATE generates an RSA private key of the specified length (in bytes) in PKCS#1 format.

Note

The larger the length specified, the longer it takes for the function to generate a private key.

8.11.5.1 RSA_PRIVATE Examples

1. select rdb$set_context('USER_SESSION', 'private_key', rsa_private(256))
2. from rdb$database;

Warning

Putting private keys in the context variables is not secure. SYSDBA and users with the role RDB$ADMIN or the system privilege MONITOR_ANY_ATTACHMENT can see all context variables from all attachments.

See alsoSection 8.11.6, RSA_PUBLIC(), Section 8.11.3, RSA_DECRYPT()

8.11.6 RSA_PUBLIC()

Available inDSQL, PSQL

Result typeVARBINARY

Syntax

1. RSA_PUBLIC (private_key)

Table 8.11.6.1 RSA_PUBLIC Function Parameters

RSA_PUBLIC returns the RSA public key in PKCS#1 format for the provided RSA private key (also PKCS#1 format).

8.11.6.1 RSA_PUBLIC Examples

Tip

Run the example of the RSA_PRIVATE function first.

1. select rdb$set_context('USER_SESSION', 'public_key',
2. rsa_public(rdb$get_context('USER_SESSION', 'private_key'))) from rdb$database;

See alsoSection 8.11.5, RSA_PRIVATE(), Section 8.11.4, RSA_ENCRYPT()

8.11.7 RSA_SIGN_HASH()

Available inDSQL, PSQL

Result typeVARBINARY

Syntax

1. RSA_SIGN_HASH (message_digest
2. KEY private_key
3. [HASH <hash>] [SALT_LENGTH salt_length]
4. [PKCS_1_5])

6. <hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 8.11.7.1 RSA_SIGN_HASH Function Parameters

RSA_SIGN_HASH performs PSS encoding of the message_digest to be signed, and signs using the RSA private key.

By default, OAEP padding is used. The PKCS_1_5 option will switch to the less secure PKCS 1.5 padding. This option was introduced in Firebird 4.0.1.

Warning

The PKCS_1_5 option is only for backward compatibility with systems applying PKCS 1.5 padding. For security reasons, it should not be used in new projects.

Caution

This function expects the hash of a message (or message digest), not the actual message. The hash argument should specify the algorithm that was also used to generate that hash.

A function that accepts the actual message to hash might be introduced in a future version of Firebird.

PSS encoding

Probabilistic Signature Scheme (PSS) is a cryptographic signature scheme specifically developed to allow modern methods of security analysis to prove that its security directly relates to that of the RSA problem. There is no such proof for the traditional PKCS#1 v1.5 scheme.

8.11.7.1 RSA_SIGN_HASH Examples

Tip

Run the example of the RSA_PRIVATE function first.

1. select rdb$set_context('USER_SESSION', 'msg',
2. rsa_sign_hash(crypt_hash('Test message' using sha256)
3. key rdb$get_context('USER_SESSION', 'private_key'))) from rdb$database;

See alsoSection 8.11.8, RSA_VERIFY_HASH(), Section 8.11.5, RSA_PRIVATE(), Section 8.3.7, CRYPT_HASH()

8.11.8 RSA_VERIFY_HASH()

Available inDSQL, PSQL

Result typeBOOLEAN

Syntax

1. RSA_VERIFY_HASH (message_digest
2. SIGNATURE signature KEY public_key
3. [HASH <hash>] [SALT_LENGTH salt_length]
4. [PKCS_1_5])

6. <hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 8.11.8.1 RSA_VERIFY Function Parameters

RSA_VERIFY_HASH performs PSS encoding of the message_digest to be verified, and verifies the digital signature using the provided RSA public key.

By default, OAEP padding is used. The PKCS_1_5 option will switch to the less secure PKCS 1.5 padding. This option was introduced in Firebird 4.0.1.

Warning

The PKCS_1_5 option is only for backward compatibility with systems applying PKCS 1.5 padding. For security reasons, it should not be used in new projects.

Caution

This function expects the hash of a message (or message digest), not the actual message. The hash argument should specify the algorithm that was also used to generate that hash.

A function that accepts the actual message to hash might be introduced in a future version of Firebird.

8.11.8.1 RSA_VERIFY_HASH Examples

Tip

Run the examples of the RSA_PRIVATE, RSA_PUBLIC and RSA_SIGN_HASH functions first.

1. select rsa_verify_hash(
2. crypt_hash('Test message' using sha256)
3. signature rdb$get_context('USER_SESSION', 'msg')
4. key rdb$get_context('USER_SESSION', 'public_key'))
5. from rdb$database

See alsoSection 8.11.7, RSA_SIGN_HASH(), Section 8.11.6, RSA_PUBLIC(), Section 8.3.7, CRYPT_HASH()