Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server : Pourquoi utiliser du SQL dynamique (chaîne concaténée contenant du SQL) et pourquoi l’interdire ?

C'est un sujet qui fait débat et souvent les arguments des pro SQL dynamique et de ses opposant sont faibles.

Qu'est ce que le SQL dynamique ?

C'est toute action qui vise à créer une requête SQL à partir de chaînes de caractère. Cette possibilité est offerte côté « client » avec par exemple .Net permettant de remplir une chaine de caractère et de la passer à SQL Server ou côté serveur où il est possible de créer un variable de type char et d'y mettre la dite requête avant de l'exécuter au moyen de EXEC ou de la procédure stockée sp_executesql.

Prenons tout d'abord les défauts du système…

  • Très fortement sujet à de l'injection de code SQL, donc attention à la sécurité de l'application !
  • Génère des compilations pour chaque chaîne de requêtes différentes sur le serveur de base de données

La génération de compilation est un phénomène « normal » et même souhaitable dans certains cas. Le SQL dynamique est un peu un cas particulier, dans la mesure ou EXEC va compiler la chaîne de requête qui lui ai passée, sauf si le plan d'exécution correspondant exactement à celui d'une requête ayant la même chaîne de requête au caractère près (ajoutez un espace ou même un commentaire et adieu la réutilisation du plan, bonjour la compilation).

La compilation étant utilisatrice de processeur, sur un serveur ayant déjà un usage excessif de cette ressource, cela peut aller à la catastrophe. Mais si le serveur en question n'en fait pas un usage extrême, ce phénomène ne se révélera pas bloquant et ne sera sans doute pas visible.

Le premier est lui beaucoup plus critique, en effet l'injection de code SQL peut conduite à exécuter n'importe quelle portion de code SQL sur un serveur SQL Server. A partir du moment où la portion de code SQL est reprise d'une URL ou d'une zone de saisie accessible à l'utilisateur et qu'il n'y a ni contrôle de la saisie ni protection de la chaîne (suppression ou ajout de certains caractères) avant sa concaténation dans la requête, tout devient possible pour un éventuel pirate.

Comment se débarrasser de ces problèmes ?

Pour les questions de compilation et recompilation, soit en utilisant sp_executesql et en identifiant manuellement les paramètres dans la chaine de requête :

-- SQL dynamqique pur et dur

DECLARE @name sysname;

DECLARE @sql nvarchar(max);

 

SET @name = N'objects';

SET @sql = N'SELECT * FROM sys.all_objects WHERE name = ''' + CAST(@name as nvarchar) + '''';

 

EXEC (@sql);

GO

 

-- Version utilisant des paramètres explicites

DECLARE @name sysname;

DECLARE @sql nvarchar(max);

 

SET @name = N'objects';

SET @sql = N'SELECT * FROM sys.all_objects WHERE name = @name';

 

EXEC sp_executesql @sql, N'@name nvarchar(128)', @name;

GO

Une autre méthode serait de laisser le soin de trouver les paramètres à SQL Server, ce qui est possible depuis SQL Server 2005 en activant l'option suivante sur une base de données particulière :

USE [master]

GO

ALTER DATABASE [xyz] SET PARAMETERIZATION FORCED WITH NO_WAIT

GO

 

Attention toutefois à cette option, car forcer cette recherche de paramètres peut avoir des conséquences négatives sur certaines requêtes. Le moteur se comportera un peut comme si vous aviez uniquement des procédures stockées, ce qui n'est pas bénéfique dans tous les cas (exemple ici : http://blogs.codes-sources.com/christian/archive/2008/04/25/sql-server-pourquoi-toto-is-null-or-champ-toto-pose-probl-me-ou-l-int-r-t-du-sql-dynamique.aspx).

Pour l'aspect sécurité on aura recourt à des astuces ou des fonctions fort bien documenter pour vérifier le code :

-- supprimez la ligne avec le OBJECT_NAME et vous verrez :o(

DECLARE @name sysname;

DECLARE @sql nvarchar(max);

 

SET @name = 'sys.tables;DROP TABLE xyz;'

SET @name = OBJECT_NAME(OBJECT_ID(@name))

SET @sql = N'SELECT * FROM ''' + CAST(@name as nvarchar) + '''';

 

EXEC (@sql);

GO

 

-- Un cas plus générique avec QUOTENAME

DECLARE @name sysname;

DECLARE @sql nvarchar(max);

 

SET @name = 'sys.tables;DROP TABLE xyz;'

SET @name = QUOTENAME(@name)

SET @sql = N'SELECT * FROM ''' + CAST(@name as nvarchar) + '''';

 

EXEC (@sql);

GO

 

Et bien entendue, veillez à ce que l'application n'est pas tous les droits pour que vous ne tombiez pas dans une portions de code non protéger exécutant un bout de code via un login qui aura tout pouvoir en tant que sysadmin sur le serveur.

Maintenant les avantages

Oui, il y en a, contrairement à ce que pensent les détracteurs de cette méthode, c'est même le choix idéal de requêtes de types : Je veux trier par n'importe quelle colonne ou combinaison de colonne, je ne maitrise pas mes critères de filtres (ne connait le nombre qui me sera passé, peuvent être nul, etc.)

Prenons le premier cas, comment écrivez-vous une procédure stockée permettant de réaliser un tri sur n'importe quelle colonne d'une table, tout en permettant l'utilisation de l'index de cette colonne s'il existe ?

CREATE PROCEDURE MaProc @sort int = 0

AS

 

    IF @sort = 0

        SELECT id, name FROM dbo.MaTable ORDER BY id;

    IF @sort = 1

        SELECT id, name FROM dbo.MaTable ORDER BY name;

    IF @sort = 2

        SELECT id, name FROM dbo.MaTable ORDER BY price;

    

GO

Cette méthode est la seule réellement propre en ce qui concernant l'utilisation des plans et des index. Maintenant si je vous dis que j'ai 50 colonnes dans ma table et que mon utilisateur peut combiner celles-ci librement… Le nombre de critères de tri maximum avoisinera approximativement le nombre possible de tirage du Loto ce qui ne permettra pas d'écrire la requête sous la forme présente juste au dessus.

La réponse est alors le SQL dynamique, on passera un argument de type chaîne concaténé et séparée par des virgule, xml (sous SQL Server 2005) ou de type table (sous SQL Server 2008) en paramètre. On prendra soins de bien vérifier que ce paramètres contient bien une liste de colonne valide et au besoin les mots clefs ASC et DESC et rien de plus. La chaîne de requête est alors générée et exécutée.

CREATE PROCEDURE MaProc @sort nvarchar(2000)

AS

 

    -- On vérifie ici la validité du paramètre sort (exemple)

    IF @sort LIKE '%DROP%' OR @sort LIKE '%ALTER%' OR @sort LIKE '%CREATE%'

        RETURN -1

    -- Fin de vérification

 

    DECLARE @sql nvarchar(max);

    SET @sql = N'SELECT id, name FROM dbo.MaTable ORDER BY ';

    SET @sql = @sql + @sort;

 

    EXEC(@sql)

    

GO

 

Autre exemple vous pouvez réaliser une boucle qui ira vérifier l'existence du nom de la/des colonne(s). Ici la compilation est anecdotique en rapport à la flexibilité procurée par le SQL dynamique. Il reste possible d'opter pour une paramétrisation forcé comme indiqué plus haut.

La réponse au débat, est qu'il faut savoir quand et comment l'utiliser et tout est affaire de bon sens.

Bon SQL…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié lundi 13 juillet 2009 14:00 par christian
Classé sous : ,

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01