8.11. Cryptographic Functions

8.11.1. DECRYPT()

Available inDSQL, PSQL

Result typeVARBINARY or BLOB

Syntax 

  |DECRYPT ( encrypted_input
  |  USING <algorithm> [MODE <mode>]
  |  KEY key
  |  [IV iv] [<ctr_type>] [CTR_LENGTH ctr_length]
  |  [COUNTER initial_counter] )
  | 
  |!! See syntax of <<fblangref40-scalarfuncs-encrypt,ENCRYPT>> for further rules !!

Table 8.81. DECRYPT Function Parameters
ParameterDescription

encrypted_input

Encrypted input as a blob or (binary) string

See Table 8.82, “ENCRYPT Function Parameters” for other 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

  |select decrypt(x'0154090759DF' using sober128 key 'AbcdAbcdAbcdAbcd' iv '01234567')
  |  from rdb$database;
  |select decrypt(secret_field using aes mode ofb key '0123456701234567' iv init_vector)
  |  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 

   |ENCRYPT ( input
   |  USING <algorithm> [MODE <mode>]
   |  KEY key
   |  [IV iv] [<ctr_type>] [CTR_LENGTH ctr_length]
   |  [COUNTER initial_counter] )
   | 
   |<algorithm> ::= <block_cipher> | <stream_cipher>
   | 
   |<block_cipher> ::=
   |    AES | ANUBIS | BLOWFISH | KHAZAD | RC5
   |  | RC6 | SAFER+ | TWOFISH | XTEA
   | 
   |<stream_cipher> ::= CHACHA20 | RC4 | SOBER128
   | 
   |<mode> ::= CBC | CFB | CTR | ECB | OFB
   | 
   |<ctr_type> ::= CTR_BIG_ENDIAN | CTR_LITTLE_ENDIAN

Table 8.82. ENCRYPT Function Parameters
ParameterDescription

input

Input to encrypt as a blob or (binary) string

algorithm

The algorithm to use for decryption

mode

The algorithm mode; only for block ciphers

key

The encryption/decryption key

iv

Initialization vector or nonce; should be specified for block ciphers in all modes except ECB, and all stream ciphers except RC4

ctr_type

Endianness of the counter; only for CTR mode. Default is CTR_LITTLE_ENDIAN.

ctr_length

Counter length; only for CTR mode. Default is size of iv.

initial_counter

Initial counter value; only for CHACHA20. Default is 0.

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.83, “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.83. Encryption Algorithm Requirements
AlgorithmKey size (bytes)Block size (bytes)Notes

Block Ciphers

AES

16, 24, 32

16

Key size determines the AES variant: 16 bytes → AES-128 24 bytes → AES-192 32 bytes → AES-256

ANUBIS

16 - 40, in steps of 4

16

 

BLOWFISH

8 - 56

8

 

KHAZAD

16

8

 

RC5

8 - 128

8

 

RC6

8 - 128

16

 

SAFER+

16, 24, 32

16

 

TWOFISH

16, 24, 32

16

 

XTEA

16

8

 

Stream Ciphers

CHACHA20

16, 32

1

Nonce size (IV) is 8 or 12 bytes. For nonce size 8, initial_counter is a 64-bit integer, for size 12, 32-bit.

RC4

5 - 256

1

 

SOBER128

4x

1

Nonce size (IV) is 4y bytes, the length is independent of key size.

8.11.2.1. ENCRYPT Examples

  |select encrypt('897897' using sober128 key 'AbcdAbcdAbcdAbcd' iv '01234567')
  |  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 

  |RSA_DECRYPT (encrypted_input KEY private_key
  |  [LPARAM tag_string] [HASH <hash>] [PKCS_1_5])
  | 
  |<hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 8.84. RSA_DECRYPT Function Parameters
ParameterDescription

encrypted_input

Input data to decrypt

private_key

Private key to apply, PKCS#1 format

