Discussion :

[StringBuilder]

Si, il s'agit bien de SQL dynamique.

Mais quel est l'intérêt ?

Les noms des tables peuvent-ils évoluer dans le temps, de façon dynamique ? (c'est à dire sans action au niveau du programme).

Vous feriez mieux :
- D'inclure ce code de "propre" dans vos procédures de modifications de lignes
- Ou mieux, de gérer ce code "proprement" sur des triggers directement au niveau des tables concernées.

Sinon, vous devrez mettre à jour votre procédure pour effectuer un contrôle d'injection au niveau du nom de la table.
Mais honnêtement, je trouve ça très sale de vouloir refactoriser au prix de SQL Dynamique.

Pour moi, le SQL Dynamique est utile dans deux cas :
- Scripts de maintenance (régénération d'index par exemple)
- Logiciel qui manipule directement la structure de la base (création/modification/suppression de tables), avec tous les problèmes potentiels de performances et de maintenance qui vont avec.

Pour les autres cas, pour moi le SQL Dynamique est à bannir, car non seulement il y a un risque réel d'injection, mais aussi et surtout, il y a des risques réels :
- de plantage (que se passe-t-il si une table ne contient pas le flag, ou orthographié différemment ?)
- de performances (les requêtes dynamiques sont rarement paramétrée, et peuvent donc s'avérer catastrophiques en termes de performances, et altérer les performances des autres requêtes, en bouffant inutilement du cache de plan d'exécution)
- la maintenabilité est complexe, car ces requêtes ne sont pas détectées comme dépendantes des objets qu'elles références. Ainsi, en cas de maintenance (changement de schéma, suppression/modification/création de tables) on peut parfaitement oublier de mettre à jour des requêtes, et ne s'en rendre compte qu'une fois en production, quand c'est planté.

[SQLpro]

C'est pas aussi simple. Lorsque l'on a des IHM de recherche complexe, il n'est pas possible de prévoir tous les cas à l'avance et même si c'était le cas cela serait épouvantable pour les performances.

un exemple, avec 10 colonnes dans une table si on laisse les utilisateurs faire du ET/OU avec du NOT du IN et du LIKE sur chaque membre de prédicat, dites moi comment faire sans du SQL dynamique ?

A +

[StringBuilder]

La chaîne de caractère envoyée par le programme .NET est "forcément" dynamique (puisque par essence, c'est une requête qui arrive "telle qu'elle" depuis le réseau, sans aucune certitude que c'est bien le bon programme qui l'a envoyée).

En revanche, on peut parfaitement générer dynamiquement une requête SQL depuis un programme tout en lui passant des paramètres.

Et c'est sur ce point que j'insiste. Faire des requêtes qui utilisent des noms d'objets (tables, colonnes, etc.) différentes en fonction de l'âge du capitaine, moi ça me choque.

Genre ça, ça me choque.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
exec 'SELECT ' + @mescolonnes + ' FROM ' + @matable + ' WHERE ' + @macondition;

En revanche, que le programme .NET génère une requête du type, ça ne me choque pas :

Code csharp :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Stack<Condition> conditions = new Stack<Condition>();
condition.Push(new Condition("nom", "=", "Georges");
condition.Push(new Condition("age", ">", 18);
 
SqlCommand cmd = cnx.CreateCommand();
 
StringBuilder sb = new StringBuilder("select id, nom from ma_vue where 1 = 1");
while (Condition cond = conditions.Pop())
{
    sb.AppendFormat(" and {0} {1} @{0}", cond.fieldname, cond.operator);
    cmd.Parameters.AddWithValue(cond.fieldname, cond.value);
}
cmd.CommandText = sb.ToString();
DataReader dr = cmd.OpenReader();

(code écrit à la va vite, juste pour illustrer, il est crade et ne compile certainement pas, et on peut parfaitement imaginer des imbrications afin de gérer les AND/OR et autres joyeusetés)

On va lire sur une vue, donc avec des actions limitées, pas d'accès direct aux tables.

L'avantage, c'est que s'il on relance la même requête, avec cette fois un filtre non plus sur "Georges", mais "Nicolas", on garde le même plan, on n'en regénère pas un nouveau.

Ensuite, si on veut absolument passer par une PS, pas de problème. Mais je ne vois pas l'intérêt.

Une fois l'application authentifiée, on doit pouvoir lui faire confiance, sinon on n'expose pas directement la base, on passe par l'intermédiaire d'un WebService par exemple (auquel on peut faire confiance).

Mais forcer 100% des requêtes, notamment de sélection/recherche à passer par du SQL Dynamique via une procédure stockée, j'avoue que je n'en vois pas l'intérêt, si ce n'est provoquer de mauvaises performances.

Et d'ailleurs, rien n'empêche de recevoir ceci comme commande :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sp_faitmontruc 'toto';drop database mabase;

En lieu et place de :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sp_faitmontruc 'toto', ';', 'drop', 'database', 'mabase':

Il suffit que le développeur de l'application doit étourdi ou qu'un petit malin récupère les informations de connexion de l'application pour se connecter lui-même (généralement, on trouve ces informations dans le fichier *.ini ou *.config situé dans le répertoire d'installation de l'application...

La seule chose dans laquelle on ne peux pas avoir confiance (et ou on peut réellement faire quelque chose), c'est dans les valeurs littérales, puisque aussi bien soit le programme .NET, le jour on SQL Server 2014 est remplacé par SQL Server X, on ne peut en aucun cas être sûr que les caractères d'échappement, mots réservés et autres seront toujours les mêmes avec certitude.

Il faut alors impérativement passer par des paramètres. Ce qu'on peut parfaitement faire avec .NET (cf. mon exemple ci-dessus).

[JeanYvette]

En un sens c'est pas forcement la chose la plus belle de l'univers, je le conçois. Mais c'est ce qu'il m'a été demandé sur le principe. ensuite l'intérêt de faire cela est la généralité. en effet cette fonction peut gérer les différentes tables de la bdd, et donc ne pas avoir à dupliquer ces fonctions pour chacune des tables (ce que je trouverais limite moins beau).
Ensuite elle peut être appelé du coup, et c'est le but, dans une procédure plus grande, qui aura comme paramètre une date. Qui ira voir toutes les requetes CRUD (sans select bien sur) effectués après cette date paramétre et enregistré dans notre table historique. Et comme cela récupérer le nom de la table (@table) ainsi que la clé primaire de cette table ! D'où la partie dynamique que je trouvais beaucoup mieux
Après j'ai percuté une chose, la fonction que je vous ai copié, ne sera jamais appelée directement via le site, elle sera appelée que par la plus grand procédure (qui ne prends donc qu'une date en paramètre). De plus, son utilité est très limitée, elle ne servira même surement jamais (enfin j'espère en un sens)

[SQLpro]

La chaîne de caractère envoyée par le programme .NET est "forcément" dynamique (puisque par essence, c'est une requête qui arrive "telle qu'elle" depuis le réseau, sans aucune certitude que c'est bien le bon programme qui l'a envoyée).

En revanche, on peut parfaitement générer dynamiquement une requête SQL depuis un programme tout en lui passant des paramètres.

NON !

Comment fais tu par exemple pour préciser dans une recherche la collation ?
Comment fais tu pour que l'utilisateur puisse faire du DISTINCT ou non ?
Comment fais tu pour rajouter un ESCAPE dans le like si l'utilisateur veut rechercher un joker ?

A +

[StringBuilder]

Perso, je préfère gérer ces éléments dynamiques côté .NET que côté SQL.

Sinon, la procédure SQL qui se charge de tout ça risque de rapidement devenir une usine à gaz.

[elsuket]

Comment fais tu par exemple pour préciser dans une recherche la collation ?
Comment fais tu pour que l'utilisateur puisse faire du DISTINCT ou non ?
Comment fais tu pour rajouter un ESCAPE dans le like si l'utilisateur veut rechercher un joker ?

On peut tout à fait récupérer cela dans l'IHM et générer la chaîne de requêtes dans le code avant de la soumettre à SQL Server.
Cela permet d'effectuer les contrôles nécessaires dans le code, puisque dans ce cas on ne fait pas appel à une procédure stockée.

Pour ce scénario, placer tout le code dans une procédure stockée ne me semble pas non plus outrageux, simplement le contrôle des caractères "non-recevables" et de l'absence de mots-clé (ordres DDL et DML) me semble un peu lourd, même si une petite série de IF fait tout à fait l'affaire, puisqu'on sait que les moteurs de bases de données relationnelles ne sont pas optimisés pour la manipulation de chaînes de caractères.

@++