Veillez à ce que les requêtes soient uniquement "raisonnables".

Question

Dans notre organisation, nous avons besoin de laisser les employés filtrer les données dans notre application web en fournissant des clauses WHERE. Ce système fonctionne parfaitement depuis longtemps, mais il arrive que des utilisateurs fournissent des requêtes qui nécessitent des analyses complètes de grandes tables ou des jointures inefficaces, etc.

Un clown pourrait écrire quelque chose comme :

select * from big_table where
Name in (select name from some_table where name like '%search everything%')
or name in ('a', 'b', 'c')
or price < 20
or price > 40
or exists (select 1 from some_other_table where col1 + col2 + col3 = 4)
or exists (select 1 from table_a, table+b)

Évidemment, ce n'est pas une bonne façon d'interroger ces tables avec des valeurs calculées, des colonnes non indexées, beaucoup de OU et une jointure sans restriction sur table_a et table_b.

Mais pour un utilisateur, cela peut être tout à fait logique.

Quel est donc le meilleur moyen, le cas échéant, de permettre aux utilisateurs internes de fournir une requête à la base de données tout en garantissant qu'elle ne verrouillera pas une douzaine de tables et ne bloquera pas le serveur web pendant 5 minutes ?

Je suppose qu'il s'agit d'un moyen programmatique en c#/sql-server pour obtenir le plan d'exécution d'une requête avant son exécution. Et si c'est le cas, quels facteurs contribuent au coût ? Coût estimé des E/S ? Coût estimé du CPU ? Quelles seraient les limites raisonnables pour dire à l'utilisateur que sa requête n'est pas bonne ?

EDIT : Nous sommes une société d'études de marché. Nous avons des milliers d'enquêtes, chacune avec ses propres données. Nous avons des dizaines de chercheurs qui veulent découper ces données de manière arbitraire. Nous disposons d'outils qui leur permettent de construire des filtres "valides" à l'aide d'une interface graphique, mais certains "utilisateurs expérimentés" veulent fournir leurs propres requêtes. Je me rends compte que ce n'est pas la norme ou la meilleure pratique, mais comment faire autrement pour permettre à des dizaines d'utilisateurs d'interroger des tables pour obtenir les lignes qu'ils veulent en utilisant des conditions arbitrairement complexes et des conditions qui changent constamment ?

Answer 1

La prémisse de votre question est la suivante :

Dans notre organisation, nous avons besoin de laisser les employés filtrer les dates dans notre application web en fournissant des clauses WHERE.

Je trouve que cette prémisse est défectueuse à première vue. Je ne peux pas imaginer une situation où j'autoriserais les utilisateurs à faire cela. En plus des problèmes que vous avez déjà identifiés, vous vous exposez à des attaques par injection SQL.

Je vous recommande vivement de réévaluer vos besoins pour voir si vous ne pouvez pas élaborer un moyen plus sûr et plus ciblé de permettre à vos utilisateurs d'effectuer des recherches.

Toutefois, si vos utilisateurs sont suffisamment sophistiqués (et dignes de confiance !) pour fournir directement des clauses WHERE, ils doivent être informés de ce qu'ils peuvent et ne peuvent pas soumettre comme filtre.

Answer 2

Vous pouvez essayer d'utiliser ce qui suit :

SET SHOWPLAN_ALL ON
GO
SET FMTONLY ON
GO
<<< Your SQL code here >>>
GO
SET FMTONLY OFF
GO
SET SHOWPLAN_ALL OFF
GO

Ensuite, vous pouvez analyser ce que vous avez. Quant à savoir où tracer la ligne sur diverses choses, cela va demander une certaine expérience. Il y a des choses à surveiller, mais rien de bien précis. L'examen des plans de requête est souvent plus un art qu'une science.

Comme d'autres l'ont souligné, je pense que votre problème est plus profond que les implications technologiques. Le fait que vous laissiez des personnes non qualifiées accéder à votre base de données de cette manière est le problème sous-jacent. Par expérience, je constate souvent que les entreprises sont trop paresseuses ou trop inexpérimentées pour saisir correctement les exigences de leur application. Je ne dis pas que c'est nécessairement le cas dans votre environnement d'entreprise, mais c'est ce que j'ai vu.

Answer 3

En plus d'essayer de contrôler ce que les utilisateurs saisissent (ce qui est une bataille perdue d'avance, il y aura toujours un nouvel employé qui proposera une requête immaginative), je me pencherais sur le Resource Governor, voir Gestion des charges de travail SQL Server avec Resource Governor . Vous placez les requêtes ad hoc dans un pool séparé et vous plafonnez les ressources allouées. De cette façon, vous pouvez atténuer le problème en limitant les dommages qu'une mauvaise requête peut causer à l'infrastructure du réseau. autre tâches.

Vous devez également envisager de donner accès aux données par d'autres moyens, tels que Pivot de puissance et laisser les utilisateurs masser leurs données aussi fort qu'ils le souhaitent sur leur propre Excel. Les utilisateurs professionnels adorent cela, et l'impact sur le serveur de traitement des transactions est minime.

Answer 4

Au lieu d'autoriser les employés à écrire directement (ajouter à) des requêtes, puis d'essayer de calculer le coût de la requête avant de l'exécuter, pourquoi ne pas créer une sorte de fonction de recherche avancée ou de filtre qui n'écrit PAS de SQL que vous ne pouvez pas contrôler ?

Answer 5

Il s'agit d'une pratique courante dans les très grandes entreprises qui créent des applications internes. Souvent, au cours de la phase de conception, vous limitez les critères ou vous imposez des limites raisonnables aux plages de données, mais une fois que l'entreprise a mis la main sur l'application, la direction de l'unité opérationnelle demande que les restrictions soient supprimées. À mon avis, il s'agit d'un problème de gestion et non d'ingénierie.

Nous avons dressé un profil de tous les critères et trouvé les plus grands contrevenants, à la fois les utilisateurs et les types de requêtes qui causaient le plus de problèmes, et nous avons imposé des limites à certaines de ces requêtes. Nous avons également ajouté à l'application certaines requêtes très coûteuses qui étaient utilisées régulièrement. L'application mettait les résultats en cache et exécutait les requêtes lorsque la charge était faible. Nous avons également créé des requêtes optimisées pour les utilisateurs standard et donné aux seuls utilisateurs spécifiés la possibilité de rechercher n'importe quoi. Ce ne sont que quelques idées.