Le problème Unicode de SQL Server : pourquoi vos données ne sont peut-être pas ce que vous croyez qu'elles sont ?

Introduction

Cela fait plusieurs années maintenant que je fais de la recherche sur Unicode. Ces derniers mois, j’ai pu me pencher sur la gestion d’Unicode dans d’autres bases de données comme MySQL / MariaDB et y trouver quelques comportements intéressants (ex : les effets de la désactivation du mode strict, le comportement des fonctions de manipulation de chaines de caractères (p. 73)). Mais là, pour Microsoft SQL Server, toutes mes attentes ont été dépassées, c’est une véritable usine à piège. SQL Server semble coincé depuis des décennies par une dette technique, forcée par un objectif de rétrocompatibilité. Mais quand des nouvelles décisions sont prises, ce n’est pas pour autant que les nouvelles fonctionnalités sont implémentées correctement ou que les erreurs du passé sont effacées. Plongeons sans plus attendre dans les méandres de la gestion d’Unicode dans la base de données de Microsoft : SQL Server.

Encodage par défaut

Derrière ce titre simple, se cache une réalité trop complexe.

Pour compliquer les choses d’entrée de jeu, et contrairement à d’autres SGBD, SQL Server n’a pas un encodage par défaut global, mais plutôt un encodage par défaut différent selon le contexte. Cela va parfois impliquer une impossibilité de changer l’encodage par défaut (possible de changer l’encodage manuellement, mais pas de changer définitivement la valeur qui sera utilisée si rien n’est spécifié) et aussi une complexité pour déterminer quel encodage est actuellement utilisé pour une donnée.

On peut donc facilement se retrouver avec un mélange d’encodage (Unicode ou non) sans très bien savoir ce qu’il se passe.

Premier élément de contextualisation : l’encodage final d’une colonne va être différent selon son type de donnée (DATA_TYPE). Il est à noter que l’encodage ne s’applique et n’est pertinent que pour les colonnes dont le type est « chaîne de caractères », principalement : char, varchar, text, nchar, nvarchar, ntext.

D’ailleurs, contre-intuitivement, il n’est pas possible de définir directement l’encodage souhaité (jeu de caractères CHARACTER_SET_NAME que l’on va amalgamer à l’encodage) mais que l’algorithme de collationnement (COLLATION_NAME) qui, lui, induira l’encodage auquel il est associé.

Un deuxième élément de contexte qui influe sur l’encodage par défaut est la langue dans laquelle le serveur Windows hébergeant la base de données est installé.

Prenons comme exemple une table avec différents types de colonnes, sans préciser d’encodage.

CREATE TABLE column_encoding
(
    a CHAR(10),
    b VARCHAR(10),
    c TEXT,
    d NCHAR(10),
    e NVARCHAR(10),
    f NTEXT
);

Allons regarder l’algorithme de collationnement et l’encodage qui ont été définis.

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'column_encoding';

Sur un Microsoft SQL Server 2016 (13.0) & 2019 (15.0) où Windows est installé en Français, le résultat est le suivant :

COLUMN_NAME|DATA_TYPE|CHARACTER_MAXIMUM_LENGTH|CHARACTER_SET_NAME|COLLATION_NAME|
-----------+---------+------------------------+------------------+--------------+
a          |char     |                      10|iso_1             |French_CI_AS  |
b          |varchar  |                      10|iso_1             |French_CI_AS  |
c          |text     |              2147483647|iso_1             |French_CI_AS  |
d          |nchar    |                      10|UNICODE           |French_CI_AS  |
e          |nvarchar |                      10|UNICODE           |French_CI_AS  |
f          |ntext    |              1073741823|UNICODE           |French_CI_AS  |

On constate que, tant dans MS SQL Server 2016 que dans MS SQL Server 2019, les types char, varchar et text utilisent le jeu de caractères iso_1, tandis que les types « n » (nchar, nvarchar, ntext) utilisent le jeu de caractères UNICODE.

Deux nouveaux problèmes viennent d’apparaitre :

• iso_1 ne veut absolument rien dire
• UNICODE peut avoir du sens comme jeu de caractère, mais ne nous indique pas quel encodage est utilisé

Pour essayer d’y voir plus clair, allons interroger la table système des jeux de caractères.

SELECT [description]
FROM   dbo.syscharsets
WHERE  [name] = N'iso_1';

description                                                 |
------------------------------------------------------------+
ISO 8859-1 (Latin-1) - Western European 8-bit character set.|

Deux nouveaux problèmes viennent d’apparaitre :

• Microsoft nous dit que le label iso_1 correspondrait à ISO 8859-1 (Latin-1). Problème ? C’est faux ! Il s’agit en réalité de CP1252 mal étiqueté en tant que ISO-8859-1. Les deux jeux de caractères sont similaires, mais différents. C’est une erreur de Microsoft, mais comme ils l’ont propagé partout et pendant trop longtemps, ils ont décidé de ne pas la corriger. Il n’est donc pas possible de croire la documentation de Microsoft.
• Le label UNICODE n’est pas dans la table dbo.syscharsets… Car, en fait, UNICODE n’est ici pas un jeu de caractères, mais une fonction qui renverra UCS-2 ou UTF-16 selon la version du serveur SQL Server (un élément de contexte qui peut faire varier l’encodage par défaut en plus).

Avant d’aller plus loin dans le détail des comportements, on peut déjà faire le constat suivant sur le choix de conception de Microsoft quant à l’intégration d’Unicode.

Il est aisé de comprendre que les systèmes ou langages de programmation qui datent un peu ont connu une période avant et après Unicode et qu’ils ont dû gérer cette transition. Le choix de Microsoft de prioriser à tout prix la rétrocompatibilité est critiquable, mais tout à fait concevable et acceptable pour une entreprise de taille importante qui a des responsabilités et besoin de stabilité pour ses clients.

Cependant, un grand nombre de systèmes et de langages de programmation ont fait le choix de gérer cette transition en changeant l’encodage par défaut d’ASCII ou équivalent à l’un de ceux proposés par Unicode (souvent UTF-8, mais aussi UTF-16) et en mettant à jour toutes les fonctions (ex : celles de manipulations de chaînes de caractères) pour prendre en compte le jeu de caractères plus larges et toutes les nouvelles règles et contraintes qui s’y appliquent. Ce choix-là n’implique pas vraiment de rupture de compatibilité comme la première partie de la table Unicode reprend intégralement la table ASCII, justement par soucis de rétrocompatibilité, et qu’avec UTF-8 ces premiers caractères vont s’inscrire sur un octet avec la même valeur qu’en ASCII. Passer d’ASCII (pas pour les autres encodages ISO 8859) à UTF-8 (pas pour UTF-16 et UTF-32) est donc transparent. Les fonctions qui opéraient jadis sur l’ASCII continuent de fonctionner pour Unicode sur la plage correspondant à l’ASCII et doivent être étendues pour fonctionner au-delà.

