0

All of my tenanted tables in sql server have a field customer_id. I'm attempting to write an interceptor for NPoco that will extract all table and alias names from the query. match it to an exception list of tables that are not tenanted, and modify the select to check the tenant key of all tenanted tables in the where clause.

I'm having a really hard time finding a sql parser that can extract table names and aliases from a sql statement.

A good solution would parse the following statement.

SELECT fis.OrderDateKey, SUM(fis.SalesAmount) AS TotalSales
FROM FactInternetSales fis
  Join product on fis.productid = product.productid
  where p.name like 'prefix%'
  GROUP BY fis.OrderDateKey
  HAVING fis.OrderDateKey > 20010000
  ORDER BY fis.OrderDateKey;

Would provide enough information for me to reliably construct a dictionary with 2 items {"FactInternetSales", "fis"} and {"product", "product"}

So far I have only found tokenizers that only know types that are too generic for me to rely upon. (ie keyword, identifier, operator, text). Is there anything out there that can do this sort of work in .net. Or are there better strategies to append these tenant checks to every query ran?

Matthew Vines
  • 27,253
  • 7
  • 76
  • 97
  • 1
    There was a similar question recently which I can't find at the moment. One suggestion was to create a `view` or `stored procedure` containing the query and use [`sys.sql_dependencies`](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-dependencies-transact-sql) on the new view/SP to get the referenced objects. It doesn't help with aliases, but may be a reliable means to get the referenced tables and views. Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. – HABO Aug 04 '17 at 19:15
  • Making a round trip to the database is less than ideal, but it may be useful as a post compile process to make sure dev's don't forget to check the tenant in every query. – Matthew Vines Aug 04 '17 at 21:40
  • 1
    [This](https://stackoverflow.com/questions/45551533/list-all-tables-used-in-a-sql-statement) question popped up recently. A suggested product (Gasp!) was [General SQL Parser](http://www.sqlparser.com/). – HABO Aug 07 '17 at 22:02
  • And another [question](https://stackoverflow.com/questions/45618020/error-on-parsing-t-sql-using-tsql120parser#45618020) that leads to [`TSql120Parser`](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.transactsql.scriptdom.tsql120parser.aspx?f=255&MSPPError=-2147217396). Looks promising, e.g. [`TableReferenceWithAlias`](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.transactsql.scriptdom.tablereferencewithalias.aspx). – HABO Aug 10 '17 at 15:52
  • @HABO Thanks for keeping up with this. See my answer below. I'm about 85% of the way there, and far enough to start using the solution. I just need to improve it to handle sub-queries. – Matthew Vines Aug 10 '17 at 16:50

2 Answers2

1

This answer was useful in finding a solution to my problem.

If anyone is interested, this is my first pass. We actually use systemId as our tenant, so you will see that in the code. So far, the only queries I've found that it doesn't handle are those with subqueries. I'm digging into that problem now, but I may not add it in, as the vast majority of what my team needs is handled here.

Matthew Vines
  • 27,253
  • 7
  • 76
  • 97
0

Sorry, but I have some doubts about.

  1. You have all the tables in the server, right? in sys.tables you have all the table names. in sys.columns are the name of columns of that tables, caution with the schema. In sys.columns also have user_types... You could Match this tables with the select for find tables and columns. There are views system tables too, and of stored procedures, even the content...
  2. If the tables doesn't exists, the selects doesn't work, so I don't understand the question.
  3. If you use sql server profile (if you can), it could catch more information about any query to the database.
  4. In StackOverflow you have to write some of the code or a link related with some work about you your question. howto.

Ask me any question, hope to help!! :)

Javier Jimenez Matilla
  • 302
  • 1
  • 7
  • apologies, maybe I wasn't clear enough. I need to take the statement select * from tableA a where mycol = 'x' and convert it to select * from tableA a where (mycol = 'x') AND a.customer_id = 1234 on every query that is run. But statements obviously get much more complicated than this. So I am looking for a sql parser or something that can convert the sql to an expression tree, so I can reliably extract all the tables referenced by the statement, and append tenant checks to the where clause. – Matthew Vines Aug 04 '17 at 21:37
  • something like this? https://msdn.microsoft.com/en-us/library/aa479086.aspx – Javier Jimenez Matilla Aug 04 '17 at 22:03
  • That does list some strategies. I'm using #3, tenant column. And I would prefer not to do the filtering via sql view as described in that article. I'm trying to implement a similar strategy on the .net side, so that I don't have to roll out a new sql user every time I bring in a new customer. But that involves parsing some sql, and I just haven't found a good solution yet. – Matthew Vines Aug 07 '17 at 15:11