1

The c# application I'm working with executes many T-SQL SELECT queries that are stored as strings in a database.

These queries almost all involve selecting data from and joining with several tables from within the database.

Certain combinations should not be permitted, e.g. column 1 from table X should not be returned if table Y is joined on table X. A simpler example might be "Never include data from table Z"

There are many such rules and currently it is down to the author of the SQL to check that the data returned does not violate any of these rules.

I cannot edit the database in any way.

What options are there for me to determine whether a specific query violates one of these rules?

Is it possible to determine what tables/columns are accessed in a query?

I would need the solution to be reliable, I have considered parsing the SQL e.g. by using a Regex but I think this falls somewhat short of the "reliable" requirement.

9
  • I cannot edit the database in any way. that's really unfortunate, the database is where you'd typically implement permissions like this. I'm very interested in your requirements, sounds like a weird solution. Out of curiosity, what is it all about?
    – JᴀʏMᴇᴇ
    Commented Mar 6, 2018 at 15:06
  • 1
    Maybe this helps: stackoverflow.com/questions/589096/parsing-sql-code-in-c-sharp
    – πάντα ῥεῖ
    Commented Mar 6, 2018 at 15:07
  • 1
    Certain combinations should not be permitted, e.g. column 1 from table X should not be returned if table Y is joined on table X. A simpler example might be "Never include data from table Z" -- Why aren't these combinations coded into the database as constraints and triggers? Or, a better question, why are these combinations not allowed?
    – Robert Harvey
    Commented Mar 6, 2018 at 15:49
  • 5
    Rather then let the user type up the SQL string why not offer them a query builder that knows these rules?
    – candied_orange
    Commented Mar 6, 2018 at 18:15
  • 1
    I wonder if you can get the explain plan of the TSQL before its execution. Does SQL server allow you get the Explain Plan of T-SQL?
    – Laiv
    Commented Mar 7, 2018 at 7:56

1 Answer 1

Reset to default
1

If you need to parse T-SQL specifically, you may want to consider using Microsoft's SMO (SQL Server Management Objects) library: SQL Server Management Objects

Relevant namespace: Microsoft.SqlServer.Management.SqlParser.Parser

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.