Toutefois, cela n’a pas été le choix de Microsoft. Plutôt que de migrer de l’ancien système au nouveau, eux ont choisi de garder l’ancien système et de faire coexister avec le nouveau (l’empilement technologique, comme pour les réseaux de téléphonie mobile 2G/3G/4G/5G). Chez Microsoft, pas uniquement pour SQL Server d’ailleurs, mais plus largement pour tout l’écosystème Windows, on va donc avoir d’un côté l’héritage : les fonctions, types de données, etc. ne fonctionnant qu’avec l’ASCII; et d’un autre côté le monde moderne : les fonctions, types de données, etc. fonctionnant au pire soit pour les « caractères larges » (encodés sur plus de 1 octet) soit au mieux pour Unicode et prenant en compte toutes ses spécificités. C’est pourquoi dans MS SQL Server, on va se retrouver avec les types de données char, varchar et text pour l’« ASCII » et nchar, nvarchar, ntext pour l’« Unicode ». De même pour les fonctions : CHAR() vs NCHAR(), CONCAT() vs CONCAT_WS() (vous noterez que cette fois-ci ce n’est pas NCONCAT()), ASCII() vs UNICODE(), ou encore STR() vs UNISTR (et pas NSTR ni STR_WS). Le lecteur éclairé se doute que l’emploi de la mauvaise fonction ou du mauvais type de données aura des conséquences et que la coexistence des deux systèmes entraine son lot de complexité supplémentaire.

Malgré cette volonté de préserver la rétrocompatibilité, Microsoft introduit parfois des changements. Par exemple, dans la documentation des types char et varchar, on peut lire (modulo la correction de la traduction) le paragraphe suivant :

À compter de SQL Server 2019 (15.x), lorsqu’un collationnement supportant UTF-8 est utilisé, ces types de données stockent la plage complète de données de caractères Unicode et utilisent l’encodage de caractères UTF-8. Si un collationnement non-UTF-8 est spécifié, ces types de données stockent uniquement un sous-ensemble de caractères pris en charge par la page de codes correspondante de ce collationnement.

Cela signifie que l’encodage UTF-8 a été ajouté et est pris en charge par défaut uniquement pour les types char et varchar (mais pas pour text ?) et uniquement lorsqu’un collationnement correspondant est défini ; sinon, l’encodage reste CP1252.

En effet, dans la version 2016 de SQL Server, il y a 52 algorithmes de collationnement rien que pour le Français.

SELECT name, description FROM sys.fn_helpcollations() WHERE name LIKE 'French%';

name                     |description                                                                                                        |
-------------------------+-------------------------------------------------------------------------------------------------------------------+
French_BIN               |French, binary sort                                                                                                |
French_BIN2              |French, binary code point comparison sort                                                                          |
French_CI_AI             |French, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive                              |
French_CI_AI_WS          |French, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive                                |
French_CI_AI_KS          |French, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive                                |
French_CI_AI_KS_WS       |French, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive                                  |
[…]
French_100_CS_AS_SC      |French-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters    |
French_100_CS_AS_WS_SC   |French-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters      |
French_100_CS_AS_KS_SC   |French-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters      |
French_100_CS_AS_KS_WS_SC|French-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters        |

Dans SQL Server 2019, il y en a 68 pour le Français car UTF-8 a été introduit.

name                          |description                                                                                                              |
------------------------------+-------------------------------------------------------------------------------------------------------------------------+
French_BIN                    |French, binary sort                                                                                                      |
French_BIN2                   |French, binary code point comparison sort                                                                                |
French_CI_AI                  |French, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive                                    |
French_CI_AI_WS               |French, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive                                      |
French_CI_AI_KS               |French, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive                                      |
French_CI_AI_KS_WS            |French, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive                                        |
[…]
French_100_CS_AS_SC_UTF8      |French-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters, UTF8    |
French_100_CS_AS_WS_SC_UTF8   |French-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters, UTF8      |
French_100_CS_AS_KS_SC_UTF8   |French-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters, UTF8      |
French_100_CS_AS_KS_WS_SC_UTF8|French-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters, UTF8        |

Effectivement si l’on force un algorithme de collationnement supportant l’UTF-8 (ex : French_100_CI_AS_SC_UTF8) sur SQL Server 2019, cela va bien passer l’encodage en UTF-8.

CREATE TABLE column_encoding_utf8
(
    a CHAR(10) COLLATE French_100_CI_AS_SC_UTF8,
    b VARCHAR(10) COLLATE French_100_CI_AS_SC_UTF8,
    d NCHAR(10) COLLATE French_100_CI_AS_SC_UTF8,
    e NVARCHAR(10) COLLATE French_100_CI_AS_SC_UTF8
);

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'column_encoding_utf8';

COLUMN_NAME|DATA_TYPE|CHARACTER_MAXIMUM_LENGTH|CHARACTER_SET_NAME|COLLATION_NAME          |
-----------+---------+------------------------+------------------+------------------------+
a          |char     |                      10|utf8              |French_100_CI_AS_SC_UTF8|
b          |varchar  |                      10|utf8              |French_100_CI_AS_SC_UTF8|
d          |nchar    |                      10|UNICODE           |French_100_CI_AS_SC_UTF8|
e          |nvarchar |                      10|UNICODE           |French_100_CI_AS_SC_UTF8|

Une fois encore le lecteur avisé aura remarqué que text et ntext n’ont pas été inclus dans l’exemple ci-dessus. C’est parce que ce sont des types de données obsolètes où l’encodage est codé en dur, par conséquent l’algorithme de collationnement ne peut pas être changé. Celui qui tenterait l’opération découvrirait le message d’erreur ci-dessous.

SQL Error [4188] [S0001]: Column or parameter 'f' has type 'ntext' and collation 'French_100_CI_AS_SC_UTF8'. The legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.

Néanmoins, nchar et nvarchar sont restés à UNICODE plutôt que de passer à utf8 quand on force un collationnement UTF-8. On se retrouve donc dans la situation cocasse où les types char et varchar (types « ASCII » ) supportent Unicode via UTF-8 et où les types nchar et nvarchar (types « Unicode ») ne supportent pas l’encodage Unicode UTF-8, mais uniquement l’encodage Unicode UTF-16. Et s’il s’agit d’un MS SQL Server 2012 ou plus ancien ou Azure SQL, alors la fonction UNICODE() renverra même UCS-2 qui est un ancêtre d’Unicode, mais ne fait partie du standard Unicode. En bref les types non-Unicode peuvent supporter de l’Unicode et les types Unicode peuvent supporter du non-Unicode.

Essayons de résumer les valeurs supportées en un tableau.

Légende :

• CP1252* : CP1252 ou autre encodage sur 1 octet
• < 2019 veut dire avant la version 2019 de SQL Server
• >= 2019 veut dire pour SQL Server 2019 et après (tout du moins jusqu’à 2022)

En conclusion, l’encodage par défaut dépend donc a minima :

• du type de données
• du collationnement
• de la langue du serveur
• de la version de SQL Server

Collationnement

L’algorithme de collationnement va régir des règles de tri, de transformation de casse et d’accentuation dans le but de comparaison. Ces règles influent sur un résultat de nombreuses opérations dans la base de données.

Comme vu précédemment, dans SQL Server impossible de définir l’encodage directement, il faut appliquer un algorithme de collationnement qui lui va induire un encodage. Mais, pour que ça ne soit pas trop simple, bien sûr l’encodage induit n’est pas forcément explicite dans le nom de l’algorithme de collationnement.

Avant d’en parler plus en détail, il faut expliquer les conventions de nommage de ceux-ci.

• _CS (case-sensitive) : sensible à la casse ; _CI (case-insensitive) : insensible à la casse
• _AS (accent-sensitive) : sensible aux accents ; _AI (accent-insensitive) : insensible aux accents
• _KS (kana-sensitive) : sensibles aux Kanas
• _WS (width-sensitive) : sensible à la largeur
• _VSS (variation-selector-sensitive) : sensible aux sélecteurs de variation
• _BIN (binaire) : binaire (bits)
• _BIN2 (binaire code point) : binaire (point de code Unicode)
• _UTF8 (UTF-8) : stock les données en UTF-8
• _SC (supplementary character) : permet d’utiliser les 17 plans Unicode et pas juste BMP (quand UTF-8 ou UTF-16 est utilisé)

