Dans mon article précédent, « Utilisation de LIKE avec des variables et des modèles dans SQL », nous avons exploré le comportement du prédicat LIKE dans différents scénarios, de l'Embedded SQL au Dynamic SQL, et l'impact sur les performances lorsque des caractères génériques et des variables entrent en jeu. Cet article visait à se familiariser avec l'écriture d'une requête LIKE fonctionnelle. Mais écrire du SQL efficace n'est que le point de départ. Pour créer des applications fiables, évolutives et sécurisées, vous devez comprendre les bonnes pratiques qui sous-tendent tout SQL, y compris les requêtes utilisant LIKE.

Cet article franchit une nouvelle étape. Nous aborderons quelques points clés pour renforcer votre code SQL, éviter les pièges courants et garantir l'exécution correcte, efficace et sécurisée de vos instructions SELECT. J'utiliserai les instructions SELECT avec le prédicat LIKE comme exemple, montrant comment ces principes généraux affectent directement vos requêtes et leurs résultats.

Protection et sécurité

Avant tout, une application haute performance est inutile si elle n'est pas sécurisée. Lors de l'utilisation de LIKE avec des données utilisateur, la menace la plus importante est l'injection SQL, qui peut compromettre l'intégrité de vos résultats et la sécurité de l'ensemble de votre base de données.

Je vous rappelle que l'injection SQL est actuellement classée A03:2021 – Injection, ce qui la place en troisième position du Top 10 de l'OWASP pour 2021. Bien que la mise à jour de l'OWASP 2021 l'ait fait passer de la première à la troisième place, cette menace reste pertinente et impactante, même si elle n'est pas la plus répandue. Autre changement : le champ d'application a été élargi, passant de la seule injection SQL à l'injection en général, englobant SQL, NoSQL, les commandes du système d'exploitation, etc. Elle a été signalée dans 94 % des applications testées, avec un taux d'incidence moyen de 3,37 %, ce qui en fait l'une des vulnérabilités les plus courantes.

Alors, comment s'en protéger ? L'autre jour, un membre a posé une question sur la Communauté avec un exemple de code vulnérable à ce problème précis. Il transmettait directement la saisie utilisateur à la chaîne de requête, offrant ainsi à un attaquant la possibilité d'injecter du code SQL malveillant dans la requête, de la modifier et potentiellement d'obtenir un accès non autorisé ou de modifier des données.

Ce problème est traité dans plusieurs articles, le mien et celui de @Ash Sherzhanov en anglais. Pour faire court, évitez de concaténer la saisie utilisateur directement dans l'instruction SQL. Donc, NE FAITES PAS ceci :

 SET input = "Smith"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE '%"_input_"%'")
 SET rs = statement.%Execute()
 DO rs.%Display()

Dans ce cas, l'attaquant peut utiliser l'entrée pour transmettre un code malveillant, par exemple :

 SET input = "' or 1=1 --"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE '%"_input_"%'")
 SET rs = statement.%Execute()
 DO rs.%Display()

En résumé, l'attaquant peut collecter toutes les informations sur les clients de la banque sans aucune connaissance préalable.

Utilisez plutôt le symbole réservé « ? » :

 SET input = "Smith"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE ?")
 SET rs = statement.%Execute("%"_input_"%")
 DO rs.%Display()

Bonnes pratiques :

• Utilisez des paramètres préparés. Ils séparent la structure de la requête des valeurs d'entrée, empêchant ainsi les attaquants d'injecter du code SQL malveillant. En SQL dynamique, il s'agit d'un espace réservé « ? », en SQL embarqué, d'un paramètre nommé précédé de « : ».
• Validation des entrées utilisateur. Valider les entrées utilisateur avant de les insérer dans les requêtes SQL est une autre mesure préventive importante. Utilisez des expressions régulières ou des fonctions de validation appropriées pour vérifier la validité des données.
• Utilisez des modèles dans les descriptions de classe. Ce point est similaire au point précédent, mais permet de vérifier les entrées lors de l'ajout de nouvelles instances.
• Filtrage des caractères spéciaux. Filtrer les caractères spéciaux pouvant être utilisés pour injecter du code SQL peut également contribuer à prévenir les attaques. Utilisez des fonctions de filtrage appropriées pour supprimer ou échapper les caractères potentiellement dangereux.
• Utilisez des procédures stockées. Les procédures stockées sont des objets de base de données qui encapsulent un ensemble d'instructions SQL, permettant une exécution et une réutilisation efficaces. En utilisant des procédures stockées, vous pouvez centraliser la logique de votre application et réduire le risque d'injections SQL.
• Mettre à jour régulièrement les composants logiciels. Assurez-vous de mettre à jour régulièrement InterSystems IRIS et ses composants logiciels associés afin de bénéficier des derniers correctifs de sécurité.

Performances

Une application performante peut gérer d'importants volumes de données et des utilisateurs simultanés sans délai. L'efficacité de vos requêtes SQL est au cœur de ces performances. Une requête lente peut expirer ou, dans un système concurrent, bloquer des ressources et produire des résultats incohérents. Pour une requête utilisant le prédicat LIKE, la différence entre un plan d'exécution rapide et un plan d'exécution lent peut altérer considérablement l'expérience utilisateur et la fiabilité des données renvoyées.

