Post processing a sql string
-
We have a section of our application that deals with reporting, using MS reporting services. We also have a custom data extension that performs some post processing on the datasets returned (basically stripping out records that the runtime user would not be authorised to see). The current method we are using is to run the sql, generate a dataset, check to see if it contains sensitive data (looking at column names) and then loop through the rows, removing those that are not required. Obviously, this is a lot slower than it would be to just alter the sql to remove these rows using criteria in the where clause, but we can't see a way to robustly parse the sql to establish A) That the query is actually accessing sensitive data. B) Where we can insert our chunk of sql into the where clause. For simple reports, this is relatively easy, but as some of the reports are considerably more complex and the sql will contain calls to functions and in some cases creating temp tables, it rapidly becomes more complex. So, the question - does anyone know of a robust method of taking a string of arbitrary sql and parsing to allow us to alter the where clause of whichever part of it returns data?
-
We have a section of our application that deals with reporting, using MS reporting services. We also have a custom data extension that performs some post processing on the datasets returned (basically stripping out records that the runtime user would not be authorised to see). The current method we are using is to run the sql, generate a dataset, check to see if it contains sensitive data (looking at column names) and then loop through the rows, removing those that are not required. Obviously, this is a lot slower than it would be to just alter the sql to remove these rows using criteria in the where clause, but we can't see a way to robustly parse the sql to establish A) That the query is actually accessing sensitive data. B) Where we can insert our chunk of sql into the where clause. For simple reports, this is relatively easy, but as some of the reports are considerably more complex and the sql will contain calls to functions and in some cases creating temp tables, it rapidly becomes more complex. So, the question - does anyone know of a robust method of taking a string of arbitrary sql and parsing to allow us to alter the where clause of whichever part of it returns data?
I don't have a real solution for your problem but a simple idea. After reading your message I assume that you know the structure of the result (schema). Therefore it should be possible to wrap your query: select from where This way you can get rid of the unwanted data on the db server.
-^-^-^-^-^- no risk no funk
-
I don't have a real solution for your problem but a simple idea. After reading your message I assume that you know the structure of the result (schema). Therefore it should be possible to wrap your query: select from where This way you can get rid of the unwanted data on the db server.
-^-^-^-^-^- no risk no funk
No, and this is really the problem, the sql that we are passed could contain largely anything.