5 ms·
When I worked on a similar functionality, I used sql parser and AST walker to verify that no mutations are done and only a subset of sql was whitelisted. That w
by zihotki 1y ago
When I worked on a similar functionality, I used sql parser and AST walker to verify that no mutations are done and only a subset of sql was whitelisted. That worked as a charm and it wasn't hard to implement.
Benefits - no sql injections, cheap syntax check.
- supriyo-biswas 1y agoA similar approach that I used for an application is to take a simplified query language as input such as `name*~"john" emp_id>3000` and use a hand-crafted parser to turn it into a SQL query.
- tootubular 1y agoI just shipped a feature exactly like this... Jira has the same thing with JQL, which is what inspired my work. Safe from SQL injection and can be used directly by power users or managed through form inputs for basic search/filtering. We use Elasticsearch for other data atm, but I'm hopeful this new PostgreSQL only approach wins out as it makes authz so much simpler since it all composes into one query.
- emmelaich 1y agoThat exists as @apenwarr's afterquery: https://apenwarr.ca/log/20121218 https://apenwarr.ca/log/20121218 As it is the URL string, you can share it easily.