How to parse or generate dynamic SQL from react querybuilder output json

Question

for one of my project we are using react-querybuilder lib and the end result will be sent to server to filter data accordingly, my backend use Sequelize, Express with JavaScript.

My concern is how to make sql queries or Sequelize command from the output json of react querybuilder

The output json is something like below-

{
  "combinator": "and",
  "rules": [
    {
      "field": "first_name",
      "operator": "beginsWith",
      "value": "Stev",
    },
    {
      "field": "last_name",
      "operator": "in",
      "value": "Vai, Vaughan",
    },
  ],
}

I could use the formatQuery to make sql from it but will that raise any security concerns or is there nay other proper approach to implement this on backend?

Answer 1

formatQuery can produce SQL WHERE clauses with inline values, but also with parameterized values that can be used as bind variables by a database client. The parameterized format can greatly reduce (but not eliminate!) the risk of SQL injection attacks.

If you were running straight SQL, I would recommend using the parameterized or parameterized_named formats, but with Sequelize I don't know if formatQuery will actually help. You might need to create your own transformer to convert from RQB to Sequelize.

Also (I hope this is appropriate here), I created a training course for react-querybuilder that covers both server- and client-based SQL generation. It's called Building Advanced Admin Reporting in React.