Certains points n’ont pas vraiment de sens :

• Définir l’encodage devrait pour être fait indépendamment de l’algorithme de collationnement
• Les algorithmes de collationnement servent à définir des règles de comparaison, à ce titre _UTF8 et _SC ne sont pas véritablement des règles de collationnement mais un bidouillage dû au fait qu’il n’est pas possible de définir l’encodage indépendamment de l’algorithme de collationnement
• _SC devrait être implicite et sélectionné par défaut pour les encodages qui le supportent

Dans mon lab, comme la machine Windows est en Français, le collationnement par défaut au niveau du serveur est French_CI_AS. La requête suivante peut être utilisée pour le vérifier.

SELECT SERVERPROPERTY('Collation');

            |
------------+
French_CI_AS|

Idem pour déterminer le collationnement par défaut au niveau de la base, qui est hérité du serveur.

SELECT DATABASEPROPERTYEX('nom_base_ici', 'Collation');

            |
------------+
French_CI_AS|

Et enfin pour les colonnes, cela sera hérité de la base.

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'nom_table_ici';

Il existe également une syntaxe alternative plus ancienne utilisant sys.

Jusqu’à présent, nous n'avons pas encore parlé de problème de sécurité, mais uniquement de complexité et comportements contre-intuitifs et piégeux. Toutes ces explications seront cependant utiles pour comprendre la suite.

Jeux de caractères limités

Je vais directement dévoiler la conclusion de cette section. Contrairement à une base de données intelligemment architecturée, SQL Server permet d’insérer des caractères invalides dans l’encodage ciblé et il n’existe pas non plus de mode strict permettant de lever une exception pour ce genre de cas. Avec SQL Server, lorsqu’un jeu de caractère « étroit » comme iso_1 (256 codes de symbole) est employé, insérer des caractères Unicode (centaines de milliers de codes de symbole) en dehors de la plage compatible est autorisé. Comme le caractère invalide n’est pas représentable dans cet encodage, il est alors automatiquement et silencieusement converti à l’aide d’un algorithme maison de Microsoft : best fit mapping (traduisible par « correspondance optimale » ou « meilleur ajustement »). Ce qui donnera lieux à des collisions et des mutations en pagaille.

Maintenant essayons d’observer le comportement de SQL Server quand le caractère ＜ (U+FF1C) est injecté dans une colonne utilisant le collationnement par défaut (French_CI_AS) de mon serveur. ＜ (U+FF1C) est un caractère qui sera forcément encodé sur plusieurs octets (3 en UTF-8, 2 en UTF-16, voir sortie unisec), qui existe depuis longtemps et se trouve dans le plan BMP. Ce caractère n’existe pas dans les pages de code Windows sur un octet (ex : CP1252).

$ unisec dump hex ＜
UTF-8: ef bc 9c
UTF-16BE: ff1c
UTF-16LE: 1cff
UTF-32BE: 0000ff1c
UTF-32LE: 1cff0000

$ unisec properties char '＜' | head -8
Name:               FULLWIDTH LESS-THAN SIGN
Code Point:         U+FF1C (65308)

Block:              Halfwidth and Fullwidth Forms
Category:           Symbol
Sub-Category:       Math_Symbol
Script:             Common
Since (age):        Version 1.1

Pour la comparaison et pour se repérer dans les sorties hexadécimales des outils, le caractère a sera utilisé comme témoin.

$ unisec dump hex a
UTF-8: 61
UTF-16BE: 0061
UTF-16LE: 6100
UTF-32BE: 00000061
UTF-32LE: 61000000

$ unisec properties char 'a' | head -8
Name:               LATIN SMALL LETTER A
Code Point:         U+0061 (97)

Block:              Basic Latin
Category:           Letter
Sub-Category:       Lowercase_Letter
Script:             Latin
Since (age):        Version 1.1

Requête SQL permettant de créer la table de test (testé sur MS SQL Server 2016 & 2019) :

CREATE TABLE my_narrow_column
(
    mytext_narrow VARCHAR(10) COLLATE French_CI_AS,
    mytext_wide NVARCHAR(10) COLLATE French_CI_AS
);

Vérification de l’encodage des colonnes.

SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_narrow_column' AND COLUMN_NAME LIKE 'mytext_%';
/* Sortie :
iso_1
UNICODE
*/

Insertion des données.

INSERT INTO my_narrow_column VALUES ('a', 'a'); /* => Ok, pas d'erreur */
INSERT INTO my_narrow_column VALUES ('＜', '＜'); /* => Ok, pas d'erreur */

La sortie montre que ＜ (U+FF1C) c’est transformé en < (U+003C) à l’insertion, aussi bien pour la colonne mytext_narrow (iso_1) que pour mytext_wide (UNICODE).

$ sqlcmd query --database Unicode --query 'SELECT mytext_narrow, mytext_wide FROM my_narrow_column;'
mytext_narrow mytext_wide
------------- -----------
a             a
<             <

(2 rows affected)

$ sqlcmd query --database Unicode --query 'SELECT mytext_narrow, mytext_wide FROM my_narrow_column;' | awk 'NR >= 3 && NR <= 4' | hexxy
0000000: 6120 2020 2020 2020 2020 2020 2020 6120  a             a
0000010: 2020 2020 2020 2020 200a 3c20 2020 2020           .<
0000020: 2020 2020 2020 2020 3c20 2020 2020 2020          <
0000030: 2020 200a                                         .

Comme expliqué précédemment, il était attendu que la colonne en iso_1 convertisse la valeur avec BestFit, mais pour la colonne en UNICODE la valeur devrait pouvoir être stockée, hors elle a tout de même été convertie. Comme les bases du lab sont en MS SQL Server 2016 & 2019, UNICODE retourne UTF-16. Et même si UCS-2 était retourné, ＜ (U+FF1C) est dans BMP donc aussi encodable en UCS-2. S’il avait s’agit d’un caractère hors-BMP, alors une explication aurait pu provenir d’une limitation que Microsoft documente sur le type nvarchar.

SQL Server propose des types de données tels que nchar et nvarchar pour stocker des données Unicode dans la plage BMP (000000 - 00FFFF), que le moteur de base de données encode à l’aide du codage UCS-2.

Dans SQL Server 2012 (11.x), Microsoft a introduit la famille _SC d’’algorithme de collationnement afin de pouvoir utiliser la plage Unicode complète avec nchar et nvarchar.

À partir de SQL Server 2017 (14.x), tous les algorithmes de collationnement (uniquement les nouveaux à partir de cette version, pas rétroactif) supportent automatiquement _SC par défaut, qui, par conséquent, n’apparaitra plus dans le nom. Il est donc impossible de savoir si les plans 1 à 17 sont supportés ou uniquement le premier simplement en lisant le nom de l’algorithme de collationnement. Cela dépendra donc aussi de la version de l’algorithme de collationnement. Les algorithmes de collationnement de version 80 ne prennent pas en charge l’attribut _SC, les versions 90 et 100 le supporte ; les versions 140 ne le prennent pas en charge, car elles sont _SC par défaut.

