0

We are building a reporting framework into our application, which necessitates the use of a query builder. Ultimately, we want power users to be able to build SELECT queries to be used to populate the report dataset.

Datasets are built using a DataAdapter (either MSSQL or SQLite). Are there any tools we can use to ensure that the queries built by the end user can only be SELECT statements?

EDIT:

As mentioned above, we target SQLite as one of our supported backends No DB permissions can be set on this platform.

pixelbadger
  • 1,556
  • 9
  • 24

4 Answers4

3

Set right permissions to DB. It's the best solution.

EDIT:

For SQLLite you can set read only permissions for file - in the file system.

VikciaR
  • 3,324
  • 20
  • 32
2

Give the user that you execute the SQL as only the db_datareader permission to ensure that they cannot do anything but read the data.

This question gives more info on how to do that: How to give a user only select permission on a database

Community
  • 1
  • 1
Fiona - myaccessible.website
  • 14,481
  • 16
  • 82
  • 117
1

If the query builder is done in house, and if your query builder returns a the SQL statement in a string, you can parse it either looking for Update statements keyworks or with Regex, if you want to spare the users the trouble of creating an update query then realizing that they can't run it, then you should consider doing this check continiously as they create the query. Alternatively, you can use a third party query builder, like this one: http://www.activequerybuilder.com/, unfortunately i belive it doesn't support anything else but Select statements but it may be worth the shot.

Miguel Garcia
  • 806
  • 5
  • 9
0

I think all you have to do is wrap the QueryBuilder and expose only permited operations. I is not good to do thinks the other way around, like letting the user construct a query and at the end you tell him it is not permissable.

Swift
  • 1,861
  • 14
  • 17