tag_string

An additional system-specific tag to identify which system encrypted the message; default is NULL. If the tag does not match what was used during encryption, RSA_DECRYPT will not decrypt the data.

hash

The hash used for OAEP padding; default is SHA256.

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.

  |select cast(rsa_decrypt(rdb$get_context('USER_SESSION', 'msg')
  |  key rdb$get_context('USER_SESSION', 'private_key')) as varchar(128))
  |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 

  |RSA_ENCRYPT (input KEY public_key
  |  [LPARAM tag_string] [HASH <hash>] [PKCS_1_5])
  | 
  |<hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 8.85. RSA_ENCRYPT Function Parameters
ParameterDescription

input

Input data to encrypt

public_key

Public key to apply, PKCS#1 format

tag_string

An additional system-specific tag to identify which system encrypted the message; default is NULL.

hash

The hash used for OAEP padding; default is SHA256.

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.

  |select rdb$set_context('USER_SESSION', 'msg', rsa_encrypt('Some message'
  |  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 

  |RSA_PRIVATE (key_length)

Table 8.86. RSA_PRIVATE Function Parameters
ParameterDescription

key_length

Key length in bytes; minimum 4, maximum 1024. A size of 256 bytes (2048 bits) or larger is recommended.

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

  |select rdb$set_context('USER_SESSION', 'private_key', rsa_private(256))
  |  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 

  |RSA_PUBLIC (private_key)

Table 8.87. RSA_PUBLIC Function Parameters
ParameterDescription

private_key

RSA private key in PKCS#1 format

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.

  |select rdb$set_context('USER_SESSION', 'public_key',
  |  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 

  |RSA_SIGN_HASH (message_digest
  |  KEY private_key
  |  [HASH <hash>] [SALT_LENGTH salt_length]
  |  [PKCS_1_5])
  | 
  |<hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 8.88. RSA_SIGN_HASH Function Parameters
ParameterDescription

message_digest

Hash of message to sign. The hash algorithm used should match hash

private_key

RSA private key in PKCS#1 format

hash

Hash to generate PSS encoding; default is SHA256. This should be the same hash as used to generate message_digest.

salt_length

Length of the desired salt in bytes; default is 8; minimum 1, maximum 32. The recommended value is between 8 and 16.

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.

  |select rdb$set_context('USER_SESSION', 'msg',
  |  rsa_sign_hash(crypt_hash('Test message' using sha256)
  |    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.8, “CRYPT_HASH()”

8.11.8. RSA_VERIFY_HASH()

Available inDSQL, PSQL

Result typeBOOLEAN

Syntax 

  |RSA_VERIFY_HASH (message_digest
  |  SIGNATURE signature KEY public_key
  |  [HASH <hash>] [SALT_LENGTH salt_length]
  |  [PKCS_1_5])
  | 
  |<hash> ::= MD5 | SHA1 | SHA256 | SHA512

Table 8.89. RSA_VERIFY Function Parameters
ParameterDescription

message_digest

Hash of message to verify. The hash algorithm used should match hash

signature

Expected signature of input generated by RSA_SIGN_HASH

public_key

RSA public key in PKCS#1 format matching the private key used to sign

hash

Hash to use for the message digest; default is SHA256. This should be the same hash as used to generate message_digest, and as used in RSA_SIGN_HASH

salt_length

Length of the salt in bytes; default is 8; minimum 1, maximum 32. Value must match the length used in RSA_SIGN_HASH.

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.

  |select rsa_verify_hash(
  |  crypt_hash('Test message' using sha256)
  |  signature rdb$get_context('USER_SESSION', 'msg')
  |  key rdb$get_context('USER_SESSION', 'public_key'))
  |from rdb$database

See alsoSection 8.11.7, “RSA_SIGN_HASH()”, Section 8.11.6, “RSA_PUBLIC()”, Section 8.3.8, “CRYPT_HASH()”