We have an app that is used for data mining on our client database.
Typical uses include getting a list of clients and their email addresses, running reports about user transactions between certain dates and returning clients that live in a specific area.
High level functional requirements are:
- Each data mining query needs to be able to exported to excel.
- Parameters need to be supported (so that we can filter results etc).
- Other apps would need to be able to access these queries as well as their results.
It has been implemented as follows:
- A database function is created for each SQL query.
- There is also a table that contains details of each function as well as required parameters as their types.
- There is a web front end where the SQL queries are maintained, and can be run and exported.
- A web service exists that other apps can use to run queries. The method to run a query returns a dataset and gets passes parameters and their values in XML.
Our current solution works but there are some problems with maintaining the hundreds of DB functions (would it be necessary to use functions - couldn't we just store SQL in tables?). I also have some security concerns around SQL injection as our front end basically allows the users to input any SQL.
Has anyone had to develop something similar and what approach did you use? What was your reasoning for using that approach?