ADHOC QUERY为客户 [英] ADHOC QUERY for client

问题描述

我有一个新项目，我们必须为客户提供执行特殊查询的功能，他们可以分组或过滤等。



我是新来的，我很难发现我们怎么做？



1）可以吗？从UI完成

2）或者必须给他们一个数据库？



任何人都可以指导如何进行分析。



我尝试过：



试图找到各种BI工具这将是有用的。

Hi,

I have a new project wherein we have to provide client the feature to perform adhoc queries where they can group by or filter etc.

I am new to this and I am finding hard how can we do this?

1) Can it be done from UI
2) or a database has to be given to them ?

Can anyone guide how to proceed with analysis for this.

What I have tried:

Trying to find various BI tools which will serve the purpose.

推荐答案

快速回答：

1）是

2）否

首先，根据用户的角色和访问权限确定允许用户进行哪种查询。

其次，在服务器端，编写SQL脚本每个查询都要牢记 SQL注入 [ ^ ]

第三，在客户端，列出登录用户的查询类型允许根据其角色和访问权限确定UI控件，以允许他输入查询所需的参数。例如，如果选项已修复，请使用单选按钮，复选框或下拉列表进行选择，否则使用文本框。尽可能限制用户使用自由文本。

最后，用户将他的选择查询和所需参数提交给服务器端脚本，其中包括清理并在将参数注入正确的SQL脚本以进行进一步处理之前验证这些参数。

The quick answers to:
1) Yes
2) No
First, determine what kind of queries the users are allowed to make, based on their roles and access rights.
Second, on server-side, compose the SQL script for each query bearing in mind SQL Injection[^]
Third, on the client-side, list out the type of queries that a logged in user is allowed to make, based on his role and access right, determine the UI controls to allow him to enter the necessary parameters for queries. For example, if the options are fixed, use radio buttons, check boxes, or drop down for selection, else text boxes. As far as possible, limit the use of free text by the user.
Lastly, the user will submit his choice of query and the required parameters to the server-side script which, among other things, cleanse and validate the parameters before injecting them into the correct SQL script for further processing.

我之前曾在一个有类似要求的环境中工作过。

我们有一个隔夜的工作创建了一个单独的只读数据库，可以被用户/其他客户查询。



我们还有一个可以运行预定义查询的UI实时数据库或复制数据库取决于需要...例如今天剩下什么工作与昨天完成了什么工作



我们的方法是坚持要求他们（IT部门）发送他们的要求，我们为他们建立报告......他们可以实时运行（两种类型的输出...... excel或c sv）或者它是在一夜之间运行的。



我们确实开发了一种我想要这个查询系统，它的行为非常有限（避免例如SQL注入。



我们确定与客户端的适当对话最终可能会针对实时数据库或MI数据库进行*受控*查询（每天晚上复制纯粹为了用户查询而不用F *在现场系统上的表现）。我们让程序运行读取文本（xml）文件，以确定要运行的查询以及存储结果的位置。



我不得不说，这是一个非常成功的系统，只需要积极管理。



生活就是这样，他们还发现了第三方工具，允许他们建立自己的临时查询......他们（商业用户）认为这很棒。我们（IT）知道它不是。花费公司很多（如数百万）的钱



TL; DR; ....控制像临床瘟疫一样的临时查询。让用户思考他们正在做特别的事情，把它紧紧地绑在一起他们不能放屁。



为咆哮道歉......这就是为什么我在30岁时变灰的一个原因

I have previously worked in an environment that had a similar requirement.
We had an overnight job that created a separate read-only database that could be queried by users/other clients.

We also had a UI that could run pre-defined queries against either the live database OR the "copy database" depending upon need ... for example "What work is left outstanding today" versus "What work was completed yesterday"

Our approach was to insist that they sent us (the IT dept) their requirements and we built the reports for them ... they could either be run in real time (two types of outputs ... excel or csv) or it was run for them overnight.

We did actually develop a sort of "I want this query" system that was very VERY limited in it's behaviours (avoid SQL Injection for example).

We did determine that appropriate conversation with the "client" could end up with *controlled* queries against either the live database or the MI database (copy taken each night purely for the users to query without F*ing up performance on the live system). We had programs running that read text (xml) files to determine what query to run and where to store the results.

I have to say, it was a very successful system, it just needed to be actively managed.

Life being what it is, they also discovered a 3rd party tool that would allow them to build their own ad-hoc queries... they (the business users) thought it was great. We (IT) knew it was s**t. Cost the company lots (like millions) of money

TL;DR; .... control "ad-hoc" queries like the bubonic plague. Let users "think" they're doing ad-hoc, tie it down so tight they cannot fart.

Apologies for the ranting ... this is the one single reason why I went grey in my 30's

使用报告服务为此

这篇关于ADHOC QUERY为客户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！