1. Nous connaissons tous les index et leur capacité à améliorer considérablement les performances des requêtes. Cependant, outre la nécessité de disposer d'indices appropriés, il est également essentiel de les utiliser efficacement. Pour une requête LIKE, la présence et la conception appropriées d'un index, ainsi que son utilisation, déterminent si la base de données effectue une recherche rapide ou une analyse longue et complète de la table.

L'exemple ci-dessus n'est pas très adapté aux index. Lorsqu'un motif LIKE commence par un caractère générique, un index B-tree standard ne peut pas être utilisé pour affiner la recherche. La base de données est obligée d'analyser chaque ligne de la table pour trouver une correspondance. Pour les tables volumineuses, cela entraîne une requête lente, susceptible de se bloquer, voire d'expirer, empêchant ainsi l'obtention de résultats. Par conséquent, n'écrivez PAS de requêtes comme celle-ci :

SELECT * FROM Banking.Person WHERE name LIKE '%Smith%'

Cela nécessitera une analyse complète de la table ou de l'index pour trouver « Smith » n'importe où dans le nom, y compris le prénom et le nom de famille.

Si vous connaissez les premières lettres de la chaîne recherchée, indiquez-les sans caractère générique (% ou _) :

SELECT * FROM Banking.Person WHERE name LIKE 'Smith%'

Mieux encore, utilisez le prédicat %STARTSWITH au lieu de LIKE :

 SET input = "Smith"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name %STARTSWITH ?")
 SET rs = statement.%Execute(input)
 DO rs.%Display()

Dans ce cas, la base de données peut effectuer une analyse rapide de l'index, réduisant ainsi considérablement le temps de requête. Cela garantit une récupération rapide des résultats.

Petite information intéressante : par défaut, les comparaisons de chaînes %STARTSWITH et LIKE ne sont pas sensibles à la casse. La requête suivante renvoie donc le même résultat :

 Set input = "smith%"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE ?")
 SET rs = statement.%Execute(input)
 DO rs.%Display()

2. Une autre façon d'influencer les performances des requêtes consiste à utiliser le classement approprié pour une propriété de chaîne. IRIS propose plusieurs classements, les plus courants étant :

• SQLUPPER : Classement par défaut pour la plupart des types de données de chaîne. Il effectue des comparaisons insensibles à la casse en convertissant en interne la valeur de la colonne et le motif en majuscules avant la comparaison. Bien que pratique, cette opération interne peut entraîner une légère baisse de performances par rapport à une comparaison binaire directe.
• SQLSTRING : Ce classement effectue une comparaison binaire exacte et sensible à la casse. Il est souvent plus rapide pour les comparaisons d'égalité et de type LIKE où la sensibilité à la casse est requise, car il évite l'étape de conversion interne.

Si la sensibilité à la casse ou un tri linguistique spécifique est requis, définissez explicitement le classement de la colonne ou utilisez SQLSTRING dans la clause LIKE. Cela empêche la base de données d'effectuer une comparaison insensible à la casse inattendue ou inefficace.

Si, pour une raison quelconque, vous ne pouvez pas/ne souhaitez pas appliquer le classement à la propriété elle-même, vous pouvez toujours utiliser la fonction de classement SQL %SQLSTRING. Cela convertit l'expression en un format trié comme une chaîne sensible à la casse. %SQLSTRING supprime les espaces de fin (espaces, tabulations, etc.) de la chaîne, puis ajoute un espace au début de la chaîne.

Par conséquent, si vous souhaitez rechercher tous les « Smith » et non « smith » ou tout autre type de « sMith », vous pouvez utiliser %SQLSTRING directement dans votre instruction SQL :

 SET input = "Smith%"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE %SQLSTRING name LIKE %SQLSTRING ?")
 SET rs = statement.%Execute(input)
 DO rs.%Display()

Cet exemple utilise le format %SQLSTRING pour rendre cette comparaison sensible à la casse. Il renvoie tous les noms commençant par « Smith ». Notez que dans ce cas, vous devez appliquer le classement %SQLSTRING aux deux extrémités de l'instruction. Cependant, si la colonne elle-même possède déjà le classement SQLSTRING défini dans la propriété de classe, il vous suffit d'appliquer %SQLSTRING au littéral ou à la variable comparée, car la colonne est déjà dans le classement souhaité. Si la colonne possède SQLUPPER (valeur par défaut), l'application de %SQLSTRING aux deux extrémités remplace correctement la valeur par défaut de la colonne.

Bonnes pratiques :

• Attention aux caractères génériques. Dans la mesure du possible, évitez de les utiliser comme premier symbole dans la chaîne de recherche.
• Optimisez le classement. Tenez compte de vos requêtes les plus courantes et configurez le classement directement dans la description de la classe ou dans la requête elle-même.

En résumé, le prédicat LIKE est un outil polyvalent et puissant pour la recherche de motifs. Cependant, son efficacité et sa fiabilité sont intrinsèquement liées à des principes plus larges de performance et de sécurité. Une requête LIKE avec un caractère générique initial peut ne pas renvoyer de résultats rapidement, tandis qu'une requête LIKE vulnérable peut être manipulée pour renvoyer un ensemble de résultats totalement différent de celui prévu. En combinant la syntaxe et les techniques de gestion des variables de l'article précédent avec les meilleures pratiques d'indexation et un engagement constant envers les requêtes paramétrées, vous pouvez garantir que vos instructions LIKE sont non seulement fonctionnelles, mais aussi rapides, sécurisées et fiables, fournissant systématiquement à votre application les résultats corrects.