SQL Server 2019 (15.x) étend la prise en charge des caractères supplémentaires aux types de données char et varchar grâce aux nouveaux algorithmes de collationnement compatibles UTF-8 (_UTF8). Ces types de données permettent également de représenter l’ensemble des caractères Unicode.

Or, on observe toujours le même comportement sur MS SQL Server 2019 avec le jeu de caractères French_100_CI_AS_SC, qui est censé utiliser l’UTF-16, et French_100_CI_AS_SC_UTF8, qui utilise l’UTF-8. Il doit donc y avoir une autre cause.

CREATE TABLE my_narrow_column2
(
    narrow VARCHAR(10) COLLATE French_CI_AS,
    wide1 NVARCHAR(10) COLLATE French_CI_AS,
    wide2 NVARCHAR(10) COLLATE French_100_CI_AS_SC,
    wide3 NVARCHAR(10) COLLATE French_100_CI_AS_SC_UTF8 /* Only for MS SQL Server 2019 */
);

SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_narrow_column2'; /* => narrow = iso_1, widex = UNICODE */

INSERT INTO my_narrow_column2 VALUES ('a', 'a', 'a', 'a'); /* => Ok */
INSERT INTO my_narrow_column2 VALUES ('＜', '＜', '＜', '＜'); /* => Ok */

$ sqlcmd query --query 'SELECT narrow, wide1, wide2, wide3 FROM my_narrow_column2;' | awk 'NR >= 3 && NR <= 4' | hexxy
0000000: 6120 2020 2020 2020 2020 2061 2020 2020  a          a
0000010: 2020 2020 2020 6120 2020 2020 2020 2020        a
0000020: 2061 2020 2020 2020 2020 200a 3c20 2020   a         .<
0000030: 2020 2020 2020 203c 2020 2020 2020 2020         <
0000040: 2020 3c20 2020 2020 2020 2020 203c 2020    <          <
0000050: 2020 2020 2020 200a                                 .

J’ai testé avec la version sensible à la casse _CS au lieu de la version insensible à la casse _CI, au cas où celle-ci aurait été mal implémentée et provoquerait un conflit, mais cela n’a rien changé ; jusqu’à présent, il a été impossible d’insérer un caractère hors BMP.

À ce stade, je soupçonne qu’il s’agit d’un problème d’interface et non d’un problème de base de données. Jusqu’à présent, go-sqlcmd était utilisé (un outil Microsoft).

Pour vérifier que cela vienne du produit, il est possible de se connecter à la base de données avec dBeaver et insérer, stocker et récupérer des caractères non BMP. Ce serait donc potentiellement Microsoft go-sqlcmd qui perturbe les encodages ? Peut-être côté client ou peut-être en se connectant à la base de données avec UCS-2 au lieu d’UTF-16 pour les insertions ? Pour en avoir le cœur net, utilisons plutôt FreeTDS avec la gemme Ruby TinyTDS (tiny_tds), pour un meilleur contrôle de l’encodage.

require 'tiny_tds'
require 'tty-table'

# Convertisseur de tableau de hachages en tableau formaté
def table_print(table)
  headers = table.first.keys
  rows = table.map{ |h| h.values }
  puts TTY::Table.new(header: headers, rows: rows).render(:basic)
end

# Connection to database
client = TinyTds::Client.new(
  username: 'edited', password: 'edited',
  host: 'edited', port: 1433,
  database: 'Unicode', azure: false,
  encoding: 'UTF-8', use_utf16: true
)

# Créer une table
query = <<~SQL
  CREATE TABLE my_narrow_column
  (
      mytext_narrow VARCHAR(10) COLLATE French_CI_AS,
      mytext_wide NVARCHAR(10) COLLATE French_CI_AS
  );
SQL
client.execute(query).insert
# Insert values
client.execute("INSERT INTO my_narrow_column VALUES ('a', 'a');").insert
client.execute("INSERT INTO my_narrow_column VALUES ('＜', '＜');").insert

# Vérifier le jeu de caractères et le collationnemlent des colonnes
query = <<~SQL
  SELECT CHARACTER_SET_NAME, COLLATION_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'my_narrow_column'
    AND COLUMN_NAME LIKE 'mytext_%';
SQL
table_print(client.execute(query).to_a)

# Récupérer les données insérées
table_print(client.execute('SELECT mytext_narrow, mytext_wide FROM my_narrow_column;').to_a)

# Fermer la connexion à la base de données
client.close

# […]

# Créer une table
query = <<~SQL
  CREATE TABLE my_narrow_column2
  (
    narrow VARCHAR(10) COLLATE French_CI_AS,
    wide1 NVARCHAR(10) COLLATE French_CI_AS,
    wide2 NVARCHAR(10) COLLATE French_100_CI_AS_SC,
    wide3 NVARCHAR(10) COLLATE French_100_CI_AS_SC_UTF8 /* Only for MS SQL Server 2019 */
  );
SQL
client.execute(query).insert
# Insert values
client.execute("INSERT INTO my_narrow_column2 VALUES ('a', 'a', 'a', 'a');").insert
client.execute("INSERT INTO my_narrow_column2 VALUES ('＜', '＜', '＜', '＜');").insert

# Vérifier le jeu de caractères et le collationnemlent des colonnes
query = <<~SQL
  SELECT CHARACTER_SET_NAME, COLLATION_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'my_narrow_column2';
SQL
table_print(client.execute(query).to_a)

# Récupérer les données insérées
table_print(client.execute('SELECT narrow, wide1, wide2, wide3 FROM my_narrow_column2;').to_a)

# […]

Le comportement observé reste identique entre TinyTDS (tiny_tds) et go-sqlcmd, même si, cette fois-ci, l’encodage du client TinyTDS (tiny_tds) est défini sur UTF-8 et que l’encodage de la base de données pour les caractères « larges » est forcé en UTF-8 (afin de s’assurer que le moteur de base de données n’utilise pas UCS-2). La récupération des données ne pose pas de problème, mais leur insertion est altérée par le client. En essayant également l’extension ms-mssql.mssql de Visual Studio Code, on obtient le même résultat ; cette extension ne propose pas de paramètres d’encodage, sauf pour l’exportation CSV.

Sur dBeaver, l’insertion de requêtes contenant des caractères « larges » depuis la console SQL ou l’insertion manuelle de ces caractères en modifiant les cellules dans la vue du tableau de données permet de les stocker correctement. Le problème semble donc être côté client, non ?

Chaînes de caractères littérales (Character String literals)

J’ai trouvé la réponse tout à fait par accident en regardant une vidéo au hasard sur YouTube. Dans MS SQL Server, toutes les chaînes littérales sont par défaut non-Unicode (quel encodage ?) même lorsque la colonne est configurée en Unicode. Pour prendre en charge l’Unicode, il faut ajouter le préfixe « N » avant le premier guillemet.

Comparons les deux cas, avec et sans, en prenant un exemple hors-BMP plus visuel :

INSERT INTO my_narrow_column_emo VALUES ('👻', '👻', '👻', '👻');
INSERT INTO my_narrow_column_emo VALUES (N'👻', N'👻', N'👻', N'👻');
SELECT narrow, wide1, wide2, wide3 FROM my_narrow_column_emo;

$ sqlcmd query --database unicode --query 'SELECT narrow, wide1, wide2, wide3 FROM my_narrow_column_emo;' | awk 'NR >= 3 && NR <= 4'
??         ??         ??         ??
??         👻          👻          👻

