This is my first story in the medium. I have read lots of articles here, but have not written one(ps: shame on me). I will start writing and sharing my story here.
Why the SQL parser
I write lots of APIs every day, and each of them has a similar flow：
- connector the store: such as MySQL, PG
- query data from the store
- apply business logic to the queried data
It’s simple when we do not take speed or other performance issues into consideration. But one day, our client give out a demand that every SQL query required corresponding authority.
For Example: when we set the user only has read the table or column data authority, it means the user could send select SQL to our server. The server should deny or throw an error when a user does something that more than its authority.
When we got this demand, we know a SQL parser is required to accomplish this demand.
How to design SQL parser
We developed a simple SQL parser to achieve our goal！Nodejs is the main language that I used to develop, so we choose the peg.js:
PEG.js is a simple parser generator for JavaScript that produces fast parsers with excellent error reporting. You can use it to process complex data or computer languages and build transformers, interpreters, compilers and other tools easily.
We separate the authority into three parts: take the table authority as an example, the column authority is similar
- SQL type: it could be select, update, insert or delete
- database name: it could be null or string indicate the database name the SQL visited
- table name: the table name that the SQL visited
A valid authority row format is {type}::{database name}::{table name},
we set a user has read all tables on a database named taozhi, the authority is select::taozhi::(.*) : means user can read[select] all tables[(.*): regex match] in database[taozhi]
- SQL parser parse the SQL get the corresponding authorities
- get the user authority
- check the user authority contains the SQL authorities
Using the SQL parser
You could read more for usage in my GitHub repository. Star it if you like it.
Thanks for reading
Thanks for taking the time to read my article and I hope you enjoyed it! I will continue writing and sharing my store here.
