SQL Server vu par Christian Robert

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…