$ sqlcmd query --database unicode --query 'SELECT narrow, wide1, wide2, wide3 FROM my_narrow_column_emo;' | awk 'NR >= 3 && NR <= 4' | hexxy
0000000: 3f3f 2020 2020 2020 2020 203f 3f20 2020  ??         ??
0000010: 2020 2020 2020 3f3f 2020 2020 2020 2020        ??
0000020: 203f 3f20 2020 2020 2020 200a 3f3f 2020   ??        .??
0000030: 2020 2020 2020 20f0 9f91 bb20 2020 2020         ....
0000040: 2020 2020 20f0 9f91 bb20 2020 2020 2020       ....
0000050: 2020 20f0 9f91 bb20 2020 2020 2020 2020     ....
0000060: 0a

Ainsi, 👻 était littéralement converti en ?? lorsqu’il était inséré sous forme de chaîne littérale classique, même dans les colonnes NVARCHAR. En revanche, lorsqu’il était inséré avec le préfixe N, il n’était converti que dans la colonne VARCHAR.

Reprenons l’exemple de U+ff1c ＜.

INSERT INTO my_narrow_column2 VALUES ('＜', '＜', '＜', '＜');
INSERT INTO my_narrow_column2 VALUES (N'＜', N'＜', N'＜', N'＜');
SELECT narrow, wide1, wide2, wide3 FROM my_narrow_column2;

$ sqlcmd query --database unicode --query 'SELECT narrow, wide1, wide2, wide3 FROM my_narrow_column2;' | awk 'NR >= 3 && NR <= 4'
<          <          <          <
<          ＜          ＜          ＜

$ sqlcmd query --database unicode --query 'SELECT narrow, wide1, wide2, wide3 FROM my_narrow_column2;' | awk 'NR >= 3 && NR <= 4' | hexxy
0000000: 3c20 2020 2020 2020 2020 203c 2020 2020  <          <
0000010: 2020 2020 2020 3c20 2020 2020 2020 2020        <
0000020: 203c 2020 2020 2020 2020 200a 3c20 2020   <         .<
0000030: 2020 2020 2020 20ef bc9c 2020 2020 2020         ...
0000040: 2020 2020 efbc 9c20 2020 2020 2020 2020      ...
0000050: 20ef bc9c 2020 2020 2020 2020 200a         ...         .

Comme nous l’avons vu au début, le caractère U+ff1c ＜ a été transformé en caractère U+003c < lorsque nous utilisons une chaîne littérale non compatible Unicode ou une colonne avec un encodage non Unicode. Cette fois-ci, ce n’est pas ??.

La question est la suivante : l’algorithme BestFit de Microsoft est-il en quelque sorte compatible avec l’Unicode et s’appuie-t-il sur une propriété Unicode telle que Decomposition_Mapping, Idn_Mapping, NFKC_Simple_Casefold, ou bien tente-t-il de trouver un caractère de remplacement approprié de manière totalement arbitraire et propriétaire ?

Conversion implicite

Comme nous l’avons vu précédemment, la conversion implicite existe et est déterminée par le type d’instruction : affectation, insertion, comparaison, autres expressions, etc. Dans certains cas, le type de données résultant dépend des règles de priorité des types de données. Ce graphique montre que la conversion automatique peut être destructive, car une conversion implicite peut se produire, par exemple, de nchar vers char ou de nvarchar vers varchar.

Best Fit

Dans leur article intitulé « WorstFit », Orange Tsai et Splitline se sont penchés sur ce qui se passe lorsque Windows convertit une chaîne de caractères « à plage large » (par exemple, encodée en UTF-16) en une chaîne « à plage étroite » (par exemple, encodée en CP1252), ainsi que sur la manière dont s’effectue cette conversion via l’algorithme BestFit (meilleure adéquation) de Microsoft.

• Orange Tsai - WorstFit: Unveiling Hidden Transformers in Windows ANSI!
• Devcore - WorstFit: Unveiling Hidden Transformers in Windows ANSI!

Sous le capot, les fonctions API internes de Microsoft WideCharToMultiByte et RtlUnicodeStringToAnsiString sont utilisées pour convertir l’API « W » (wide = Unicode) en API « A » (ANSI = pages de codes Windows). Cette conversion n’est pas normalisée et dépend de l’éditeur. La correspondance personnalisée de Microsoft, connu sous le nom de Best Fit, est stocké sous forme de base de données textuelle sur le site web d’Unicode. Orange Tsai et Splitline ont également facilité la recherche dans cette correspondance grâce à leur site web. Exemple de requêtes pour les caractères présentés précédemment :

• ＜ (U+FF1C) ➡️ < (0x003c) : CP:1252 WCHAR:0xff1c
• 👻 (U+1F47B) ➡️ ?? (car sans correspondance) : CP:1252 WCHAR:0x1f47b

Plus loin dans l’article, ils expliquent comment transformer ce vecteur d’attaque en véritables vulnérabilités, telles que la manipulation des noms de fichiers, la division des arguments et la confusion des variables d’environnement.

L’attaque « WorstFit » s’apparente à la collision par transformation de casse, mais elle est plus efficace, car elle offre davantage de candidats à la collision et permet de trouver plus facilement des fonctions qui effectuent implicitement la conversion que des opérations de transformation de casse. Elle pourrait même s’apparenter davantage à la collision avec des formes normalisées en mode de compatibilité, telles que NFKC et NFKD, mais elle est moins performante, car l’intervalle de destination est plus restreint et le résultat dépend de la page de codes.

Un exemple de collisions générées par les attaques WorstFit décrites dans l’article, en transformant :

• Trait d’union conditionnel (U+00AD, Soft Hyphen) en Trait d’union-signe moins (U+002D, Hyphen-Minus) pour injecter des arguments dans les CGI avec CP 932, 936 & 950
• Barre oblique pleine chasse (U+FF0F, Fullwidth Solidus), Barre oblique inversée pleine chasse (U+FF3C, Fullwidth Reverse Solidus), Symbole yen (U+00A5, Yen Sign), Symbole won (U+20A9, Won Sign) et beaucoup d’autres vers Barre oblique (U+002F, Solidus) ou Barre oblique inversée (U+005C, Reverse Solidus) afin de manipuler les chemins de fichier et de créer des remontées de répertoire avec CP 874, 125x, 932, 949
• Guillemet pleine chasse (U+FF02, Fullwidth Quotation Mark), Barre oblique inversée pleine chasse (U+FF3C, Fullwidth Reverse Solidus), Symbole yen (U+00A5, Yen Sign), Symbole won (U+20A9, Won Sign) and beaucoup d’autres vers Guillemet anglais (U+0022, Quotation Mark) ou Barre oblique inversée (U+005C, Reverse Solidus) pour découper les arguments d’outils CLI et mener à l’exécution de commande avec CP 874, 125x, 932, 949

Les attaques décrites dans l’article visaient principalement des applications web, des scripts CGI et certains exécutables bien connus. L'article va s’attacher ici à identifier des attaques similaires sur IIS, en exploitant les jeux de caractères limités associés à la conversion implicite.

Afin de mesurer à quel point les collisions basées sur WorsFit sont plus performantes que celles basées sur la normalisation, voici quelques chiffres :

• seulement 2 caractères se transforment en < (U+003C, LESS-THAN SIGN) après une transformation NFKC ou NFKD
• 22 caractères se transforment en < (U+003C, LESS-THAN SIGN) après une correspondance BestFit

