Instructions de procédure SQL (PSQL)Fonctions agrégées

Exemple `CRYPT_HASH`

Example 1. Utilisation de la fonction CRYPT_HASH

SELECT CRYPT_HASH(x USING SHA256) FROM MyTable;
-- Résultat de type VARBINARY

`DECRYPT()`

Disponible en

DSQL, PSQL

Syntaxe

DECRYPT (encrypted_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 1. paramètres de fonction `DECRYPT`
Paramètre	Description
encrypted_input	BLOB crypté ou chaîne de caractères (binaire)
algorithm	Algorithme de cryptage. Les algorithmes de bloc et de flux sont pris en charge.
mode	Mode de chiffrement. Obligatoire pour les algorithmes de chiffrement par blocs.
key	Clé de cryptage.
iv	Vecteur d’initialisation (IV). Doit être spécifié pour tous les ciphers de bloc sauf `ECB` et tous les ciphers de flux sauf `RC4`.
ctr_type	Ordre des octets du compteur. Ne peut être spécifié qu’en mode `CTR`. La valeur par défaut est `CTR_LITTLE_ENDIAN`.
ctr_length	Longueur du compteur en octets. Ne peut être spécifié qu’en mode `CTR`. La valeur par défaut est la longueur du vecteur d’initialisation IV.
initial_counter	Valeur initiale du compteur. Ne peut être spécifiée que pour l’algorithme `CHACHA20`. La valeur par défaut est 0.

type de résultat de retour

BLOB ou VARBINARY.

La fonction DECRYPT décrypte les données à l’aide d’un chiffrement symétrique. La taille des chaînes transmises à cette fonction doit répondre aux exigences de l’algorithme et du mode sélectionnés.

Example 1. Utilisation de la fonction DECRYPT

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;

Voir aussi :

[fblangref-scalarfuncs-encrypt].

docnext count = 16

`ENCRYPT()`

Disponible en

DSQL, PSQL

Syntaxe

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 1. paramètres de fonction `ENCRYPT`
Paramètre	Description
input	Une expression de type chaîne ou `BLOB` à crypter.
algorithm	Algorithme de cryptage. Les algorithmes de bloc et de flux sont pris en charge.
mode	Mode de chiffrement. Obligatoire pour les algorithmes de chiffrement par blocs.
key	Clé de cryptage.
iv	Vecteur d’initialisation (IV). Doit être spécifié pour tous les ciphers de bloc sauf `ECB` et tous les ciphers de flux sauf `RC4`.
ctr_type	Ordre des octets du compteur. Ne peut être spécifié qu’en mode `CTR`. La valeur par défaut est `CTR_LITTLE_ENDIAN`.
ctr_length	Longueur du compteur en octets. Ne peut être spécifié qu’en mode `CTR`. La valeur par défaut est la longueur du vecteur d’initialisation IV.
initial_counter	Valeur initiale du compteur. Ne peut être spécifiée que pour l’algorithme `CHACHA20`. La valeur par défaut est 0.

type de résultat de retour

BLOB ou VARBINARY.

La fonction ENCRYPT permet de crypter des données en utilisant un chiffrement symétrique.

Note

Cette fonction renvoie BLOB SUB_TYPE BINARY si le premier argument est BLOB, et VARBINARY pour tous les autres types de texte et binaires.
Les tailles des chaînes (par exemple, key et iv) transmises à cette fonction doivent répondre aux exigences de l’algorithme et du mode choisis.Voir le tableau [fblangref-scalarfuncs-tbl-encrypt-req] pour plus de détails.
- En règle générale, la taille de iv doit correspondre à la taille du bloc de l’algorithme.
- Pour les modes ECB et CBC, input doit être un multiple de la taille du bloc, vous devrez le remplir manuellement avec des zéros ou des espaces si nécessaire.

Les caractéristiques des différents algorithmes et modes dépassent le cadre de ce guide linguistique.

Table 2. Exigences des algorithmes de cryptage
Algorithme	Taille de la clé (octet)	Taille du bloc (octet)	Note
Cryptage par blocs
`AES`	16, 24, 32	16
`ANUBIS`	16 - 40, par incréments 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
Cryptage en ligne
`CHACHA20`	16, 32	1	La taille (IV) est de 8 ou 12 octets. Pour la taille 8, initial_counter est un entier de 64 bits, pour la taille 12, c’est un entier de 32 bits.
`RC4`	5 - 256	1
`SOBER128`	4x	1	La taille (IV) est de 4y octets, la longueur est indépendante de la taille de la clé.

Example 1. Utilisation de la fonction ENCRYPT

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

Voir aussi :

[fblangref-scalarfuncs-decrypt].

`RSA_PRIVATE()`

Disponible en

DSQL, PSQL

Syntaxe

RSA_PRIVATE (size)

Table 1. paramètres de fonction RSA_PRIVATE
Paramètre	Description
size	Taille de la clé en octets.

Le type de résultat renvoyé :

VARBINARY

La fonction RSA_PRIVATE renvoie la clé privée RSA d’une longueur donnée (en octets) au format PKCS#1 sous forme de chaîne VARBINARY.

Example 1. Utilisation de la fonction RSA_PRIVATE

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

Voir aussi :

[fblangref-scalarfuncs-rsa_public].

`RSA_PUBLIC()`

Disponible en

DSQL, PSQL

Syntaxe

RSA_PUBLIC (private-key)

Table 1. paramètres de fonction RSA_PUBLIC
Paramètre	Description
private-key	Clé privée RSA.

Le type de résultat renvoyé :

VARBINARY

La fonction RSA_PUBLIC renvoie la clé publique RSA pour une clé privée RSA donnée. Les deux clés doivent être au format PKCS#1.

Example 1. Utilisation de la fonction RSA_PUBLIC

La clé privée doit être initialisée auparavant, voir l’exemple dans RSA_PRIVATE

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

Voir aussi :

[fblangref-scalarfuncs-rsa_private].

`RSA_ENCRYPT()`

Disponible en

DSQL, PSQL

Syntaxe

RSA_ENCRYPT (<data> KEY <public_key> [LPARAM <tag>] [HASH <hash>])

<hash> ::= { MD5 | SHA1 | SHA256 | SHA512 }

Table 1. paramètres de fonction `RSA_ENCRYPT`
Paramètre	Description
data	Données (chaîne ou BLOB) à crypter.
public_key	La clé publique RSA retournée par la fonction `RSA_PUBLIC`.
tag	Une balise système facultative qui peut être utilisée pour déterminer quel système a encodé le message. La valeur par défaut est `NULL`.
hash	Algorithme de hachage. La valeur par défaut est SHA256.

Le type de résultat renvoyé :

VARBINARY

Remplit les données à l’aide de la complétion OAEP et les crypte à l’aide de la clé publique RSA. Généralement utilisé pour crypter des clés symétriques courtes, qui sont ensuite utilisées dans des ciphers de bloc pour crypter le message.

Example 1. Utilisation de la fonction RSA_ENCRYPT

La clé publique doit être initialisée auparavant, voir l’exemple dans [fblangref-scalarfuncs-rsa_public]

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

Voir aussi :

[fblangref-scalarfuncs-rsa_public], [fblangref-scalarfuncs-rsa_decrypt].

`RSA_DECRYPT()`

Disponible en

DSQL, PSQL

Syntaxe

RSA_DECRYPT (<data> KEY <private_key> [LPARAM <tag>] [HASH <hash>])

<hash> ::= { MD5 | SHA1 | SHA256 | SHA512 }

Table 1. paramètres de fonction `RSA_DECRYPT`
Paramètre	Description
data	Données (chaîne ou BLOB) à décrypter.
private_key	La clé privée RSA retournée par la fonction `RSA_PRIVATE`.
tag	Balise système optionnelle. Doit être la même valeur que celle passée à `RSA_ENCRYPT`. Si elle ne correspond pas à celle utilisée lors du codage, cette fonction ne décryptera pas le paquet. La valeur par défaut est `NULL`.
hash	Algorithme de hachage. La valeur par défaut est SHA256.

Le type de résultat renvoyé :

VARCHAR

Déchiffre à l’aide d’une clé privée RSA et supprime les données augmentées par l’OAEP.

Example 1. Utilisation de la fonction RSA_DECRYPT

La clé privée doit être initialisée plus tôt, voir l’exemple dans [fblangref-scalarfuncs-rsa_private]. Les données de déchiffrement sont utilisées à partir de l’exemple dans [fblangref-scalarfuncs-rsa_encrypt].

select RSA_DECRYPT(rdb$get_context('USER_SESSION', 'msg')
    key rdb$get_context('USER_SESSION', 'private_key'))
from RDB$DATABASE;

Voir aussi :

[fblangref-scalarfuncs-rsa_private], [fblangref-scalarfuncs-rsa_encrypt].

`RSA_SIGN_HASH()`

Disponible en

DSQL, PSQL

Syntaxe

RSA_SIGN_HASH (<data> KEY <private_key> [HASH <hash>] [SALT_LENGTH <length>])

<hash> ::= { MD5 | SHA1 | SHA256 | SHA512 }

Table 1. paramètres de fonction `RSA_SIGN_HASH`
Paramètre	Description
data	Données (chaîne ou BLOB) à encoder.
private_key	La clé privée RSA retournée par la fonction `RSA_PRIVATE`.
hash	Algorithme de hachage. La valeur par défaut est SHA256.
length	Indique la longueur du sel souhaité et doit généralement être petite. Une bonne valeur se situe entre 8 et 16.

Le type de résultat renvoyé :

VARBINARY

Effectue le codage PSS du condensé du message pour la signature et le signe en utilisant la clé privée RSA. Renvoie la signature du message.

Example 1. Utilisation de la fonction RSA_SIGN_HASH

La clé privée doit être initialisée plus tôt voir l’exemple dans [fblangref-scalarfuncs-rsa_private].

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;

Voir aussi :

[fblangref-scalarfuncs-rsa_private], [fblangref-scalarfuncs-rsa_verify_hash].

`RSA_VERIFY_HASH()`

Disponible en

DSQL, PSQL

Syntaxe

RSA_VERIFY_HASH (<data> SIGNATURE <signature> KEY <public_key> [HASH <hash>]
  [SALT_LENGTH <length>])

<hash> ::= { MD5 | SHA1 | SHA256 | SHA512 }

Table 1. paramètres de fonction `RSA_VERIFY_HASH`
Paramètre	Description
data	Données (chaîne ou BLOB) à encoder.
signature	Signature. Doit être la valeur renvoyée par la fonction `RSA_SIGN_HASH`.
public_key	La clé publique RSA retournée par la fonction `RSA_PUBLIC`.
hash	Algorithme de hachage. La valeur par défaut est `SHA256`.
length	Indique la longueur du sceau souhaité et doit généralement être petite.Une bonne valeur se situe entre 8 et 16.

type de résultat de retour

BOOLEAN

Effectue le codage PSS du condensé du message pour la signature et vérifie sa signature numérique en utilisant la clé publique RSA. Renvoie le résultat de la vérification de la signature.

Example 1. Utilisation de la fonction RSA_VERIFY_HASH

La clé publique doit être initialisée plus tôt voir exemple dans [fblangref-scalarfuncs-rsa_public]. La signature numérique est obtenue plus tôt avec [fblangref-scalarfuncs-rsa_sign_hash].

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;

Voir aussi :

[fblangref-scalarfuncs-rsa_sign_hash], [fblangref-scalarfuncs-rsa_public].

Fonctions de conversion de type

`CAST()`

Disponible en

DSQL, PSQL

Syntaxe

CAST(value | NULL AS <type>)

<type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN relname.colname

<datatype> ::=
    <scalar_datatype> | <blob_datatype> | <array_datatype>

<scalar_datatype> ::= Voir Syntaxe des types de données scalaires.

<blob_datatype> ::= Voir Syntaxe du type de données BLOB.

<array_datatype> ::= Voir syntaxe des tableaux.

Table 1. paramètres de fonction CAST
Paramètre	Description
value	Expression SQL.
datatype	Type de données SQL.
domain	Domaine.
relname	Le nom d’une table ou d’une vue.
colname	Le nom de la colonne de la table ou de la vue.

type de résultat de retour

<type>.

La fonction CAST est utilisée pour convertir explicitement des données d’un type de données vers un autre type de données ou un autre domaine. Si cela n’est pas possible, une erreur sera émise.

Table 2. Transformations permises avec la fonction `CAST`
Type d’origine	Type en sortie
Type numérique	Types numériques, `[VAR]CHAR`, `BLOB`.
`[VAR]CHAR`, `BLOB`	`[VAR]CHAR`, `BLOB`, `BOOLEAN`, Types numériques, `DATE`, `TIME`, `TIMESTAMP`
`DATE`, `TIME`	`[VAR]CHAR`, `BLOB`, `TIMESTAMP`
`TIMESTAMP`	`[VAR]CHAR`, `BLOB`, `TIME`, `DATE`
`BOOLEAN`	`[VAR]CHAR`, `BLOB`

Notez que parfois des informations peuvent être perdues, par exemple lorsque vous convertissez le type TIMESTAMP en DATE.De plus, le fait que les types soient compatibles pour la fonction CAST ne garantit pas que la conversion sera réussie.“CAST (123456789 AS SMALLINT)” provoquera certainement une erreur, tout comme “CAST('Judgement Day' as DATE)”.

Vous pouvez appliquer une conversion de type aux paramètres de l’opérateur :

CAST (? AS INTEGER)

Cela vous permet de contrôler le type de champs de saisie.

Conversion en un domaine ou en son type de base

Lors de la conversion vers un domaine, toute restriction (NOT NULL et/ou CHECK) déclarée pour le domaine doit être satisfaite, sinon la conversion n’aura pas lieu. Rappelez-vous que la vérification CHECK passe si son calcul donne VRAI ou UNKNOWN (NULL). Pour les opérateurs suivants :

CREATE DOMAIN quint AS INT CHECK (VALUE >= 5000)
SELECT CAST (2000 AS quint) FROM rdb$database -- (1)
SELECT CAST (8000 AS quint) FROM rdb$database -- (2)
SELECT CAST (null AS quint) FROM rdb$database -- (3)

seul (1) se terminera par une erreur.

Si le modificateur TYPE OF est utilisé, l’expression sera convertie au type de base du domaine, en ignorant les contraintes. Pour le domaine quint déclaré ci-dessus, les deux conversions seront équivalentes et les deux réussiront :

SELECT CAST (2000 AS TYPE OF quint) FROM rdb$database
SELECT CAST (2000 AS INT) FROM rdb$database

Lorsque TYPE OF est utilisé avec le type [VAR]CHAR, son jeu de caractères et son ordre de tri (collate) sont conservés.

CREATE DOMAIN iso20 VARCHAR(20) CHARACTER SET iso8859_1;
CREATE DOMAIN dunl20 VARCHAR(20) CHARACTER SET iso8859_1 COLLATE du_nl;
CREATE TABLE zinnen (zin VARCHAR(20));
COMMIT;
INSERT INTO zinnen VALUES ('Deze');
INSERT INTO zinnen VALUES ('Die');
INSERT INTO zinnen VALUES ('die');
INSERT INTO zinnen VALUES ('deze');
SELECT CAST(zin AS TYPE OF iso20) FROM zinnen ORDER BY 1;
-- returns Deze -> Die -> deze -> die
SELECT CAST(zin AS TYPE OF dunl20) FROM zinnen ORDER BY 1;
-- returns deze -> Deze -> die -> Die

Warning

Si la définition du domaine est modifiée, les conversions existantes du domaine ou de son type peuvent devenir erronées. Si de telles conversions se produisent dans les modules PSQL, leurs erreurs peuvent être détectées. Champ d’application RDB$VALID_BLR.

Conversion en type de colonne

Permet de convertir une expression en un type de colonne d’une table ou d’une vue existante. Seul le type lui-même sera utilisé, pour les types de chaînes de caractères, le même jeu de caractères sera utilisé, mais pas la séquence de tri. Les restrictions et les valeurs par défaut de la colonne originale ne sont pas appliquées.

CREATE TABLE ttt (
  s VARCHAR(40) CHARACTER SET utf8 COLLATE unicode_ci_ai
);
COMMIT;
SELECT CAST ('Jag har många vänner' AS TYPE OF COLUMN ttt.s)
FROM rdb$database;

Warning

Si la définition d’une colonne est modifiée, les conversions existantes de son type peuvent devenir erronées. Si de telles conversions se produisent dans les modules PSQL, leurs erreurs peuvent être détectées. Champ RDB$VALID_BLR.

Voir aussi :

[fblangref-datatypes-convert-explicit].

Exemple de conversion de type

SELECT CAST ('12' || '-June-' || '1959' AS DATE) FROM rdb$database

Notez que dans certains cas, vous n’aurez pas besoin d’utiliser la syntaxe de conversion comme dans l’exemple ci-dessus, car Firebird comprendra à partir du contexte (comparaison avec un champ DATE) comment interpréter la chaîne :

UPDATE People SET AgeCat = 'Old'
WHERE BirthDate < '1-Jan-1943'

Mais cela n’est pas toujours possible. La conversion dans l’exemple ci-dessous ne peut pas être omise car le système essaiera de convertir la chaîne en un nombre afin de lui soustraire un nombre :

SELECT CAST('TODAY' AS DATE) - 7 FROM rdb$database

Fonctions d’opérations binaires

`BIN_AND()`

Disponible en

DSQL, PSQL

Syntaxe

BIN_AND (number, number [, number ...])

Table 1. paramètres de fonction `BIN_AND`
Paramètre	Description
number	Entier.

type de résultat de retour

SMALLINT, INTEGER, BIGINT ou INT128

La fonction BIN_AND renvoie le résultat de l’opération ET binaire des arguments.

Voir aussi :

[fblangref-scalarfuncs-bin-or], [fblangref-scalarfuncs-bin-xor].

`BIN_NOT()`

Disponible en

DSQL, PSQL

Syntaxe

BIN_NOT (number)

Table 1. paramètres de fonction `BIN_NOT`
Paramètre	Description
number	Entier.

type de résultat de retour

SMALLINT, INTEGER, BIGINT ou INT128

La fonction BIN_NOT renvoie le résultat d’une opération NOT au sens du bit sur l’argument.

Voir aussi :

[fblangref-scalarfuncs-bin-or], [fblangref-scalarfuncs-bin-and].

`BIN_OR()`

Disponible en

DSQL, PSQL

Syntaxe

BIN_OR (number, number [, number ...])

Table 1. paramètres de fonction `BIN_OR`
Paramètre	Description
number	Entier.

type de résultat de retour

SMALLINT, INTEGER, BIGINT ou INT128

La fonction BIN_OR renvoie le résultat de l’opération "bitwise OR" des arguments.

Voir aussi :

[fblangref-scalarfuncs-bin-and], [fblangref-scalarfuncs-bin-xor].

`BIN_SHL()`

Disponible en

DSQL, PSQL

Syntaxe

BIN_SHL (number, shift)

Table 1. paramètres de fonction BIN_SHL
Paramètre	Description
number	Entier.
shift	Le nombre de bits par lequel la valeur number est décalée.

type de résultat de retour

BIGINT ou INT128.

La fonction BIN_SHL renvoie le premier paramètre décalé bit à bit de la valeur du second paramètre, c’est-à-dire a << b ou a-2^b.

Voir aussi :

[fblangref-scalarfuncs-bin-shr].

`BIN_SHR()`

Disponible en

DSQL, PSQL

Syntaxe

BIN_SHR (number, shift)

Table 1. paramètres de fonction `BIN_SHR`
Paramètre	Description
number	Entier.
shift	Le nombre de bits par lequel la valeur numérique est décalée.

Le type de résultat renvoyé :

BIGINT ou INT128.

La fonction BIN_SHR renvoie le premier paramètre décalé bit à bit de la valeur du second paramètre, c’est-à-dire a >> b ou a/2^b.

L’opération effectuée est un décalage arithmétique vers la droite (SAR), ce qui signifie que le signe du premier opérande est toujours conservé.

Voir aussi :

[fblangref-scalarfuncs-bin-shl].

`BIN_XOR()`

Disponible en

DSQL, PSQL

Syntaxe

BIN_XOR (number, number [, number ...])

Table 1. paramètres de fonction `BIN_XOR`
Paramètre	Description
number	Entier.

type de résultat de retour

SMALLINT, INTEGER, BIGINT ou INT128

La fonction BIN_XOR renvoie le résultat de l’opération XOR bit à bit des arguments.

Voir aussi :

[fblangref-scalarfuncs-bin-and], [fblangref-scalarfuncs-bin-or].

Exemple CRYPT_HASH

Exemple CRYPT_HASH

DECRYPT()

docnext count = 16

ENCRYPT()

RSA_PRIVATE()

RSA_PUBLIC()

RSA_ENCRYPT()

RSA_DECRYPT()

RSA_SIGN_HASH()

RSA_VERIFY_HASH()

Fonctions de conversion de type

CAST()

Conversion en un domaine ou en son type de base

Conversion en type de colonne

Exemple de conversion de type

Fonctions d’opérations binaires

BIN_AND()

BIN_NOT()

BIN_OR()

BIN_SHL()

BIN_SHR()

BIN_XOR()

Exemple `CRYPT_HASH`

`DECRYPT()`

`ENCRYPT()`

`RSA_PRIVATE()`

`RSA_PUBLIC()`

`RSA_ENCRYPT()`

`RSA_DECRYPT()`

`RSA_SIGN_HASH()`

`RSA_VERIFY_HASH()`

`CAST()`

`BIN_AND()`

`BIN_NOT()`

`BIN_OR()`

`BIN_SHL()`

`BIN_SHR()`

`BIN_XOR()`