Use the parser to identify / prevent clauses

[vbackeberg]

Hi,
I am looking for a tool to authorize on the query level.
I have tested the parser on a few bigger queries and I am wondering if I could use it for reliably identifying statements and clauses for whitelisting/blacklisting.

Examples:

• Prevent all statements except select.
• Prevent all from clauses with table/identifier named "users".

It looks like I can run down the tree and do some relatively simple filtering, but I am not sure if I am overlooking something.

[nene]

In general I would advise using the access rights of your database to manage these sorts of restrictions.

That being said, it's a question of doing static analysis on the syntax tree.

Prevent all statements except select.

This is pretty trivial to achieve.

Prevent all from clauses with table/identifier named "users".

This is much harder. Especially if you want to avoid false positives. For example:

WITH users AS (SELECT * FROM products)
SELECT * FROM users;

That query doesn't touch the users table, but when you only look at the FROM clause, it will look like it does. Similarly you need to account for aliases. Also you need to consider schemas, like do you want to restrict access to public.users or to auth.users. So there's quite a lot of things you need to track to figure out the actual tables being touched by a query.

I can say though that the output of sql-parser-cst is probably too low-level for this. You can't easily tell from the output of this parser whether an identifier refers to a column, table, function or something else. You really want a proper Abstract Syntax Tree to begin your analysis with, not the low-level Concrete Syntax Tree, which this parser produces. I started work on sql-parser-ast, but it's nowhere close to finished.

[vbackeberg]

Thanks for your in-depth reply!
Overall, I think parsing the tree seems a bit too complex for my use case where I have relatively static queries, although your AST project sounds promising for a future experiment.