Par exemple, pour une application en .NET C# sous Windows, les chaînes de caractères seront encodées en UTF-16, mais si la colonne de la base de données IIS utilise un encodage ANSI, si les données sont implicitement converties ou si un type inapproprié est utilisé (char, varchar, text), les données seront alors implicitement converties à l’aide de la correspondance « Best Fit ». Il est facile de comprendre comment une chaîne Unicode pourrait contourner un filtre ou un WAF, mais une fois convertie en ANSI, elle pourrait devenir une véritable charge utile.

Le code PHP ci-dessous montre une charge utile XSS classique qui se fait échapper par l’application d’un côté et une version Unicode qui ne se fait pas échapper, mais qui est invalide et ne sera pas exécutée de l’autre.

/* Charge utile XSS classique, valide mais échappée */
print_r(htmlspecialchars("<script>alert('test')</script>"));
/* => &lt;script&gt;alert(&#039;test&#039;)&lt;/script&gt; */

/* Charge utile Unicode (Worst Fit), non échappée mais invalide */
print_r(htmlspecialchars("〈script〉alert(ʹtestʹ)〈/script〉"));
/* => 〈script〉alert(ʹtestʹ)〈/script〉 */

Cependant, une fois passé le filtre applicatif, la charge utile se retrouvera envoyée pour stockage en base de données. Ici comme l’encodage sera CP1252, la charge utile Unicode va se trouver automatiquement convertit via WorstFit afin de redevenir une charge utile valide qui sera exécutée.

CREATE TABLE worstfitx ( vary VARCHAR(50) COLLATE French_CI_AS );

SELECT COLUMN_NAME, TABLE_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'worstfitx';
/* => vary,varchar,50,iso_1,French_CI_AS */

INSERT INTO worstfitx VALUES (N'〈script〉alert(ʹtestʹ)〈/script〉');

SELECT vary FROM worstfitx;
/* => <script>alert('test')</script> */

De même, même lorsque les fichiers sont stockés sur le système de fichiers, on y accède rarement par leur nom de fichier, mais plutôt par une référence unique ; la correspondance entre cette référence et le nom de fichier, ainsi que les droits d’accès, sont souvent stockés dans la base de données. Ainsi, lors du téléchargement d’un fichier, l’application stocke le nom de fichier sous un format UTF-16, mais lorsqu’elle souhaite le lire ultérieurement, le chemin d’accès est récupéré depuis la base de données et converti en CP-1252. L’inclusion de fichiers locaux est donc accessible de cette manière.

CREATE TABLE worstfity ( id SMALLINT, file_name VARCHAR(50) COLLATE French_CI_AS );

SELECT COLUMN_NAME, TABLE_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'worstfity';
/* =>
id,worstfity,smallint,NULL,NULL,NULL
file_name,worstfity,varchar,50,iso_1,French_CI_AS
*/

/* U+ff0E + U+2216 */
INSERT INTO worstfity VALUES (45, N'．．∖．．∖．．∖．．∖．．∖Windows∖win.ini');

SELECT file_name FROM worstfity WHERE id = 45;
/* => ..\..\..\..\..\Windows\win.ini */

Dans ce cas, le fichier serait stocké à l’emplacement C:\inetpub\uploads\．．∖．．∖．．∖．．∖．．∖Windows∖win.ini, mais l’accès au fichier portant l’identifiant 45 correspondra en réalité à C:\inetpub\uploads\..\..\..\..\..\ Windows\win.ini, et donc C:\Windows\win.ini une fois le chemin normalisé. Cependant, ．．∖ aura potentiellement permis de contourner un filtre.

De plus, sur la plupart des pages de codes, le caractère de secours pour les caractères sans correspondance est ? (U+003F). Les mêmes attaques que celles possibles lorsque le mode strict de MySQL est désactivé peuvent donc être utilisées : expansion de nom de fichier shell, injection de quantificateurs d’expressions régulières, de paramètres de requête HTTP ou contournement des pare-feux applicatifs.

Par exemple, pour stocker un ? lorsque ? est filtré, il suffirait d’enregistrer n’importe quel caractère non spécifié dans la table de correspondance « Best Fit » de la page de codes de destination.

INSERT INTO worstfitx VALUES (N'<ʭphp');

SELECT vary FROM worstfitx ;
/* => <?php */

Collationnement et collisions de transformation de casse

Comme nous l’avons vu dans la section consacrée à l’encodage par défaut, le collationnement par défaut est toujours insensible à la casse (_CI).

Cela signifie que des collisions de transformation de casse peuvent survenir implicitement lors de l’utilisation de fonctions et d’opérateurs de comparaison de chaînes. Par exemple, la documentation relative à LIKE indique explicitement que les comparaisons LIKE sont affectées par le collationnement, contrairement à l’opérateur = (comparaison ou affectation de chaînes) qui n’indique rien.

Ici, les deux opérateurs (= et LIKE) génèrent des collisions.

SELECT IIF('abc' = 'ABC', 1, 0);
/* => 1 */
SELECT IIF(N'abc' = N'ABC', 1, 0);
/* => 1 */


SELECT 'equals' WHERE 'abc' LIKE 'ABC';
/* => equals */
SELECT 'equals' WHERE N'abc' LIKE N'ABC';
/* => equals */

En pratique, = est aussi affecté par la casse.

CREATE TABLE eq_test
  (
    ncol1_d VARCHAR(10) COLLATE French_CI_AS,
    ncol1_u VARCHAR(10) COLLATE French_CI_AS,

    wcol2_d NVARCHAR(10) COLLATE French_CI_AS,
    wcol2_u NVARCHAR(10) COLLATE French_CI_AS,

    ncol3_d VARCHAR(10) COLLATE French_CS_AS,
    ncol3_u VARCHAR(10) COLLATE French_CS_AS,

    wcol4_d NVARCHAR(10) COLLATE French_CS_AS,
    wcol4_u NVARCHAR(10) COLLATE French_CS_AS,

    ncol5_d VARCHAR(10) COLLATE French_100_CI_AS_SC,
    ncol5_u VARCHAR(10) COLLATE French_100_CI_AS_SC,

    wcol6_d NVARCHAR(10) COLLATE French_100_CI_AS_SC,
    wcol6_u NVARCHAR(10) COLLATE French_100_CI_AS_SC,

    ncol7_d VARCHAR(10) COLLATE French_100_CS_AS_SC,
    ncol7_u VARCHAR(10) COLLATE French_100_CS_AS_SC,

    wcol8_d NVARCHAR(10) COLLATE French_100_CS_AS_SC,
    wcol8_u NVARCHAR(10) COLLATE French_100_CS_AS_SC
  );

INSERT INTO eq_test VALUES ('abc', 'ABC', 'abc', 'ABC', 'abc', 'ABC', 'abc', 'ABC', 'abc', 'ABC', 'abc', 'ABC', 'abc', 'ABC', 'abc', 'ABC');

SELECT IIF(ncol1_d = ncol1_u, 1, 0) FROM eq_test; /* => 1 */
SELECT IIF(wcol2_d = wcol2_u, 1, 0) FROM eq_test; /* => 1 */
SELECT IIF(ncol3_d = ncol3_u, 1, 0) FROM eq_test; /* => 0 */
SELECT IIF(wcol4_d = wcol4_u, 1, 0) FROM eq_test; /* => 0 */
SELECT IIF(ncol5_d = ncol5_u, 1, 0) FROM eq_test; /* => 1 */
SELECT IIF(wcol6_d = wcol6_u, 1, 0) FROM eq_test; /* => 1 */
SELECT IIF(ncol7_d = ncol7_u, 1, 0) FROM eq_test; /* => 0 */
SELECT IIF(wcol8_d = Wcol8_u, 1, 0) FROM eq_test; /* => 0 */

Le même comportement apparait avec des caractères non-ASCII.

INSERT INTO eq_test VALUES ('ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS');

SELECT IIF(ncol1_d = ncol1_u, 1, 0) FROM eq_test; /* => 1 */
SELECT IIF(wcol2_d = wcol2_u, 1, 0) FROM eq_test; /* => 1 */
SELECT IIF(ncol3_d = ncol3_u, 1, 0) FROM eq_test; /* => 0 */
SELECT IIF(wcol4_d = wcol4_u, 1, 0) FROM eq_test; /* => 0 */
SELECT IIF(ncol5_d = ncol5_u, 1, 0) FROM eq_test; /* => 1 */
SELECT IIF(wcol6_d = wcol6_u, 1, 0) FROM eq_test; /* => 1 */
SELECT IIF(ncol7_d = ncol7_u, 1, 0) FROM eq_test; /* => 0 */
SELECT IIF(wcol8_d = Wcol8_u, 1, 0) FROM eq_test; /* => 0 */

De même avec LIKE.

INSERT INTO eq_test VALUES ('ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS', 'ß', 'SS');

SELECT 'equals col1' FROM eq_test WHERE ncol1_d LIKE ncol1_u; /* => equals col1 */
SELECT 'equals col2' FROM eq_test WHERE wcol2_d LIKE wcol2_u; /* => equals col2 */
SELECT 'equals col3' FROM eq_test WHERE ncol3_d LIKE ncol3_u; /* => */
SELECT 'equals col4' FROM eq_test WHERE wcol4_d LIKE wcol4_u; /* => */
SELECT 'equals col5' FROM eq_test WHERE ncol5_d LIKE ncol5_u; /* => equals col5 */
SELECT 'equals col6' FROM eq_test WHERE wcol6_d LIKE wcol6_u; /* => equals col6 */
SELECT 'equals col7' FROM eq_test WHERE ncol7_d LIKE ncol7_u; /* => */
SELECT 'equals col8' FROM eq_test WHERE wcol8_d LIKE wcol8_u; /* => */

Fonctions et opérateurs de chaînes (de caractères)

ASCII() / UNICODE()

ASCII() - Renvoie la valeur du code ASCII du caractère situé le plus à gauche d’une expression de caractères.

L’ASCII est un jeu de caractères à 7 bits. L’ASCII étendu ou Haut ASCII est un jeu de caractères à 8 bits qui n’est pas pris en charge par la fonction ASCII.

Ainsi, lorsqu’un caractère multioctet est transmis, cette fonction ne renvoie que la valeur numérique de l’octet le plus à gauche, et non celle du caractère le plus à gauche. Dans d’autres SGBD, cette fonction a souvent été mise à jour avec l’arrivée de l’Unicode pour renvoyer la valeur du point de code des caractères Unicode (ce qui serait rétrocompatible). Ce n’est pas le cas dans SQL Server. Microsoft a conservé ASCII() et a implémenté une fonction distincte, UNICODE(), pour les caractères/chaînes plus larges.

Par exemple, le point de code du caractère ＜ est FF1C en hexadécimal ou 65308 en décimal. En UTF-8, il sera encodé sur 3 octets sous la forme ef bc 9c, et en UTF-16LE sur 2 octets sous la forme 1cff.

➜ unisec hexdump "＜"
UTF-8: ef bc 9c
UTF-16BE: ff1c
UTF-16LE: 1cff
UTF-32BE: 0000ff1c
UTF-32LE: 1cff0000

Dans un langage moderne, on pourrait s’attendre à ce que la fonction ASCII() se comporte comme UNICODE(), mais même si l’on sait qu’elle ne gère pas correctement l’Unicode, on s’attend au moins à ce qu’elle tronque la chaîne de caractères larges en ne lisant que les 7 ou 8 premiers bits. Or, ce n’est pas ce qui se passe : la chaîne est plutôt convertie à l’aide de BestFit afin de forcer le caractère Unicode à s’adapter plus ou moins au format ASCII. La documentation indique que pour l’argument de ASCII(), l’expression doit être de type char ou varchar, alors qu’elle est de type nchar ou nvarchar pour UNICODE().

Ainsi, par exemple, U+FF1C correspond à U+003C, ce qui donne 60, alors qu’une troncature des 8 premiers bits de la valeur UTF-16 aurait donné 28.

SELECT ASCII(N'＜');
/* => 60 */

Une meilleure approche aurait consisté à mettre en place un mode strict : si ce mode est activé, déclencher une erreur ; s’il est désactivé, convertir toutes les valeurs non ASCII en ?. Ou, au pire, ne pas effectuer de conversion implicite et se contenter d’une troncature simple. Au mieux, puisque UNICODE() est rétrocompatible avec ASCII(), il suffirait de supprimer ASCII() et de faire de cette dernière un alias de UNICODE().

Mais comme BestFit utilise un caractère de remplacement pour les caractères sans correspondance, lors de la conversion en CP-1252, tous les caractères larges sans correspondance renverront la valeur 63 pour ?.

SELECT ASCII(N'🥷');
/* => 63 */

Même avec UNICODE(), il faut veiller à ne pas oublier le modificateur de chaîne de caractères N.

SELECT UNICODE(N'🥷');
/* => 55358 */

SELECT UNICODE('🥷');
/* => 63 */

De plus, le nom de la fonction et la documentation sont totalement mensongers : ASCII() ne renvoie pas la valeur ASCII ! Au contraire, ASCII renvoie la valeur d’encodage correspondant à l’encodage associé au collationnement associé à la langue actuelle (qui correspondra à la valeur par défaut du collationnement par défaut de la base de données actuelle, et non au collationnement du serveur) applicable aux types char / varchar, ce qui, pour le français, sera CP-1252. Mais CP-1252 n’est pas l’ASCII ! L’encodage ASCII ne comporte que 128 valeurs, qui peuvent être stockées sous la forme d’un entier de 7 bits, tandis que l’encodage CP-1252 comporte 256 valeurs, qui peuvent être stockées sous la forme d’un entier de 8 bits. La fonction ASCII() fonctionne donc également pour la plage 0x80 à 0xff, et ne renverra pas la valeur non officielle Extended-ASCII ou ISO/IEC 8859-1, mais la valeur actuelle de la page de codes Windows.

SELECT DATABASEPROPERTYEX('unicode', 'Collation');
/* French_CI_AS (=> CP-1252 mal étiquetté en tant que iso_1 ) */

SELECT ASCII(N'€');
/* => 128 (0x80) */

SELECT ASCII(N'É');
/* => 201 (0xC9) */

SELECT ASCII(N'ｩ');
/* => 63 (?) */

En effet, la même requête ne renverra pas les mêmes valeurs si la base de données utilise un autre collationnement par défaut.

SELECT DATABASEPROPERTYEX('japan', 'Collation');
/* Japanese_CI_AS (=> CP-932) */

SELECT ASCII(N'€');
/* => 63 (?) */

SELECT ASCII(N'É');
/* => 69 (0x45, E, seems to have been BestFitted) */

SELECT ASCII(N'ｩ');
/* => 169 (0xa9) */

CHAR() / NCHAR()

CHAR() - Renvoie le caractère d’un octet correspondant au code entier spécifié, tel que défini par le jeu de caractères et l’encodage de l’algorithme de collationnement par défaut de la base de données actuelle.

De même, la fonction CHAR() opère principalement sur la plage de la page de codes actuelle (et non sur l’ASCII, contrairement à ce qui est indiqué). Il convient également de noter qu’elle utilise l’encodage et le collationnement par défaut de la base de données actuelle, et non ceux de la colonne récupérée, par exemple.

SELECT CHAR(90);
/* => Z */

SELECT CHAR(128);
/* => € */

SELECT CHAR(65308);
/* => NULL */

La fonction CHAR() renvoie la valeur NULL pour toute valeur supérieure à 255. Pour une variante compatible Unicode, il existe la fonction NCHAR(). Là encore, il aurait été préférable de supprimer la fonction CHAR() et d’en faire plutôt un alias de NCHAR().

Vecteur d’attaque :

Toute saisie d’un caractère > 255 dans la fonction CHAR() renvoi NULL. Or NULL se propage avec la concaténation.

SELECT 'PREFIXE' + CHAR(256) + 'SUFFIXE' /* => NULL */
SELECT 'succès' WHERE 'PREFIXE' + CHAR(256) + 'SUFFIXE' IS NULL /* => succès */

Ce comportement pourra être dangereux lors de la création d’un chemin de fichier, par exemple.

LEN()

LEN() - Renvoie le nombre de caractères de la chaîne de caractères spécifiée, sans compter les espaces à la fin.

DECLARE @v1 AS VARCHAR (40), @v2 AS NVARCHAR (40);

SELECT @v1 = N'＜',
       @v2 = N'＜';

SELECT LEN(@v1) AS [VARCHAR LEN]; /* => 1 */
SELECT LEN(@v2) AS [NVARCHAR LEN]; /* => 1 */

SELECT @v1 = N'💩',
       @v2 = N'💩';

SELECT LEN(@v1) AS [VARCHAR LEN]; /* => 2 */
SELECT LEN(@v2) AS [NVARCHAR LEN]; /* => 2 */

SELECT @v1 = N'諭',
       @v2 = N'諭';

SELECT LEN(@v1) AS [VARCHAR LEN]; /* => 2 */
SELECT LEN(@v2) AS [NVARCHAR LEN]; /* => 2 */

La fonction LEN() renvoie la longueur correcte pour certains caractères, mais une longueur erronée pour d’autres. Une partie de l’explication figure dans la documentation.

Si vous utilisez les collations SC, l’entier retourné en valeur de retour considère les paires de caractères de substitution UTF-16 (surrogates) comme un seul caractère.

Ainsi, la fonction LEN() renvoie la taille correcte uniquement pour les caractères BMP, et la valeur 2 pour les caractères non-BMP. Avec le classement _SC, la fonction LEN() compte les caractères de substitution UTF-16 comme un seul caractère (comme il se doit).

CREATE TABLE len_test
  (
    ncol1 VARCHAR(10) COLLATE French_CI_AS,
    wcol2 NVARCHAR(10) COLLATE French_CI_AS,
    ncol3 VARCHAR(10) COLLATE French_100_CI_AS_SC,
    wcol4 NVARCHAR(10) COLLATE French_100_CI_AS_SC,
    ncol5 VARCHAR(10) COLLATE French_100_CI_AS_SC_UTF8,
    wcol6 NVARCHAR(10) COLLATE French_100_CI_AS_SC_UTF8
  );

INSERT INTO len_test VALUES (N'諭', N'諭', N'諭', N'諭', N'諭', N'諭');

SELECT ncol1 AS [VARCHAR CI_AS],
       wcol2 AS [NVARCHAR CI_AS],
       ncol3 AS [VARCHAR 100_CI_AS_SC],
       wcol4 AS [NVARCHAR 100_CI_AS_SC],
       ncol5 AS [VARCHAR 100_CI_AS_SC_UTF8],
       wcol6 AS [NVARCHAR 100_CI_AS_SC_UTF8]
  FROM len_test;

SELECT LEN(ncol1) AS [VARCHAR CI_AS],
       LEN(wcol2) AS [NVARCHAR CI_AS],
       LEN(ncol3) AS [VARCHAR 100_CI_AS_SC],
       LEN(wcol4) AS [NVARCHAR 100_CI_AS_SC],
       LEN(ncol5) AS [VARCHAR 100_CI_AS_SC_UTF8],
       LEN(wcol6) AS [NVARCHAR 100_CI_AS_SC_UTF8]
  FROM len_test;

Pour le type VARCHAR (en dehors de la collation _UTF8), l’explication est simple : la conversion BestFit transforme 諭 en ??, de sorte que la valeur deux correspond en réalité à la longueur de ?? et non à celle de 諭. Lorsque l’on utilise la collation _SC, la longueur renvoyée est correcte, mais des problèmes persistent avec certains types de colonnes.

VARCHAR CI_AS NVARCHAR CI_AS VARCHAR 100_CI_AS_SC NVARCHAR 100_CI_AS_SC VARCHAR 100_CI_AS_SC_UTF8 NVARCHAR 100_CI_AS_SC_UTF8
------------- -------------- -------------------- --------------------- ------------------------- --------------------------
??            諭             ??                   諭                    諭                        諭

VARCHAR CI_AS NVARCHAR CI_AS VARCHAR 100_CI_AS_SC NVARCHAR 100_CI_AS_SC VARCHAR 100_CI_AS_SC_UTF8 NVARCHAR 100_CI_AS_SC_UTF8
------------- -------------- -------------------- --------------------- ------------------------- --------------------------
2             2              2                    1                     1                         1

Pour que la fonction LEN() renvoie la taille correcte, il faut que le type de colonne et l’encodage/le collationnement soient tous deux « compatibles Unicode » ; sinon, des tailles inattendues s’afficheront.

Impactes potentiels :

• Débordement de mémoire tampon
• Troncature des chaînes de caractères
• Accès incorrect à l'index
• Assainissement mal placé

Conclusion

Un développeur d’application se reposant sur Microsoft SQL Server a-t-il une chance de pouvoir gérer Unicode de manière sécurisé dans la base de données ? Non, probablement pas, ou plutôt, il y a de grandes chances de tomber dans l’un des nombreux pièges tendus par Microsoft. Définition indirecte de l’encodage, comportements par défaut dangereux, mécanisme contre-intuitifs ou caché, empilement technologique, nommage trompeur, documentation incomplète ou erronée, etc. la gestion d’Unicode de MS SQL Server est comparable à une forêt de nuit : obscure et semée d’embuches. La situation n’évolue pas clairement. Même si de nouvelles fonctions viennent étendre le support d’Unicode à l’arrivée de nouvelles versions de MS SQL Server, celles-ci étant quasi-toujours optionnelles et pas par défaut, cela participe à étendre le support fonctionnel, mais pas à améliorer la sécurité. Peu d’espoir de changement à horizon proche. Afin de ce prémunir de tous ces problèmes spécifiques à SQL Server, le mieux reste d'utiliser un autre SGBD.

Versions testées

L’ensemble des tests précédents ont été menés sur les versions ci-dessous de Microsoft SQL Server…

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
        Oct 28 2016 18:17:30
        Copyright (c) Microsoft Corporation
        Developer Edition (64-bit) on Windows Server 2019 Standard 6.3 <X64> (Build 17763: ) (Hypervisor)

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
        Sep 24 2019 13:48:23
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

… à l’exception de LEN() qui a été testé sur Microsoft SQL Server 2022 - 16.0.4260.1.